Re: role self-revocation - Mailing list pgsql-hackers

> On Mar 7, 2022, at 10:28 AM, Tom Lane <tgl@sss.pgh.pa.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
messagewould clearly need to be updated, because it suggests the role should have admin_option on itself, a situation
whichis not currently supported. 

Perhaps we should support that, though, by adding a reflexive aclitem[] to pg_authid (meaning it tracks which
privilegesa role has on itself) with tracking of who granted it, so that revocation can be handled properly.  The
aclitemcould 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
trackwho granted it, such that further revocations could properly refuse if the revoker doesn't have sufficient
privilegesvis-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-conceptpatch unless you suggest a better approach. 

Thoughts?


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