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. -- Robert Haas EDB: http://www.enterprisedb.com