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?

A.H. · Accepted Answer · 2011-10-13T18:45:55.243

78

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;

edited Oct 13 '11 at 18:45

answered Oct 13 '11 at 18:40

A.H.

63,967
15
92
126

Hi there A.H. ninja skills you must have. You were right! The Access privileges was null. – AndreDurao Oct 13 '11 at 19:25
7

Ahh many thanks for those 3 hours of my life that you just gave me back with this one! – cjauvin Jan 18 '14 at 15:57
What does the `c` in the permission represent, so I can add that by itself, instead of adding all permission I don't want to be added? – Oct 06 '16 at 16:25
@WorstForum: `c` is `CREATE`, `u` is `USAGE`. These are the only permissions supported by a schema. – A.H. Oct 06 '16 at 18:14
I had the same problem but with RDS... so no possibility to run grant all command. Any ideas? – gillien Apr 23 '18 at 09:58
6

Actually, giving `GRANT ALL ON SCHEMA public TO public` isn't such a good idea in terms of security, as it grants usage and create privileges on the public schema to all users. Maybe with `GRANT USAGE ON SCHEMA public TO user_x;` should be enough. I found a good detailed explanation of the "Access privileges" column here: https://severalnines.com/blog/postgresql-privileges-and-security-locking-down-public-schema – SebaGra Sep 06 '18 at 14:43

score 1 · Answer 2 · answered Feb 21 '22 at 09:39

Did not find any relations.

You will also get this if your database has no tables.

iripmu=# select * from testtable;
 id |    Name
----+------------
  1 | Bear
  2 | Tiger
(2 rows)

iripmu=# drop table if exists testtable;
DROP TABLE
iripmu=# \dt
Did not find any relations.

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

2 Answers2

Did not find any relations.

Linked

Related