postgresql数据库所有者无法访问数据库-“未找到任何关系”。 [英] postgresql database owner can't access database - "No relations found."

问题描述

我有一个用户：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数据库所有者无法访问数据库-“未找到任何关系”。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！