From: Tom Lane <tgl@sss.pgh.pa.us>
To: Robert Haas <robertmhaas@gmail.com>
cc: =?UTF-8?Q?=C3=81lvaro_Herrera?= <alvherre@alvh.no-ip.org>,
        Virender Singla <virender.cse@gmail.com>,
        pgsql-bugs@lists.postgresql.org, Aniket Jha <aniketkumarj@gmail.com>
Subject: Re: Major Version Upgrade failure due to orphan roles entries in catalog
In-reply-to: <1103272.1772227345@sss.pgh.pa.us>
References: <202502131716.7mgkcnrem2hn@alvherre.pgsql> <2939991.1740089974@sss.pgh.pa.us> <CA+TgmoauoiW4ydDhdrseg+DD4Kwha=+TSZp18BrJeHKx3o1Fdw@mail.gmail.com> <179448.1772033773@sss.pgh.pa.us> <CA+TgmoYFc1x11Y7AHxn6Jb0mvJ9-aPttmjqezj=YKnHmqVb-Wg@mail.gmail.com> <265501.1772038216@sss.pgh.pa.us> <CA+TgmobNKFc34nj+w6iX0zWJ1=mfYWqQS91PqsJrm4VD4o1yOw@mail.gmail.com> <296083.1772041154@sss.pgh.pa.us> <CA+TgmoYJ8Y1G1r9U7pRwnyw=8dmM0xygLguE+bdihkN8GwPuCQ@mail.gmail.com> <301798.1772044229@sss.pgh.pa.us> <CA+TgmobZRL02-R7k_i3ENYz8CmqCPUZv6cHY51BejqPy7OhFJw@mail.gmail.com> <304751.1772045891@sss.pgh.pa.us> <1103272.1772227345@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
	message dated "Fri, 27 Feb 2026 16:22:25 -0500"
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="----- =_aaaaaaaaaa0"
Content-ID: <1104353.1772227709.0@sss.pgh.pa.us>
Date: Fri, 27 Feb 2026 16:28:51 -0500
Message-ID: <1104379.1772227731@sss.pgh.pa.us>
Archived-At: <https://www.postgresql.org/message-id/1104379.1772227731%40sss.pgh.pa.us>
Precedence: bulk

------- =_aaaaaaaaaa0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1104353.1772227709.1@sss.pgh.pa.us>

I wrote:
> So I end with the attached draft patch.

Sigh, this time with it really attached.

			regards, tom lane


------- =_aaaaaaaaaa0
Content-Type: text/x-diff; name="v1-clean-up-dumpRoleMembership.patch";
	charset="us-ascii"
Content-ID: <1104353.1772227709.2@sss.pgh.pa.us>
Content-Description: v1-clean-up-dumpRoleMembership.patch
Content-Transfer-Encoding: quoted-printable

diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 65f8e3a41f1..3062636a2ce 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -1292,7 +1292,7 @@ dumpRoleMembership(PGconn *conn)
 	 * that no longer exist.  If we find such cases, print a warning and ski=
p
 	 * the entry.
 	 */
-	dump_grantors =3D (PQserverVersion(conn) >=3D 160000);
+	dump_grantors =3D (server_version >=3D 160000);
 =

 	/*
 	 * Previous versions of PostgreSQL also did not have grant-level options=
.
@@ -1357,7 +1357,7 @@ dumpRoleMembership(PGconn *conn)
 		if (PQgetisnull(res, start, i_role))
 		{
 			/* translator: %s represents a numeric role OID */
-			pg_log_warning("found orphaned pg_auth_members entry for role %s",
+			pg_log_warning("ignoring role grant for missing role with OID %s",
 						   PQgetvalue(res, start, i_roleid));
 			break;
 		}
@@ -1374,6 +1374,11 @@ dumpRoleMembership(PGconn *conn)
 =

 		remaining =3D end - start;
 		done =3D pg_malloc0_array(bool, remaining);
+
+		/*
+		 * We use a hashtable to track the member names that have been granted
+		 * admin option.  Usually a hashtable is overkill, but sometimes not.
+		 */
 		ht =3D rolename_create(remaining, NULL);
 =

 		/*
@@ -1401,50 +1406,56 @@ dumpRoleMembership(PGconn *conn)
 			for (i =3D start; i < end; ++i)
 			{
 				char	   *member;
-				char	   *admin_option;
 				char	   *grantorid;
-				char	   *grantor;
+				char	   *grantor =3D NULL;
+				bool		dump_grantor =3D dump_grantors;
 				char	   *set_option =3D "true";
+				char	   *admin_option;
 				bool		found;
 =

 				/* If we already did this grant, don't do it again. */
 				if (done[i - start])
 					continue;
 =

-				/* Complain about, then ignore, entries with orphaned OIDs. */
+				/* Complain about, then ignore, entries for unknown members. */
 				if (PQgetisnull(res, i, i_member))
 				{
 					/* translator: %s represents a numeric role OID */
-					pg_log_warning("found orphaned pg_auth_members entry for role %s",
+					pg_log_warning("ignoring role grant to missing role with OID %s",
 								   PQgetvalue(res, i, i_memberid));
 					done[i - start] =3D true;
 					--remaining;
 					continue;
 				}
-				if (PQgetisnull(res, i, i_grantor))
+				member =3D PQgetvalue(res, i, i_member);
+
+				/* If the grantor is unknown, complain and dump without it. */
+				grantorid =3D PQgetvalue(res, i, i_grantorid);
+				if (dump_grantor)
 				{
-					/* translator: %s represents a numeric role OID */
-					pg_log_warning("found orphaned pg_auth_members entry for role %s",
-								   PQgetvalue(res, i, i_grantorid));
-					done[i - start] =3D true;
-					--remaining;
-					continue;
+					if (PQgetisnull(res, i, i_grantor))
+					{
+						/* translator: %s represents a numeric role OID */
+						pg_log_warning("grant of role \"%s\" to \"%s\" has invalid grantor =
OID %s",
+									   role, member, grantorid);
+						pg_log_warning_detail("This grant will be dumped without GRANTED BY=
.");
+						dump_grantor =3D false;
+					}
+					else
+						grantor =3D PQgetvalue(res, i, i_grantor);
 				}
 =

-				member =3D PQgetvalue(res, i, i_member);
-				grantor =3D PQgetvalue(res, i, i_grantor);
-				grantorid =3D PQgetvalue(res, i, i_grantorid);
 				admin_option =3D PQgetvalue(res, i, i_admin_option);
 				if (dump_grant_options)
 					set_option =3D PQgetvalue(res, i, i_set_option);
 =

 				/*
-				 * If we're not dumping grantors or if the grantor is the
+				 * If we're not dumping the grantor or if the grantor is the
 				 * bootstrap superuser, it's fine to dump this now. Otherwise,
 				 * it's got to be someone who has already been granted ADMIN
 				 * OPTION.
 				 */
-				if (dump_grantors &&
+				if (dump_grantor &&
 					atooid(grantorid) !=3D BOOTSTRAP_SUPERUSERID &&
 					rolename_lookup(ht, grantor) =3D=3D NULL)
 					continue;
@@ -1486,7 +1497,7 @@ dumpRoleMembership(PGconn *conn)
 				}
 				if (optbuf->data[0] !=3D '\0')
 					appendPQExpBuffer(querybuf, " WITH %s", optbuf->data);
-				if (dump_grantors)
+				if (dump_grantor)
 					appendPQExpBuffer(querybuf, " GRANTED BY %s", fmtId(grantor));
 				appendPQExpBuffer(querybuf, ";\n");
 =


------- =_aaaaaaaaaa0--