public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hüseyin Demir <[email protected]>
To: Tom Lane <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Cc: Laurenz Albe <[email protected]>
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
Date: Fri, 19 Jun 2026 09:40:47 +0200
Message-ID: <CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com> (raw)
In-Reply-To: <CAB5wL7aKGFFEF1SM54GxvaQPH=7Bs=HuUCqi7F4Ve025+Z950A@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>
Found a problem and fixed it quickly.
v2 introduced a regression: the new query in getAdditionalACLs() joined
pg_catalog.pg_authid to check whether a grantee OID still exists. pg_authid
is restricted to superusers because it stores password hashes. That caused
pg_dump to fail with "permission denied for table pg_authid" whenever it
ran as a non-superuser role
v3 fixes this by joining pg_catalog.pg_roles instead. pg_roles is a view
defined directly on top of pg_authid. Since we only need to check whether a
row with a given OID exists, pg_roles is sufficient and correct here.
Hüseyin Demir <[email protected]>, 19 Haz 2026 Cum, 08:25 tarihinde
şunu yazdı:
> I simplified the patch and only changed the SQL query.
>
>
> The v2 patch correctly filters `pg_init_privs` entries whose grantee OID
> has no corresponding row in `pg_authid`, without affecting valid entries.
>
> Regards,
> Demir.
>
> Hüseyin Demir <[email protected]>, 12 Haz 2026 Cum, 18:22 tarihinde
> şunu yazdı:
>
>> Hi,
>>
>> I worked on pg_dump and discussed it with Laurenz Albe. Created the
>> attached patch.
>>
>> The fix filters dangling grantees out of each initprivs array at query
>> time, using NULLIF/ARRAY/NOT EXISTS against pg_authid. Entries for
>> grantee = 0 (PUBLIC) are never filtered. If all entries for an object
>> are dangling, NULL is returned and no ACL statement is emitted. Since
>> we cannot restore grants to non-existent roles. correct outcome,
>>
>> The patch includes a TAP test (008_pg_dump_dangling_initprivs.pl) that
>> reproduces the scenario using allow_system_table_mods to create a
>> dangling pg_init_privs entry, then verifies pg_dump exits cleanly and
>> emits no invalid GRANT.
>>
>> I have not prepared backpatch branches yet.
>>
>> Regards.
>>
>> Hüseyin Demir <[email protected]>, 11 Haz 2026 Per, 07:49
>> tarihinde şunu yazdı:
>> >
>> > > The orphaned-rows problem shouldn't exist in v17 and later (see
>> > > 534287403, 35dd40d34, and related commits). The OP is apparently
>> > > complaining about an upgrade from v14, where such rows could exist.
>> >
>> > Yes I was working on upgrading the PostgreSQL version from v14 to v18
>> > and was able to solve the problem by removing the danling records from
>> > pg_init_privs.
>> >
>> > > I wonder if it'd be sane for pg_dump to just skip dangling role
>> > > references in pg_init_privs.
>> >
>> > It will change the behavior of pg_dump and it's a general purpose tool
>> > because when we instruct pg_dump to filter orphan records it will
>> > change the content in the system catalogs.
>> >
>> > For now I suppose we have two options: either pg_upgrade or pg_dump.
>> >
>> > Regards.
>> >
>> >
>> > Tom Lane <[email protected]>, 7 Haz 2026 Paz, 17:52 tarihinde şunu
>> yazdı:
>> > >
>> > > Greg Sabino Mullane <[email protected]> writes:
>> > > >> 5. Verify orphan records remain in pg_init_privs:
>> > >
>> > > > Thanks for providing a failing use case. I ran this on a 18.3
>> server and
>> > > > found no orphaned rows - but I used the pg_stat_statements extension
>> > > > instead of pg_wait_sampling. Could you try your experiment using
>> > > > pg_stat_statements? And could you also show us the contents of the
>> errant
>> > > > rows in pg_init_privs for the failing case?
>> > >
>> > > The orphaned-rows problem shouldn't exist in v17 and later (see
>> > > 534287403, 35dd40d34, and related commits). The OP is apparently
>> > > complaining about an upgrade from v14, where such rows could exist.
>> > >
>> > > I don't especially care for the proposed fix of making pg_upgrade
>> > > refuse to run. Manually correcting such situations would be tedious
>> > > and error-prone. Plus, it's inconsistent with what we did about
>> > > related issues with role GRANTs (see 29d75b25b and 74b4438a7).
>> > > I wonder if it'd be sane for pg_dump to just skip dangling role
>> > > references in pg_init_privs.
>> > >
>> > > regards, tom lane
>>
>
Attachments:
[application/octet-stream] v3-0001-pg_dump-skip-dangling-initprivs.patch (5.9K, ../CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com/3-v3-0001-pg_dump-skip-dangling-initprivs.patch)
download | inline diff:
From 92c175cf94a5e63e77767e552c511020711cb712 Mon Sep 17 00:00:00 2001
From: Huseyin Demir <[email protected]>
Date: Fri, 19 Jun 2026 07:39:22 +0200
Subject: [PATCH] pg_dump: skip pg_init_privs entries for non-existent roles
MIME-Version: 1.0
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 8bit
pg_init_privs, introduced in PostgreSQL 9.6, records the "initial"
privilege state of objects — either set by initdb (privtype 'i') or by
CREATE EXTENSION scripts (privtype 'e'). pg_dump reads this catalog to
determine which ACL changes the user made on top of the defaults, so it
can emit the right GRANT/REVOKE statements.
Before commit 53428740391, PostgreSQL did not record role dependencies
for pg_init_privs entries in pg_shdepend, meaning DROP ROLE could leave
behind ACL entries whose grantee OID no longer exists in pg_authid.
Cross-cluster restores (dumping from one system where a role existed,
restoring to another where it does not) can produce the same situation
even on modern releases.
The old code passed these dangling aclitem entries through unchanged,
causing pg_dump to emit statements such as
GRANT EXECUTE ON FUNCTION foo() TO "87868";
where "87868" is a numeric OID — invalid SQL that would fail on restore.
Fix by filtering dangling grantees out of each initprivs array at query
time: for every aclitem whose grantee OID does not appear in pg_authid
(excluding grantee = 0, which means PUBLIC), the entry is silently
dropped. If all entries for an object are dangling the result is NULL
and no ACL is emitted, which is correct — we cannot restore grants to
roles that do not exist.
A similar issue in pg_dumpall was fixed by commit 74b4438a70b.
This bug has existed since pg_init_privs was introduced in 9.6.
Backpatch to 14 (oldest currently supported branch).
Author: Huseyin Demir <[email protected]>
Discussion: https://postgr.es/m/19483-80de42dc4e62cfd6%40postgresql.org
Backpatch-through: 14
---
src/bin/pg_dump/pg_dump.c | 14 +++-
.../t/008_pg_dump_dangling_initprivs.pl | 76 +++++++++++++++++++
2 files changed, 88 insertions(+), 2 deletions(-)
create mode 100644 src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a0f7f8e2168..03b39081930 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -10900,8 +10900,18 @@ getAdditionalACLs(Archive *fout)
if (fout->remoteVersion >= 90600)
{
printfPQExpBuffer(query,
- "SELECT objoid, classoid, objsubid, privtype, initprivs "
- "FROM pg_init_privs");
+ "SELECT pip.objoid, pip.classoid, pip.objsubid, pip.privtype,\n"
+ " NULLIF(\n"
+ " ARRAY(\n"
+ " SELECT elt FROM pg_catalog.unnest(pip.initprivs) AS elt\n"
+ " WHERE NOT EXISTS (\n"
+ " SELECT 1 FROM pg_catalog.aclexplode(ARRAY[elt]) ace\n"
+ " LEFT JOIN pg_catalog.pg_roles a ON a.oid = ace.grantee\n"
+ " WHERE ace.grantee <> 0 AND a.oid IS NULL\n"
+ " )\n"
+ " ), ARRAY[]::pg_catalog.aclitem[]\n"
+ " ) AS initprivs\n"
+ "FROM pg_catalog.pg_init_privs pip");
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
diff --git a/src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl b/src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl
new file mode 100644
index 00000000000..f4e90cadca6
--- /dev/null
+++ b/src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl
@@ -0,0 +1,76 @@
+# Copyright (c) 2024-2026, PostgreSQL Global Development Group
+#
+# Tests that pg_dump silently skips pg_init_privs entries that reference
+# roles no longer present in pg_authid, rather than emitting invalid GRANT
+# statements with numeric OIDs as role names.
+
+use strict;
+use warnings FATAL => 'all';
+
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+$node->safe_psql('postgres', 'CREATE DATABASE regress_dangling');
+
+# Simulate an extension-installed object whose initprivs referenced a role
+# that was later dropped (or never existed on this cluster). We cannot use
+# normal DROP ROLE because pg_shdepend would block it, so we delete the role
+# directly from pg_authid with allow_system_table_mods, leaving a dangling
+# grantee OID in pg_init_privs.
+$node->safe_psql(
+ 'regress_dangling',
+ q{
+SET allow_system_table_mods = true;
+
+CREATE ROLE ghost_role;
+
+CREATE FUNCTION public.test_func() RETURNS int LANGUAGE sql AS 'SELECT 1';
+
+INSERT INTO pg_init_privs (objoid, classoid, objsubid, privtype, initprivs)
+SELECT p.oid,
+ (SELECT oid FROM pg_class WHERE relname = 'pg_proc'),
+ 0,
+ 'e',
+ ARRAY[('ghost_role=X/' || current_user)::aclitem]
+FROM pg_proc p
+WHERE p.proname = 'test_func'
+AND p.pronamespace = 'public'::regnamespace;
+
+DELETE FROM pg_authid WHERE rolname = 'ghost_role';
+});
+
+my $tempdir = PostgreSQL::Test::Utils::tempdir;
+my $dump_file = "$tempdir/dangling.sql";
+
+# pg_dump must succeed even though pg_init_privs has a dangling grantee OID.
+command_ok(
+ [
+ 'pg_dump',
+ '--port' => $node->port,
+ '--schema-only',
+ '-f' => $dump_file,
+ 'regress_dangling',
+ ],
+ 'pg_dump succeeds with dangling pg_init_privs entries');
+
+my $dump = slurp_file($dump_file);
+
+# The function itself must still appear in the dump.
+like($dump, qr/CREATE FUNCTION public\.test_func/,
+ 'function is present in dump');
+
+# No GRANT statement should reference a bare numeric OID as a role name.
+unlike($dump, qr/GRANT\b.*\bTO\s+"[0-9]+"/,
+ 'no GRANT with numeric OID as role name');
+
+# No GRANT at all for test_func: all initprivs entries were dangling and
+# proacl is NULL, so there is nothing to emit.
+unlike($dump, qr/GRANT\b.*\btest_func/,
+ 'no GRANT for test_func when all initprivs entries are dangling');
+
+done_testing();
--
2.50.1 (Apple Git-155)
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: <CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com>
* 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