On 11/17/21 11:07, Mark Dilger wrote: > >> On Nov 17, 2021, at 6:31 AM, Andrew Dunstan <and...@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 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
