Postgresql – How to prevent a login from ‘listing’ a table or view definition in PostgreSQL

authenticationauthorizationpermissionspostgresqlSecurity

Is there a way to prevent a login from listing the tables and columns in a schema?

I have to give access to a remote login to query on a single view; however, I also must make sure that such login cannot list every object on that schema.

Is there something like MS SQL SERVER's DENY VIEW ANY DEFINITION TO public; on PostgreSQL?

Best Answer

The documentation to the rescue:

UPDATE

[...]

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. Also, after revoking this permission, existing backends might have statements that have previously performed this lookup, so this is not a completely secure way to prevent object access.

This means, the following would satisfy your requirements, unless alice tries hard enough:

CREATE SCHEMA invisible; -- no privileges granted on this schema to alice or public

CREATE VIEW invisible.iamnothere AS SELECT 'something not very interesting'::text AS data;

GRANT SELECT ON TABLE invisible.iamnothere TO alice;

Now alice will see the following (using psql, and hopefully it is the same in other clients, too):

SELECT * FROM invisible.iamnothere;
              data              
────────────────────────────────
 something not very interesting

\dt invisible.*
No matching relations found.

If you are serious

...about preventing your user from seeing other objects, it will be slightly more complicated. You have to handle the notice in the docs about system tables access.

First, do

REVOKE ALL ON ALL TABLES IN SCHEMA  pg_catalog FROM public, alice;

This, as it suggests, can be disruptive to anyone who is able to browse the schemas through implicitly being a member of public (all roles are members of the latter). To restore the status quo for them (but not alice), you can do:

CREATE ROLE object_browser;

GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO object_browser;

GRANT object_browser TO bob;

After this, bob will be able to list stuff, while poor alice is confined to querying that single view.