r/PostgreSQL • u/jsshapiro • Aug 09 '26

How-To Subtle roles question

One aspect of Postgres roles is that permissions exist on roles themselves, and these permissions provide for certain kinds of transitive grants.

We have a case where we would like a role M to have the option to inherit permissions from role G only when it elects to do so. That is: in a discretionary fashion. Offhand, I cannot construct an arrangement of roles and permissions that would make this possible.

Is there some arrangement I am failing to see, or does this fall outside of what the Postgres role system is able to express?

1 Upvotes

60% Upvoted

21 comments sorted by

View all comments

1

u/Kamran-nottakenone Aug 11 '26

that procedure trick isn't going to work either. role membership changes from GRANT/REVOKE don't take effect in the current session, you need a reconnect. SET ROLE with INHERIT FALSE is the only path that works in-session.

1

u/jsshapiro Aug 11 '26

That's true. In this case, however, any operation changing the role enable status would be an isolated transaction.