0

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?

1 Answer 1

0

PostgreSQL does not have a negative permission. Grants are positive permissions, I.e. “this role can access these resources,” and the lack of a granted permission results in a denial of access. Grants are additive, so if a role a user is a member of has SELECT on a table, the user will have SELECT on the table regardless of a more specific grant.

To accomplish what you want, split your role into two, one for access to the rest of the tables and one that has update grants for the precious table(s). Grant the first role to everyone and the second role to only the users allowed to update the precious tables.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.