Re: Role Self-Administration - Mailing list pgsql-hackers

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?