CODEWITHSUNDEEP
postgresqlpermissionsdatabase-schemasql-grantpostgresql-9.5
AndreKR
AndreKR

Reputation: 33678

Restrict user to one schema in PostgreSQL?

Is it possible in PostgreSQL to create a user that can only access a single schema?

Here is what I tried:

REVOKE ALL ON DATABASE testdb FROM public;
GRANT CONNECT ON DATABASE testdb TO testuser;

When I connect as testuser indeed I cannot access the actual data:

> SELECT * FROM some_table;
ERROR:  permission denied for relation some_table

However, I can still list all the tables, etc. in all the other schemas:

SELECT * FROM pg_tables;
     schemaname     |                 tablename                 | tableowner | tablespace | hasindexes | hasrules | hastriggers | rowsecurity
--------------------+-------------------------------------------+------------+------------+------------+----------+-------------+-------------
 test2              | foo                                       | postgres   |            | t          | f        | f           | f
 test2              | bar                                       | postgres   |            | t          | f        | f           | f
...

Upvotes: 3

Views: 13305

Answers (2)

Christopher Compeau
Christopher Compeau

Reputation: 420

I was able to do this like so:

GRANT USAGE ON SCHEMA schema_name TO user_name;
ALTER USER user_name SET search_path = schema_name;

The ALTER USER statement like this is a way of permanently setting the search path for schemas the way you would set the schema search path of an individual sessions with 

SET SEARCH_PATH= schema_name_1, schema_name_2;

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246268

It is impossible to configure PostgreSQL so that a user can only see those objects in the system catalogs for which he or she has permissions.

If you need such a setup, you should create a database per user.

Upvotes: 5

Related Questions