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, 12 Jun 2026 18:22:03 +0200
Message-ID: <CAB5wL7bunPCi93Bwi7zN_1gFAZhKLwS=Fsub91SW417xujGtNA@mail.gmail.com> (raw)
In-Reply-To: <CAB5wL7bo-7okBCJ24fdVn4UhnuPLPG22eQjaLJZ9GJtv0FEWMg@mail.gmail.com>
References: <[email protected]>
<CAKAnmmLYXcBSV8i5MVuiwu8tkX-JDGmZtJ38_YbdUTwoDW7xKg@mail.gmail.com>
<[email protected]>
<CAB5wL7bo-7okBCJ24fdVn4UhnuPLPG22eQjaLJZ9GJtv0FEWMg@mail.gmail.com>
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] v1-0001-pg_dump-skip-dangling-initprivs.patch (9.6K, ../CAB5wL7bunPCi93Bwi7zN_1gFAZhKLwS=Fsub91SW417xujGtNA@mail.gmail.com/2-v1-0001-pg_dump-skip-dangling-initprivs.patch)
download | inline diff:
From 0ab17b6cf2e3b2e38e0a090faf5860ec7ded89fe Mon Sep 17 00:00:00 2001
From: Huseyin Demir <[email protected]>
Date: Fri, 12 Jun 2026 18:00:47 +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.
---
src/bin/pg_dump/pg_dump.c | 117 ++++++++++--------
.../t/008_pg_dump_dangling_initprivs.pl | 76 ++++++++++++
2 files changed, 144 insertions(+), 49 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..8c4d5afee14 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -10900,69 +10900,88 @@ 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_authid 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);
ntups = PQntuples(res);
- for (i = 0; i < ntups; i++)
+ if (ntups > 0)
{
- Oid objoid = atooid(PQgetvalue(res, i, 0));
- Oid classoid = atooid(PQgetvalue(res, i, 1));
- int objsubid = atoi(PQgetvalue(res, i, 2));
- char privtype = *(PQgetvalue(res, i, 3));
- char *initprivs = PQgetvalue(res, i, 4);
- CatalogId objId;
- DumpableObject *dobj;
+ int i_objoid = PQfnumber(res, "objoid");
+ int i_classoid = PQfnumber(res, "classoid");
+ int i_objsubid = PQfnumber(res, "objsubid");
+ int i_privtype = PQfnumber(res, "privtype");
+ int i_initprivs = PQfnumber(res, "initprivs");
- objId.tableoid = classoid;
- objId.oid = objoid;
- dobj = findObjectByCatalogId(objId);
- /* OK to ignore entries we haven't got a DumpableObject for */
- if (dobj)
+ for (i = 0; i < ntups; i++)
{
- /* Cope with sub-object initprivs */
- if (objsubid != 0)
+ Oid objoid = atooid(PQgetvalue(res, i, i_objoid));
+ Oid classoid = atooid(PQgetvalue(res, i, i_classoid));
+ int objsubid = atoi(PQgetvalue(res, i, i_objsubid));
+ char privtype = *(PQgetvalue(res, i, i_privtype));
+ char *initprivs = PQgetvalue(res, i, i_initprivs);
+ CatalogId objId;
+ DumpableObject *dobj;
+
+ objId.tableoid = classoid;
+ objId.oid = objoid;
+ dobj = findObjectByCatalogId(objId);
+ /* OK to ignore entries we haven't got a DumpableObject for */
+ if (dobj)
{
- if (dobj->objType == DO_TABLE)
+ /* Cope with sub-object initprivs */
+ if (objsubid != 0)
{
- /* For a column initprivs, set the table's ACL flags */
- dobj->components |= DUMP_COMPONENT_ACL;
- ((TableInfo *) dobj)->hascolumnACLs = true;
+ if (dobj->objType == DO_TABLE)
+ {
+ /* For a column initprivs, set the table's ACL flags */
+ dobj->components |= DUMP_COMPONENT_ACL;
+ ((TableInfo *) dobj)->hascolumnACLs = true;
+ }
+ else
+ pg_log_warning("unsupported pg_init_privs entry: %u %u %d",
+ classoid, objoid, objsubid);
+ continue;
}
- else
- pg_log_warning("unsupported pg_init_privs entry: %u %u %d",
- classoid, objoid, objsubid);
- continue;
- }
- /*
- * We ignore any pg_init_privs.initprivs entry for the public
- * schema, as explained in getNamespaces().
- */
- if (dobj->objType == DO_NAMESPACE &&
- strcmp(dobj->name, "public") == 0)
- continue;
+ /*
+ * We ignore any pg_init_privs.initprivs entry for the public
+ * schema, as explained in getNamespaces().
+ */
+ if (dobj->objType == DO_NAMESPACE &&
+ strcmp(dobj->name, "public") == 0)
+ continue;
- /* Else it had better be of a type we think has ACLs */
- if (dobj->objType == DO_NAMESPACE ||
- dobj->objType == DO_TYPE ||
- dobj->objType == DO_FUNC ||
- dobj->objType == DO_AGG ||
- dobj->objType == DO_TABLE ||
- dobj->objType == DO_PROCLANG ||
- dobj->objType == DO_FDW ||
- dobj->objType == DO_FOREIGN_SERVER)
- {
- DumpableObjectWithAcl *daobj = (DumpableObjectWithAcl *) dobj;
+ /* Else it had better be of a type we think has ACLs */
+ if (dobj->objType == DO_NAMESPACE ||
+ dobj->objType == DO_TYPE ||
+ dobj->objType == DO_FUNC ||
+ dobj->objType == DO_AGG ||
+ dobj->objType == DO_TABLE ||
+ dobj->objType == DO_PROCLANG ||
+ dobj->objType == DO_FDW ||
+ dobj->objType == DO_FOREIGN_SERVER)
+ {
+ DumpableObjectWithAcl *daobj = (DumpableObjectWithAcl *) dobj;
- daobj->dacl.privtype = privtype;
- daobj->dacl.initprivs = pstrdup(initprivs);
+ daobj->dacl.privtype = privtype;
+ daobj->dacl.initprivs = pstrdup(initprivs);
+ }
+ else
+ pg_log_warning("unsupported pg_init_privs entry: %u %u %d",
+ classoid, objoid, objsubid);
}
- else
- pg_log_warning("unsupported pg_init_privs entry: %u %u %d",
- classoid, objoid, objsubid);
}
}
PQclear(res);
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: <CAB5wL7bunPCi93Bwi7zN_1gFAZhKLwS=Fsub91SW417xujGtNA@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