Thread: privileges for ALTER ROLE/DATABASE SET

privileges for ALTER ROLE/DATABASE SET

From
Nathan Bossart
Date:
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



Re: privileges for ALTER ROLE/DATABASE SET

From
Tom Lane
Date:
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



Re: privileges for ALTER ROLE/DATABASE SET

From
Nathan Bossart
Date:
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



Re: privileges for ALTER ROLE/DATABASE SET

From
Noah Misch
Date:
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.