Re: Granting SET and ALTER SYSTE privileges for GUCs

On 11/17/21 11:07, Mark Dilger wrote:
>
>> 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
creatingpredefined roles with privileges, that was a great idea precisely because sites could define their own security
policiesusing 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.
Soit 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?)
 
>

I agree it's not ideal. At the time I suggested a more flexible approach
I hadn't really thought about the problems of upgrading. If you can come
up with something that works there then I'll be all ears.


cheers


andrew


--
Andrew Dunstan
EDB: https://www.enterprisedb.com