Re: Role Self-Administration

Greetings,

* Noah Misch (noah(at)leadboat(dot)com) wrote:
> 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.)

12.7 <revoke statement>

Specifically the bit about how a role authorization is said to be
identified if it defines the grant of the role revoked to the grantee
*with grantor A*. Reading it again these many years later, that seems
to indicate that you need to actually be the grantor or able to be the
grantor who performed the original grant in order to revoke it,
something that wasn't done in the original implementation of roles.

> > 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.

I've not been entirely sure if that's a great idea or not either, but I
didn't see any particular holes in Tom's suggestion that we use this as
a way to identify a tree of roles, except for this particular issue that
a role is currently able to 'opt out', which seems like a mistake in the
original role implementation and not an issue with Tom's actual idea to
use it in this way.

I do think that getting the role management sorted out with just the
general concepts of 'tenant' and 'landlord' as discussed in the thread
with Mark about changes to CREATEROLE and adding of other predefined
roles is a necessary first step, and only after we feel like we've
solved that should we come back to the idea of using that for other
things, such as event trigger firing.

> > - 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.

It's a bit dense, but my take on the revoke statement description is
that the short answer is "yes, the standard does take a position on
this" at least as it relates to role memberships. As for if a role
would have the ability to control it for themselves, that seems like a
natural extension of the general approach whereby a role can't grant
themselves admin role on their own role if they don't already have it,
but some other, appropriately privileged role, could.

I don't feel it's necessary to track additional information about who
created a specific role. Simply having, when that role is created,
the creator be automatically granted admin rights on the role created
seems like it'd be sufficient.

> > 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?

In my reading, yes, it's much the same. I invite others to try and read
through it and see if they agree with my conclusions. Again, this is
really all on the 'revoke statement' side and isn't really covered on
the 'grant' side.

Thanks,

Stephen