Re: role self-revocation

> On Mar 7, 2022, at 10:28 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Does anything interesting break if you do just take it out?

SET SESSION AUTHORIZATION regress_priv_group2;
GRANT regress_priv_group2 TO regress_priv_user5; -- ok: a role can self-admin
-NOTICE: role "regress_priv_user5" is already a member of role "regress_priv_group2"
+ERROR: must have admin option on role "regress_priv_group2"

This test failure is just a manifestation of the intended change, but assuming we make no other changes, the error message would clearly need to be updated, because it suggests the role should have admin_option on itself, a situation which is not currently supported.

Perhaps we should support that, though, by adding a reflexive aclitem[] to pg_authid (meaning it tracks which privileges a role has on itself) with tracking of who granted it, so that revocation can be handled properly. The aclitem could start out null, meaning the role has by default the traditional limited self-admin which the code comments discuss:

/*
* 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.
*
* Withholding self-admin in a security-restricted operation prevents
* object owners from harnessing the session user identity during
* administrative maintenance. Suppose Alice owns a database, has
* issued "GRANT alice TO bob", and runs a daily ANALYZE. Bob creates
* an alice-owned SECURITY DEFINER function that issues "REVOKE alice
* FROM carol". If he creates an expression index calling that
* function, Alice will attempt the REVOKE during each ANALYZE.
* Checking InSecurityRestrictedOperation() thwarts that attack.
*
* Withholding self-admin in SECURITY DEFINER functions makes their
* behavior independent of the calling user. There's no security or
* SQL-standard-conformance need for that restriction, though.
*
* A role cannot have actual WITH ADMIN OPTION on itself, because that
* would imply a membership loop. Therefore, we're done either way.
*/

For non-null aclitem[], we could support REVOKE ADMIN OPTION FOR joe FROM joe, and for explicit re-grants, we could track who granted it, such that further revocations could properly refuse if the revoker doesn't have sufficient privileges vis-a-vis the role that granted it in the first place.

I have not yet tried to implement this, and might quickly hit problems with the idea, but will take a stab at a proof-of-concept patch unless you suggest a better approach.

Thoughts?

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company