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

On 2025-Feb-11, Virender Singla wrote:

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

Hmm, I think fixing the bug as Tom suggests downthread is probably a
good idea, but I think we should in addition change pg_dumpall to avoid
printing a GRANT line if there's no grantee. Maybe turning one of these LEFT
JOINs into a regular inner join is a sufficient fix for that:

/* Generate and execute query. */
printfPQExpBuffer(buf, "SELECT ur.rolname AS role, "
"um.rolname AS member, "
"ug.oid AS grantorid, "
"ug.rolname AS grantor, "
"a.admin_option");
if (dump_grant_options)
appendPQExpBufferStr(buf, ", a.inherit_option, a.set_option");
appendPQExpBuffer(buf, " FROM pg_auth_members a "
"LEFT JOIN %s ur on ur.oid = a.roleid "
"LEFT JOIN %s um on um.oid = a.member "
"LEFT JOIN %s ug on ug.oid = a.grantor "
"WHERE NOT (ur.rolname ~ '^pg_' AND um.rolname ~ '^pg_')"
"ORDER BY 1,2,4", role_catalog, role_catalog, role_catalog);

--
Álvaro Herrera Breisgau, Deutschland — https://www.EnterpriseDB.com/
"I am amazed at [the pgsql-sql] mailing list for the wonderful support, and
lack of hesitasion in answering a lost soul's question, I just wished the rest
of the mailing list could be like this." (Fotis)
https://postgr.es/m/200606261359.k5QDxE2p004593@auth-smtp.hol.gr