Re: Granting SET and ALTER SYSTE privileges for GUCs

> On Nov 17, 2021, at 6:31 AM, Andrew Dunstan <andrew@dunslane.net> wrote:
>
> Well, I was trying (perhaps not very well) to imagine how to deal with
> someone modifying the permissions of one of the predefined roles. Say
> pg_foo has initial permission to set bar and baz, and the DBA removes
> permission to set baz. How is pg_dump going to emit the right commands
> to allow a safe pg_upgrade? Maybe we should say that the permissions for
> the predefined roles are immutable, so only permissions sets for user
> defined roles are mutable.

I find this somewhat amusing.  When you suggested off-list that I make gucs individually grantable rather than creating
predefinedroles with privileges, that was a great idea precisely because sites could define their own security policies
usingtheir own site-defined roles: 

  CREATE ROLE admin_type_a NOLOGIN NOSUPERUSER;
  CREATE ROLE admin_type_b NOLOGIN NOSUPERUSER;
  ...
  GRANT ALTER SYSTEM ON guc_a1, guc_a2, guc_a3, ... TO admin_type_a;
  GRANT ALTER SYSTEM ON guc_b1, guc_b2, guc_b3, ... TO admin_type_b;
  ...

That has all the power of a system based on predefined roles, but with site-specific flexibility, which is better.  So
itamuses me that we'd now be talking about granting some of these to predefined roles, as that is a regression in
flexibility. (How would a site revoke it from one of those predefined roles if they wanted a different policy?) 

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