public inbox for [email protected]
help / color / mirror / Atom feedBUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
28+ messages / 7 participants
[nested] [flat]
* BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-05-18 05:06 PG Bug reporting form <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: PG Bug reporting form @ 2026-05-18 05:06 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]
The following bug has been logged on the website:
Bug reference: 19483
Logged by: Hüseyin Demir
Email address: [email protected]
PostgreSQL version: 18.3
Operating system: CentOS 9
Description:
Hi,
I encountered a problem during the pg_upgrade.
pg_upgrade --check passes all consistency checks but then fails during
the actual upgrade when pg_restore attempts to GRANT privileges to a
role OID (e.g. "16424") that no longer exists. The root cause is orphan
records in pg_init_privs whose ACL entries reference a dropped role by
OID. pg_upgrade does not detect these dangling references during its
pre-flight checks.
Steps to Reproduce
1. Create a role and database with that role as owner:
CREATE ROLE benchmark_owner SUPERUSER;
CREATE DATABASE my_db OWNER benchmark_owner;
2. Connect to the database and install pg_wait_sampling as that role:
\c my_db
SET ROLE benchmark_owner;
CREATE EXTENSION pg_wait_sampling;
RESET ROLE;
3. Verify pg_init_privs now contains rows with benchmark_owner in the
ACL (privtype = 'e', OIDs 16429, 16434, 16439, 16443, etc.)
4. Reassign owned objects and drop the role:
\c postgres
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity WHERE datname = 'my_db';
ALTER DATABASE my_db RENAME TO my_db_v2;
\c my_db_v2
REASSIGN OWNED BY benchmark_owner TO postgres;
DROP OWNED BY benchmark_owner;
\c postgres
DROP ROLE benchmark_owner;
5. Verify orphan records remain in pg_init_privs:
SELECT pip.objoid
FROM pg_init_privs pip
CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
LEFT JOIN pg_authid a ON a.oid = ace.grantee
WHERE a.oid IS NULL AND ace.grantee <> 0;
-- Returns 22 rows referencing OID 16461 (the dropped role)
6. Run pg_upgrade --check:
/usr/pgsql-18/bin/pg_upgrade \
--old-datadir=/var/lib/pgsql/14/data \
--new-datadir=/var/lib/pgsql/18/data \
--old-bindir=/usr/pgsql-14/bin/ \
--new-bindir=/usr/pgsql-18/bin/ \
--check --jobs=4
Result: "Clusters are compatible" -- all checks pass.
[postgres@localhost data]$ /usr/pgsql-18/bin/pg_upgrade
--old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/18/data
--old-bindir=/usr/pgsql-14/bin/ --new-bindir=/usr/pgsql-18/bin/ --check
--jobs=4
Performing Consistency Checks on Old Live Server
------------------------------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for not-null constraint inconsistencies ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
*Clusters are compatible*
7. Run pg_upgrade (without --check):
[postgres@localhost data]$ /usr/pgsql-18/bin/pg_upgrade
--old-datadir=/var/lib/pgsql/14/data --new-datadir=/var/lib/pgsql/18/data
--old-bindir=/usr/pgsql-14/bin/ --new-bindir=/usr/pgsql-18/bin/ --jobs=4
Performing Consistency Checks
-----------------------------
Checking cluster versions ok
Checking database connection settings ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for contrib/isn with bigint-passing mismatch ok
Checking data type usage ok
Checking for not-null constraint inconsistencies ok
Creating dump of global objects ok
Creating dump of database schemas
ok
Checking for presence of required libraries ok
Checking database user is the install user ok
Checking for prepared transactions ok
Checking for new cluster tablespace directories ok
If pg_upgrade fails after this point, you must re-initdb the
new cluster before continuing.
Performing Upgrade
------------------
Setting locale and encoding for new cluster ok
Analyzing all rows in the new cluster ok
Freezing all rows in the new cluster ok
Deleting files from new pg_xact ok
Copying old pg_xact to new server ok
Setting oldest XID for new cluster ok
Setting next transaction ID and epoch for new cluster ok
Deleting files from new pg_multixact/offsets ok
Copying old pg_multixact/offsets to new server ok
Deleting files from new pg_multixact/members ok
Copying old pg_multixact/members to new server ok
Setting next multixact ID and offset for new cluster ok
Resetting WAL archives ok
Setting the default char signedness for new cluster ok
Setting frozenxid and minmxid counters in new cluster ok
Restoring global objects in the new cluster ok
Restoring database schemas in the new cluster
my_db_v2
*failure*
Consult the last few lines of
"/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log"
for
the probable cause of the failure.
Failure, exiting
*failure*
Consult the last few lines of
"/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16384.log"
for
the probable cause of the failure.
Failure, exiting
child process exited abnormally: status 256
Failure, exiting
[postgres@localhost data]$
The pg_upgrade log as follow
[postgres@localhost data]$ cat
/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log
command: "/usr/pgsql-18/bin/pg_dump" --host /var/lib/pgsql/18/data --port
50432 --username postgres --no-data --sequence-data --quote-all-identifiers
--binary-upgrade --format=custom --statistics --no-sync
--file="/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/dump/pg_upgrade_dump_16425.custom"
'dbname=my_db_v2' >>
"/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log"
2>&1
command: "/usr/pgsql-18/bin/pg_restore" --host /var/lib/pgsql/18/data --port
50432 --username postgres --create --exit-on-error --verbose
--transaction-size=250 --dbname template1
"/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/dump/pg_upgrade_dump_16425.custom"
>>
"/var/lib/pgsql/18/data/pg_upgrade_output.d/20260518T065340.119/log/pg_upgrade_dump_16425.log"
2>&1
pg_restore: connecting to database for restore
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE "my_db_v2"
pg_restore: connecting to new database "my_db_v2"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating DATABASE PROPERTIES "my_db_v2"
pg_restore: connecting to new database "my_db_v2"
pg_restore: executing SELECT pg_catalog.set_config('search_path', '',
false);
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating SCHEMA "public"
pg_restore: creating COMMENT "SCHEMA "public""
pg_restore: creating EXTENSION "pg_wait_sampling"
pg_restore: creating COMMENT "EXTENSION "pg_wait_sampling""
pg_restore: creating FUNCTION "public.pg_wait_sampling_get_current(integer)"
pg_restore: creating FUNCTION "public.pg_wait_sampling_get_history()"
pg_restore: creating FUNCTION "public.pg_wait_sampling_get_profile()"
pg_restore: creating FUNCTION "public.pg_wait_sampling_reset_profile()"
pg_restore: creating VIEW "public.pg_wait_sampling_current"
pg_restore: creating VIEW "public.pg_wait_sampling_history"
pg_restore: creating VIEW "public.pg_wait_sampling_profile"
pg_restore: creating ACL "SCHEMA "public""
pg_restore: creating ACL "public.FUNCTION
"pg_wait_sampling_reset_profile"()"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 4338; 0 0 ACL FUNCTION
"pg_wait_sampling_reset_profile"() postgres
pg_restore: error: could not execute query: ERROR: role "16424" does not
exist
Command was: SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);
REVOKE ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() FROM
PUBLIC;
REVOKE ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() FROM
"postgres";
SET SESSION AUTHORIZATION "16424";
GRANT ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() TO
"16424";
RESET SESSION AUTHORIZATION;
SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);
REVOKE ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() FROM
"16424";
GRANT ALL ON FUNCTION "public"."pg_wait_sampling_reset_profile"() TO
"postgres";
Root Cause
When a role is dropped, DROP OWNED BY and REASSIGN OWNED BY do NOT
cascade to pg_init_privs. This leaves ACL entries in pg_init_privs
referencing the dropped role by its numeric OID.
pg_dump (run internally by pg_upgrade) faithfully dumps these orphan
ACL entries and emits SET SESSION AUTHORIZATION "<OID>" commands.
pg_restore then fails because the numeric OID is not a valid role name
in the new cluster.
pg_upgrade's --check phase does not include a check for orphan role
references inside pg_init_privs ACLs, so the problem is not caught
before the destructive upgrade steps begin.
Expected Behavior
pg_upgrade --check should detect orphan ACL entries in pg_init_privs
(where ace.grantee does not match any pg_authid.oid and is not 0) and
report an error before declaring the clusters compatible.
Possible Fix
Add a new pre-flight check in pg_upgrade (src/bin/pg_upgrade/check.c)
that runs the following query against each user database in the old
cluster:
SELECT DISTINCT pip.objoid
FROM pg_init_privs pip
CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
LEFT JOIN pg_authid a ON a.oid = ace.grantee
WHERE a.oid IS NULL AND ace.grantee <> 0;
If any rows are returned, pg_upgrade should abort with a descriptive
error message telling the user to either drop or clean up the orphan
ACL entries before upgrading.
Workaround
Before running pg_upgrade, manually clean up orphan pg_init_privs
entries in all user databases:
-- Identify affected objects
SELECT pip.objoid
FROM pg_init_privs pip
CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
LEFT JOIN pg_authid a ON a.oid = ace.grantee
WHERE a.oid IS NULL AND ace.grantee <> 0;
-- Remove the orphan rows (adjust WHERE clause as needed)
DELETE FROM pg_init_privs
WHERE objoid IN (
SELECT pip.objoid
FROM pg_init_privs pip
CROSS JOIN LATERAL aclexplode(pip.initprivs) ace
LEFT JOIN pg_authid a ON a.oid = ace.grantee
WHERE a.oid IS NULL AND ace.grantee <> 0
);
Note: Direct modification of system catalogs requires
allow_system_table_mods = on.
How should we proceed or solve this case ?
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-05-20 13:06 Greg Sabino Mullane <[email protected]>
parent: PG Bug reporting form <[email protected]>
0 siblings, 2 replies; 28+ messages in thread
From: Greg Sabino Mullane @ 2026-05-20 13:06 UTC (permalink / raw)
To: [email protected]; [email protected]
>
> PostgreSQL version: 18.3
> ...
5. Verify orphan records remain in pg_init_privs:erprise Postgres Software
> Products & Tech Support
>
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?
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-07 10:53 Hüseyin Demir <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-07 10:53 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]
Hi, sorry for the late reply. There was a problem in my mailbox.
I was able to reproduce the same issue with the pg_stat_statements
extension. The problem is valid for orphan pg_init_privs records.
Therefore, I created a simple patch to introduce a new check to pg_upgrade
binary.
PS: I'm working on PostgreSQL 14 (on different minor versions)
Please see [1]
<https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail....;
to see and review the problem I try to solve.
```
my_db_v2=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid = pip.objoid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'pg_stat_statements'
AND pip.privtype = 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------+--------------------
16458 | 1255 | e | {16449=X/16449} | pg_stat_statements
16466 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
16471 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
(3 rows)
```
I applied the following steps.
1. Create the role and database on postgres database.
```
CREATE ROLE benchmark_owner SUPERUSER;
CREATE DATABASE my_db OWNER benchmark_owner;
```
2. Connect to the my_db and execute the following commands.
```
SET ROLE benchmark_owner;
create extension pg_stat_statements;
```
Afterwards, I see the records in pg_init_privs
my_db=# reset role;
RESET
my_db=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid = pip.objoid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'pg_stat_statements'
AND pip.privtype = 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------------------------------------+--------------------
16458 | 1255 | e | {benchmark_owner=X/benchmark_owner} | pg_stat_statements
16466 | 1259 | e |
{benchmark_owner=arwdDxt/benchmark_owner,=r/benchmark_owner} |
pg_stat_statements
16471 | 1259 | e |
{benchmark_owner=arwdDxt/benchmark_owner,=r/benchmark_owner} |
pg_stat_statements
(3 rows)
3. Connect to postgres database and execute the following ones.
```
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'my_db';ALTER DATABASE my_db RENAME TO my_db_v2;
```
4. Connect to my_db_2 and execute the following ones.
```
REASSIGN OWNED BY benchmark_owner TO postgres;
DROP OWNED BY benchmark_owner;
```
5. Connect to postgres database and drop the role we created the extension
```
DROP ROLE benchmark_owner;
```
6. Connect to my_db_2 and check the dangling/orphan records.
```
my_db_v2=# SELECT pip.objoid, pip.classoid, pip.privtype, pip.initprivs,
e.extname
FROM pg_init_privs pip
JOIN pg_depend d ON d.objid = pip.objoid
JOIN pg_extension e ON e.oid = d.refobjid
WHERE e.extname = 'pg_stat_statements'
AND pip.privtype = 'e';
objoid | classoid | privtype | initprivs | extname
--------+----------+----------+--------------------------------+--------------------
16458 | 1255 | e | {16449=X/16449} | pg_stat_statements
16466 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
16471 | 1259 | e | {16449=arwdDxt/16449,=r/16449} | pg_stat_statements
(3 rows)
```
[1]
https://www.postgresql.org/message-id/flat/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail....
Greg Sabino Mullane <[email protected]>, 20 May 2026 Çar, 15:07 tarihinde
şunu yazdı:
> PostgreSQL version: 18.3
>> ...
>
> 5. Verify orphan records remain in pg_init_privs:erprise Postgres Software
>> Products & Tech Support
>>
>
> 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?
>
>
>
>
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-07 15:51 Tom Lane <[email protected]>
parent: Greg Sabino Mullane <[email protected]>
1 sibling, 1 reply; 28+ messages in thread
From: Tom Lane @ 2026-06-07 15:51 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: [email protected]; [email protected]
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
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-11 05:49 Hüseyin Demir <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-11 05:49 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]
> 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
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-12 16:22 Hüseyin Demir <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-12 16:22 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]; Laurenz Albe <[email protected]>
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)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-19 06:25 Hüseyin Demir <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-19 06:25 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]; Laurenz Albe <[email protected]>
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] v2-0001-pg_dump-skip-dangling-initprivs.patch (5.9K, ../../CAB5wL7aKGFFEF1SM54GxvaQPH=7Bs=HuUCqi7F4Ve025+Z950A@mail.gmail.com/3-v2-0001-pg_dump-skip-dangling-initprivs.patch)
download | inline diff:
From b0a6d379972aee5cad3f7aeed576f1745ddf6178 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..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)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-19 07:40 Hüseyin Demir <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-19 07:40 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]; Laurenz Albe <[email protected]>
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)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-20 21:53 Laurenz Albe <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Laurenz Albe @ 2026-06-20 21:53 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; Tom Lane <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]
On Fri, 2026-06-19 at 09:40 +0200, Hüseyin Demir wrote:
> Found a problem and fixed it quickly.
I think that we need something like this fix, because a failing
upgrade is a bug. For the same reason I think that the fix
should be backpatched.
I looked at your patch and found that the query you added doesn't
cover the important case where the grantor is a non-existing role
(that is, the original extension owner was dropped).
The grantor appears in SET SESSION AUTHORIZATION commands in the
dump, which make the restore and consequently the upgrade fail.
I suggest a query like this one:
SELECT pip.objoid, pip.classoid, pip.objsubid, pip.privtype,
NULLIF(
ARRAY(
SELECT elt FROM pg_catalog.unnest(pip.initprivs) AS elt
/* that is valid, that is, there is not ... */
WHERE NOT EXISTS (
/* ... a non-existing grantor ... */
SELECT 1 FROM pg_catalog.aclexplode(ARRAY[elt]) ace
WHERE NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_roles AS r1
WHERE r1.oid = ace.grantor
)
/* ... or a non-existing grantee that isn't 0 */
OR ace.grantee <> 0
AND NOT EXISTS (
SELECT 1 FROM pg_catalog.pg_roles AS r2
WHERE r2.oid = ace.grantee
)
)
),
ARRAY[]::pg_catalog.aclitem[]
) AS initprivs
FROM pg_catalog.pg_init_privs pip;
You see that I added some comments, because the query is almost
incomprehensible. I couldn't think of a more elegant solution.
I think that you also should add an extensive code comment that
explains why this hack is needed.
I am undecided if the regression test with the artificially created
broken initial privileges is a good idea or not. After all, we are
not testing the real thing here (for example, the test didn't catch
the omission described above).
I am attaching a test extension that I installed in a v14 database
to test your patch; perhaps you'll find it useful. It creates all
kinds of objects that have an ACL. Dropping the role that created
the extension leaves various junk entries in pg_init_privs that you
can use to test your patch.
Yours,
Laurenz Albe
Attachments:
[application/x-compressed-tar] ext.tgz (1.4K, ../../[email protected]/2-ext.tgz)
download
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-21 05:47 Laurenz Albe <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Laurenz Albe @ 2026-06-21 05:47 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; Tom Lane <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]
On Sat, 2026-06-20 at 23:53 +0200, I wrote:
> I think that we need something like this fix, because a failing
> upgrade is a bug. For the same reason I think that the fix
> should be backpatched.
>
> I looked at your patch and found that the query you added doesn't
> cover the important case where the grantor is a non-existing role
>
> I suggest a query like this one: [...]
Further testing shows that changing this query isn't enough.
There are three more places where pg_dump queries pg_init_privs
(in getAggregates, getFuncs and dumpTable).
So we'd have to use a similarly ugly query in all these places,
which doesn't seem particularly attractive and introduces
considerable code duplication.
One approach I can think of is to have a macro SAFE_INITPRIVS
that contains the ugly subquery and is used in all these places.
The other idea is to do some post-processing of the aclitems
found, but they are in string form and would need to get parsed
again, which doesn't look attractive either.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-22 05:44 Hüseyin Demir <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-22 05:44 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Hi,
> Further testing shows that changing this query isn't enough.
> There are three more places where pg_dump queries pg_init_privs
> (in getAggregates, getFuncs and dumpTable).
>
> So we'd have to use a similarly ugly query in all these places,
> which doesn't seem particularly attractive and introduces
> considerable code duplication.
>
> One approach I can think of is to have a macro SAFE_INITPRIVS
> that contains the ugly subquery and is used in all these places.
>
> The other idea is to do some post-processing of the aclitems
> found, but they are in string form and would need to get parsed
> again, which doesn't look attractive either.
Thanks for the feedback.
I created a simple v4 patch to cover your feedback
v4 applies the filter at all four sites where pg_dump queries pg_init_privs:
- getAggregates() — WHERE clause comparison
- getFuncs() — WHERE clause comparison
- getAdditionalACLs() — SELECT expression (object-level initprivs)
- PREPQUERY_GETCOLUMNACLS — SELECT expression (column-level initprivs,
objsubid != 0)
Secondly to avoid duplicating the multi-line subquery at every call
site, I introduced a SAFE_INITPRIVS(col) macro.
I tried to add more clean and detailed comments for future commits but
please let me know if you have additional feedback on it.
Regards,
Demir.
Attachments:
[application/octet-stream] v4-0001-pg_dump-skip-dangling-initprivs.patch (13.9K, ../../CAB5wL7ZuFwu2Sd-xmn-w5-BbrKk_Mu_1ubYow_qaqFGhJOiMXw@mail.gmail.com/2-v4-0001-pg_dump-skip-dangling-initprivs.patch)
download | inline diff:
From 912db0194bcc8f7094feb22d28a5f05e634dc9d2 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 or grantor 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.
A dangling grantee caused pg_dump to emit statements such as
GRANT EXECUTE ON FUNCTION foo() TO "87868";
where "87868" is a numeric OID — invalid SQL that fails on restore.
A dangling grantor caused pg_dump to emit
SET SESSION AUTHORIZATION "87868";
before the GRANT, which likewise fails on restore or pg_upgrade.
There is a second, more subtle bug: getAggregates() and getFuncs() use
pip.initprivs unfiltered in their WHERE clause to decide whether an
object needs ACL output at all. When all initprivs entries for an
object are dangling and the live proacl is NULL, the comparison
p.proacl IS DISTINCT FROM pip.initprivs
evaluates to true (NULL IS DISTINCT FROM <non-NULL array>), causing the
object to be selected for ACL output. After getAdditionalACLs() returns
NULL for the now-empty initprivs, dumpACL() computes the delta from
acldefault to NULL and emits a spurious REVOKE that breaks restore.
Fix by filtering each aclitem whose grantor or non-PUBLIC grantee OID
does not appear in pg_authid, and by applying that filter at all four
sites where pg_dump queries pg_init_privs:
- getAggregates(): WHERE clause comparison
- getFuncs(): WHERE clause comparison
- getAdditionalACLs(): SELECT expression (object-level initprivs)
- PREPQUERY_GETCOLUMNACLS: SELECT expression (column-level initprivs,
objsubid != 0)
To avoid duplicating the multi-line subquery at every call site, define
a SAFE_INITPRIVS(col) macro that expands to the filtering expression via
C adjacent string literal concatenation.
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.
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 | 58 ++++++-
.../t/008_pg_dump_dangling_initprivs.pl | 161 ++++++++++++++++++
2 files changed, 211 insertions(+), 8 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..7190691ca77 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -237,6 +237,44 @@ static int nsequences = 0;
fmtQualifiedId((obj)->dobj.namespace->dobj.name, \
(obj)->dobj.name)
+/*
+ * SQL fragment that strips dangling role OIDs from a pg_init_privs aclitem[]
+ * column. An entry is dangling when its grantor or non-PUBLIC grantee no
+ * longer exists in pg_authid. Before commit 53428740391 pg_shdepend did not
+ * record dependencies for pg_init_privs, so DROP ROLE could silently leave
+ * behind such entries; cross-cluster restores can produce the same situation
+ * on modern releases. Passing dangling entries through causes pg_dump to
+ * emit "SET SESSION AUTHORIZATION <oid>" or "GRANT ... TO <oid>" with a
+ * numeric OID as role name, which fails on restore. We silently drop any
+ * entry whose grantor or grantee is missing; if all entries are removed the
+ * result is NULL.
+ *
+ * The argument must be a compile-time string literal naming the aclitem[]
+ * column (always "pip.initprivs" in practice). C adjacent string literal
+ * concatenation splices the expansion into the surrounding query string.
+ */
+#define SAFE_INITPRIVS(col) \
+ "NULLIF(\n" \
+ " ARRAY(\n" \
+ " SELECT elt FROM pg_catalog.unnest(" col ") AS elt\n" \
+ " /* keep only valid entries, i.e. those without... */\n" \
+ " WHERE NOT EXISTS (\n" \
+ " SELECT 1 FROM pg_catalog.aclexplode(ARRAY[elt]) ace\n" \
+ " /* ...a non-existing grantor... */\n" \
+ " WHERE NOT EXISTS (\n" \
+ " SELECT 1 FROM pg_catalog.pg_roles AS r1\n" \
+ " WHERE r1.oid = ace.grantor\n" \
+ " )\n" \
+ " /* ...or a non-existing grantee (0 means PUBLIC) */\n" \
+ " OR ace.grantee <> 0\n" \
+ " AND NOT EXISTS (\n" \
+ " SELECT 1 FROM pg_catalog.pg_roles AS r2\n" \
+ " WHERE r2.oid = ace.grantee\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,
@@ -6906,7 +6944,8 @@ getAggregates(Archive *fout)
"p.pronamespace != "
"(SELECT oid FROM pg_namespace "
"WHERE nspname = 'pg_catalog') OR "
- "p.proacl IS DISTINCT FROM pip.initprivs",
+ "p.proacl IS DISTINCT FROM "
+ SAFE_INITPRIVS("pip.initprivs"),
agg_check);
if (dopt->binary_upgrade)
appendPQExpBufferStr(query,
@@ -7088,7 +7127,8 @@ getFuncs(Archive *fout)
"refclassid = 'pg_extension'::regclass AND "
"deptype = 'e')");
appendPQExpBufferStr(query,
- "\n OR p.proacl IS DISTINCT FROM pip.initprivs");
+ "\n OR p.proacl IS DISTINCT FROM "
+ SAFE_INITPRIVS("pip.initprivs"));
appendPQExpBufferChar(query, ')');
}
else
@@ -10900,8 +10940,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);
@@ -17097,10 +17138,11 @@ dumpTable(Archive *fout, const TableInfo *tbinfo)
* and risk to avoid hard-wiring that knowledge here.
*/
appendPQExpBufferStr(query,
- "SELECT at.attname, "
- "at.attacl, "
- "'{}' AS acldefault, "
- "pip.privtype, pip.initprivs "
+ "SELECT at.attname,\n"
+ "at.attacl,\n"
+ "'{}' AS acldefault,\n"
+ "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..a0ba3650989
--- /dev/null
+++ b/src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl
@@ -0,0 +1,161 @@
+# 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
+# or SET SESSION AUTHORIZATION statements with numeric OIDs as role names.
+#
+# Four cases are tested:
+# 1. Dangling grantee -- the role that received the privilege was dropped.
+# 2. Dangling grantor -- the role that granted the privilege was dropped
+# (this caused broken SET SESSION AUTHORIZATION output).
+# 3. Column-level ACL -- same dangling-grantee scenario but for a column
+# initprivs entry (objsubid != 0).
+# 4. Spurious REVOKE -- a pg_catalog function whose only initprivs entry is
+# all-dangling; without filtering the WHERE clause in
+# getFuncs() the object is incorrectly selected for ACL
+# output and dumpACL() emits a spurious REVOKE.
+
+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');
+
+# Set up all four dangling cases in one session. We cannot use normal
+# DROP ROLE because pg_shdepend would block it, so we delete roles directly
+# from pg_authid with allow_system_table_mods.
+$node->safe_psql(
+ 'regress_dangling',
+ q{
+SET allow_system_table_mods = true;
+
+-- Case 1: dangling grantee.
+-- ghost_grantee received EXECUTE; after deletion its OID becomes dangling.
+CREATE ROLE ghost_grantee;
+
+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_catalog.pg_class WHERE relname = 'pg_proc'),
+ 0,
+ 'e',
+ ARRAY[('ghost_grantee=X/' || current_user)::aclitem]
+FROM pg_catalog.pg_proc p
+WHERE p.proname = 'test_func_grantee'
+AND p.pronamespace = 'public'::regnamespace;
+
+-- Case 2: dangling grantor.
+-- ghost_grantor is the grantor; after deletion its OID appears in
+-- SET SESSION AUTHORIZATION output, causing restore failures.
+CREATE ROLE ghost_grantor;
+
+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_catalog.pg_class WHERE relname = 'pg_proc'),
+ 0,
+ 'e',
+ ARRAY[('=X/' || 'ghost_grantor')::aclitem]
+FROM pg_catalog.pg_proc p
+WHERE p.proname = 'test_func_grantor'
+AND p.pronamespace = 'public'::regnamespace;
+
+-- Case 3: column-level dangling grantee (objsubid != 0).
+-- ghost_col_role received SELECT on a column; after deletion its OID
+-- would appear in a GRANT ... ON TABLE t (col) statement.
+CREATE ROLE ghost_col_role;
+
+CREATE TABLE public.test_tbl (col1 int);
+
+INSERT INTO pg_init_privs (objoid, classoid, objsubid, privtype, initprivs)
+SELECT t.oid,
+ (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'pg_class'),
+ 1,
+ 'e',
+ ARRAY[('ghost_col_role=r/' || current_user)::aclitem]
+FROM pg_catalog.pg_class t
+WHERE t.relname = 'test_tbl'
+AND t.relnamespace = 'public'::regnamespace;
+
+-- Case 4: spurious REVOKE caused by all-dangling initprivs and NULL proacl.
+-- ghost_catalog_role is the only grantee; after deletion the unfiltered WHERE
+-- clause in getFuncs() would select int4 for ACL output and dumpACL() would
+-- emit a spurious "REVOKE EXECUTE FROM PUBLIC" that breaks restore.
+CREATE ROLE ghost_catalog_role;
+
+INSERT INTO pg_init_privs (objoid, classoid, objsubid, privtype, initprivs)
+SELECT p.oid,
+ (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'pg_proc'),
+ 0,
+ 'i',
+ ARRAY[('ghost_catalog_role=X/' || current_user)::aclitem]
+FROM pg_catalog.pg_proc p
+WHERE p.proname = 'int4'
+AND p.pronamespace = (SELECT oid FROM pg_catalog.pg_namespace
+ WHERE nspname = 'pg_catalog')
+LIMIT 1;
+
+-- Remove all ghost roles, leaving dangling OIDs in pg_init_privs.
+DELETE FROM pg_catalog.pg_authid
+WHERE rolname IN ('ghost_grantee', 'ghost_grantor', 'ghost_col_role',
+ 'ghost_catalog_role');
+});
+
+my $tempdir = PostgreSQL::Test::Utils::tempdir;
+my $dump_file = "$tempdir/dangling.sql";
+
+# pg_dump must succeed even though pg_init_privs has dangling role OIDs.
+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 user-defined objects must still appear in the dump.
+like($dump, qr/CREATE FUNCTION public\.test_func_grantee/,
+ 'function with dangling grantee is present in dump');
+like($dump, qr/CREATE FUNCTION public\.test_func_grantor/,
+ 'function with dangling grantor is present in dump');
+like($dump, qr/CREATE TABLE public\.test_tbl/,
+ 'table with dangling column initprivs is present in dump');
+
+# No 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');
+unlike($dump, qr/SESSION AUTHORIZATION\s+"[0-9]+"/,
+ 'no SET SESSION AUTHORIZATION with numeric OID as role name');
+
+# No GRANT at all for the dangling-grantee function.
+unlike($dump, qr/GRANT\b.*\btest_func_grantee/,
+ 'no GRANT for test_func_grantee when all initprivs entries are dangling');
+
+# No GRANT at all for the dangling-grantor function.
+unlike($dump, qr/GRANT\b.*\btest_func_grantor/,
+ 'no GRANT for test_func_grantor when all initprivs entries are dangling');
+
+# No column GRANT for the dangling column initprivs.
+unlike($dump, qr/GRANT\b.*\btest_tbl\b.*\(col1\)/,
+ 'no column GRANT for test_tbl when column initprivs entries are dangling');
+
+# No spurious REVOKE for the catalog function whose only initprivs entry was
+# all-dangling. Without the WHERE clause filter a delta from acldefault to
+# NULL would have caused dumpACL() to emit "REVOKE EXECUTE FROM PUBLIC".
+unlike($dump, qr/REVOKE\b.*\bint4\s*\(/,
+ 'no spurious REVOKE for int4 when all initprivs entries are dangling');
+
+done_testing();
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-22 06:07 Hüseyin Demir <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-22 06:07 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
>
> > Further testing shows that changing this query isn't enough.
> > There are three more places where pg_dump queries pg_init_privs
> > (in getAggregates, getFuncs and dumpTable).
> >
> > So we'd have to use a similarly ugly query in all these places,
> > which doesn't seem particularly attractive and introduces
> > considerable code duplication.
> >
> > One approach I can think of is to have a macro SAFE_INITPRIVS
> > that contains the ugly subquery and is used in all these places.
> >
> > The other idea is to do some post-processing of the aclitems
> > found, but they are in string form and would need to get parsed
> > again, which doesn't look attractive either.
>
>
> v4 applies the filter at all four sites where pg_dump queries pg_init_privs:
> - getAggregates() — WHERE clause comparison
> - getFuncs() — WHERE clause comparison
> - getAdditionalACLs() — SELECT expression (object-level initprivs)
> - PREPQUERY_GETCOLUMNACLS — SELECT expression (column-level initprivs,
> objsubid != 0)
>
> Secondly to avoid duplicating the multi-line subquery at every call
> site, I introduced a SAFE_INITPRIVS(col) macro.
One question from my side: can't we use function instead of macro ?
Would it be more accurate for future readers ?
Wanted to ask your opinion besides the v4 patch.
Regards,
Demir.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-22 17:23 Laurenz Albe <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Laurenz Albe @ 2026-06-22 17:23 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
On Mon, 2026-06-22 at 08:07 +0200, Hüseyin Demir wrote:
> > v4 applies the filter at all four sites where pg_dump queries pg_init_privs:
> > - getAggregates() — WHERE clause comparison
> > - getFuncs() — WHERE clause comparison
> > - getAdditionalACLs() — SELECT expression (object-level initprivs)
> > - PREPQUERY_GETCOLUMNACLS — SELECT expression (column-level initprivs,
> > objsubid != 0)
> >
> > Secondly to avoid duplicating the multi-line subquery at every call
> > site, I introduced a SAFE_INITPRIVS(col) macro.
Great, that's just what I had in mind.
> One question from my side: can't we use function instead of macro ?
> Would it be more accurate for future readers ?
That would work too, but if you do it in C rather than with the
preprocessor, you have to deal with string manipulation, which
will makes the patch more complicated. I think it is better the
way it is now.
I'll mark the patch "ready for committer".
Since there have been very few reports of this problem, the question
remains if we need this patch at all, or of it should be backpatched.
My opinion is that it should; every upgrade or restore failure is
one too many.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-22 18:30 Tom Lane <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Tom Lane @ 2026-06-22 18:30 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Hüseyin Demir <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Laurenz Albe <[email protected]> writes:
> Since there have been very few reports of this problem, the question
> remains if we need this patch at all, or of it should be backpatched.
> My opinion is that it should; every upgrade or restore failure is
> one too many.
I have a more pressing concern: has any performance testing been
done on this? It looks like it'd be absolutely catastrophic for
pg_dump performance on databases with lots of objects.
The implementation direction I'd been vaguely imagining was for
pg_dump's buildACLCommands() to drop any AclItems that contain
dangling role references (ie, numeric OIDs where a role name
should be). If the given role name contains any non-digit
characters then it's certainly not dangling, so most of the time
this'd be a very cheap check. However, if somebody does
CREATE USER "007";
GRANT ALL ON TABLE mi6_operations TO "007";
we mustn't get fooled by that. The backend is doing us no favors by
not making numeric OIDs visibly different from all-digit role names
in AclItems. In HEAD I'd advocate fixing that on the server side
(as attached), but we can't assume that a back-branch server has such
a fix. What we could do with an old server is issue a query (once per
pg_dump run) to collect all the valid all-digit role names, which
should surely be a short list in most databases, and then filter
against that within buildACLCommands().
regards, tom lane
Attachments:
[text/x-diff] quote-all-digit-role-names-in-aclitemout.patch (451B, ../../[email protected]/2-quote-all-digit-role-names-in-aclitemout.patch)
download | inline diff:
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index 01caa12eca7..84803351f18 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -235,6 +235,10 @@ putid(char *p, const char *s)
break;
}
}
+ /* If name is all-digits, quote it to distinguish from lookup failure */
+ if (safe && strspn(s, "0123456789") == strlen(s))
+ safe = false;
+
if (!safe)
*p++ = '"';
for (src = s; *src; src++)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-23 08:32 Hüseyin Demir <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-23 08:32 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
> I have a more pressing concern: has any performance testing been
> done on this? It looks like it'd be absolutely catastrophic for
> pg_dump performance on databases with lots of objects.
>
> The implementation direction I'd been vaguely imagining was for
> pg_dump's buildACLCommands() to drop any AclItems that contain
> dangling role references (ie, numeric OIDs where a role name
> should be). If the given role name contains any non-digit
> characters then it's certainly not dangling, so most of the time
> this'd be a very cheap check. However, if somebody does
>
> CREATE USER "007";
> GRANT ALL ON TABLE mi6_operations TO "007";
>
> we mustn't get fooled by that. The backend is doing us no favors by
> not making numeric OIDs visibly different from all-digit role names
> in AclItems. In HEAD I'd advocate fixing that on the server side
> (as attached), but we can't assume that a back-branch server has such
> a fix. What we could do with an old server is issue a query (once per
> pg_dump run) to collect all the valid all-digit role names, which
> should surely be a short list in most databases, and then filter
> against that within buildACLCommands().
I see the approach and it's a valid concern. I can create a v5 to
comply with your suggestion and can create a new patch to be applied
to the current HEAD.
After preparing the v5 I'm going to create a new CF to be patched to
HEAD since this fix will be backpatched.
Regards,
Demir.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-24 06:14 Hüseyin Demir <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-24 06:14 UTC (permalink / raw)
To: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]
Hi,
You can see the attached v5 patch. Basically, v5 solves the problem by
adding dangling-role filtering directly inside buildACLCommands(). A
role name that consists entirely of digits is potentially a dangling
OID reference and it resolves by querying pg_authid once at the start
of the dump for any legitimate all-digit role names. The sorted list
is passed as new digitRoles/nDigitRoles parameters to
buildACLCommands(), which skips any REVOKE or GRANT item whose grantee
or grantor matches a dangling reference.
I defined a static called is_dangling_role_ref in dumputils.c and
introduced two new parameters to buildACLCommands to eliminate the
dangling grantee or grantor objects.
When it comes to patching the HEAD I'm going to create and submit
another small patch.
Regards,
Demir.
Hüseyin Demir <[email protected]>, 23 Haz 2026 Sal, 10:32
tarihinde şunu yazdı:
>
> > I have a more pressing concern: has any performance testing been
> > done on this? It looks like it'd be absolutely catastrophic for
> > pg_dump performance on databases with lots of objects.
> >
> > The implementation direction I'd been vaguely imagining was for
> > pg_dump's buildACLCommands() to drop any AclItems that contain
> > dangling role references (ie, numeric OIDs where a role name
> > should be). If the given role name contains any non-digit
> > characters then it's certainly not dangling, so most of the time
> > this'd be a very cheap check. However, if somebody does
> >
> > CREATE USER "007";
> > GRANT ALL ON TABLE mi6_operations TO "007";
> >
> > we mustn't get fooled by that. The backend is doing us no favors by
> > not making numeric OIDs visibly different from all-digit role names
> > in AclItems. In HEAD I'd advocate fixing that on the server side
> > (as attached), but we can't assume that a back-branch server has such
> > a fix. What we could do with an old server is issue a query (once per
> > pg_dump run) to collect all the valid all-digit role names, which
> > should surely be a short list in most databases, and then filter
> > against that within buildACLCommands().
>
> I see the approach and it's a valid concern. I can create a v5 to
> comply with your suggestion and can create a new patch to be applied
> to the current HEAD.
>
> After preparing the v5 I'm going to create a new CF to be patched to
> HEAD since this fix will be backpatched.
>
> Regards,
> Demir.
Attachments:
[application/octet-stream] v5-0001-pg_dump-skip-dangling-initprivs.patch (17.0K, ../../CAB5wL7YFeL0VP7EZu6wtWx=UhXuDp3dTBVJwFvSRzBi7qNscfQ@mail.gmail.com/2-v5-0001-pg_dump-skip-dangling-initprivs.patch)
download | inline diff:
From 7279eaac4416c9952616b8908bc1f8286764a03f Mon Sep 17 00:00:00 2001
From: Huseyin Demir <[email protected]>
Date: Wed, 24 Jun 2026 07:38:51 +0200
Subject: [PATCH] pg_dump: skip pg_init_privs entries for non-existent roles
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 or grantor OID no longer exists in
pg_authid. Cross-cluster restores can produce the same situation even
on modern releases.
Dangling entries caused pg_dump to emit invalid SQL such as
"GRANT ... TO "87868"" or "SET SESSION AUTHORIZATION "87868"" with
a numeric OID as role name, which fails on restore or pg_upgrade.
A further issue: getAggregates() and getFuncs() use pip.initprivs
unfiltered in their WHERE clause. When all initprivs entries for an
object are dangling and the live proacl is NULL, the comparison
"p.proacl IS DISTINCT FROM pip.initprivs" evaluates to true, causing
the object to be selected for ACL output. dumpACL() then computes the
delta from acldefault to NULL and emits a spurious REVOKE that breaks
restore.
Fix by adding dangling-role filtering directly inside buildACLCommands().
A role name that consists entirely of digits is potentially a dangling
OID reference; we resolve the ambiguity by querying pg_authid once at
the start of the dump for any legitimate all-digit role names (normally
none exist). The sorted list is passed as new digitRoles/nDigitRoles
parameters to buildACLCommands(), which skips any REVOKE or GRANT item
whose grantee or grantor matches a dangling reference.
This approach adds zero overhead in the common case (most role names
contain non-digit characters and are rejected at the first character),
and confines the fix to the single function that generates all ACL
output.
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/dumputils.c | 52 +++++-
src/bin/pg_dump/dumputils.h | 3 +-
src/bin/pg_dump/pg_dump.c | 29 +++-
src/bin/pg_dump/pg_dumpall.c | 6 +-
.../t/008_pg_dump_dangling_initprivs.pl | 162 ++++++++++++++++++
5 files changed, 245 insertions(+), 7 deletions(-)
create mode 100644 src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index dfb1f603a43..956e1597717 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -28,6 +28,8 @@ static bool parseAclItem(const char *item, const char *type,
PQExpBuffer grantee, PQExpBuffer grantor,
PQExpBuffer privs, PQExpBuffer privswgo);
static char *dequoteAclUserName(PQExpBuffer output, char *input);
+static bool is_dangling_role_ref(const char *name,
+ char **digitRoles, int nDigitRoles);
static void AddAcl(PQExpBuffer aclbuf, const char *keyword,
const char *subname);
@@ -69,6 +71,40 @@ sanitize_line(const char *str, bool want_hyphen)
}
+/*
+ * is_dangling_role_ref
+ * Return true if 'name' looks like a dangling numeric OID reference.
+ *
+ * aclitemout() falls back to printing a missing role's OID as bare decimal
+ * digits when it cannot find the role in pg_authid. Any name that is not
+ * all-decimal is certainly a real role name. For all-decimal names we
+ * resolve the ambiguity against digitRoles[], a sorted array of rolnames
+ * from pg_authid that consist entirely of digits (fetched once per run).
+ * If digitRoles is NULL we conservatively assume all-digit names are valid.
+ * The empty string means PUBLIC and is never dangling.
+ */
+static bool
+is_dangling_role_ref(const char *name, char **digitRoles, int nDigitRoles)
+{
+ const char *p;
+
+ /* Empty string means PUBLIC -- never dangling */
+ if (*name == '\0')
+ return false;
+
+ /* Fast path: any non-digit character means it is a real role name */
+ for (p = name; *p; p++)
+ if (!isdigit((unsigned char) *p))
+ return false;
+
+ /* All-digit name: resolve ambiguity against the known-valid list */
+ if (digitRoles == NULL)
+ return false; /* no list supplied, assume valid */
+
+ return bsearch(&name, digitRoles, nDigitRoles,
+ sizeof(char *), pg_qsort_strcmp) == NULL;
+}
+
/*
* Build GRANT/REVOKE command(s) for an object.
*
@@ -104,7 +140,8 @@ bool
buildACLCommands(const char *name, const char *subname, const char *nspname,
const char *type, const char *acls, const char *baseacls,
const char *owner, const char *prefix, int remoteVersion,
- PQExpBuffer sql)
+ PQExpBuffer sql,
+ char **digitRoles, int nDigitRoles)
{
bool ok = true;
char **aclitems = NULL;
@@ -218,6 +255,11 @@ buildACLCommands(const char *name, const char *subname, const char *nspname,
break;
}
+ /* skip entries whose grantee or grantor no longer exists */
+ if (is_dangling_role_ref(grantee->data, digitRoles, nDigitRoles) ||
+ is_dangling_role_ref(grantor->data, digitRoles, nDigitRoles))
+ continue;
+
if (privs->len > 0)
{
appendPQExpBuffer(firstsql, "%sREVOKE %s ON %s ",
@@ -263,6 +305,11 @@ buildACLCommands(const char *name, const char *subname, const char *nspname,
if (parseAclItem(grantitems[i], type, name, subname, remoteVersion,
grantee, grantor, privs, privswgo))
{
+ /* skip entries whose grantee or grantor no longer exists */
+ if (is_dangling_role_ref(grantee->data, digitRoles, nDigitRoles) ||
+ is_dangling_role_ref(grantor->data, digitRoles, nDigitRoles))
+ continue;
+
/*
* If the grantor isn't the owner, we'll need to use SET SESSION
* AUTHORIZATION to become the grantor. Issue the SET/RESET only
@@ -390,7 +437,8 @@ buildDefaultACLCommands(const char *type, const char *nspname,
*/
if (!buildACLCommands("", NULL, NULL, type,
acls, acldefault, owner,
- prefix->data, remoteVersion, sql))
+ prefix->data, remoteVersion, sql,
+ NULL, 0))
{
destroyPQExpBuffer(prefix);
return false;
diff --git a/src/bin/pg_dump/dumputils.h b/src/bin/pg_dump/dumputils.h
index d231ce1d654..f7aeebb9099 100644
--- a/src/bin/pg_dump/dumputils.h
+++ b/src/bin/pg_dump/dumputils.h
@@ -41,7 +41,8 @@ extern char *sanitize_line(const char *str, bool want_hyphen);
extern bool buildACLCommands(const char *name, const char *subname, const char *nspname,
const char *type, const char *acls, const char *baseacls,
const char *owner, const char *prefix, int remoteVersion,
- PQExpBuffer sql);
+ PQExpBuffer sql,
+ char **digitRoles, int nDigitRoles);
extern bool buildDefaultACLCommands(const char *type, const char *nspname,
const char *acls, const char *acldefault,
const char *owner,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index a0f7f8e2168..6b4f6984430 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -153,6 +153,10 @@ static bool dosync = true; /* Issue fsync() to make dump durable on disk. */
static Oid g_last_builtin_oid; /* value of the last builtin oid */
+/* Sorted list of all-digit role names fetched once from pg_authid */
+static char **digitRoleNames = NULL;
+static int nDigitRoleNames = 0;
+
/* The specified names/patterns should to match at least one entity */
static int strict_names = 0;
@@ -10899,6 +10903,25 @@ getAdditionalACLs(Archive *fout)
/* Fetch initial-privileges data */
if (fout->remoteVersion >= 90600)
{
+ PGresult *rolres;
+
+ /*
+ * Collect all role names that consist entirely of digits. We need
+ * this to distinguish legitimate all-digit role names (e.g. "007")
+ * from dangling numeric OIDs that aclitemout() prints for missing
+ * roles. This list is normally empty; it is stored in file-scope
+ * statics so that buildACLCommands() can use it throughout the dump.
+ */
+ printfPQExpBuffer(query,
+ "SELECT rolname FROM pg_catalog.pg_authid "
+ "WHERE rolname ~ '^[0-9]+$' ORDER BY 1");
+ rolres = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+ nDigitRoleNames = PQntuples(rolres);
+ digitRoleNames = pg_malloc_array(char *, nDigitRoleNames);
+ for (i = 0; i < nDigitRoleNames; i++)
+ digitRoleNames[i] = pg_strdup(PQgetvalue(rolres, i, 0));
+ PQclear(rolres);
+
printfPQExpBuffer(query,
"SELECT objoid, classoid, objsubid, privtype, initprivs "
"FROM pg_init_privs");
@@ -16610,7 +16633,8 @@ dumpACL(Archive *fout, DumpId objDumpId, DumpId altDumpId,
appendPQExpBufferStr(sql, "SELECT pg_catalog.binary_upgrade_set_record_init_privs(true);\n");
if (!buildACLCommands(name, subname, nspname, type,
initprivs, acldefault, owner,
- "", fout->remoteVersion, sql))
+ "", fout->remoteVersion, sql,
+ digitRoleNames, nDigitRoleNames))
pg_fatal("could not parse initial ACL list (%s) or default (%s) for object \"%s\" (%s)",
initprivs, acldefault, name, type);
appendPQExpBufferStr(sql, "SELECT pg_catalog.binary_upgrade_set_record_init_privs(false);\n");
@@ -16635,7 +16659,8 @@ dumpACL(Archive *fout, DumpId objDumpId, DumpId altDumpId,
if (!buildACLCommands(name, subname, nspname, type,
acls, baseacls, owner,
- "", fout->remoteVersion, sql))
+ "", fout->remoteVersion, sql,
+ digitRoleNames, nDigitRoleNames))
pg_fatal("could not parse ACL list (%s) or default (%s) for object \"%s\" (%s)",
acls, baseacls, name, type);
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index c1f43113c53..59264b85a1b 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -1575,7 +1575,8 @@ dumpRoleGUCPrivs(PGconn *conn)
if (!buildACLCommands(fparname, NULL, NULL, "PARAMETER",
paracl, acldefault,
- parowner, "", server_version, buf))
+ parowner, "", server_version, buf,
+ NULL, 0))
{
pg_log_error("could not parse ACL list (%s) for parameter \"%s\"",
paracl, parname);
@@ -1738,7 +1739,8 @@ dumpTablespaces(PGconn *conn)
if (!skip_acls &&
!buildACLCommands(fspcname, NULL, NULL, "TABLESPACE",
spcacl, acldefault,
- spcowner, "", server_version, buf))
+ spcowner, "", server_version, buf,
+ NULL, 0))
{
pg_log_error("could not parse ACL list (%s) for tablespace \"%s\"",
spcacl, spcname);
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..dcf33a4af05
--- /dev/null
+++ b/src/bin/pg_dump/t/008_pg_dump_dangling_initprivs.pl
@@ -0,0 +1,162 @@
+# Copyright (c) 2021-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
+# or SET SESSION AUTHORIZATION statements with numeric OIDs as role names.
+#
+# Five cases are tested:
+# 1. Dangling grantee -- the role that received the privilege was dropped.
+# 2. Dangling grantor -- the role that granted the privilege was dropped.
+# 3. Column-level dangling grantee (objsubid != 0).
+# 4. Spurious REVOKE -- a pg_catalog function whose only initprivs entry is
+# all-dangling; without filtering, the object is incorrectly selected for
+# ACL output and dumpACL() emits a spurious REVOKE.
+# 5. Legitimate all-digit role -- a role named "007" must NOT be filtered.
+
+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');
+
+$node->safe_psql(
+ 'regress_dangling',
+ q{
+SET allow_system_table_mods = true;
+
+-- Case 1: dangling grantee.
+CREATE ROLE ghost_grantee;
+
+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_catalog.pg_class WHERE relname = 'pg_proc'),
+ 0,
+ 'e',
+ ARRAY[('ghost_grantee=X/' || current_user)::aclitem]
+FROM pg_catalog.pg_proc p
+WHERE p.proname = 'test_func_grantee'
+AND p.pronamespace = 'public'::regnamespace;
+
+-- Case 2: dangling grantor.
+CREATE ROLE ghost_grantor;
+
+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_catalog.pg_class WHERE relname = 'pg_proc'),
+ 0,
+ 'e',
+ ARRAY[('=X/' || 'ghost_grantor')::aclitem]
+FROM pg_catalog.pg_proc p
+WHERE p.proname = 'test_func_grantor'
+AND p.pronamespace = 'public'::regnamespace;
+
+-- Case 3: column-level dangling grantee (objsubid != 0).
+CREATE ROLE ghost_col_role;
+
+CREATE TABLE public.test_tbl (col1 int);
+
+INSERT INTO pg_init_privs (objoid, classoid, objsubid, privtype, initprivs)
+SELECT t.oid,
+ (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'pg_class'),
+ 1,
+ 'e',
+ ARRAY[('ghost_col_role=r/' || current_user)::aclitem]
+FROM pg_catalog.pg_class t
+WHERE t.relname = 'test_tbl'
+AND t.relnamespace = 'public'::regnamespace;
+
+-- Case 4: spurious REVOKE caused by all-dangling initprivs and NULL proacl.
+CREATE ROLE ghost_catalog_role;
+
+INSERT INTO pg_init_privs (objoid, classoid, objsubid, privtype, initprivs)
+SELECT p.oid,
+ (SELECT oid FROM pg_catalog.pg_class WHERE relname = 'pg_proc'),
+ 0,
+ 'i',
+ ARRAY[('ghost_catalog_role=X/' || current_user)::aclitem]
+FROM pg_catalog.pg_proc p
+WHERE p.proname = 'int4'
+AND p.pronamespace = (SELECT oid FROM pg_catalog.pg_namespace
+ WHERE nspname = 'pg_catalog')
+LIMIT 1;
+
+-- Case 5: legitimate all-digit role name must be preserved.
+-- The role "007" exists and receives a GRANT. The initprivs entry also
+-- references "007" (as grantee). filterDanglingAclItems must NOT remove
+-- this entry. We set up initprivs with a DIFFERENT content than proacl
+-- so that the GRANT to "007" shows up in the delta output.
+CREATE ROLE "007";
+
+CREATE FUNCTION public.test_func_007() RETURNS int LANGUAGE sql AS 'SELECT 7';
+GRANT EXECUTE ON FUNCTION public.test_func_007() TO "007";
+
+-- Remove ghost roles, leaving dangling OIDs in pg_init_privs.
+-- Do NOT remove "007" -- it is a legitimate role.
+DELETE FROM pg_catalog.pg_authid
+WHERE rolname IN ('ghost_grantee', 'ghost_grantor', 'ghost_col_role',
+ 'ghost_catalog_role');
+});
+
+my $tempdir = PostgreSQL::Test::Utils::tempdir;
+my $dump_file = "$tempdir/dangling.sql";
+
+# pg_dump must succeed even though pg_init_privs has dangling role OIDs.
+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 user-defined objects must still appear in the dump.
+like($dump, qr/CREATE FUNCTION public\.test_func_grantee/,
+ 'function with dangling grantee is present in dump');
+like($dump, qr/CREATE FUNCTION public\.test_func_grantor/,
+ 'function with dangling grantor is present in dump');
+like($dump, qr/CREATE TABLE public\.test_tbl/,
+ 'table with dangling column initprivs is present in dump');
+like($dump, qr/CREATE FUNCTION public\.test_func_007/,
+ 'function for legitimate all-digit role is present in dump');
+
+# No statement should reference a bare numeric OID as a role name.
+unlike($dump, qr/SESSION AUTHORIZATION\s+"[0-9]+"/,
+ 'no SET SESSION AUTHORIZATION with numeric OID as role name');
+
+# No GRANT at all for the dangling-grantee function.
+unlike($dump, qr/GRANT\b.*\btest_func_grantee/,
+ 'no GRANT for test_func_grantee when all initprivs entries are dangling');
+
+# No GRANT at all for the dangling-grantor function.
+unlike($dump, qr/GRANT\b.*\btest_func_grantor/,
+ 'no GRANT for test_func_grantor when all initprivs entries are dangling');
+
+# No column GRANT for the dangling column initprivs.
+unlike($dump, qr/GRANT\b.*\btest_tbl\b.*\(col1\)/,
+ 'no column GRANT for test_tbl when column initprivs entries are dangling');
+
+# No spurious REVOKE for the catalog function whose only initprivs entry was
+# all-dangling.
+unlike($dump, qr/REVOKE\b.*\bint4\s*\(/,
+ 'no spurious REVOKE for int4 when all initprivs entries are dangling');
+
+# Case 5: the legitimate all-digit role "007" MUST still get its GRANT.
+like($dump, qr/GRANT\b.*\btest_func_007\b.*TO\s+"007"/,
+ 'GRANT to legitimate all-digit role "007" is preserved');
+
+done_testing();
--
2.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-24 09:56 Laurenz Albe <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 2 replies; 28+ messages in thread
From: Laurenz Albe @ 2026-06-24 09:56 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; Tom Lane <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; [email protected]
On Wed, 2026-06-24 at 08:14 +0200, Hüseyin Demir wrote:
> You can see the attached v5 patch. Basically, v5 solves the problem by
> adding dangling-role filtering directly inside buildACLCommands(). A
> role name that consists entirely of digits is potentially a dangling
> OID reference and it resolves by querying pg_authid once at the start
> of the dump for any legitimate all-digit role names.
The patch looks good and passes my tests.
It fails the regression tests on my system with
pg_dump: error: query failed: ERROR: permission denied for table pg_authid
I think you should use pg_roles rather than pg_authid, so that it
remains possible to use pg_dump with a non-superuser.
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
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-24 12:19 Hüseyin Demir <[email protected]>
parent: Laurenz Albe <[email protected]>
1 sibling, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-24 12:19 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [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
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-24 14:57 Tom Lane <[email protected]>
parent: Laurenz Albe <[email protected]>
1 sibling, 1 reply; 28+ messages in thread
From: Tom Lane @ 2026-06-24 14:57 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Hüseyin Demir <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Laurenz Albe <[email protected]> writes:
> 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.
That is a good point, but I would put the blame on aclitemout: in such
a case it's entirely impossible for pg_dump to distinguish whether an
apparently all-numeric role name in an ACL item is the valid role or a
dangling OID.
I was tempted yesterday to propose a simpler solution in which
we back-patch the putid() fix I showed earlier, and just change
dumputils.c to drop ACLs that have unquoted all-numeric grantees.
(If the grantor part is a dangling OID, we could omit GRANTED BY, as
we did recently for role grants.) Now the problem with this is that
if you have a case like my "007" example, you're going to lose some
grants if you dump with an updated pg_dump from a not-updated server.
That cure is very likely worse than the disease.
So what I'm thinking today is we apply the putid() fix only in HEAD,
and make dumputils.c ignore unquoted all-numeric roles only if
server version >= 19. This means we don't have a fix for the actually
known problems with old server versions, which is kind of sad, but
given Laurenz's point I don't think a reliable fix is possible with
an unpatched server.
An alternative answer is to back-patch the putid() fix and teach
dumputils.c to consider the server minor version when deciding whether
to reject unquoted all-numeric roles. We don't typically make pg_dump
pay attention to minor versions, but it would provide a pathway for
users to deal with this problem: if you've got dangling grants then
update the old server before dumping.
regards, tom lane
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-25 07:55 Hüseyin Demir <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 2 replies; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-25 07:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Hi,
> So what I'm thinking today is we apply the putid() fix only in HEAD,
> and make dumputils.c ignore unquoted all-numeric roles only if
> server version >= 19. This means we don't have a fix for the actually
> known problems with old server versions, which is kind of sad, but
> given Laurenz's point I don't think a reliable fix is possible with
> an unpatched server.
> An alternative answer is to back-patch the putid() fix and teach
> dumputils.c to consider the server minor version when deciding whether
> to reject unquoted all-numeric roles. We don't typically make pg_dump
> pay attention to minor versions, but it would provide a pathway for
> users to deal with this problem: if you've got dangling grants then
> update the old server before dumping.
It will require an almost mandatory minor-version update prior to the
upgrade, which adds extra friction and a more complex upgrade story
for our users since both sides need to be updated.
Another point is that quoting all-digit roles alters the aclitem::text
representation across psql, extensions, and client apps giving it a
much broader behavioral impact than just pg_dump.
Finally, it introduces a bit of a maintenance headache for committers,
who will have to adjust the macro on HEAD after back-patching to v18.
Happy to hear other ideas.
Regards,
Demir.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-25 10:44 Rui Zhao <[email protected]>
parent: Hüseyin Demir <[email protected]>
1 sibling, 0 replies; 28+ messages in thread
From: Rui Zhao @ 2026-06-25 10:44 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; +Cc: Tom Lane <[email protected]>; Laurenz Albe <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
Hi Hüseyin,
I reviewed and tested v6. The filtering logic is correct and applied in the
right place: doing it in the source queries (getAdditionalACLs and the
column-level ACL query) means the dangling entries never reach the
binary-upgrade "SET SESSION AUTHORIZATION <oid>" path, which is where the
upgrade actually broke. Using pg_roles instead of pg_authid for the
existence check is also right for non-superuser pg_dump. With the test
running, all of its assertions pass.
Two things on the test:
1. The TAP test doesn't run for me at all -- it dies in setup with
"role \"rui\" does not exist". The cause is that the aclitem literals
are built by concatenating current_user unquoted, e.g.
ARRAY[('ghost_grantee=X/' || current_user)::aclitem]
My bootstrap superuser is "rui.zhao", so this becomes
'ghost_grantee=X/rui.zhao', and aclitemin parses the grantor only up to
the dot:
=# SELECT ('g=X/' || 'a.b')::aclitem;
ERROR: role "a" does not exist
So the test fails before any assertion runs on any cluster whose
superuser name needs quoting (a dot, uppercase, etc.). Wrapping it as
quote_ident(current_user) in the four aclitem literals fixes it (the
test then passes 12/12 here). A bit ironic given the patch is about
handling odd role names.
2. The PUBLIC case (grantee = 0) isn't covered. The "ace.grantee <> 0"
branch is what keeps PUBLIC grants from being filtered, but there's no
test for either direction: a valid PUBLIC grant ("=r/validgrantor")
being kept, or a PUBLIC grant whose grantor is dangling ("=r/ghost")
being dropped. Worth a case or two.
Thanks,
Rui
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-25 13:53 Tom Lane <[email protected]>
parent: Hüseyin Demir <[email protected]>
1 sibling, 0 replies; 28+ messages in thread
From: Tom Lane @ 2026-06-25 13:53 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; +Cc: Laurenz Albe <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
=?UTF-8?Q?H=C3=BCseyin_Demir?= <[email protected]> writes:
>> An alternative answer is to back-patch the putid() fix and teach
>> dumputils.c to consider the server minor version when deciding whether
>> to reject unquoted all-numeric roles. We don't typically make pg_dump
>> pay attention to minor versions, but it would provide a pathway for
>> users to deal with this problem: if you've got dangling grants then
>> update the old server before dumping.
> It will require an almost mandatory minor-version update prior to the
> upgrade, which adds extra friction and a more complex upgrade story
> for our users since both sides need to be updated.
Only if you actually have dangling ACL references, which I think
is the case for a vanishingly small set of users --- otherwise
we'd have heard more complaints and been motivated to fix this
long ago. Let's not optimize for the broken case.
regards, tom lane
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-26 05:18 Laurenz Albe <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Laurenz Albe @ 2026-06-26 05:18 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
On Wed, 2026-06-24 at 14:19 +0200, Hüseyin Demir wrote:
>
> 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
No, that's not good. If you are running the complicated subquery for
every table dumped, you are re-introducing the performance regression
from the v4 patch that Tom justly complained about.
On the other hand, I agree with you that Tom's idea to make this fix
depend on a minor update of the source server that fixes the string
representation of aclitems is not so great. Few people undergo the
hassle of applying the latest minor update to a server they are about
to update (and I am not even speaking about the users who keep running
on the 14.3 they went into production with). Yes, the problem that
the present patch is trying to address is a rare one, and we should
keep the maintenance and performance burden incurred moderate.
But what good is a fix that won't work for a good percentage of the
affected cases, even if they are few?
Here is my latest idea (hold your noses):
Instead of having pg_dump query "FROM pg_catalog.pg_init_privs pip",
how about writing "(FROM (VALUES (...), (...), ...) AS pip", where the
VALUES clause is composed from a query against pg_init_privs run once
at the beginning of pg_dump that excludes the bad entries?
Critizism I forsee is that a) this is ugly and b) very long VALUES
statements might also constitute a performance regression.
However, I have yet to see an extension that produces a hundred
initial privilege entries.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-26 06:56 Hüseyin Demir <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-06-26 06:56 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
> > 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
>
> No, that's not good. If you are running the complicated subquery for
> every table dumped, you are re-introducing the performance regression
> from the v4 patch that Tom justly complained about.
The correlated subquery in getAdditionalACLs adds overhead
proportional to pg_init_privs row count × aclitems per row, with each
check being an indexed OID lookup. For a database with 5,000
pg_init_privs rows this is probably a few extra milliseconds. I added
performance test results with v6. I think it's a sustainable operation
but If I'm missing sth. let me know.
> On the other hand, I agree with you that Tom's idea to make this fix
> depend on a minor update of the source server that fixes the string
> representation of aclitems is not so great. Few people undergo the
> hassle of applying the latest minor update to a server they are about
> to update (and I am not even speaking about the users who keep running
> on the 14.3 they went into production with). Yes, the problem that
> the present patch is trying to address is a rare one, and we should
> keep the maintenance and performance burden incurred moderate.
> But what good is a fix that won't work for a good percentage of the
> affected cases, even if they are few?
>
> Here is my latest idea (hold your noses):
> Instead of having pg_dump query "FROM pg_catalog.pg_init_privs pip",
> how about writing "(FROM (VALUES (...), (...), ...) AS pip", where the
> VALUES clause is composed from a query against pg_init_privs run once
> at the beginning of pg_dump that excludes the bad entries?
> Critizism I forsee is that a) this is ugly and b) very long VALUES
> statements might also constitute a performance regression.
> However, I have yet to see an extension that produces a hundred
> initial privilege entries.
I'm not against this approach but the tradeoff is the same with v6 I
suppose. Since the overhead of processing the full VALUES clause on
every EXECUTE is unnecessary. v6 goes directly to pg_init_privs
indexed by objoid = $1.
But definitely we can prepare a new patch to cover these expectations.
With that in mind, I would like to propose splitting the work into two
separate patches:
1. pg_dump fix (backpatch to 14): the v6 SQL OID-level filter. Works
on any server >= 9.6 without requiring a source-side update. Fixes the
known breakage for users upgrading from pre-17 clusters that
accumulated dangling entries via DROP ROLE.
2. putid() fix (HEAD only): improve aclitemout so that a dangling OID
is rendered in a form that is unambiguously distinct from a valid role
name. This is the right long-term fix for the text representation and
would benefit any future tool that processes aclitem output. It is
independent of the pg_dump fix and does not need to be backpatched for
patch 1 to be correct.
New dangling entries will not live on modern servers. However, users
upgrading from pre-17 clusters may have years of accumulated dangling
entries that pg_upgrade will carry forward silently. The pg_dump fix
is a one-time for that migration window.
Does this split make sense to the reviewers? If so, I will prepare v7
of the pg_dump patch (incorporating any further feedback on v6) and a
separate patch for the putid() fix in HEAD.
Regards,
Demir.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-06-26 08:30 Laurenz Albe <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Laurenz Albe @ 2026-06-26 08:30 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
On Fri, 2026-06-26 at 08:56 +0200, Hüseyin Demir wrote:
> > > 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
> >
> > No, that's not good. If you are running the complicated subquery for
> > every table dumped, you are re-introducing the performance regression
> > from the v4 patch that Tom justly complained about.
>
> The correlated subquery in getAdditionalACLs adds overhead
> proportional to pg_init_privs row count × aclitems per row, with each
> check being an indexed OID lookup. For a database with 5,000
> pg_init_privs rows this is probably a few extra milliseconds. I added
> performance test results with v6. I think it's a sustainable operation
> but If I'm missing sth. let me know.
I have no problem with the subquery being in getAdditionalACLs().
But dumpTable() is executed once per table that gets dumped, right?
So the complicated subquery will run once per table.
> > Here is my latest idea (hold your noses):
> > Instead of having pg_dump query "FROM pg_catalog.pg_init_privs pip",
> > how about writing "(FROM (VALUES (...), (...), ...) AS pip", where the
> > VALUES clause is composed from a query against pg_init_privs run once
> > at the beginning of pg_dump that excludes the bad entries?
> > Critizism I forsee is that a) this is ugly and b) very long VALUES
> > statements might also constitute a performance regression.
> > However, I have yet to see an extension that produces a hundred
> > initial privilege entries.
>
> I'm not against this approach but the tradeoff is the same with v6 I
> suppose. Since the overhead of processing the full VALUES clause on
> every EXECUTE is unnecessary. v6 goes directly to pg_init_privs
> indexed by objoid = $1.
Ah, so you are saying that the effort per table will be much less.
Could you measure the difference with a database with - say - 10000 tables?
> But definitely we can prepare a new patch to cover these expectations.
Let's agree on the proper approach before you go to the effort of writing
another patch.
> With that in mind, I would like to propose splitting the work into two
> separate patches:
>
> 1. pg_dump fix (backpatch to 14): the v6 SQL OID-level filter. Works
> on any server >= 9.6 without requiring a source-side update. Fixes the
> known breakage for users upgrading from pre-17 clusters that
> accumulated dangling entries via DROP ROLE.
>
> 2. putid() fix (HEAD only): improve aclitemout so that a dangling OID
> is rendered in a form that is unambiguously distinct from a valid role
> name. This is the right long-term fix for the text representation and
> would benefit any future tool that processes aclitem output. It is
> independent of the pg_dump fix and does not need to be backpatched for
> patch 1 to be correct.
I think that we all agree on that.
> New dangling entries will not live on modern servers. However, users
> upgrading from pre-17 clusters may have years of accumulated dangling
> entries that pg_upgrade will carry forward silently.
... or rather fail doing so ...
> The pg_dump fix
> is a one-time for that migration window.
Right.
> Does this split make sense to the reviewers? If so, I will prepare v7
> of the pg_dump patch (incorporating any further feedback on v6) and a
> separate patch for the putid() fix in HEAD.
It makes sense to me.
I'd say that your v6 patch is fine if the performance impact can be
shown to be small.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-07-02 21:09 Laurenz Albe <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Laurenz Albe @ 2026-07-02 21:09 UTC (permalink / raw)
To: Hüseyin Demir <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
On Fri, 2026-06-26 at 10:30 +0200, Laurenz Albe wrote:
> I'd say that your v6 patch is fine if the performance impact can be
> shown to be small.
To keep the ball rolling, I ran a pg_upgrade --link from a debug-enabled
build on a database with 20000 empty tables.
pg_upgrade took between 19 and 25 minutes, and I saw no noticeable effect
of the patch. So I guess this is good to go.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-07-10 07:00 Hüseyin Demir <[email protected]>
parent: Laurenz Albe <[email protected]>
0 siblings, 1 reply; 28+ messages in thread
From: Hüseyin Demir @ 2026-07-10 07:00 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
> > I'd say that your v6 patch is fine if the performance impact can be
> > shown to be small.
>
> To keep the ball rolling, I ran a pg_upgrade --link from a debug-enabled
> build on a database with 20000 empty tables.
>
> pg_upgrade took between 19 and 25 minutes, and I saw no noticeable effect
> of the patch. So I guess this is good to go.
>
> Yours,
> Laurenz Albe
Thanks for the pg_upgrade test.
Appreciated.
^ permalink raw reply [nested|flat] 28+ messages in thread
* Re: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table
@ 2026-07-10 17:37 Hüseyin Demir <[email protected]>
parent: Hüseyin Demir <[email protected]>
0 siblings, 0 replies; 28+ messages in thread
From: Hüseyin Demir @ 2026-07-10 17:37 UTC (permalink / raw)
To: Laurenz Albe <[email protected]>; +Cc: Tom Lane <[email protected]>; Greg Sabino Mullane <[email protected]>; [email protected]
> > > I'd say that your v6 patch is fine if the performance impact can be
> > > shown to be small.
> >
> > To keep the ball rolling, I ran a pg_upgrade --link from a debug-enabled
> > build on a database with 20000 empty tables.
> >
> > pg_upgrade took between 19 and 25 minutes, and I saw no noticeable effect
> > of the patch. So I guess this is good to go.
> >
> > Yours,
> > Laurenz Albe
>
> Thanks for the pg_upgrade test.
>
> Appreciated.
Rebased v6 on current master.
Attachments:
[application/octet-stream] v7-0001-pg_dump-skip-dangling-initprivs.patch (9.6K, ../../CAB5wL7aTaOyijT3qJF9jAQNrv1K74sQkEyu+zkb54rYDc_NBdg@mail.gmail.com/2-v7-0001-pg_dump-skip-dangling-initprivs.patch)
download | inline diff:
From 3c76b1af9c64934797641f595d2c03b809c3088f 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 4d660d14b4c..9a720f6fa4c 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,
@@ -10748,8 +10777,9 @@ getAdditionalACLs(Archive *fout)
/* Fetch initial-privileges data */
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);
@@ -16892,7 +16922,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.50.1 (Apple Git-155)
^ permalink raw reply [nested|flat] 28+ messages in thread
end of thread, other threads:[~2026-07-10 17:37 UTC | newest]
Thread overview: 28+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-05-18 05:06 BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table PG Bug reporting form <[email protected]>
2026-05-20 13:06 ` Greg Sabino Mullane <[email protected]>
2026-06-07 10:53 ` Hüseyin Demir <[email protected]>
2026-06-07 15:51 ` Tom Lane <[email protected]>
2026-06-11 05:49 ` Hüseyin Demir <[email protected]>
2026-06-12 16:22 ` Hüseyin Demir <[email protected]>
2026-06-19 06:25 ` Hüseyin Demir <[email protected]>
2026-06-19 07:40 ` Hüseyin Demir <[email protected]>
2026-06-20 21:53 ` Laurenz Albe <[email protected]>
2026-06-21 05:47 ` Laurenz Albe <[email protected]>
2026-06-22 05:44 ` Hüseyin Demir <[email protected]>
2026-06-22 06:07 ` Hüseyin Demir <[email protected]>
2026-06-22 17:23 ` Laurenz Albe <[email protected]>
2026-06-22 18:30 ` Tom Lane <[email protected]>
2026-06-23 08:32 ` Hüseyin Demir <[email protected]>
2026-06-24 06:14 ` Hüseyin Demir <[email protected]>
2026-06-24 09:56 ` Laurenz Albe <[email protected]>
2026-06-24 12:19 ` Hüseyin Demir <[email protected]>
2026-06-26 05:18 ` Laurenz Albe <[email protected]>
2026-06-26 06:56 ` Hüseyin Demir <[email protected]>
2026-06-26 08:30 ` Laurenz Albe <[email protected]>
2026-07-02 21:09 ` Laurenz Albe <[email protected]>
2026-07-10 07:00 ` Hüseyin Demir <[email protected]>
2026-07-10 17:37 ` Hüseyin Demir <[email protected]>
2026-06-24 14:57 ` Tom Lane <[email protected]>
2026-06-25 07:55 ` Hüseyin Demir <[email protected]>
2026-06-25 10:44 ` Rui Zhao <[email protected]>
2026-06-25 13:53 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox