Re: role self-revocation

On Mon, Mar 7, 2022 at 1:16 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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.