Feedback-ID: ia2694551:Fastmail
Date: Thu, 13 Feb 2025 18:16:02 +0100
From: =?utf-8?Q?=C3=81lvaro?= Herrera <alvherre@alvh.no-ip.org>
To: Virender Singla <virender.cse@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org, Aniket Jha <aniketkumarj@gmail.com>
Subject: Re: Major Version Upgrade failure due to orphan roles entries in
 catalog
Message-ID: <202502131716.7mgkcnrem2hn@alvherre.pgsql>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: <CAM6Zo8woa62ZFHtMKox6a4jb8qQ=w87R2L0K8347iE-juQL2EA@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/202502131716.7mgkcnrem2hn%40alvherre.pgsql>
Precedence: bulk

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