Re: role self-revocation

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> > On Mar 7, 2022, at 12:16 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > tgl> Having said that, one thing that I find fishy is that it's not clear
> > tgl> where the admin privilege for a role originates. After "CREATE ROLE
> > tgl> alice", alice has no members, therefore none that have admin privilege,
> > tgl> therefore the only way that the first member could be added is via
> > tgl> superuser deus ex machina. This does not seem clean.
>
> > I agree with that, but I don't think it's a sufficient reason for
> > keeping the self-admin exception, because the same problem exists for
> > non-login roles. I don't even think it's the right idea conceptually
> > to suppose that the power to administer a role originates from the
> > role itself.
>
> Actually, that's the same thing I was trying to say. But if it doesn't
> originate from the role itself, where does it originate from?
>
> > In my opinion, the right to
> > administer a role - regardless of whether or not it is a login role -
> > most naturally vests in the role that created it, or something in that
> > direction at least, if not that exact thing.
>
> 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.

I agree that this has some appeal, but it's not desirable in all cases
and so I wouldn't want it to be fully baked into the system ala the role
'owner' concept.

> Obviously some work needs to be done to make that happen (and we should
> see whether the SQL spec has some different idea).

Agreed on this, though I don't recall it having much to say on it.

Thanks,

Stephen