can you revoke a schema from pg_read_all_data, with postgres?-CodePudding

I have a user, which should be able to READ ONLY everything in a db, BUT a specific schema.

There are new schemas added all the time, per account, so I would prefer to allow everything and then deny that specific schema rather than allowing new schemas as they come in.

I'd like to do something like:

GRANT pg_read_all_data TO user;
REVOKE ALL ON SCHEMA xx FROM user;

how can this be achieved?

CodePudding user response：

You have to grant the privileges on all objects in all other schemas individually:

GRANT USAGE ON SCHEMA mayread1 TO somerole;
GRANT SELECT ON ALL TABLES IN SCHEMA mayread1 TO somerole;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA mayread1 TO somerole;

Repeat the above for all schemas except the one where you want to deny access.