On Sun, Mar 6, 2022 at 2:09 PM David G. Johnston <david.g.johns...@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