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(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?)
>

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