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

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?

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]/messages/by-id/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com
</messages/by-id/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com&gt;
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]: /messages/by-id/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com
/messages/by-id/CAB5wL7aig++XphVjyBjvXG-=UE+=mk3xfZZxkxV5XS4Hb58aHA@mail.gmail.com

Greg Sabino Mullane <htamfids@gmail.com>, 20 May 2026 Çar, 15:07 tarihinde
şunu yazdı:

Greg Sabino Mullane <htamfids@gmail.com> 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

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 <tgl@sss.pgh.pa.us>, 7 Haz 2026 Paz, 17:52 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 <huseyin.d3r@gmail.com>, 11 Haz 2026 Per, 07:49
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 <huseyin.d3r@gmail.com>, 12 Haz 2026 Cum, 18:22 tarihinde
şunu yazdı:

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 <huseyin.d3r@gmail.com>, 19 Haz 2026 Cum, 08:25 tarihinde
şunu yazdı:

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

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

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.

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.

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

Laurenz Albe <laurenz.albe@cybertec.at> 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

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.

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 <huseyin.d3r@gmail.com>, 23 Haz 2026 Sal, 10:32
tarihinde şunu yazdı:

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

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

Laurenz Albe <laurenz.albe@cybertec.at> 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

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.