Grant access to just one schema in postgreSQL

Question

I have a user just_one_schema_user.

In my database I have two schemas: public and sample

How can I make this user to see just the sample?

This is what I did:

GRANT USAGE ON SCHEMA sample TO just_one_schema_user

REVOKE ALL PRIVILEGES ON SCHEMA public FROM just_one_schema_user

But the user still can list the tables in public and see their structures.

• Islingre: I guess this is not possible. All structural information are stored in the schema pg_catalog and you cannot revoke the privileges on this schema, since the user would lose his ability to look up any objects from any schema.
• Laurenz Albe: @Islingre You are right about pg_catalog. However, the question is about public.
• Islingre: @LaurenzAlbe Yes it is. But as long as I have access to pg_catalog, I should be able to see the schema, should I not? I am not sure, but I would be surprised if revoking privileges on some other schema would affect queries to the catalog.
• Laurenz Albe: Oh, you are correct, sorry.

Answer 1

About access to the table metadata:

As Islingre commented, there is no good way to hide that information from users.

You would have to deny the user access to the pg_class, pg_namespace and pg_proc and similar tables. This can be done if you set allow_system_table_mods to on, and PostgreSQL will continue functioning, but a lot of things will no longer work:

• Using the psql utility commands like \d or \dt

• Similar tools for other tools

• Monitoring systems

Essentially, you won't be able to see any metadata any more.

There is no way to allow a user to see only some of the metadata, it is all or nothing.

But that is not a problem. There is no good reason to keep metadata from anybody - that is public information.

PostgreSQL doesn't consider that a security problem. Just because I know that there is a table customer with a column credit_card_number doesn't get me any closer to accessing it if permissions are set properly.

About access to the objects in public:

A REVOKE that removes a privilege that has never been granted will silently do nothing.

The USAGE privilege on schema public is granted to PUBLIC, not just_one_schema_user.

Use this to show the permissions in psql:

\dn+

You are looking for:

REVOKE CREATE, USAGE ON SCHEMA public FROM public;

I would recommend storing no application data in public, only extensions. Then don't revoke USAGE, only CREATE.