Re: role self-revocation

Mark Dilger <mark(dot)dilger(at)enterprisedb(dot)com> writes:
>> On Mar 7, 2022, at 12:01 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>>
>> It's been pointed out upthread that this would have undesirable
>> security implications, because the admin option would be inherited,
>> and the implicit permission isn't.

> Right, but with a reflexive self-admin-option, we could document that it works in a non-inherited way. We'd just be saying the current hard-coded behavior is an option which can be revoked rather than something you're stuck with.

After reflection, I think that role self-admin is probably a bad idea that
we should stay away from. It could perhaps be reasonable given some other
system design and/or syntax than what SQL gives us, but we're dealing in
SQL. It doesn't make sense to GRANT a role to itself, and therefore it
likewise doesn't make sense to GRANT WITH ADMIN OPTION.

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?

Having said that, one thing that I find fishy is that it's not clear
where the admin privilege for a role originates. After "CREATE ROLE
alice", alice has no members, therefore none that have admin privilege,
therefore the only way that the first member could be added is via
superuser deus ex machina. This does not seem clean. 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). Perhaps I'm
reinventing something that was already discussed upthread. I wonder
what the SQL spec has to say on this point, too.

regards, tom lane