Major Version Upgrade failure due to orphan roles entries in catalog

Virender Singla <virender.cse@gmail.com>

From: Virender Singla <virender.cse@gmail.com>

To: pgsql-bugs@lists.postgresql.org

Cc: Aniket Jha <aniketkumarj@gmail.com>

Date: 2025-02-11T10:02:42Z

Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

Avoid race condition between "GRANT role" and "DROP ROLE".
- 98fc31d64991 18.0 landed
Fix pg_dumpall to cope with dangling OIDs in pg_auth_members.
- d850a6600cec 16.9 landed
- ce1475acd5e2 14.18 landed
- 6df3be415cdb 15.13 landed
- 29d75b25b567 18.0 landed
- 16eff4261f46 17.5 landed
- 5302ff95cadd 13.21 landed
Ensure that pg_auth_members.grantor is always valid.
- 6566133c5f52 16.0 cited

Hi,

We have identified an issue causing upgrade failures. The following steps
detail how to reproduce the issue:

*Create an orphan role entry*

/* Postgres version:: PostgreSQL 16.6 */
/* The same can be reproduced in version 17 as well */

create role my_group;
create role dropped_member;
begin;
grant my_group to dropped_member;
OTHER SESSION: drop role dropped_member;
BACK IN ORIGINAL SESSION:
commit;

*Upgrade to Postgres v17*

And the upgrade fails with an error :


*GRANT "my_group" TO "" WITH INHERIT TRUE GRANTED BY "postgres";ERROR:
zero-length delimited identifier at or near """"*

The issue seems to be coming from pg_dumpall for building grants during
pg_upgrade.

https://github.com/postgres/postgres/blob/master/src/bin/pg_dump/pg_dumpall.c#L992

-Virender