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?
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;