public inbox for [email protected]
help / color / mirror / Atom feedFrom: Hüseyin Demir <[email protected]>
To: Laurenz Albe <[email protected]>
Cc: Tom Lane <[email protected]>
Cc: Greg Sabino Mullane <[email protected]>
Cc: [email protected]
Subject: Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
Date: Wed, 24 Jun 2026 14:19:47 +0200
Message-ID: <CAB5wL7Zy89DeVXHFvTL72KChkq=2jLXhH14zrC2n5sutLTqZDg@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
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>
<CAB5wL7aK6UaukMSegzEG68y0VrKOg7wFnPNRaDaHreBf2iVWag@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAB5wL7ZuFwu2Sd-xmn-w5-BbrKk_Mu_1ubYow_qaqFGhJOiMXw@mail.gmail.com>
<CAB5wL7ai8AiaFV6B8J=w6vK1Q6Z9Hmp_y3PFL2Zzpx6Y8xPZ2A@mail.gmail.com>
<[email protected]>
<[email protected]>
<CAB5wL7btFp-XRBfv_J-YperoK+e0uYje3ZYsfTzCuHcUWWrvbw@mail.gmail.com>
<CAB5wL7YFeL0VP7EZu6wtWx=UhXuDp3dTBVJwFvSRzBi7qNscfQ@mail.gmail.com>
<[email protected]>
>
> 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
Thank you for the reviews.
This v6 patch adds a SAFE_INITPRIVS macro that filters aclitem[]
arrays server-side by checking that each entry's grantor and grantee
OID still exists in pg_roles. It is applied in exactly two queries:
1. getAdditionalACLs() -- the one-time fetch of pg_init_privs at startup
2. dumpTable() column ACL prepared statement -- per-table column initprivs
Crucially, the WHERE clauses in getAggregates()/getFuncs() are NOT
modified. Those queries use raw pip.initprivs only for object
selection (not output), and any spuriously-selected objects produce
zero output because the stored initprivs (from getAdditionalACLs) is
already filtered.
pg_roles is used instead of pg_authid to support non-superuser pg_dump.
So this patch covers the security and performance concerns together I
believe. In addition to this, I tried to introduce different tests to
verify it.
When it comes to performance results I tried to draw a conclusion.
Performance testing shows the overhead is ~1-2ms on the one-time
pg_init_privs fetch (249-749 rows) and zero measurable impact on a
database with 10,000 functions + 500 aggregates.
The filtering adds ~1ms (249 rows) to ~2ms (749 rows) to the one-time
getAdditionalACLs() query that runs at pg_dump startup. This is a
fixed cost that does NOT scale with the number of functions,
aggregates, or other objects in the database.
Let me know if you have additional feedback and concerns.
Regards,
Demir
Performance Results
===================
Test Environment:
- CPU: Apple M2 Pro (12 cores)
- RAM: 32 GB
- OS: macOS 26.5.1 (darwin 25.5.0, arm64)
- Compiler: Apple clang 21.0.0 (clang-2100.1.1.101)
- PostgreSQL: 19beta1 (built from source, -O2, no --enable-cassert)
- Disk: internal SSD (APFS)
Wall Clock Timing: pg_dump --schema-only (7 runs each, milliseconds)
---------------------------------------------------------------------
Database: perf_baseline
pg_init_privs rows: 249 | user functions/aggs: 0
Unpatched: 49.69 52.29 46.42 49.73 63.11 51.58 53.50
Patched v6: 52.49 50.23 50.02 51.87 48.83 65.23 64.58
Median unpatched: 51.58 ms
Median patched: 52.49 ms
Delta: +0.91 ms (+1.8%)
Database: perf_10k_functions
pg_init_privs rows: 249 | user functions/aggs: 10500
Unpatched: 1619.04 1674.27 1638.30 1598.82 1597.56 1613.76 1591.44
Patched v6: 1582.23 1584.01 1657.28 1598.13 1573.98 1576.01 1572.51
Median unpatched: 1613.76 ms
Median patched: 1582.23 ms
Delta: -31.53 ms (-2.0%) [within noise, patched is NOT slower]
Database: perf_dangling_500
pg_init_privs rows: 749 (500 dangling) | user functions/aggs: 500
Unpatched: 107.28 104.59 104.23 102.89 102.20 96.26 102.95
Patched v6: 103.90 105.64 104.61 105.18 106.71 105.50 107.20
Median unpatched: 102.95 ms
Median patched: 105.50 ms
Delta: +2.55 ms (+2.5%)
Summary Table:
| Database | initprivs rows | Median unpatched | Median patched | Delta |
|--------------------|----------------|------------------|----------------|------------|
| perf_baseline | 249 | 51.58 ms | 52.49 ms | +0.9 ms |
| perf_10k_functions | 249 | 1613.76 ms | 1582.23 ms | -31.5 ms * |
| perf_dangling_500 | 749 | 102.95 ms | 105.50 ms | +2.6 ms |
* Negative delta = within measurement noise; the patch does NOT slow
down large-schema dumps.
EXPLAIN ANALYZE: getAdditionalACLs() Query Isolation
-----------------------------------------------------
Database: perf_baseline (249 rows, no dangling entries)
Unpatched:
Seq Scan on pg_init_privs (actual time=0.005..0.014 rows=249 loops=1)
Buffers: shared hit=3
Planning Time: 0.198 ms
Execution Time: 0.043 ms
Patched v6:
Seq Scan on pg_init_privs pip (actual time=0.042..1.037 rows=249 loops=1)
Buffers: shared hit=5
SubPlan array_1
-> Function Scan on unnest elt (actual time=0.003..0.003 rows=1.65 loops=249)
SubPlan exists_1
-> Function Scan on aclexplode ace (actual time=0.001..0.001 rows=0 loops=411)
SubPlan exists_3
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
SubPlan exists_5
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
Planning Time: 0.620 ms
Execution Time: 1.082 ms
Overhead: +1.04 ms (one-time cost at dump startup)
Database: perf_dangling_500 (749 rows, 500 dangling entries)
Unpatched:
Seq Scan on pg_init_privs (actual time=0.005..0.032 rows=749 loops=1)
Buffers: shared hit=9
Planning Time: 0.230 ms
Execution Time: 0.069 ms
Patched v6:
Seq Scan on pg_init_privs pip (actual time=0.038..2.174 rows=749 loops=1)
Buffers: shared hit=96
SubPlan array_1
-> Function Scan on unnest elt (actual time=0.002..0.002 rows=0.55 loops=749)
Rows Removed by Filter: 1
SubPlan exists_1
-> Function Scan on aclexplode ace (actual time=0.001..0.001 rows=0.55 loops=911)
SubPlan exists_3
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
SubPlan exists_5
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
Planning Time: 0.517 ms
Execution Time: 2.225 ms
Overhead: +2.16 ms (worst case: 500 dangling entries filtered)
Key observation: pg_authid is scanned ONCE and hashed (loops=1). The
hashed subplan is reused for all 749 rows, making the overhead O(n) in
pg_init_privs rows with a very small constant.
EXPLAIN ANALYZE: getAggregates WHERE Clause (UNCHANGED)
--------------------------------------------------------
Database: perf_10k_functions (500 user aggregates, 10000 user functions)
Hash Left Join (actual time=1.004..1.113 rows=500 loops=1)
Hash Cond: (p.oid = pip.objoid)
Filter: (p.pronamespace <> ... OR p.proacl IS DISTINCT FROM pip.initprivs)
Rows Removed by Filter: 163
Buffers: shared hit=348
-> Seq Scan on pg_proc p (actual time=0.039..1.029 rows=663 loops=1)
Filter: (prokind = 'a')
Rows Removed by Filter: 13274
-> Hash (actual time=0.022..0.022 rows=69 loops=1)
-> Seq Scan on pg_init_privs pip (actual time=0.010..0.013 rows=69 loops=1)
Filter: (classoid = 1255 AND objsubid = 0)
Rows Removed by Filter: 180
Planning Time: 0.834 ms
Execution Time: 1.168 ms
This query is byte-for-byte IDENTICAL in patched and unpatched code.
The patch does NOT modify any WHERE clause. Timing is identical.
The filtering adds ~1ms (249 rows) to ~2ms (749 rows) to the one-time
getAdditionalACLs() query that runs at pg_dump startup. This is a fixed
cost that does NOT scale with the number of functions, aggregates, or
other objects in the database.
The critical performance test is perf_10k_functions (10,000 functions +
500 aggregates): the patched version shows NO regression. The median is
actually 31ms faster, which is just noise. The per-object queries
(getAggregates, getFuncs) are completely unchanged and produce identical
execution plans.
Attachments:
[text/plain] performance_tests.txt (5.6K, ../CAB5wL7Zy89DeVXHFvTL72KChkq=2jLXhH14zrC2n5sutLTqZDg@mail.gmail.com/2-performance_tests.txt)
download | inline:
Performance Results
===================
Test Environment:
- CPU: Apple M2 Pro (12 cores)
- RAM: 32 GB
- OS: macOS 26.5.1 (darwin 25.5.0, arm64)
- Compiler: Apple clang 21.0.0 (clang-2100.1.1.101)
- PostgreSQL: 19beta1 (built from source, -O2, no --enable-cassert)
- Disk: internal SSD (APFS)
Wall Clock Timing: pg_dump --schema-only (7 runs each, milliseconds)
---------------------------------------------------------------------
Database: perf_baseline
pg_init_privs rows: 249 | user functions/aggs: 0
Unpatched: 49.69 52.29 46.42 49.73 63.11 51.58 53.50
Patched v6: 52.49 50.23 50.02 51.87 48.83 65.23 64.58
Median unpatched: 51.58 ms
Median patched: 52.49 ms
Delta: +0.91 ms (+1.8%)
Database: perf_10k_functions
pg_init_privs rows: 249 | user functions/aggs: 10500
Unpatched: 1619.04 1674.27 1638.30 1598.82 1597.56 1613.76 1591.44
Patched v6: 1582.23 1584.01 1657.28 1598.13 1573.98 1576.01 1572.51
Median unpatched: 1613.76 ms
Median patched: 1582.23 ms
Delta: -31.53 ms (-2.0%) [within noise, patched is NOT slower]
Database: perf_dangling_500
pg_init_privs rows: 749 (500 dangling) | user functions/aggs: 500
Unpatched: 107.28 104.59 104.23 102.89 102.20 96.26 102.95
Patched v6: 103.90 105.64 104.61 105.18 106.71 105.50 107.20
Median unpatched: 102.95 ms
Median patched: 105.50 ms
Delta: +2.55 ms (+2.5%)
Summary Table:
| Database | initprivs rows | Median unpatched | Median patched | Delta |
|--------------------|----------------|------------------|----------------|------------|
| perf_baseline | 249 | 51.58 ms | 52.49 ms | +0.9 ms |
| perf_10k_functions | 249 | 1613.76 ms | 1582.23 ms | -31.5 ms * |
| perf_dangling_500 | 749 | 102.95 ms | 105.50 ms | +2.6 ms |
* Negative delta = within measurement noise; the patch does NOT slow
down large-schema dumps.
EXPLAIN ANALYZE: getAdditionalACLs() Query Isolation
-----------------------------------------------------
Database: perf_baseline (249 rows, no dangling entries)
Unpatched:
Seq Scan on pg_init_privs (actual time=0.005..0.014 rows=249 loops=1)
Buffers: shared hit=3
Planning Time: 0.198 ms
Execution Time: 0.043 ms
Patched v6:
Seq Scan on pg_init_privs pip (actual time=0.042..1.037 rows=249 loops=1)
Buffers: shared hit=5
SubPlan array_1
-> Function Scan on unnest elt (actual time=0.003..0.003 rows=1.65 loops=249)
SubPlan exists_1
-> Function Scan on aclexplode ace (actual time=0.001..0.001 rows=0 loops=411)
SubPlan exists_3
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
SubPlan exists_5
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
Planning Time: 0.620 ms
Execution Time: 1.082 ms
Overhead: +1.04 ms (one-time cost at dump startup)
Database: perf_dangling_500 (749 rows, 500 dangling entries)
Unpatched:
Seq Scan on pg_init_privs (actual time=0.005..0.032 rows=749 loops=1)
Buffers: shared hit=9
Planning Time: 0.230 ms
Execution Time: 0.069 ms
Patched v6:
Seq Scan on pg_init_privs pip (actual time=0.038..2.174 rows=749 loops=1)
Buffers: shared hit=96
SubPlan array_1
-> Function Scan on unnest elt (actual time=0.002..0.002 rows=0.55 loops=749)
Rows Removed by Filter: 1
SubPlan exists_1
-> Function Scan on aclexplode ace (actual time=0.001..0.001 rows=0.55 loops=911)
SubPlan exists_3
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
SubPlan exists_5
-> Seq Scan on pg_authid (rows=18 loops=1) [hashed]
Planning Time: 0.517 ms
Execution Time: 2.225 ms
Overhead: +2.16 ms (worst case: 500 dangling entries filtered)
Key observation: pg_authid is scanned ONCE and hashed (loops=1). The
hashed subplan is reused for all 749 rows, making the overhead O(n) in
pg_init_privs rows with a very small constant.
EXPLAIN ANALYZE: getAggregates WHERE Clause (UNCHANGED)
--------------------------------------------------------
Database: perf_10k_functions (500 user aggregates, 10000 user functions)
Hash Left Join (actual time=1.004..1.113 rows=500 loops=1)
Hash Cond: (p.oid = pip.objoid)
Filter: (p.pronamespace <> ... OR p.proacl IS DISTINCT FROM pip.initprivs)
Rows Removed by Filter: 163
Buffers: shared hit=348
-> Seq Scan on pg_proc p (actual time=0.039..1.029 rows=663 loops=1)
Filter: (prokind = 'a')
Rows Removed by Filter: 13274
-> Hash (actual time=0.022..0.022 rows=69 loops=1)
-> Seq Scan on pg_init_privs pip (actual time=0.010..0.013 rows=69 loops=1)
Filter: (classoid = 1255 AND objsubid = 0)
Rows Removed by Filter: 180
Planning Time: 0.834 ms
Execution Time: 1.168 ms
This query is byte-for-byte IDENTICAL in patched and unpatched code.
The patch does NOT modify any WHERE clause. Timing is identical.
The filtering adds ~1ms (249 rows) to ~2ms (749 rows) to the one-time
getAdditionalACLs() query that runs at pg_dump startup. This is a fixed
cost that does NOT scale with the number of functions, aggregates, or
other objects in the database.
The critical performance test is perf_10k_functions (10,000 functions +
500 aggregates): the patched version shows NO regression. The median is
actually 31ms faster, which is just noise. The per-object queries
(getAggregates, getFuncs) are completely unchanged and produce identical
execution plans.
[application/octet-stream] v6-0001-pg_dump-skip-dangling-initprivs.patch (9.6K, ../CAB5wL7Zy89DeVXHFvTL72KChkq=2jLXhH14zrC2n5sutLTqZDg@mail.gmail.com/3-v6-0001-pg_dump-skip-dangling-initprivs.patch)
download | inline diff:
From 39686f2d89fdaa55b41816bcbe18d444b7f8f507 Mon Sep 17 00:00:00 2001
From: huseyin <[email protected]>
Date: Wed, 24 Jun 2026 13:25:25 +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
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 or grantor OID no longer exists in
pg_authid. Cross-cluster restores can produce the same situation on
modern releases.
Dangling entries caused pg_dump to emit invalid SQL such as
"GRANT ... TO "87868"" with a numeric OID as role name, which fails
on restore or pg_upgrade. Additionally, when all initprivs entries for
an object are dangling and proacl is NULL, a spurious REVOKE was emitted.
Fix by filtering each aclitem whose grantor or non-PUBLIC grantee OID
does not appear in pg_roles (used instead of pg_authid to support
non-superuser pg_dump). The filtering is applied server-side in the
queries that fetch pg_init_privs data (getAdditionalACLs and the
column-level ACL prepared statement), where the OID check is
authoritative. This avoids modifying the WHERE clauses in
getAggregates/getFuncs, preserving pg_dump performance on large schemas.
If all entries for an object are dangling the result is NULL and no ACL
is emitted, which is correct -- we cannot restore grants involving roles
that do not exist.
Author: Hüseyin Demir <[email protected]>
Discussion: https://postgr.es/m/19483-80de42dc4e62cfd6%40postgresql.org
Backpatch-through: 14
---
src/bin/pg_dump/pg_dump.c | 37 ++++-
.../t/008_pg_dump_dangling_initprivs.pl | 140 ++++++++++++++++++
2 files changed, 174 insertions(+), 3 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 c56437d6057..ee4c14a3635 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -237,6 +237,35 @@ static int nsequences = 0;
fmtQualifiedId((obj)->dobj.namespace->dobj.name, \
(obj)->dobj.name)
+/*
+ * SQL expression that filters dangling role OIDs from a pg_init_privs
+ * aclitem[] column. An aclitem is dangling when its grantor or non-PUBLIC
+ * grantee OID no longer exists in pg_roles. We use pg_roles rather than
+ * pg_authid so that non-superuser pg_dump works.
+ *
+ * Applied only in queries that fetch pg_init_privs data (not in WHERE clauses
+ * of per-object queries) to avoid running this subquery per function/aggregate.
+ */
+#define SAFE_INITPRIVS(col) \
+ "NULLIF(\n" \
+ " ARRAY(\n" \
+ " SELECT elt FROM pg_catalog.unnest(" col ") AS elt\n" \
+ " WHERE NOT EXISTS (\n" \
+ " SELECT 1 FROM pg_catalog.aclexplode(ARRAY[elt]) ace\n" \
+ " WHERE NOT EXISTS (\n" \
+ " SELECT 1 FROM pg_catalog.pg_roles\n" \
+ " WHERE oid = ace.grantor\n" \
+ " )\n" \
+ " OR (ace.grantee <> 0\n" \
+ " AND NOT EXISTS (\n" \
+ " SELECT 1 FROM pg_catalog.pg_roles\n" \
+ " WHERE oid = ace.grantee\n" \
+ " ))\n" \
+ " )\n" \
+ " ),\n" \
+ " ARRAY[]::pg_catalog.aclitem[]\n" \
+ ")"
+
static void help(const char *progname);
static void setup_connection(Archive *AH,
const char *dumpencoding, const char *dumpsnapshot,
@@ -10900,8 +10929,9 @@ 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"
+ " " SAFE_INITPRIVS("pip.initprivs") " AS initprivs\n"
+ "FROM pg_catalog.pg_init_privs pip");
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
@@ -17100,7 +17130,8 @@ dumpTable(Archive *fout, const TableInfo *tbinfo)
"SELECT at.attname, "
"at.attacl, "
"'{}' AS acldefault, "
- "pip.privtype, pip.initprivs "
+ "pip.privtype,\n"
+ " " SAFE_INITPRIVS("pip.initprivs") " AS initprivs\n"
"FROM pg_catalog.pg_attribute at "
"LEFT JOIN pg_catalog.pg_init_privs pip ON "
"(at.attrelid = pip.objoid "
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..1580a25638c
--- /dev/null
+++ b/src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl
@@ -0,0 +1,140 @@
+# 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');
+
+# --- Setup ---
+# Simulate dangling pg_init_privs entries by inserting grants for a role
+# and then deleting the role directly from pg_authid (bypassing pg_shdepend).
+$node->safe_psql(
+ 'regress_dangling',
+ q{
+SET allow_system_table_mods = true;
+
+-- Roles for testing
+CREATE ROLE ghost_grantee;
+CREATE ROLE ghost_grantor;
+CREATE ROLE "007";
+
+-- Case 1: dangling grantee (function)
+CREATE FUNCTION public.test_func_grantee() 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_grantee=X/' || current_user)::aclitem]
+FROM pg_proc p
+WHERE p.proname = 'test_func_grantee'
+ AND p.pronamespace = 'public'::regnamespace;
+
+-- Case 2: dangling grantor (function)
+CREATE FUNCTION public.test_func_grantor() RETURNS int LANGUAGE sql AS 'SELECT 2';
+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[(current_user || '=X/ghost_grantor')::aclitem]
+FROM pg_proc p
+WHERE p.proname = 'test_func_grantor'
+ AND p.pronamespace = 'public'::regnamespace;
+
+-- Case 3: dangling column-level grantee (table)
+CREATE TABLE public.test_tbl (id int, secret text);
+INSERT INTO pg_init_privs (objoid, classoid, objsubid, privtype, initprivs)
+SELECT c.oid,
+ (SELECT oid FROM pg_class WHERE relname = 'pg_class'),
+ 2, 'e',
+ ARRAY[('ghost_grantee=r/' || current_user)::aclitem]
+FROM pg_class c
+WHERE c.relname = 'test_tbl'
+ AND c.relnamespace = 'public'::regnamespace;
+
+-- Case 4: spurious REVOKE -- all-dangling initprivs on a catalog function
+-- with NULL proacl (simulates the spurious-selection scenario)
+CREATE FUNCTION public.test_func_revoke() RETURNS int LANGUAGE sql AS 'SELECT 3';
+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_grantee=X/' || current_user)::aclitem]
+FROM pg_proc p
+WHERE p.proname = 'test_func_revoke'
+ AND p.pronamespace = 'public'::regnamespace;
+
+-- Case 5: valid all-digit role "007" with a grant (must NOT be filtered)
+CREATE FUNCTION public.test_func_007() RETURNS int LANGUAGE sql AS 'SELECT 7';
+GRANT EXECUTE ON FUNCTION public.test_func_007() TO "007";
+
+-- Now delete the ghost roles to create dangling OIDs
+DELETE FROM pg_authid WHERE rolname = 'ghost_grantee';
+DELETE FROM pg_authid WHERE rolname = 'ghost_grantor';
+
+
+});
+
+my $tempdir = PostgreSQL::Test::Utils::tempdir;
+my $dump_file = "$tempdir/dangling.sql";
+
+# pg_dump must succeed even with dangling pg_init_privs entries.
+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);
+
+# --- Case 1: dangling grantee ---
+like($dump, qr/CREATE FUNCTION public\.test_func_grantee/,
+ 'case 1: function is present in dump');
+unlike($dump, qr/GRANT\b.*\btest_func_grantee/,
+ 'case 1: no GRANT for function with dangling grantee');
+
+# --- Case 2: dangling grantor ---
+like($dump, qr/CREATE FUNCTION public\.test_func_grantor/,
+ 'case 2: function is present in dump');
+unlike($dump, qr/GRANT\b.*\btest_func_grantor/,
+ 'case 2: no GRANT for function with dangling grantor');
+
+# --- Case 3: column-level dangling ---
+like($dump, qr/CREATE TABLE public\.test_tbl/,
+ 'case 3: table is present in dump');
+unlike($dump, qr/GRANT\b.*\btest_tbl\b.*secret/i,
+ 'case 3: no column GRANT for dangling column-level entry');
+
+# --- Case 4: spurious REVOKE ---
+like($dump, qr/CREATE FUNCTION public\.test_func_revoke/,
+ 'case 4: function is present in dump');
+unlike($dump, qr/REVOKE\b.*\btest_func_revoke/,
+ 'case 4: no spurious REVOKE for function with all-dangling initprivs');
+
+# --- Case 5: valid all-digit role "007" ---
+like($dump, qr/CREATE FUNCTION public\.test_func_007/,
+ 'case 5: function is present in dump');
+like($dump, qr/GRANT\b.*\btest_func_007\b.*TO\s+"007"/,
+ 'case 5: GRANT to valid all-digit role "007" is preserved');
+
+# --- General: no numeric OID as role name (other than the valid "007") ---
+# Match any GRANT ... TO "digits" where the digits are NOT "007".
+unlike($dump, qr/GRANT\b.*\bTO\s+"(?!007")[0-9]+"/,
+ 'no GRANT with bare numeric OID as role name (other than valid "007")');
+
+done_testing();
--
2.54.0
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: <CAB5wL7Zy89DeVXHFvTL72KChkq=2jLXhH14zrC2n5sutLTqZDg@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