Let’s say I have a user on my PostgreSQL instance called "myuser", and I add it to a new group called "update_all" like this:
CREATE ROLE update_all WITH NOLOGIN ROLE myuser;
I then grant the update_all role permission to update every table in every schema in every database. My thinking is that I can revoke update permissions from all users by default, and just grant this role to any user that I want to let update tables. However, say I decide that I want myuser to be able to update tables in every database except "mydb". Is there a way to explicitly revoke privileges that are granted to a user by a role it is a member of without removing it from that role? I would also be interested to know whether this is possible at the schema level. The closest thing I’ve found is row-level security policies, but it seems like they aren’t a perfect solution because you’d have to apply the same policy to every table in the database or schema one by one. It seems like there should be a way to explicitly deny even privileges that are inherited. Is anything like this possible?