CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.1sql-grant
Andrus
Andrus

Reputation: 27919

How to enable access to one schema and public schema in Postgres

Postgres 9.1 database contains public, firma1 and firma6 and other schemas.

How to allow access to public and firma6 schemas only for specific user.

I tried

revoke all on schema firma1 from public;
revoke all on all tables in schema firma1 from public cascade;

GRANT ALL ON SCHEMA public TO restristeduser;
GRANT ALL ON SCHEMA firma6 TO restristeduser;

If restristeduser logs to database from pgadmin, it still sees all other user names and firma1 table list (It cannot see firma1 table contents only)

How to fix this so that user cannot see firma1 schema objects and database user names ?

Upvotes: 0

Views: 2088

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656391

That's not easily possible. System catalogs listing all objects are visible to all users of the same database (and some to all users of the same database cluster) in default installations, and that's necessary for normal operation.

If you want to hide the mere existence of objects, you need separate databases (or even separate database clusters).

If you have a lot of common objects, an alternative would be to let the user connect to a separate database (cluster) and access selected objects via dblink or foreign data wrapper (running with privileges of an admin user).

Upvotes: 3

Related Questions