public inbox for [email protected]
help / color / mirror / Atom feedFrom: Álvaro Herrera <[email protected]>
To: Virender Singla <[email protected]>
Cc: [email protected]
Cc: Aniket Jha <[email protected]>
Subject: Re: Major Version Upgrade failure due to orphan roles entries in catalog
Date: Thu, 13 Feb 2025 18:16:02 +0100
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAM6Zo8woa62ZFHtMKox6a4jb8qQ=w87R2L0K8347iE-juQL2EA@mail.gmail.com>
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/[email protected]
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected], [email protected]
Subject: Re: Major Version Upgrade failure due to orphan roles entries in catalog
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox