On Sun, Mar 6, 2022 at 10:19 AM Robert Haas <robertmh...@gmail.com> wrote: > > On Fri, Mar 4, 2022 at 5:20 PM David G. Johnston > <david.g.johns...@gmail.com> wrote: > > I think I disagree. Or, at least, the superuser has full control of > > dictating how role membership is modified and that seems sufficient. > > The point is that the superuser DOES NOT have full control. The > superuser cannot prevent relatively low-privileged users from undoing > things that the superuser did intentionally and doesn't want reversed. > > The choice of names in my example wasn't accidental. If the granted > role is a login role, then the superuser's intention was to vest the > privileges of that role in some other role, and it is surely not right > for that role to be able to decide that it doesn't want it's > privileges to be so granted. That's why I chose the name "peon". In > your example, where you chose the name "admin", the situation is less > clear. If we imagine the granted role as a container for a bundle of > privileges, giving it the ability to administer itself feels more > reasonable. However, I am very much unconvinced that it's correct even > there. Suppose the superuser grants "admin" to both "joe" and "sally". > Now "joe" can SET ROLE to "admin" and revoke it from "sally", and the > superuser has no tool to prevent this. > > Now you can imagine a situation where the superuser is totally OK with > either "joe" or "sally" having the ability to lock the other one out, > but I don't think it's right to say that this will be true in all > cases. >
Another example here is usage of groups in pg_hba.conf, if the admin has a group of users with stronger authentication requirements: e.g., hostssl all +certonlyusers all cert map=certmap clientcert=1 and one can remove their membership, they can change their authentication requirements.