Thread: privileges for ALTER ROLE/DATABASE SET
Hi hackers, Presently, if a role has privileges to SET a parameter, it is able to ALTER ROLE/DATABASE SET that parameter, provided it otherwise has permission to alter that role/database. This includes cases where the role only has SET privileges via the new pg_parameter_acl catalog. For example, if a role is granted the ability to SET a PGC_SUSET GUC, it also has the ability to ALTER ROLE/DATABASE SET that GUC. A couple of recent threads have alluded to the possibility of introducing a new set of privileges for ALTER ROLE/DATABASE SET [0] [1], so I thought I'd start the discussion. First, is it necessary to introduce new privileges, or should the ability to SET a parameter be enough to ALTER ROLE/DATABASE SET it? AFAICT this is roughly the behavior before v15, but it simply disallowed non-superusers from setting certain parameters. Second, if new privileges are required, what would they look like? My first instinct is to add GRANT ALTER ROLE ON PARAMETER and GRANT ALTER DATABASE ON PARAMETER. Thoughts? [0] https://postgr.es/m/1732511.1658332210%40sss.pgh.pa.us [1] https://postgr.es/m/20220714225735.GB3173833%40nathanxps13 -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
Nathan Bossart <nathandbossart@gmail.com> writes:
> Presently, if a role has privileges to SET a parameter, it is able to ALTER
> ROLE/DATABASE SET that parameter, provided it otherwise has permission to
> alter that role/database. This includes cases where the role only has SET
> privileges via the new pg_parameter_acl catalog. For example, if a role is
> granted the ability to SET a PGC_SUSET GUC, it also has the ability to
> ALTER ROLE/DATABASE SET that GUC. A couple of recent threads have alluded
> to the possibility of introducing a new set of privileges for ALTER
> ROLE/DATABASE SET [0] [1], so I thought I'd start the discussion.
> First, is it necessary to introduce new privileges, or should the ability
> to SET a parameter be enough to ALTER ROLE/DATABASE SET it?
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?
regards, tom lane
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. And I agree that requiring extra permissions for this feels excessive. Maybe someone else has a use-case in mind, though. I figured it would be good to hash this out prior to 15.0, at which point changing the behavior would become substantially more difficult. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
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.