postgresqlschemapublicrights

Postgres user can't select from public tables


Hello I have one user in my postgresql db \dt output for the user:

 test=> \dt
                  List of relations
 Schema |           Name           | Type  |  Owner
--------+--------------------------+-------+----------
 public | code                     | table | postgres
 public | code_to_indicator        | table | postgres
 public | indicator                | table | postgres

As you can see owner of the tables is postgres But if run \dt command for postgres user it says:

postgres=# \dt
Did not find any relations.

How is it possible? Also if I will try to select by postgres user it also will fail:

postgres=# select * from indicator;
ERROR:  relation "indicator" does not exist
LINE 1: select * from indicator;

Can anyone help with it? Everything was fine till today, something went wrong I even don't know what. PostgreSQL version: 11.4


Solution

  • The postgres=# at the start of the psql prompt means you're connected to the postgres database, which is created as a placeholder for admin connections, and usually doesn't have anything in it. If you don't specify a database on the command line, psql looks for a database with the same name as the user, so the postgres user will connect to this by default.

    To connect to the test database instead, you can either type \c test into the psql prompt, or launch it using psql -d test.