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

From b0a6d379972aee5cad3f7aeed576f1745ddf6178 Mon Sep 17 00:00:00 2001
From: Huseyin Demir <huseyin.d3r@gmail.com>
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 <huseyin.d3r@gmail.com>
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..4467d5d6389 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_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);
 
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)