Re: role self-revocation

On Mon, Mar 7, 2022 at 1:16 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Based on Robert's archaeological dig, it now seems that the fact that
> we have any such behavior at all was just a mistake.  What would be
> lost if we drop it?
>

Probably nothing that couldn't be replaced, and with a better model, but I
do have a concern that there are setups in the wild inadvertently using
this behavior.  Enough so that I would vote to change it but include a
migration GUC to restore the current behavior, probably with a deprecation
warning.  Kinda depends on the post-change dump/restore mechanics.  But
just tearing it out wouldn't seem extraordinary for us.


>
> Having said that, one thing that I find fishy is that it's not clear
> where the admin privilege for a role originates.


I do not see a problem with there being no inherent admin privilege for a
role.  A superuser or CREATEROLE user holds admin privilege on all roles in
the cluster.  They can delegate the privilege to administer a role to yet
another role in the system.  The necessitates creating two roles - the one
being administered and the one being delegated to.  I don't see a benefit
to saving which specific superuser or CREATEROLE user "owns" the role that
is to be administered.  Not unless non-owner CREATEROLE users are prevented
from exercising admin privileges on the role.  That all said, I'd accept
the choice to include such ownership information as a requirement for
meeting the auditing needs of DBAs.  But I would argue that such auditing
probably needs to be external to the working system - the fact that
ownership can be changed reduces the benefit of an in-database value.

> If we recorded

which user created the role, we could act as though that user has
> admin privilege (whether or not it's a member).


I suppose we could record the current owner of a role but that seems
unnecessary.  I dislike using the "created" concept by virtue of the fact
that, for routines, "security definer" implies creator but it actually
means "security owner".

David J.