MIME-Version: 1.0
From: Virender Singla <virender.cse@gmail.com>
Date: Tue, 11 Feb 2025 15:32:42 +0530
Message-ID: <CAM6Zo8woa62ZFHtMKox6a4jb8qQ=w87R2L0K8347iE-juQL2EA@mail.gmail.com>
Subject: Major Version Upgrade failure due to orphan roles entries in catalog
To: pgsql-bugs@lists.postgresql.org
Cc: Aniket Jha <aniketkumarj@gmail.com>
Content-Type: multipart/alternative; boundary="000000000000b0c1c1062ddaed62"
Archived-At: <https://www.postgresql.org/message-id/CAM6Zo8woa62ZFHtMKox6a4jb8qQ%3Dw87R2L0K8347iE-juQL2EA%40mail.gmail.com>
Precedence: bulk

--000000000000b0c1c1062ddaed62
Content-Type: text/plain; charset="UTF-8"

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

--000000000000b0c1c1062ddaed62
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">Hi,<div><br></div><div>We have identified an issue causing=
 upgrade failures. The following steps detail how to reproduce the issue:</=
div><div><br></div><div><b>Create an orphan role entry</b></div><div><br></=
div><div>/* Postgres version::=C2=A0PostgreSQL 16.6 */=C2=A0</div><div>/* T=
he same can be reproduced in version 17 as well */</div><div><br></div><div=
>create role my_group;<br>create role dropped_member;<br>begin;<br>grant my=
_group to dropped_member;<br>OTHER SESSION: drop role dropped_member;<br>BA=
CK IN ORIGINAL SESSION:<br>commit;<br></div><div><br></div><div><b>Upgrade =
to Postgres v17</b></div><div><br></div><div>And the upgrade fails with an =
error :</div><div><br></div><div><b>GRANT &quot;my_group&quot; TO &quot;&qu=
ot; WITH INHERIT TRUE GRANTED BY &quot;postgres&quot;;<br>ERROR: zero-lengt=
h delimited identifier at or near &quot;&quot;&quot;&quot;</b></div><div><b=
r></div><div>The issue seems to be coming from pg_dumpall for building gran=
ts during pg_upgrade.</div><div><br></div><div><a href=3D"https://github.co=
m/postgres/postgres/blob/master/src/bin/pg_dump/pg_dumpall.c#L992">https://=
github.com/postgres/postgres/blob/master/src/bin/pg_dump/pg_dumpall.c#L992<=
/a></div><div><br></div><div>-Virender</div></div>

--000000000000b0c1c1062ddaed62--