v1-0001-pg_dump-skip-dangling-initprivs.patch

From 0ab17b6cf2e3b2e38e0a090faf5860ec7ded89fe Mon Sep 17 00:00:00 2001
From: Huseyin Demir <huseyin.d3r@gmail.com>
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)