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