Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

Found a problem and fixed it quickly.

v2 introduced a regression: the new query in getAdditionalACLs() joined
pg_catalog.pg_authid to check whether a grantee OID still exists. pg_authid
is restricted to superusers because it stores password hashes. That caused
pg_dump to fail with "permission denied for table pg_authid" whenever it
ran as a non-superuser role

v3 fixes this by joining pg_catalog.pg_roles instead. pg_roles is a view
defined directly on top of pg_authid. Since we only need to check whether a
row with a given OID exists, pg_roles is sufficient and correct here.

Hüseyin Demir <huseyin(dot)d3r(at)gmail(dot)com>, 19 Haz 2026 Cum, 08:25 tarihinde
şunu yazdı:

> I simplified the patch and only changed the SQL query.
>
>
> The v2 patch correctly filters `pg_init_privs` entries whose grantee OID
> has no corresponding row in `pg_authid`, without affecting valid entries.
>
> Regards,
> Demir.
>
> Hüseyin Demir <huseyin(dot)d3r(at)gmail(dot)com>, 12 Haz 2026 Cum, 18:22 tarihinde
> şunu yazdı:
>
>> Hi,
>>
>> I worked on pg_dump and discussed it with Laurenz Albe. Created the
>> attached patch.
>>
>> The fix filters dangling grantees out of each initprivs array at query
>> time, using NULLIF/ARRAY/NOT EXISTS against pg_authid. Entries for
>> grantee = 0 (PUBLIC) are never filtered. If all entries for an object
>> are dangling, NULL is returned and no ACL statement is emitted. Since
>> we cannot restore grants to non-existent roles. correct outcome,
>>
>> The patch includes a TAP test (008_pg_dump_dangling_initprivs.pl) that
>> reproduces the scenario using allow_system_table_mods to create a
>> dangling pg_init_privs entry, then verifies pg_dump exits cleanly and
>> emits no invalid GRANT.
>>
>> I have not prepared backpatch branches yet.
>>
>> Regards.
>>
>> Hüseyin Demir <huseyin(dot)d3r(at)gmail(dot)com>, 11 Haz 2026 Per, 07:49
>> tarihinde şunu yazdı:
>> >
>> > > The orphaned-rows problem shouldn't exist in v17 and later (see
>> > > 534287403, 35dd40d34, and related commits). The OP is apparently
>> > > complaining about an upgrade from v14, where such rows could exist.
>> >
>> > Yes I was working on upgrading the PostgreSQL version from v14 to v18
>> > and was able to solve the problem by removing the danling records from
>> > pg_init_privs.
>> >
>> > > I wonder if it'd be sane for pg_dump to just skip dangling role
>> > > references in pg_init_privs.
>> >
>> > It will change the behavior of pg_dump and it's a general purpose tool
>> > because when we instruct pg_dump to filter orphan records it will
>> > change the content in the system catalogs.
>> >
>> > For now I suppose we have two options: either pg_upgrade or pg_dump.
>> >
>> > Regards.
>> >
>> >
>> > Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, 7 Haz 2026 Paz, 17:52 tarihinde şunu
>> yazdı:
>> > >
>> > > Greg Sabino Mullane <htamfids(at)gmail(dot)com> writes:
>> > > >> 5. Verify orphan records remain in pg_init_privs:
>> > >
>> > > > Thanks for providing a failing use case. I ran this on a 18.3
>> server and
>> > > > found no orphaned rows - but I used the pg_stat_statements extension
>> > > > instead of pg_wait_sampling. Could you try your experiment using
>> > > > pg_stat_statements? And could you also show us the contents of the
>> errant
>> > > > rows in pg_init_privs for the failing case?
>> > >
>> > > The orphaned-rows problem shouldn't exist in v17 and later (see
>> > > 534287403, 35dd40d34, and related commits). The OP is apparently
>> > > complaining about an upgrade from v14, where such rows could exist.
>> > >
>> > > I don't especially care for the proposed fix of making pg_upgrade
>> > > refuse to run. Manually correcting such situations would be tedious
>> > > and error-prone. Plus, it's inconsistent with what we did about
>> > > related issues with role GRANTs (see 29d75b25b and 74b4438a7).
>> > > I wonder if it'd be sane for pg_dump to just skip dangling role
>> > > references in pg_init_privs.
>> > >
>> > > regards, tom lane
>>
>