user permissions - postgresql database owner can't access database - "No relations found."

Question

I've got an user: user_x that owns a database on postgresql and does not have any ROLE attribute like (CREATE_DB, SUPERUSER,...)

This user_x can access the whole DB, create tables (on his database), select, insert and update data.

I've got this list of databases:

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)

and the following roles:

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.

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."

So I added SUPERUSER role to user_x on the imported database server and tad? user_x can see the relations and data again.

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?

Answer 1

Perhaps the schema permissions for the public schema got mangled. What is the output of dn+ on both sites?

The output should look like this:

                          List of schemas
  Name  |  Owner   |  Access privileges   |      Description       
--------+----------+----------------------+------------------------
 public | postgres | postgres=UC/postgres | standard public schema
                   : =UC/postgres           
(1 row)

If the =UC/postgres part is missing, you can restore it with

grant all on schema public to public;