Horton

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

Alvaro Herrera <alvherre@alvh.no-ip.org>

View thread
From: Álvaro Herrera <alvherre@alvh.no-ip.org>
To: Virender Singla <virender.cse@gmail.com>
Cc: pgsql-bugs@lists.postgresql.org, Aniket Jha <aniketkumarj@gmail.com>
Date: 2025-02-13T17:16:02Z
Lists: pgsql-bugs

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Avoid race condition between "GRANT role" and "DROP ROLE".

  2. Fix pg_dumpall to cope with dangling OIDs in pg_auth_members.

  3. Ensure that pg_auth_members.grantor is always valid.

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