On Sat, Jun 02, 2012 at 05:10:03PM -0400, Tom Lane wrote:
> Bruce Momjian <[email protected]> writes:
> > On Fri, Jun 01, 2012 at 09:52:59AM -0400, Tom Lane wrote:
> >> It seems that pg_upgrade needs a check to make sure that the bootstrap
> >> superuser is named the same in old and new clusters.
>
> > The attached patch adds checks to verify the the old/new servers have
> > the same install-user oid.
>
> That may or may not be a useful check to make, but it's got
> approximately nothing to do with what I was complaining about.
>
> In particular, supposing that the user has given you a username that
> isn't the bootstrap superuser in the new cluster, this patch is not
> going to stop the update script from failing. Because the script is
> then going to try to replace the bootstrap superuser, and that is
> certainly going to give an error.
>
> I see the point of worrying about the install user as well as the
> bootstrap superuser, but wouldn't it be best to insist they be the same?
> Particularly in the new cluster, where if they aren't the same it means
> the user has manually created at least one role in the new cluster,
> which is likely to lead to OID conflicts or worse.
>
> Furthermore, if the bootstrap superusers aren't named the same, your
> patch fails to handle the original complaint. In the case the
> OP mentioned, the old cluster had
> OID 10: "ubuntu"
> some user-defined OID: "postgres"
> and the new cluster had
> OID 10: "postgres"
> If the user tells pg_upgrade to use username postgres, your check will
> not fail AFAICS, but nonetheless things are going to be messed up after
> the upgrade, because some objects and privileges that used to belong to
> the bootstrap superuser will now belong to a non-default superuser,
> whereas what used to belong to the non-default superuser will now belong
> to the bootstrap superuser. That cannot be thought desirable. For one
> reason, in the old installation the postgres role could have been
> dropped (possibly after dropping a few non-builtin objects) whereas the
> "ubuntu" role was pinned. In the new installation, "postgres" is pinned
> and "ubuntu" won't be.
>
> I think the checks that are actually needed here are (1) bootstrap
> superusers are named the same, and (2) there are no roles other than the
> bootstrap superuser in the new cluster.
You are right that it is more complex than I stated, but given the
limited feedback I got on the pg_upgrade/plplython, I figured people
didn't want to hear the details. Here they are:
There are three failure modes for pg_upgrade:
1. check failure
2. schema restore failure
3. silent failure/corruption
Of course, the later items are worse than the earlier ones. The
reporter got a "schema restore failure" while still following the
pg_upgrade instructions. My initial patch changed that #2 error to a #1
error. Tom is right that creating users in the new cluster (against
instructions), can still generate a #2 error if a new/old pg_authid.oid
match, and they are not the install user, but seeing that is something
that is against the instructions, I was going to leave that as a #2.
However, since Tom feels we should check that and make it a #1 failure,
I have added that test to the attached patch.
--
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/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index d226f00..9f3dcda
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** check_new_cluster(void)
*** 138,144 ****
* We don't restore our own user, so both clusters must match have
* matching install-user oids.
*/
! if (old_cluster.install_user_oid != new_cluster.install_user_oid)
pg_log(PG_FATAL,
"Old and new cluster install users have different values for pg_authid.oid.\n");
--- 138,144 ----
* We don't restore our own user, so both clusters must match have
* matching install-user oids.
*/
! if (old_cluster.install_role_oid != new_cluster.install_role_oid)
pg_log(PG_FATAL,
"Old and new cluster install users have different values for pg_authid.oid.\n");
*************** check_new_cluster(void)
*** 147,153 ****
* defined users might match users defined in the old cluster and
* generate an error during pg_dump restore.
*/
! if (new_cluster.user_count != 1)
pg_log(PG_FATAL, "Only the install user can be defined in the new cluster.\n");
check_for_prepared_transactions(&new_cluster);
--- 147,153 ----
* defined users might match users defined in the old cluster and
* generate an error during pg_dump restore.
*/
! if (new_cluster.role_count != 1)
pg_log(PG_FATAL, "Only the install user can be defined in the new cluster.\n");
check_for_prepared_transactions(&new_cluster);
*************** check_is_super_user(ClusterInfo *cluster
*** 618,624 ****
pg_log(PG_FATAL, "database user \"%s\" is not a superuser\n",
os_info.user);
! cluster->install_user_oid = atooid(PQgetvalue(res, 0, 1));
PQclear(res);
--- 618,624 ----
pg_log(PG_FATAL, "database user \"%s\" is not a superuser\n",
os_info.user);
! cluster->install_role_oid = atooid(PQgetvalue(res, 0, 1));
PQclear(res);
*************** check_is_super_user(ClusterInfo *cluster
*** 629,635 ****
if (PQntuples(res) != 1)
pg_log(PG_FATAL, "could not determine the number of users\n");
! cluster->user_count = atoi(PQgetvalue(res, 0, 0));
PQclear(res);
--- 629,635 ----
if (PQntuples(res) != 1)
pg_log(PG_FATAL, "could not determine the number of users\n");
! cluster->role_count = atoi(PQgetvalue(res, 0, 0));
PQclear(res);
diff --git a/contrib/pg_upgrade/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 528eea7..1d7f56c
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** typedef struct
*** 230,237 ****
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 install_user_oid; /* OID of connected user */
! Oid user_count; /* number of users defined in the cluster */
char *tablespace_suffix; /* directory specification */
} ClusterInfo;
--- 230,237 ----
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 install_role_oid; /* OID of connected role */
! Oid role_count; /* number of roles defined in the cluster */
char *tablespace_suffix; /* directory specification */
} ClusterInfo;
diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c
new file mode 100644
index 2669c09..d226f00
*** a/contrib/pg_upgrade/check.c
--- b/contrib/pg_upgrade/check.c
*************** 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,156 ----
{
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(&new_cluster);
+
+ /*
+ * We don't restore our own user, so both clusters must match have
+ * matching install-user oids.
+ */
+ if (old_cluster.install_user_oid != new_cluster.install_user_oid)
+ pg_log(PG_FATAL,
+ "Old and new cluster install users have different values for pg_authid.oid.\n");
+
+ /*
+ * We only allow the install user in the new cluster because other
+ * defined users might match users defined in the old cluster and
+ * generate an error during pg_dump restore.
+ */
+ if (new_cluster.user_count != 1)
+ pg_log(PG_FATAL, "Only the install user can be defined in the new cluster.\n");
+
+ check_for_prepared_transactions(&new_cluster);
}
*************** create_script_for_old_cluster_deletion(c
*** 579,585 ****
/*
* check_is_super_user()
*
! * Make sure we are the super-user.
*/
static void
check_is_super_user(ClusterInfo *cluster)
--- 598,604 ----
/*
* check_is_super_user()
*
! * Check we are superuser, and out user id and user count
*/
static void
check_is_super_user(ClusterInfo *cluster)
*************** 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");
--- 610,616 ----
/* 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 ****
--- 618,636 ----
pg_log(PG_FATAL, "database user \"%s\" is not a superuser\n",
os_info.user);
+ cluster->install_user_oid = atooid(PQgetvalue(res, 0, 1));
+
+ PQclear(res);
+
+ res = executeQueryOrDie(conn,
+ "SELECT COUNT(*) "
+ "FROM pg_catalog.pg_roles ");
+
+ if (PQntuples(res) != 1)
+ pg_log(PG_FATAL, "could not determine the number of users\n");
+
+ cluster->user_count = atoi(PQgetvalue(res, 0, 0));
+
PQclear(res);
PQfinish(conn);
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/pg_upgrade.h b/contrib/pg_upgrade/pg_upgrade.h
new file mode 100644
index 26aa7bb..528eea7
*** a/contrib/pg_upgrade/pg_upgrade.h
--- b/contrib/pg_upgrade/pg_upgrade.h
*************** typedef struct
*** 230,235 ****
--- 230,237 ----
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 install_user_oid; /* OID of connected user */
+ Oid user_count; /* number of users defined in the cluster */
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
