Subject: BUG #19483: pg_upgrade fails with orphan records in pg_init_priv catalog table

From: PG Bug reporting form <[email-masked]>
Date: Mon, 18 May 2026 05:06:24 +0000

The following bug has been logged on the website:

Bug reference:      19483
Logged by:          Hüseyin Demir
Email address:      [email-masked]
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 ?

---

From: Greg Sabino Mullane <[email-masked]>
Date: Wed, 20 May 2026 09:06:58 -0400

> ...

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?

---

From: Hüseyin Demir <[email-masked]>
Date: Sun, 7 Jun 2026 12:53:07 +0200

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+=[email-masked]>
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.

```
```

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.

```
```

5. Connect to postgres database and drop the role we created the extension

```
```

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+=[email-masked]

Greg Sabino Mullane <[email-masked]>, 20 May 2026 Çar, 15:07 tarihinde

---

From: Tom Lane <[email-masked]>
Date: Sun, 07 Jun 2026 11:51:59 -0400

> 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

---

From: Hüseyin Demir <[email-masked]>
Date: Thu, 11 Jun 2026 07:49:05 +0200

> 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.

> 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.

---

From: Hüseyin Demir <[email-masked]>
Date: Fri, 12 Jun 2026 18:22:03 +0200

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-masked]>, 11 Haz 2026 Per, 07:49

---

From: Hüseyin Demir <[email-masked]>
Date: Fri, 19 Jun 2026 08:25:47 +0200

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-masked]>, 12 Haz 2026 Cum, 18:22 tarihinde

---

From: Hüseyin Demir <[email-masked]>
Date: Fri, 19 Jun 2026 09:40:47 +0200

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-masked]>, 19 Haz 2026 Cum, 08:25 tarihinde

---

From: Laurenz Albe <[email-masked]>
Date: Sat, 20 Jun 2026 23:53:01 +0200

> 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

---

From: Laurenz Albe <[email-masked]>
Date: Sun, 21 Jun 2026 07:47:33 +0200

On Sat, 2026-06-20 at 23:53 +0200, I wrote:
> 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

---

From: Hüseyin Demir <[email-masked]>
Date: Mon, 22 Jun 2026 07:44:45 +0200

Hi,

> 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.

---

From: Hüseyin Demir <[email-masked]>
Date: Mon, 22 Jun 2026 08:07:16 +0200

> 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.

---

From: Laurenz Albe <[email-masked]>
Date: Mon, 22 Jun 2026 19:23:38 +0200

> > site, I introduced a SAFE_INITPRIVS(col) macro.

Great, that's just what I had in mind.

> 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

---

From: Tom Lane <[email-masked]>
Date: Mon, 22 Jun 2026 14:30:54 -0400

> My opinion is that it should; every upgrade or restore failure is

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

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++)

---

From: Hüseyin Demir <[email-masked]>
Date: Tue, 23 Jun 2026 10:32:34 +0200

> 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.

---

From: Hüseyin Demir <[email-masked]>
Date: Wed, 24 Jun 2026 08:14:53 +0200

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-masked]>, 23 Haz 2026 Sal, 10:32

---

From: Laurenz Albe <[email-masked]>
Date: Wed, 24 Jun 2026 11:56:58 +0200

On Wed, 2026-06-24 at 08:14 +0200, Hüseyin Demir wrote:
> 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

---

From: Hüseyin Demir <[email-masked]>
Date: Wed, 24 Jun 2026 14:19:47 +0200

> pg_init_privs once at the beginning of the dump, ignoring the entries

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

---

From: Tom Lane <[email-masked]>
Date: Wed, 24 Jun 2026 10:57:58 -0400

> 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

---

From: Hüseyin Demir <[email-masked]>
Date: Thu, 25 Jun 2026 09:55:59 +0200

Hi,

> 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.

---

From: Rui Zhao <[email-masked]>
Date: Thu, 25 Jun 2026 18:44:38 +0800

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

---

From: Tom Lane <[email-masked]>
Date: Thu, 25 Jun 2026 09:53:25 -0400

> 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

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 26 Jun 2026 07:18:28 +0200

> 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

---

From: Hüseyin Demir <[email-masked]>
Date: Fri, 26 Jun 2026 08:56:00 +0200

> 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.

> 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.

---

From: Laurenz Albe <[email-masked]>
Date: Fri, 26 Jun 2026 10:30:28 +0200

> 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.

> every EXECUTE is unnecessary. v6 goes directly to pg_init_privs

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.

> independent of the pg_dump fix and does not need to be backpatched for

I think that we all agree on that.

> entries that pg_upgrade will carry forward silently.

... or rather fail doing so ...

>                                                       The pg_dump fix

Right.

> 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

---

From: Laurenz Albe <[email-masked]>
Date: Thu, 02 Jul 2026 23:09:54 +0200

> I'd say that your v6 patch is fine if the performance impact can be

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

---

From: Hüseyin Demir <[email-masked]>
Date: Fri, 10 Jul 2026 09:00:35 +0200

> of the patch.  So I guess this is good to go.

Thanks for the pg_upgrade test.

Appreciated.

---

From: Hüseyin Demir <[email-masked]>
Date: Fri, 10 Jul 2026 19:37:26 +0200

> Thanks for the pg_upgrade test.

Rebased v6 on current master.