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

On Sun, Mar 6, 2022 at 2:09 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> So, do we really want to treat every single login role as a potential group by keeping the session_user exception?

I think that we DO want to continue to treat login roles as
potentially grantable privileges. That feels fundamentally useful to
me. The superuser is essentially granted the privileges of all users
on the system, and has all the rights they have, including the right
to drop tables owned by those users as if they were the owner of those
tables. If it's useful for the superuser to implicitly have the rights
of all users on the system, why should it not be useful for some
non-superuser to implicitly have the rights of some other users on the
system? I think it pretty clearly is. If one of my colleagues leaves
the company, the DBA can say "grant jdoe to rhaas" and let me mess
around with this stuff. Or, the DBA can grant me the privileges of all
my direct reports even when they're not leaving so that I can sort out
anything I need to do without superuser involvement. That all seems
cool and OK to me.

Now I think it is fair to say that we could have chosen a different
design, and MAYBE that would have been better. Nobody forced us to
conflate users and groups into a unified thing called roles, and I
think there's pretty good evidence that it's confusing and
counterintuitive in some ways. There's also no intrinsic reason why
the superuser has to be able to directly exercise the privileges of
every role rather than, say, having a way to become any given role.
But at this point, those design decisions are pretty well baked into
the system design, and I don't really think it's likely that we want
to change them. To put that another way, just because you don't like
the idea of granting one login role to another login role, that
doesn't mean that the feature doesn't exist, and as long as that
feature does exist, trying to make it work better or differently is
fair game.

But I think that's separate from your other question about whether we
should remove the session user exception. That looks tempting to me at
first glance, because we have exchanged several hundred, and it really
feels more like several million, emails on this list about how much of
a problem it is that an unprivileged user can just log in and run a
REVOKE. It breaks the idea that the people WITH ADMIN OPTION on a role
are the ones who control membership in that role. Joshua Brindle's
note upthread about the interaction of this with pg_hba.conf is
another example of that, and I think there are more. Any idea that a
role is a general-purpose way of designating a group of users for some
security critical purpose is threatened if people can make changes to
the membership of that group without being specifically authorized to
do so.

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