On Fri, Jun 01, 2012 at 09:52:59AM -0400, Tom Lane wrote:
> Bryan Murphy <[email protected]> writes:
> > The old 9.0 cluster was created by ubuntu. In this cluster there was an
> > ubuntu user with an oid of 10 and a postgres user with an oid of 16386.
>
> > The new 9.1 cluster was created with a custom build of postgres 9.1. This
> > did not have an ubuntu user, and it had a postgres user with an oid of 10.
>
> OID 10 is the bootstrap superuser, which is created with the name of the
> operating system user that ran initdb. So the above does not sound like
> anything to do with custom vs stock builds, but with who did initdb.
>
> It seems that pg_upgrade needs a check to make sure that the bootstrap
> superuser is named the same in old and new clusters.
[ Thread moved to hackers.]
OK, I have studied this. First we preserve pg_authid.oid because oids
are stored in pg_largeobject_metadata. Second, we dumpall all users,
even the install user because (from pg_dumpall.c):
* 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
* won't hurt for the CREATE to fail). This is particularly important
* for the role we are connected as, since even with --clean we will
* have failed to drop it.
So, pg_upgrade has to strip out restoring the install user because that
would cause an error on restore. That is done in
dump.c::split_old_dump().
The problem is if the old and new install users have different oids, as
the reporter verified.
The attached patch adds checks to verify the the old/new servers have
the same install-user oid.
--
Bruce Momjian <[email protected]> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ It's impossible for everything to be true. +
diff --git a/contrib/pg_upgrade/pg_upgrade.c b/contrib/pg_upgrade/pg_upgrade.c
new file mode 100644
index 465ecdd..ba81823
*** a/contrib/pg_upgrade/pg_upgrade.c
--- b/contrib/pg_upgrade/pg_upgrade.c
***************
*** 29,35 ****
* We control all assignments of pg_enum.oid because these oids are stored
* in user tables as enum values.
*
! * We control all assignments of pg_auth.oid because these oids are stored
* in pg_largeobject_metadata.
*/
--- 29,35 ----
* We control all assignments of pg_enum.oid because these oids are stored
* in user tables as enum values.
*
! * We control all assignments of pg_authid.oid because these oids are stored
* in pg_largeobject_metadata.
*/
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 2669c09..df77f53
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** static void set_locale_and_encoding(Clus
*** 16,22 ****
static void check_new_cluster_is_empty(void);
static void check_locale_and_encoding(ControlData *oldctrl,
ControlData *newctrl);
! static void check_is_super_user(ClusterInfo *cluster);
static void check_for_prepared_transactions(ClusterInfo *cluster);
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
--- 16,22 ----
static void check_new_cluster_is_empty(void);
static void check_locale_and_encoding(ControlData *oldctrl,
ControlData *newctrl);
! static void check_is_super_user_get_oid(ClusterInfo *cluster);
static void check_for_prepared_transactions(ClusterInfo *cluster);
static void check_for_isn_and_int8_passing_mismatch(ClusterInfo *cluster);
static void check_for_reg_data_type_usage(ClusterInfo *cluster);
*************** check_old_cluster(bool live_check, char
*** 69,75 ****
/*
* Check for various failure cases
*/
! check_is_super_user(&old_cluster);
check_for_prepared_transactions(&old_cluster);
check_for_reg_data_type_usage(&old_cluster);
check_for_isn_and_int8_passing_mismatch(&old_cluster);
--- 69,75 ----
/*
* Check for various failure cases
*/
! check_is_super_user_get_oid(&old_cluster);
check_for_prepared_transactions(&old_cluster);
check_for_reg_data_type_usage(&old_cluster);
check_for_isn_and_int8_passing_mismatch(&old_cluster);
*************** check_new_cluster(void)
*** 121,137 ****
{
set_locale_and_encoding(&new_cluster);
get_db_and_rel_infos(&new_cluster);
check_new_cluster_is_empty();
- check_for_prepared_transactions(&new_cluster);
check_loadable_libraries();
- check_locale_and_encoding(&old_cluster.controldata, &new_cluster.controldata);
-
if (user_opts.transfer_mode == TRANSFER_MODE_LINK)
check_hard_link();
}
--- 121,144 ----
{
set_locale_and_encoding(&new_cluster);
+ check_locale_and_encoding(&old_cluster.controldata, &new_cluster.controldata);
+
get_db_and_rel_infos(&new_cluster);
check_new_cluster_is_empty();
check_loadable_libraries();
if (user_opts.transfer_mode == TRANSFER_MODE_LINK)
check_hard_link();
+
+ check_is_super_user_get_oid(&new_cluster);
+ /* We don't restore our own user, so both clusters better have equal user oids */
+ if (old_cluster.user_oid != new_cluster.user_oid)
+ pg_log(PG_FATAL,
+ "Old and new cluster superusers have different values for pg_authid.oid");
+
+ check_for_prepared_transactions(&new_cluster);
}
*************** create_script_for_old_cluster_deletion(c
*** 577,588 ****
/*
! * check_is_super_user()
*
* Make sure we are the super-user.
*/
static void
! check_is_super_user(ClusterInfo *cluster)
{
PGresult *res;
PGconn *conn = connectToServer(cluster, "template1");
--- 584,595 ----
/*
! * check_is_super_user_get_oid()
*
* Make sure we are the super-user.
*/
static void
! check_is_super_user_get_oid(ClusterInfo *cluster)
{
PGresult *res;
PGconn *conn = connectToServer(cluster, "template1");
*************** check_is_super_user(ClusterInfo *cluster
*** 591,597 ****
/* Can't use pg_authid because only superusers can view it. */
res = executeQueryOrDie(conn,
! "SELECT rolsuper "
"FROM pg_catalog.pg_roles "
"WHERE rolname = current_user");
--- 598,604 ----
/* Can't use pg_authid because only superusers can view it. */
res = executeQueryOrDie(conn,
! "SELECT rolsuper, oid "
"FROM pg_catalog.pg_roles "
"WHERE rolname = current_user");
*************** check_is_super_user(ClusterInfo *cluster
*** 599,604 ****
--- 606,613 ----
pg_log(PG_FATAL, "database user \"%s\" is not a superuser\n",
os_info.user);
+ cluster->user_oid = atooid(PQgetvalue(res, 0, 1));
+
PQclear(res);
PQfinish(conn);
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 26aa7bb..ef59c2b
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** typedef struct
*** 230,235 ****
--- 230,236 ----
char major_version_str[64]; /* string PG_VERSION of cluster */
uint32 bin_version; /* version returned from pg_ctl */
Oid pg_database_oid; /* OID of pg_database relation */
+ Oid user_oid; /* OID of connected user */
char *tablespace_suffix; /* directory specification */
} ClusterInfo;
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
