Re: Granting SET and ALTER SYSTE privileges for GUCs

> On Nov 17, 2021, at 6:31 AM, Andrew Dunstan <andrew(at)dunslane(dot)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 predefined roles with privileges, that was a great idea precisely because sites could define their own security policies using their 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 it amuses 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