CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-schemapgadmin
Krzysztof Majewski
Krzysztof Majewski

Reputation: 2534

Grant access to just one schema in postgreSQL

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.

Upvotes: 6

Views: 8051

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246268

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.

Upvotes: 2

Related Questions