public inbox for [email protected]  

help / color / mirror / Atom feed

From: Laurenz Albe <[email protected]>
To: Hüseyin Demir <[email protected]>
To: Tom Lane <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
Date: Sat, 20 Jun 2026 23:53:01 +0200
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com>
References: <[email protected]>
	<CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
	<[email protected]>
	<CAB5wL7bo-7okBCJ24fdVn4UhnuPLPG22eQjaLJZ9GJtv0FEWMg@mail.gmail.com>
	<CAB5wL7bunPCi93Bwi7zN_1gFAZhKLwS=Fsub91SW417xujGtNA@mail.gmail.com>
	<CAB5wL7aKGFFEF1SM54GxvaQPH=7Bs=HuUCqi7F4Ve025+Z950A@mail.gmail.com>
	<CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com>

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


Attachments:

  [application/x-compressed-tar] ext.tgz (1.4K, ../[email protected]/2-ext.tgz)
  download

view thread (27+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox