Re: Inconsistent behavior of pg_dump/pg_restore on DEFAULT PRIVILEGES

Sorry I used the wrong way to send the email. The email about the bug is
here:
https://www.postgresql.org/message-id/111621616618184%40mail.yandex.ru

On Wed, Mar 31, 2021 at 11:02 AM Neil Chen <carpenter(dot)nail(dot)cz(at)gmail(dot)com>
wrote:

> 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.
>

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