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.