Horton

Re: role self-revocation

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
Cc: Mark Dilger <mark.dilger@enterprisedb.com>, "David G. Johnston" <david.g.johnston@gmail.com>, Stephen Frost <sfrost@snowman.net>, Joshua Brindle <joshua.brindle@crunchydata.com>, Andrew Dunstan <andrew@dunslane.net>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2022-03-09T21:24:04Z
Lists: pgsql-hackers 
I wrote:
> This seems like a reasonable answer to me too: the creating role has admin
> option implicitly, and can then choose to grant that to other roles.
> Obviously some work needs to be done to make that happen (and we should
> see whether the SQL spec has some different idea).

Ah, here we go: it's buried under CREATE ROLE.  SQL:2021 12.4 <role
definition> saith that when role A executes CREATE ROLE <role name>,
then

1) A grantable role authorization descriptor is created whose role name
is <role name>, whose grantor is "_SYSTEM", and whose grantee is A.

Since nobody is _SYSTEM, this grant can't be deleted except by dropping
the new role (or, maybe, dropping A?).  So that has nearly the same
end result as "the creating role has admin option implicitly".  The main
difference I can see is that it also means the creating role is a *member*
implicitly, which is something I'd argue we don't want to enforce.  This
is analogous to the way we let an object owner revoke her own ordinary
permissions, which the SQL model doesn't allow since those permissions
were granted to her by _SYSTEM.

			regards, tom lane

Commits

  1. Make role grant system more consistent with other privileges.

  2. Ensure that pg_auth_members.grantor is always valid.

  3. Remove the ability of a role to administer itself.

  4. Add tests of the CREATEROLE attribute

  5. Replace explicit PIN entries in pg_depend with an OID range test.

  6. Shore up ADMIN OPTION restrictions.

  7. Add pg_has_role() family of privilege inquiry functions modeled after the

  8. Align GRANT/REVOKE behavior more closely with the SQL spec, per discussion