Re: privileges for ALTER ROLE/DATABASE SET

On Fri, Jul 22, 2022 at 03:25:16PM -0700, Nathan Bossart wrote:
> On Fri, Jul 22, 2022 at 04:16:14PM -0400, Tom Lane wrote:
> > Clearly, you need enough privilege to SET the parameter, and you need
> > some sort of management privilege on the target role or DB. There
> > might be room to discuss what that per-role/DB privilege needs to be.
> > But I'm very skeptical that we need to manage this at the level
> > of the cross product of GUCs and roles/DBs, which is what you seem
> > to be proposing. That seems awfully unwieldy, and is there really
> > any use-case for it?
>
> Actually, I think my vote is to do nothing, except for perhaps updating the
> documentation to indicate that SET privileges on a parameter are sufficient
> for ALTER ROLE/DATABASE SET (given you have the other required privileges
> for altering the role/database). I can't think of a use-case for allowing
> a role to SET a GUC but not change the session default for another role.

If I wanted to argue for a use case, I'd point to ALTER ROLE/DATABASE SET
surviving REVOKE of SET privileges. Revoking a SET privilege promptly affects
future SET statements, but the REVOKE issuer would need to take the separate
step of clearing unwanted pg_db_role_setting entries. Even so, ...

> And I agree that requiring extra permissions for this feels excessive.
> Maybe someone else has a use-case in mind, though.

... I, too, vote to change nothing. We have lots of cases where REVOKE
doesn't reverse actions taken while the user held the privilege being revoked.
Changing that isn't worth much.