Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES

On 10/19/21, 12:54 PM, "Tom Lane" <tgl@sss.pgh.pa.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