From d8d3d2a4f37f6df5d0118b7f5211978cca22091a Mon Sep 17 00:00:00 2001
From: Bruce Momjian <bruce@momjian.us>
Date: Fri, 7 Jan 2011 21:59:29 -0500
Subject: Fix pg_upgrade of large object permissions by preserving pg_auth.oid,
 which is stored in pg_largeobject_metadata.

No backpatch to 9.0 because you can't migrate from 9.0 to 9.0 with the
same catversion (because of tablespace conflict), and a pre-9.0
migration to 9.0 has not large object permissions to migrate.
---
 src/backend/commands/user.c  | 13 +++++++++++++
 src/bin/pg_dump/pg_dumpall.c | 32 ++++++++++++++++++++++----------
 2 files changed, 35 insertions(+), 10 deletions(-)

(limited to 'src')

diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c
index be049cb9945..63f22d8adc2 100644
--- a/src/backend/commands/user.c
+++ b/src/backend/commands/user.c
@@ -35,6 +35,9 @@
 #include "utils/syscache.h"
 #include "utils/tqual.h"
 
+/* Potentially set by contrib/pg_upgrade_support functions */
+Oid			binary_upgrade_next_pg_authid_oid = InvalidOid;
+
 
 /* GUC parameter */
 extern bool Password_encryption;
@@ -393,6 +396,16 @@ CreateRole(CreateRoleStmt *stmt)
 
 	tuple = heap_form_tuple(pg_authid_dsc, new_record, new_record_nulls);
 
+	/*
+	 * pg_largeobject_metadata contains pg_authid.oid's, so we
+	 * use the binary-upgrade override, if specified.
+	 */
+	if (OidIsValid(binary_upgrade_next_pg_authid_oid))
+	{
+		HeapTupleSetOid(tuple, binary_upgrade_next_pg_authid_oid);
+		binary_upgrade_next_pg_authid_oid = InvalidOid;
+	}
+
 	/*
 	 * Insert new record in the pg_authid table
 	 */
diff --git a/src/bin/pg_dump/pg_dumpall.c b/src/bin/pg_dump/pg_dumpall.c
index 17a73b87f1d..29282324a23 100644
--- a/src/bin/pg_dump/pg_dumpall.c
+++ b/src/bin/pg_dump/pg_dumpall.c
@@ -650,7 +650,8 @@ dumpRoles(PGconn *conn)
 {
 	PQExpBuffer buf = createPQExpBuffer();
 	PGresult   *res;
-	int			i_rolname,
+	int			i_oid,
+				i_rolname,
 				i_rolsuper,
 				i_rolinherit,
 				i_rolcreaterole,
@@ -667,34 +668,34 @@ dumpRoles(PGconn *conn)
 	/* note: rolconfig is dumped later */
 	if (server_version >= 90100)
 		printfPQExpBuffer(buf,
-						  "SELECT rolname, rolsuper, rolinherit, "
+						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, rolcatupdate, "
 						  "rolcanlogin, rolconnlimit, rolpassword, "
 						  "rolvaliduntil, rolreplication, "
 			  "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment "
 						  "FROM pg_authid "
-						  "ORDER BY 1");
+						  "ORDER BY 2");
 	else if (server_version >= 80200)
 		printfPQExpBuffer(buf,
-						  "SELECT rolname, rolsuper, rolinherit, "
+						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, rolcatupdate, "
 						  "rolcanlogin, rolconnlimit, rolpassword, "
 						  "rolvaliduntil, false as rolreplication, "
 			  "pg_catalog.shobj_description(oid, 'pg_authid') as rolcomment "
 						  "FROM pg_authid "
-						  "ORDER BY 1");
+						  "ORDER BY 2");
 	else if (server_version >= 80100)
 		printfPQExpBuffer(buf,
-						  "SELECT rolname, rolsuper, rolinherit, "
+						  "SELECT oid, rolname, rolsuper, rolinherit, "
 						  "rolcreaterole, rolcreatedb, rolcatupdate, "
 						  "rolcanlogin, rolconnlimit, rolpassword, "
 						  "rolvaliduntil, false as rolreplication, "
 						  "null as rolcomment "
 						  "FROM pg_authid "
-						  "ORDER BY 1");
+						  "ORDER BY 2");
 	else
 		printfPQExpBuffer(buf,
-						  "SELECT usename as rolname, "
+						  "SELECT 0, usename as rolname, "
 						  "usesuper as rolsuper, "
 						  "true as rolinherit, "
 						  "usesuper as rolcreaterole, "
@@ -708,7 +709,7 @@ dumpRoles(PGconn *conn)
 						  "null as rolcomment "
 						  "FROM pg_shadow "
 						  "UNION ALL "
-						  "SELECT groname as rolname, "
+						  "SELECT 0, groname as rolname, "
 						  "false as rolsuper, "
 						  "true as rolinherit, "
 						  "false as rolcreaterole, "
@@ -723,10 +724,11 @@ dumpRoles(PGconn *conn)
 						  "FROM pg_group "
 						  "WHERE NOT EXISTS (SELECT 1 FROM pg_shadow "
 						  " WHERE usename = groname) "
-						  "ORDER BY 1");
+						  "ORDER BY 2");
 
 	res = executeQuery(conn, buf->data);
 
+	i_oid = PQfnumber(res, "oid");
 	i_rolname = PQfnumber(res, "rolname");
 	i_rolsuper = PQfnumber(res, "rolsuper");
 	i_rolinherit = PQfnumber(res, "rolinherit");
@@ -751,6 +753,16 @@ dumpRoles(PGconn *conn)
 
 		resetPQExpBuffer(buf);
 
+		if (binary_upgrade)
+		{
+			Oid			auth_oid = atooid(PQgetvalue(res, i, i_oid));
+
+			appendPQExpBuffer(buf, "\n-- For binary upgrade, must preserve pg_authid.oid\n");
+			appendPQExpBuffer(buf,
+			 "SELECT binary_upgrade.set_next_pg_authid_oid('%u'::pg_catalog.oid);\n\n",
+							  auth_oid);
+		}
+
 		/*
 		 * We dump CREATE ROLE followed by ALTER ROLE to ensure that the role
 		 * will acquire the right properties even if it already exists (ie, it
-- 
cgit v1.2.3