Message-ID: <f55bf7dc0a503f82abfb6f02ac91278fe59d40d9.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: Wed, 24 Jun 2026 11:56:58 +0200
In-Reply-To: 
 <CAB5wL7YFeL0VP7EZu6wtWx=UhXuDp3dTBVJwFvSRzBi7qNscfQ@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>
	 <d305af372949be0c2c4155168ca7ba97e160bc52.camel@cybertec.at>
	 <5184088806e70ed5db7e83bd6540a016b3802c1d.camel@cybertec.at>
	 <CAB5wL7ZuFwu2Sd-xmn-w5-BbrKk_Mu_1ubYow_qaqFGhJOiMXw@mail.gmail.com>
	 <CAB5wL7ai8AiaFV6B8J=w6vK1Q6Z9Hmp_y3PFL2Zzpx6Y8xPZ2A@mail.gmail.com>
	 <0d7531c192859057c6aad54c89a8d453376bd00c.camel@cybertec.at>
	 <592839.1782153054@sss.pgh.pa.us>
	 <CAB5wL7btFp-XRBfv_J-YperoK+e0uYje3ZYsfTzCuHcUWWrvbw@mail.gmail.com>
	 <CAB5wL7YFeL0VP7EZu6wtWx=UhXuDp3dTBVJwFvSRzBi7qNscfQ@mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable
User-Agent: Evolution 3.58.3 (3.58.3-1.fc43) 
MIME-Version: 1.0
Archived-At: 
 <https://www.postgresql.org/message-id/f55bf7dc0a503f82abfb6f02ac91278fe59d40d9.camel%40cybertec.at>
Precedence: bulk

On Wed, 2026-06-24 at 08:14 +0200, H=C3=BCseyin Demir wrote:
> You can see the attached v5 patch. Basically, v5 solves the problem by
> adding dangling-role filtering directly inside buildACLCommands(). A
> role name that consists entirely of digits is potentially a dangling
> OID reference and it resolves by querying pg_authid once at the start
> of the dump for any legitimate all-digit role names.

The patch looks good and passes my tests.

It fails the regression tests on my system with

  pg_dump: error: query failed: ERROR:  permission denied for table pg_auth=
id

I think you should use pg_roles rather than pg_authid, so that it
remains possible to use pg_dump with a non-superuser.

While the technique of fetching the all-numeric role names in advance
is certainly much cheaper than running a complicated subquery for
every object dumped, I have one remaining doubt:

What if there is a dangling role OID 65432 in pg_init_privs *and*
a valid role with the same name (but a different OID)?  Then the patch
would tacitly restore the dangling reference to the latter role.
Apart from the result being wrong, I wonder if that could be used for
a privilege escalation attack: you detect that there are dangling
pg_init_privs entries that grant high privileges.  Then you abuse your
CREATEROLE to create a role with the same name as the dangling OID.
After a dump and restore, your role has been assigned those privileges.

Perhaps it would be a better approach to fetch the data from
pg_init_privs once at the beginning of the dump, ignoring the entries
with dangling OIDs?

Yours,
Laurenz Albe