Re: Granting SET and ALTER SYSTE privileges for GUCs

> On Mar 30, 2022, at 6:26 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> Consider this design:

Isn't that just the design I had implemented in v8 several months ago?

Subject: [PATCH v8] Allow GRANT of SET and ALTER SYSTEM SET for gucs

Allow granting of privilege to set or alter system set variables
which otherwise can only be managed by superusers. Each
(role,variable,privilege) triple is independently grantable, so a
user may be granted privilege to SET but not to ALTER SYSTEM SET on
a variable, or vice versa. The privilege to SET a userset variable
may be granted, though doing so has no practical effect, since any
role can set userset variables anyway. Worse, there is no way to
revoke the privilege to SET a userset variable. To remedy that,
most core userset variables have been changed to suset, with
explicit grants to set the variable to public.

I don't think v9 ever got posted to the list, but v10 has:

Subject: [PATCH v10] Allow grant and revoke of privileges on settings

Allow grant and revoke of privileges to set or alter system set
configuration variables. Each (role,variable,privilege) triple can
be independently granted or revoked, so a user may be granted
privilege to SET but not to ALTER SYSTEM SET on a variable, or vice
versa. Privilege to SET a userset variable is implicitly granted to
public, but may be revoked.

If we want to backtrack to v8, that's fine. I can rebase that, port some of the other changes from v14 to it, and repost it as v15. We should review the conversation from December and January which included some arguments for allowing revokes of SET on USERSET from PUBLIC. I don't want to keep going around in circles on this.

—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company