On Mon, Oct 04, 2021 at 10:57:46PM -0400, Stephen Frost wrote: > "A role is not considered to hold WITH ADMIN OPTION on itself, but it > may grant or revoke membership in itself from a database session where > the session user matches the role."
> Here's the thing - having looked back through the standard, it seems > we're missing a bit that's included there and that makes a heap of > difference. Specifically, the SQL standard basically says that to > revoke a privilege, you need to have been able to grant that privilege > in the first place (as Andrew Dunstan actually also brought up in a > recent thread about related CREATEROLE things- > https://www.postgresql.org/message-id/837cc50a-532a-85f5-a231-9d68f2184e52%40dunslane.net > ) and that isn't something we've been considering when it comes to role > 'self administration' thus far, at least as it relates to the particular > field of the "grantor". Which SQL standard clauses are you paraphrasing? (A reference could take the form of a spec version number, section number, and rule number. Alternately, a page number and URL to a PDF would suffice.) > We can't possibly make things like EVENT TRIGGERs or CREATEROLE work > with role trees if a given role can basically just 'opt out' of being > part of the tree to which they were assigned by the user who created > them. Therefore, I suggest we contemplate two changes in this area: I suspect we'll regret using the GRANT system to modify behaviors other than whether or not one gets "permission denied". Hence, -1 on using role membership to control event trigger firing, whether or not $SUBJECT changes. > - Allow a user who is able to create roles decide if the role created is > able to 'self administor' (that is- GRANT their own role to someone > else) itself. > > - Disallow roles from being able to REVOKE role membership that they > didn't GRANT in the first place. Either of those could be reasonable. Does the SQL standard take a position relevant to the decision? A third option is to track each role's creator and make is_admin_of_role() return true for the creator, whether or not the creator remains a member. That would also benefit cases where the creator is rolinherit and wants its ambient privileges to shed the privileges of the role it's creating. > We should probably do a more thorough review > to see if there's other cases where a given role is able to REVOKE > rights that have been GRANT'd by some other role on a particular object, > as it seems like we should probably be consistent in this regard across > everything and not just for roles. That might be a bit of a pain but it > seems likely to be worth it in the long run and feels like it'd bring us > more in-line with the SQL standard too. Does the SQL standard take a position on whether REVOKE SELECT should work that way?
