Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES

Greetings,

I did some research on this bug and found that the reason for the problem
is that the pg_dump misjudged the non-global default access privileges when
exporting. The details are as follows:

> 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.
>
We can find this description in code
comments(src/backend/catalog/aclchk.c:1162). For example, if we log as user
postgres, for global entire our default ACL is
"{=X/postgres,postgres=X/postgres}", for non-global entire it's "NULL".

Now let's look at a part of the SQL statement used when pg_dump exports the
default ACL(it can be found in src/bin/pg_dump/dumputils.c:762):

> (SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM
> (SELECT acl, row_n FROM
> pg_catalog.unnest(coalesce(defaclacl,pg_catalog.acldefault(CASE WHEN
> defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::"char",defaclrole)))
> WITH ORDINALITY AS perm(acl,row_n)
> WHERE NOT EXISTS (
> SELECT 1 FROM
> pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(CASE WHEN
> defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::"char",defaclrole)))
> AS init(init_acl) WHERE acl = init_acl)) as foo)

It can be seen that when comparing the changes of default ACL, it does not
distinguish between global and non-global default ACL. It uses
{=X/postgres,postgres=X/postgres} as the non-global default ACL by mistake,
resulting in the export error.

Combined with the above research, I gave this patch to fix the bug. Hackers
can help to see if this modification is correct. I'm studying how to write
test scripts for it...

Thanks.

--
There is no royal road to learning.
HighGo Software Co.