Re: role self-revocation

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Wed, Mar 9, 2022 at 4:01 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > > 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.
> > Obviously some work needs to be done to make that happen (and we should
> > see whether the SQL spec has some different idea).
> 
> Well, the problem is that as far as I can see, the admin option is an
> optional feature of membership. You can grant someone membership
> without admin option, or with admin option, but you can't grant them
> the admin option without membership, just like you can't purchase an
> upgrade to first class without the underlying plane ticket. What would
> the syntax look even like for this? GRANT foo TO bar WITH ADMIN OPTION
> BUT WITHOUT MEMBERSHIP? Yikes.

I've been meaning to reply to your other email regarding this, but I
don't really agree that the syntax ends up being so terrible or
difficult to deal with, considering we have these same general things
for ALTER ROLE already and there hasn't been all that much complaining.
That is, we have LOGIN and NOLOGIN, CREATEROLE and NOCREATEROLE, and we
could have MEMBERSHIP and NOMEMBERSHIP pretty easily here if we wanted
to.

> But do we really have to solve this problem before we can clean up
> this session exception? I hope not, because I think that's a much
> bigger can of worms than this is.

I do believe we can deal with the above independently and at a later
time and go ahead and clean up the session excepton bit without dealing
with the above at the same time.

Thanks,

Stephen