Re: Granting SET and ALTER SYSTE privileges for GUCs

On Wed, Nov 17, 2021 at 9: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.

That's a great question, but it isn't a new problem. If I create a
brand new database and do thIs:

rhaas=# revoke execute on function pg_ls_waldir() from pg_monitor;
REVOKE

And then I do this:

[rhaas pgsql]$ pg_dump

Then the output includes this:

REVOKE ALL ON FUNCTION pg_catalog.pg_ls_waldir(OUT name text, OUT size
bigint, OUT modification timestamp with time zone) FROM pg_monitor;

I recommend looking at how that works and making this work the same way.

--
Robert Haas
EDB: http://www.enterprisedb.com