我有一个用户:user_x,它在postgresql上拥有一个数据库,并且没有任何ROLE属性,例如(CREATE_DB,SUPERUSER,...)
I've got an user: user_x that owns a database on postgresql and does not have any ROLE attribute like (CREATE_DB, SUPERUSER,...)
这user_x可以访问整个数据库,(在他的数据库上)创建表,选择,插入和更新数据。
This user_x can access the whole DB, create tables (on his database), select, insert and update data.
我有以下数据库列表:
mydatabase=> \l List of databases Name | Owner | Encoding | Collation | Ctype | Access privileges -------------------------+----------+-----------+-----------+-------+----------------------- postgres | postgres | SQL_ASCII | C | C | mydatabase | user_x | UTF8 | C | C | template0 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | SQL_ASCII | C | C | =c/postgres + | | | | | postgres=CTc/postgres whoami | postgres | SQL_ASCII | C | C | (6 rows)以及以下角色:
mydatabase=> \du List of roles Role name | Attributes | Member of -----------+-----------------------------------+----------- postgres | Superuser, Create role, Create DB | {} user_x | | {} mydatabase=> \d List of relations Schema | Name | Type | Owner --------+-----------------------------------+----------+---------- public | addresses | table | user_x public | addresses_id_seq | sequence | user_x public | assignments | table | user_x public | assignments_id_seq | sequence | user_x ...好的,直到我转储数据并还原
All right, till I dump data and restore it on another postgresql server.
将数据导入另一台服务器(具有相同的数据库名称和用户)并在psql上登录 \d 命令回复:未找到任何关系。
After import the data with on another server (with same database name and user) and logged on psql the \d command reply with: "No relations found."
因此,我在上的user_x中添加了 SUPERUSER 角色导入的数据库服务器和tadãuser_x可以再次查看关系和数据。
So I added SUPERUSER role to user_x on the imported database server and tadã user_x can see the relations and data again.
但是user_x不需要具有SUPERUSER特权才能访问此数据库。
But user_x don't need to have SUPERUSER privilege to access this database.
此导入的内容有什么问题倾倒? 现在有人解决该问题吗?
What's wrong with this imported dump? Does anyone now how to solve this?
推荐答案也许对公用的架构权限模式被弄乱了。两个站点上 \dn + 的输出是什么?
Perhaps the schema permissions for the public schema got mangled. What is the output of \dn+ on both sites?
输出应如下所示:
List of schemas Name | Owner | Access privileges | Description --------+----------+----------------------+------------------------ public | postgres | postgres=UC/postgres | standard public schema : =UC/postgres (1 row)如果 = UC / postgres 部分丢失,可以使用
If the =UC/postgres part is missing, you can restore it with
grant all on schema public to public;更多推荐
postgresql数据库所有者无法访问数据库
发布评论