From d8d3d2a4f37f6df5d0118b7f5211978cca22091a Mon Sep 17 00:00:00 2001 From: Bruce Momjian Date: Fri, 7 Jan 2011 21:59:29 -0500 Subject: [PATCH] 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. --- contrib/pg_upgrade/dump.c | 2 +- contrib/pg_upgrade/function.c | 23 ++++++------- contrib/pg_upgrade/info.c | 2 +- contrib/pg_upgrade/pg_upgrade.c | 22 ++++++++++--- contrib/pg_upgrade/pg_upgrade.h | 2 +- .../pg_upgrade_support/pg_upgrade_support.c | 13 ++++++++ src/backend/commands/user.c | 13 ++++++++ src/bin/pg_dump/pg_dumpall.c | 32 +++++++++++++------ 8 files changed, 79 insertions(+), 30 deletions(-) diff --git a/contrib/pg_upgrade/dump.c b/contrib/pg_upgrade/dump.c index 52ab4813a8..aba95f44c9 100644 --- a/contrib/pg_upgrade/dump.c +++ b/contrib/pg_upgrade/dump.c @@ -33,7 +33,7 @@ generate_old_dump(void) * * This function splits pg_dumpall output into global values and * database creation, and per-db schemas. This allows us to create - * the toast place holders between restoring these two parts of the + * the support functions between restoring these two parts of the * dump. We split on the first "\connect " after a CREATE ROLE * username match; this is where the per-db restore starts. * diff --git a/contrib/pg_upgrade/function.c b/contrib/pg_upgrade/function.c index 9c8c5865d7..1a66df295a 100644 --- a/contrib/pg_upgrade/function.c +++ b/contrib/pg_upgrade/function.c @@ -13,23 +13,16 @@ /* - * install_support_functions() + * install_db_support_functions() * * pg_upgrade requires some support functions that enable it to modify * backend behavior. */ void -install_support_functions(void) +install_db_support_functions(const char *db_name) { - int dbnum; - - prep_status("Adding support functions to new cluster"); - - for (dbnum = 0; dbnum < new_cluster.dbarr.ndbs; dbnum++) - { - DbInfo *new_db = &new_cluster.dbarr.dbs[dbnum]; - PGconn *conn = connectToServer(&new_cluster, new_db->db_name); - + PGconn *conn = connectToServer(&new_cluster, db_name); + /* suppress NOTICE of dropped objects */ PQclear(executeQueryOrDie(conn, "SET client_min_messages = warning;")); @@ -83,9 +76,13 @@ install_support_functions(void) "RETURNS VOID " "AS '$libdir/pg_upgrade_support' " "LANGUAGE C STRICT;")); + PQclear(executeQueryOrDie(conn, + "CREATE OR REPLACE FUNCTION " + " binary_upgrade.set_next_pg_authid_oid(OID) " + "RETURNS VOID " + "AS '$libdir/pg_upgrade_support' " + "LANGUAGE C STRICT;")); PQfinish(conn); - } - check_ok(); } diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c index dc66e38c04..fb6ba75b8e 100644 --- a/contrib/pg_upgrade/info.c +++ b/contrib/pg_upgrade/info.c @@ -282,7 +282,7 @@ get_rel_infos(ClusterInfo *cluster, DbInfo *dbinfo) " ON c.relnamespace = n.oid " " LEFT OUTER JOIN pg_catalog.pg_tablespace t " " ON c.reltablespace = t.oid " - "WHERE (( n.nspname NOT IN ('pg_catalog', 'information_schema') " + "WHERE (( n.nspname NOT IN ('pg_catalog', 'information_schema', 'binary_upgrade') " " AND c.oid >= %u " " ) OR ( " " n.nspname = 'pg_catalog' " diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c index 3de47fc62e..dd50eb85cc 100644 --- a/contrib/pg_upgrade/pg_upgrade.c +++ b/contrib/pg_upgrade/pg_upgrade.c @@ -224,11 +224,15 @@ prepare_new_databases(void) set_frozenxids(); + prep_status("Creating databases in the new cluster"); + + /* install support functions in the database used by GLOBALS_DUMP_FILE */ + install_db_support_functions(os_info.user); + /* - * We have to create the databases first so we can create the toast table - * placeholder relfiles. + * We have to create the databases first so we can install support + * functions in all the other databases. */ - prep_status("Creating databases in the new cluster"); exec_prog(true, SYSTEMQUOTE "\"%s/psql\" --set ON_ERROR_STOP=on " /* --no-psqlrc prevents AUTOCOMMIT=off */ @@ -247,10 +251,20 @@ prepare_new_databases(void) static void create_new_objects(void) { + int dbnum; + /* -- NEW -- */ start_postmaster(&new_cluster, false); - install_support_functions(); + prep_status("Adding support functions to new cluster"); + + for (dbnum = 0; dbnum < new_cluster.dbarr.ndbs; dbnum++) + { + DbInfo *new_db = &new_cluster.dbarr.dbs[dbnum]; + + install_db_support_functions(new_db->db_name); + } + check_ok(); prep_status("Restoring database schema to new cluster"); exec_prog(true, diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h index 7f44a9d665..01eddfb67a 100644 --- a/contrib/pg_upgrade/pg_upgrade.h +++ b/contrib/pg_upgrade/pg_upgrade.h @@ -324,7 +324,7 @@ void check_hard_link(void); /* function.c */ -void install_support_functions(void); +void install_db_support_functions(const char *db_name); void uninstall_support_functions(void); void get_loadable_libraries(void); void check_loadable_libraries(void); diff --git a/contrib/pg_upgrade_support/pg_upgrade_support.c b/contrib/pg_upgrade_support/pg_upgrade_support.c index da8bd581de..b499ff80e6 100644 --- a/contrib/pg_upgrade_support/pg_upgrade_support.c +++ b/contrib/pg_upgrade_support/pg_upgrade_support.c @@ -29,6 +29,7 @@ extern PGDLLIMPORT Oid binary_upgrade_next_index_pg_class_oid; extern PGDLLIMPORT Oid binary_upgrade_next_toast_pg_class_oid; extern PGDLLIMPORT Oid binary_upgrade_next_pg_enum_oid; +extern PGDLLIMPORT Oid binary_upgrade_next_pg_authid_oid; Datum set_next_pg_type_oid(PG_FUNCTION_ARGS); Datum set_next_array_pg_type_oid(PG_FUNCTION_ARGS); @@ -39,6 +40,7 @@ Datum set_next_index_pg_class_oid(PG_FUNCTION_ARGS); Datum set_next_toast_pg_class_oid(PG_FUNCTION_ARGS); Datum set_next_pg_enum_oid(PG_FUNCTION_ARGS); +Datum set_next_pg_authid_oid(PG_FUNCTION_ARGS); PG_FUNCTION_INFO_V1(set_next_pg_type_oid); PG_FUNCTION_INFO_V1(set_next_array_pg_type_oid); @@ -49,6 +51,7 @@ PG_FUNCTION_INFO_V1(set_next_index_pg_class_oid); PG_FUNCTION_INFO_V1(set_next_toast_pg_class_oid); PG_FUNCTION_INFO_V1(set_next_pg_enum_oid); +PG_FUNCTION_INFO_V1(set_next_pg_authid_oid); Datum @@ -120,3 +123,13 @@ set_next_pg_enum_oid(PG_FUNCTION_ARGS) PG_RETURN_VOID(); } + +Datum +set_next_pg_authid_oid(PG_FUNCTION_ARGS) +{ + Oid authoid = PG_GETARG_OID(0); + + binary_upgrade_next_pg_authid_oid = authoid; + PG_RETURN_VOID(); +} + diff --git a/src/backend/commands/user.c b/src/backend/commands/user.c index be049cb994..63f22d8adc 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 17a73b87f1..29282324a2 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 -- 2.40.0