Postgres user can't select from public tables

Question

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

Answer 1

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.

Answer 2

The ability to see tables in public or in any schema when the tablename is not qualified depends on the search_path setting.

If you haven't modified search_path in a permanent way, issue RESET search_path; in your session and try again.

How does search_path get modified if not directly by you? Playing an SQL file obtained by pg_dump has the effect of removing public from the search path, which is pretty much what this question seems to show.

If it's modified permanently through ALTER USER postgres SET search_path TO ... or per database or globally, then it needs to be undone by an equivalent ALTER command.

See How does the search_path influence identifier resolution and the "current schema" or PostgreSQL documentation for more.