Re: role self-revocation

On Mon, Mar 7, 2022 at 2:29 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> You mean the one that was based upon your "ooops"...I discounted that immediately because members cannot revoke their own membership in a group unless they were given WITH ADMIN OPTION on that group.

Oh, hmm. That example might be backwards from the case I'm talking about.

> The mere fact that the pg_hba.conf concern raised there hasn't been reported as a live issue suggests the lack of any meaningful design flaw here.

Not really. The system is full of old bugs, just as all software
system are, and the particular role self-administration behavior that
is at issue here appears to be something that was accidentally
introduced 16 years years ago in a commit that did something else and
never scrutinized from a design perspective since then.

Personally, I've been shocked by the degree to which this entire area
seems to be full of design flaws and half-baked code. I mean, just the
fact that the pg_auth_members.grantor can be left pointing to a role
OID that no longer exists is pretty crazy, right? I don't think anyone
today would consider something with that kind of wart committable.

> That isn't to say that having a LOGIN role get an automatic temporary WITH ADMIN OPTION on itself is a good thing - but there isn't any privilege escalation vector here to be squashed.  There is just a "DBAs should treat LOGIN roles as leaf nodes" expectation in which case there would be no superuser granted memberships to be removed.

Well, we may not have found one yet, but that doesn't prove none
exists. In any case, if we can agree that it's not necessarily a
desirable behavior, that's good enough for me.

(I still disagree with the idea that LOGIN roles have to be leaf
nodes. We could have a system where that's true, but that's not how
the system we actually have is designed.)

-- 
Robert Haas
EDB: http://www.enterprisedb.com