Re: Major Version Upgrade failure due to orphan roles entries in catalog

BTW a similar variant has been fixed in PG 16 but does not fix the above
case.

create role dropped_group;
create role member;
begin;
grant dropped_group to member;
OTHER SESSION: drop role dropped_group;
BACK IN ORIGINAL SESSION:
commit;

On Tue, Feb 11, 2025 at 7:50 PM Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
wrote:

> On Tue, 2025-02-11 at 15:32 +0530, Virender Singla wrote:
> > 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
>
> I agree that that is a bug.
>
> I guess the GRANT statement should put a FOR KEY SHARE lock on the
> pg_authid row
> for "dropped_member", similar to what we do for foreign keys.
>
> Yours,
> Laurenz Albe
>
> --
>
> *E-Mail Disclaimer*
> Der Inhalt dieser E-Mail ist ausschliesslich fuer den
> bezeichneten Adressaten bestimmt. Wenn Sie nicht der vorgesehene Adressat
> dieser E-Mail oder dessen Vertreter sein sollten, so beachten Sie bitte,
> dass jede Form der Kenntnisnahme, Veroeffentlichung, Vervielfaeltigung
> oder
> Weitergabe des Inhalts dieser E-Mail unzulaessig ist. Wir bitten Sie, sich
> in diesem Fall mit dem Absender der E-Mail in Verbindung zu setzen.
>
> *CONFIDENTIALITY NOTICE & DISCLAIMER
> *This message and any attachment are
> confidential and may be privileged or otherwise protected from disclosure
> and solely for the use of the person(s) or entity to whom it is intended.
> If you have received this message in error and are not the intended
> recipient, please notify the sender immediately and delete this message
> and
> any attachment from your system. If you are not the intended recipient, be
> advised that any use of this message is prohibited and may be unlawful,
> and
> you must not copy this message or attachment or disclose the contents to
> any other person.
>