Re: role self-revocation

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