Role Self-Administration

Greetings,

There's been various discussions about CREATEROLE, EVENT TRIGGERs, and
other things which hinge around the general idea that we can create a
'tree' of roles where there's some root and then from that root there's
a set of created roles, or at least roles which have been GRANT'd other
roles as part of an explicit arrangement.

The issue with many of these suggestions is that roles, currently, are
able to 'administer' themselves. That means that such role memberships
aren't suitable for such controls.

To wit, this happens:

Superuser:

=# create user u1;
CREATE ROLE
=# create user u2;
CREATE ROLE
=# grant u2 to u1;
GRANT ROLE

...

Log in as u2:

=> revoke u2 from u1;
REVOKE ROLE

...

This is because we allow 'self administration' of roles, meaning that
they can decide what other roles they are a member of. This is
documented as:

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

at: https://www.postgresql.org/docs/current/sql-grant.html

Further, we comment this in the code:

* A role can admin itself when it matches the session user and we're
* outside any security-restricted operation, SECURITY DEFINER or
* similar context. SQL-standard roles cannot self-admin. However,
* SQL-standard users are distinct from roles, and they are not
* grantable like roles: PostgreSQL's role-user duality extends the
* standard. Checking for a session user match has the effect of
* letting a role self-admin only when it's conspicuously behaving
* like a user. Note that allowing self-admin under a mere SET ROLE
* would make WITH ADMIN OPTION largely irrelevant; any member could
* SET ROLE to issue the otherwise-forbidden command.

in src/backend/utils/adt/acl.c

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

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:

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

This isn't as big a change as it might seem as we already track which
role issued a given GRANT. 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.

So, thoughts?

Thanks!

Stephen