1

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.

  • 1
    Nit-pick (not much relevant to the issue): when you psql x you login and connect to the database called x, not to a table named x. – ypercubeᵀᴹ Apr 11 '17 at 14:10
  • 2
    pg_hba.conf is irrelevant to the question because it only controls if a user is allowed to connect at all. But it does not control what the user can see once connected. – a_horse_with_no_name Apr 11 '17 at 14:49
4

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.

|improve this answer|||||

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.