Message-ID: <d305af372949be0c2c4155168ca7ba97e160bc52.camel@cybertec.at>
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in
 pg_init_priv catalog table
From: Laurenz Albe <laurenz.albe@cybertec.at>
To: =?ISO-8859-1?Q?H=FCseyin?= Demir <huseyin.d3r@gmail.com>, Tom Lane
	 <tgl@sss.pgh.pa.us>
Cc: Greg Sabino Mullane <htamfids@gmail.com>, pgsql-bugs@lists.postgresql.org
Date: Sat, 20 Jun 2026 23:53:01 +0200
In-Reply-To: 
 <CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com>
References: <19483-80de42dc4e62cfd6@postgresql.org>
	 <CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
	 <104812.1780847519@sss.pgh.pa.us>
	 <CAB5wL7bo-7okBCJ24fdVn4UhnuPLPG22eQjaLJZ9GJtv0FEWMg@mail.gmail.com>
	 <CAB5wL7bunPCi93Bwi7zN_1gFAZhKLwS=Fsub91SW417xujGtNA@mail.gmail.com>
	 <CAB5wL7aKGFFEF1SM54GxvaQPH=7Bs=HuUCqi7F4Ve025+Z950A@mail.gmail.com>
	 <CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com>
Content-Type: multipart/mixed; boundary="=-AxJGncXanuvfxNCLJPDe"
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/d305af372949be0c2c4155168ca7ba97e160bc52.camel%40cybertec.at>
Precedence: bulk

--=-AxJGncXanuvfxNCLJPDe
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Fri, 2026-06-19 at 09:40 +0200, H=C3=BCseyin 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 =3D 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 =3D 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

--=-AxJGncXanuvfxNCLJPDe
Content-Type: application/x-compressed-tar; name="ext.tgz"
Content-Disposition: attachment; filename="ext.tgz"
Content-Transfer-Encoding: base64

H4sIAAAAAAAAA+1YbW+jRhDO5/yK/VAJrPpyxrHvIl0SaYPXBBWDy4tzaVUhbG84JAwOYN9VVf97
Z9cYv2Gsa5KeKjGSbWBnZ55ndmZ2cfrFS+h7+i2jURrEEbu6mMRRlsTh2WtJC+RDp8N/QfZ/YfDy
TOq2pe5lt3UpSWctqdv68PEMtV4NQYUs0sxLEDpL4jir0js1/j+VKX3yFmHmLmnC1h/dIEG6aAnn
k3g2o1HG7jOaZqjIEOH8R0Ou5RUlPaz/d+8gAy7S51drANX1L7U67daq/rsf2+0PrP473br+/xuR
TYJtgrCimERhV5AA6WImBlFGfZo0kHiOELL6ji5DL4CnHReGm/yh/Tgk8HAc+PCcP5KNwZ2qk432
1TzkA4MdC97Sd73JJLczUPVR6agbRMuVRl/VsbbRae/gGOwC+f2PtVXVlg29x1rYX61m62/hvPHp
/FwxsW4j8pnIDrA1dMTM2ipc7DO3DTT33YR6U9cLQxfyJEvBQB4x2dA0vJ43icMQiUPTGKk9YoLH
MBhPmkgzZKwxZIIsNLan6iNiWsXcaIn6homEB1WX2t22wDwLjt2/ElDfNAboaxC5WewusqerjZGe
McAqN5DBB2EL8V/dsJHuaFrB1LGwwnnyIK3Vq7lth4RmYgOZxHZM3UJ0CXuCmyWBDxFCGtYVh1mf
h3Mf+gUDIdwRBWBxCIjovU/CqZhzB9V4yIjAfNtUFQWiy+cwK9Np6LJ9youmbEaSFS727ZdTe5p+
db/A5JAmWxy3nm4YTiDbTVW2OcWfYHWnQfJ+HqeZn9DUhSlCEwnb9+7SC4Opl8UJBOCI70JFZGsC
eYviYLoBsoS710JQQDBMoio66mEbowcTD4ergII+usd6TytuixiMsKaCumEewD7MsSrz1UtsEXO0
0k2TZaUdY8hiaCFxOo68GUXC88L7FgiN42h2bZ9I/XyOje80Xi5PmTdGIqxE0Rd2zRVwQC+kbjkk
i2gEli5femYRUAydO02VN64Ll9yjt3aIhqY6wOYj+oU8biWyZUP3sWxVtjgSYMHMw7SfkdRY9Y2V
qXIQexjyyA17zLToNXa1yuKl6hAHG6k6jOeakCkOgUhIjSYS2+zrkgHO/YKRiZd5YexfwGVx4oAK
jp4C350uZnNRWFliqfxwT0yCPHSNpJZwpIBLqjf0In/h+bSshHcqZ9WyeNGsLgEd0MunlRctbEhh
EBW2+VaRRF74fTV76HnX7BHfm27x3V3i0GNZb7BNx7JJb2MMvM7D7bZQBFXVNdjmS0Ky2y2qWsWe
l+qqHMCXqYLp3wDfSCUPbNZsyVjm2dVpsxvYh8vTfbYsrbj1lvjnnJZnGBsJInGSwp4X+Vsb4Wpo
wwLqgJhwSEHqYOCsKnmITaxpREMW7hO+IOzYEkTCcVfxIhPzy42v3Pm/8AXmhHK2SFT1oWPD0aQg
2USGY+88g+lHCo+N0uc11CY6wDyO45B60VGEh1zWmOnzJv8NaPs8l25ubpCokb6NTWWDr4ngRHC/
/6zAebNB2igtKTYWZm/CIsxKWFxfX7+ARZhVsKBvw4KWs3jJYoT0OA3/bWj4ZTRub29fQsOvovE2
OeWX5RTQeAmLspwaZ/noBHbk40TWx5NywOOMQQYLQsWbCGtE1Y2/4NnHA1V75McnbwamVF0BoAml
Jbqyhi3eoiehl6aoR/rY0Wz+jrXjesvKnn1ssVfIwqDEkr4IxToSzR2dNi+ME0qXrJOd0Onw1Dyh
1GVK5TrFOkonVnLrLCnfkwHm58jJFzrzDpdsX6NsN7XIrw7R5dVfCPS5xMiuQumOTD6znRub8j17
Te6rimNuvWVHT0iUjeEjZHAazObhNon1sWAZ0K+VB4P85LqyybW3gPzo/2NqqaWWWmqppZZaaqml
llpqqaWWWl5b/gGVv0YxACgAAA==


--=-AxJGncXanuvfxNCLJPDe--