Re: role self-revocation - Mailing list pgsql-hackers

Greetings,

* Robert Haas (robertmhaas@gmail.com) wrote:
> On Thu, Mar 10, 2022 at 5:14 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > This seems reasonable in isolation, but
> >
> > (1) it implies a persistent relationship between creating and created
> > roles.  Whether you want to call that ownership or not, it sure walks
> > and quacks like ownership.

I agree that there would be a recorded relationship (that is, one that
we write into the catalog and keep around until and unless it's removed
by an admin) between creating and created roles and that's probably the
default when CREATE ROLE is run but, unlike tables or such objects in
the system, I don't agree that we should require this to exist at
absolutely all times for every role (what would it be for the bootstrap
superuser..?).  At least today, that's distinct from how ownership in
the system works.  I also don't believe that this is necessarily an
issue for Robert's use-case, as long as there are appropriate
restrictions around who is allowed to remove or modify these
relationships.

> I agree. It's been obvious to me from the beginning that we needed
> such a persistent relationship, and also that it needed to be a
> relationship from which the created role couldn't simply walk away.
> Yet, more than six months after the first discussions of this topic,
> we still don't have any kind of agreement on what that thing should be
> called. I like my TENANT idea best, but I'm perfectly willing to call
> it ownership as you seem to prefer or WITH ADMIN OPTION as Stephen
> seems to prefer if one of those ideas gains consensus. But we've
> managed to waste all hope of making any significant progress here for
> an entire release cycle for lack of ability to agree on spelling. I
> think that's unfair to Mark, who put a lot of work into this area and
> got nothing out of it, and I think it sucks for users of PostgreSQL,
> too.

Well ... one of those actually already exists and also happens to be in
the SQL spec.  I don't necessarily agree that we should absolutely
require that the system always enforce that this relationship exist (I'd
like a superuser to be able to get rid of it and to be able to change it
too if they want) and that seems a bit saner than having the bootstrap
superuser be special in some way here as would seem to otherwise be
required.  I also feel that it would be generally useful to have more
than one of these relationships, if the user wishes, and that's
something that ownership doesn't (directly) support today.  Further,
that's supported and expected by the SQL spec too.  Even if we invented
some concept of ownership of roles, it seems like we should make most of
the other changes discussed here to bring us closer to what the spec
says about CREATE ROLE, DROP ROLE, GRANT, REVOKE, etc.  At that point
though, what's the point of having ownership?

> > (2) it seems exactly contradictory to your later point that
> >
> > > Agree. I also think that it would be a good idea to attribute grants
> > > performed by any superuser to the bootstrap superuser, or leave them
> > > unattributed somehow. Because otherwise dropping superusers becomes a
> > > pain in the tail for no good reason.
> >
> > Either there's a persistent relationship or there's not.  I don't
> > think it's sensible to treat superusers differently here.
> >
> > I think that this argument about the difficulty of dropping superusers
> > may in fact be the motivation for the existing behavior that object-
> > permissions GRANTs done by superusers are attributed to the object
> > owner; something you were unhappy about upthread.
> >
> > In the end these requirements seem mutually contradictory.  Either
> > we can have a persistent ownership relationship or not, but I don't
> > think we can have it apply in some cases and not others without
> > creating worse problems than we solve.  I'm inclined to toss overboard
> > the requirement that superusers need to be an easy thing to drop.
> > Why is that important, anyway?
>
> Well, I think you're looking at it the wrong way. Compared to getting
> useful functionality, the relative ease of dropping users is
> completely unimportant. I'm happy to surrender it in exchange for
> something else. I just don't see why we should give it up for nothing.
> If Alice creates non-superusers Bob and Charlie, and Charlie creates
> Doug, we need the persistent relationship to know that Charlie is
> allowed to drop Doug and Bob is not. But if Charlie is a superuser
> anyway, then the persistent relationship is of no use. I don't see the
> point of cluttering up the system with such dependencies. Will I do it
> that way, if that's what it takes to get the patch accepted? Sure. But
> I can't imagine any end-user actually liking it.

We need to know that Charlie is allowed to drop Doug and Bob isn't but
that doesn't make it absolutely required that this be tracked
permanently or that Alice can't decide later to make it such that Doug
can't be dropped by Charlie for whatever reason she has.  Also, I don't
think it would be such an issue to have a CASCADE for DROP ROLE which
would handle this case if we want it (and pg_auth_members is shared, so
there isn't an issue with multi-database concerns).  We could also call
it something else if people feel CASCADE would be confusing since it
wouldn't cascade to owned objects.  Or we could consider extending GRANT
to make this situation something that could be handled more easily.

Thanks,

Stephen