Hi hackers,
I received a report of missing privileges after an upgrade, and I
believe I've traced it back to pg_dump's handling of ALTER DEFAULT
PRIVILEGES IN SCHEMA. I did find a recent report [0] that looks
related, but I didn't see any follow-ups on that thread. It looks
like the issue dates back to the introduction of pg_init_privs in v9.6
[1] [2].
A simple reproduction of the issue is to run pg_dump after the
following command is run:
ALTER DEFAULT PRIVILEGES FOR ROLE nathan IN SCHEMA test GRANT EXECUTE ON FUNCTIONS TO PUBLIC;
pg_dump will emit this command for this ACL:
ALTER DEFAULT PRIVILEGES FOR ROLE nathan IN SCHEMA test REVOKE ALL ON FUNCTIONS FROM nathan;
The problem appears to be that pg_dump is comparing the entries in
pg_default_acl to the default ACL (i.e., acldefault()). This is fine
for "global" entries (i.e., entries with no schema specified), but it
doesn't work for "non-global" entries (i.e., entries with a schema
specified). This is because the default for a non-global entry is
actually an empty ACL. aclchk.c has the following comment:
/*
* The default for a global entry is the hard-wired default ACL for the
* particular object type. The default for non-global entries is an empty
* ACL. This must be so because global entries replace the hard-wired
* defaults, while others are added on.
*/
I've attached a quick hack that seems to fix this by adjusting the
pg_dump query to use NULL instead of acldefault() for non-global
entries. I'm posting this early in order to gather thoughts on the
approach and to make sure I'm not missing something obvious.
Nathan
[0] https://postgr.es/m/111621616618184%40mail.yandex.ru
[1] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=23f34fa
[2] https://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e2090d9
