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

On Fri, 2026-06-19 at 09:40 +0200, Hüseyin Demir wrote:
> Found a problem and fixed it quickly.

I think that we need something like this fix, because a failing
upgrade is a bug. For the same reason I think that the fix
should be backpatched.

I looked at your patch and found that the query you added doesn't
cover the important case where the grantor is a non-existing role
(that is, the original extension owner was dropped).
The grantor appears in SET SESSION AUTHORIZATION commands in the
dump, which make the restore and consequently the upgrade fail.

I suggest a query like this one:

SELECT pip.objoid, pip.classoid, pip.objsubid, pip.privtype,
NULLIF(
ARRAY(
SELECT elt FROM pg_catalog.unnest(pip.initprivs) AS elt
/* that is valid, that is, there is not ... */
WHERE NOT EXISTS (
/* ... a non-existing grantor ... */
SELECT 1 FROM pg_catalog.aclexplode(ARRAY[elt]) ace
WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_roles AS r1
WHERE r1.oid = ace.grantor
)
/* ... or a non-existing grantee that isn't 0 */
OR ace.grantee <> 0
AND NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_roles AS r2
WHERE r2.oid = ace.grantee
)
)
),
ARRAY[]::pg_catalog.aclitem[]
) AS initprivs
FROM pg_catalog.pg_init_privs pip;

You see that I added some comments, because the query is almost
incomprehensible. I couldn't think of a more elegant solution.

I think that you also should add an extensive code comment that
explains why this hack is needed.

I am undecided if the regression test with the artificially created
broken initial privileges is a good idea or not. After all, we are
not testing the real thing here (for example, the test didn't catch
the omission described above).

I am attaching a test extension that I installed in a v14 database
to test your patch; perhaps you'll find it useful. It creates all
kinds of objects that have an ACL. Dropping the role that created
the extension leaves various junk entries in pg_init_privs that you
can use to test your patch.

Yours,
Laurenz Albe