Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES

On 10/19/21, 12:54 PM, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I kind of wonder now whether the existing behavior is correct for either
> case. Why aren't we simply regurgitating the pg_default_acl entries
> verbatim? That is, I think maybe we don't need the acldefault call at
> all; we should just use null/empty as the starting ACL in all cases
> when printing pg_default_acl entries. Like this:

Hm. If we do this, then this command:

ALTER DEFAULT PRIVILEGES FOR ROLE myrole REVOKE ALL ON FUNCTIONS FROM PUBLIC;

is dumped as:

ALTER DEFAULT PRIVILEGES FOR ROLE myrole GRANT ALL ON FUNCTIONS TO myrole;

This command is effectively ignored when you apply it, as no entry is
added to pg_default_acl. I haven't looked too closely into what's
happening yet, but it does look like there is more to the story.

Nathan