Postgresql – Relation does not exist for some users

permissionspostgresql

As a user with admin privileges in postgres, I can psql database_name and then invoke \dt to see a list of relations. As the same (unix) user, I execute psql -h localhost -U non-privileged-user table_name, and \dt reports NO relations found. The only host entries in pg_hba.conf are of the form host analytics all 127.0.0.1/32 md5 (where 127.0.0.1 is the first entry).

Where are the relations? Why are they not visible to the alternate user. What schema tables should I look in to debug. Also, note that grant select on table_name to non-privileged-user works just fine.

Best Answer

What you see typically happens when the non-privileged user doesn't have schema level privileges. In this case, they cannot list the objects inside the given schema - with the command you tried. See the below example:

-- as a superuser, for example
CREATE DATABASE test;
\c test
CREATE TABLE public.blah (id integer);
REVOKE ALL ON SCHEMA public FROM PUBLIC;

CREATE USER u WITH PASSWORD 'u'; -- don't do this in production

-- connect as u
\c test u
\dt
No relations found.

-- but:
\dt public.*
       List of relations
 Schema │ Name │ Type  │ Owner  
────────┼──────┼───────┼────────
 public │ blah │ table │ dezso

See what the documentation says:

USAGE

For schemas, allows access to objects contained in the specified schema (assuming that the objects' own privilege requirements are also met). Essentially this allows the grantee to "look up" objects within the schema. Without this permission, it is still possible to see the object names, e.g. by querying the system tables. [...]

\dt does the latter, namely queries pg_class and pg_namespace to find matching objects. Apparently, when the schema name is not specified, the system wants to hide objects from view.

Related Question