13

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?

3
  • 1
    Apparently can not be done. Reading postgresql.org/docs/9.6/static/… I deduce than if for example you use two schemas you cannot do it also. "Without this permission, it is still possible to see the object names, e.g. by querying the system tables" Commented Jun 26, 2017 at 14:58
  • Ideally, row-level security would apply to system catalogs, so you could set rules about who can see what. But so far that's not supported. Commented Jun 27, 2017 at 0:52
  • The simplest answer to this, given that you want to DENY a VIEW from your question, is to create your tables in one schema and views in another schema and GRANT USAGE to the VIEW schema and GRANT SELECT on the VIEWs. Commented May 6, 2022 at 22:15

1 Answer 1

11

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.

7
  • Probably we with Alice have different conceptions of "hard" :)
    – Abelisto
    Commented Jun 26, 2017 at 15:14
  • 2
    @Abelisto :D In my practice, even experienced devs are sometimes without ideas when it comes to databases. Now I added a stricter solution, which is not so easy to circumvent - unless a colleague next to Alice can list the objects. Also, don't hesitate to add the solution using an FDW. Commented Jun 26, 2017 at 15:22
  • 1
    ¡Warning! Is not hard to a user accidentaly view the pg_class data. For example if you connect the database with ODBC. @dezso If you are serious section's is mandatory! Commented Jun 26, 2017 at 15:27
  • 1
    Thanks for your response. I think I will use another database with FDW. PD: We really don't like Alice :D Commented Jun 26, 2017 at 15:50
  • 1
    alice can still list other schemas if using DBeaver or PgAdmin GUI.
    – Ecil
    Commented Apr 4, 2023 at 20:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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