Re: role self-revocation

On Sun, Mar 6, 2022 at 10:19 AM Robert Haas <robertmhaas@gmail.com> wrote:
>
> On Fri, Mar 4, 2022 at 5:20 PM David G. Johnston
> <david.g.johnston@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.