> On Oct 5, 2021, at 9:23 AM, Robert Haas <robertmhaas@gmail.com> wrote: > >> - Disallow roles from being able to REVOKE role membership that they >> didn't GRANT in the first place. > > I think that's not quite the right test. For example, if alice and bob > are superusers and alice grants pg_monitor to doug, bob should be able > to revoke that grant even though he is not alice.
Additionally, role "alice" might not exist anymore, which would leave the privilege irrevocable.
Do we actually allow that case to happen today..? I didn’t think we did and instead there’s a dependency from the grant on to the Alice role. If that doesn’t exist today then I would think we’d need that and therefore this concern isn’t an issue.
It's helpful to think in terms of role ownership rather than role creation:
superuser +---> alice +---> charlie +---> diane +---> bob
It makes sense that alice can take ownership of diane and drop charlie, but not that bob can do so. Nor should charlie be able to transfer ownership of diane to alice. Nor should charlie be able to drop himself.
I dislike moving away from the ADMIN OPTION when it comes to roles as it puts us outside of the SQL standard. Having the ADMIN OPTION for a role seems, at least to me, to basically mean the things you’re suggesting “ownership” to mean- so why have two different things, especially when one doesn’t exist as a concept in the standard..?
I agree that Charlie shouldn’t be able to drop themselves in general, but I don’t think we need an “ownership” concept for that. We also prevent loops already which I think is called for in the standard already (would need to go reread and make sure though) which already prevents Charlie from granting Diane to Alice. What does the “ownership” concept actually buy us then?