On Mon, Mar 9, 2015 at 12:43:05PM -0400, Bruce Momjian wrote:
> On Fri, Mar 6, 2015 at 06:10:15PM -0500, Stephen Frost wrote:
> > Technically, there haven't been any complaints about either pg_dumpall's
> > behavior in this regard, or pg_upgrade's, but pg_upgrade's post-upgrade
> > scripts would happily remove any databases which were marked as
> > 'datallowconn = false' and that scares the daylights out of me. To that
> > end, I'd suggest patching (and back-patching) pg_upgrade to check early
> > on that:
> >
> > template0 is set to 'datallowconn = false'
> >
> > AND
> >
> > all databases except template0 are set to 'datallowconn = true'
> >
> > The first is required or anyone who has done that will get the funny
> > error that started this thread and things won't work anyway, but I
> > believe the latter is also necessary to patch and back-patch as it could
> > lead to data loss. It's not a high potential as, hopefully, people will
> > check first, but I can imagine a hosting provider or environments where
> > there are lots of independent clusters not catching this issue in their
> > testing, only to discover someone set their database to 'datallowconn =
> > false' for whatever reason and now that database is gone...
>
> Agreed. I will work on a patch for this.
Attached is a patch that implements this, and it should be backpatch to
all versions.
--
Bruce Momjian <bruce@xxxxxxxxxx> http://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/bin/pg_upgrade/check.c b/src/bin/pg_upgrade/check.c
new file mode 100644
index be66b24..5eaa67b
*** a/src/bin/pg_upgrade/check.c
--- b/src/bin/pg_upgrade/check.c
*************** static void check_databases_are_compatib
*** 19,24 ****
--- 19,25 ----
static void check_locale_and_encoding(DbInfo *olddb, DbInfo *newdb);
static bool equivalent_locale(int category, const char *loca, const char *locb);
static void check_is_install_user(ClusterInfo *cluster);
+ static void check_proper_datallowconn(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_and_dump_old_cluster(bool live_che
*** 93,98 ****
--- 94,100 ----
* Check for various failure cases
*/
check_is_install_user(&old_cluster);
+ check_proper_datallowconn(&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_is_install_user(ClusterInfo *clust
*** 640,645 ****
--- 642,699 ----
check_ok();
}
+
+
+ static void
+ check_proper_datallowconn(ClusterInfo *cluster)
+ {
+ int dbnum;
+ PGconn *conn_template1;
+ PGresult *dbres;
+ int ntups;
+ int i_datname;
+ int i_datallowconn;
+
+ prep_status("Checking for proper database connection permissions");
+
+ conn_template1 = connectToServer(cluster, "template1");
+
+ /* get database names */
+ dbres = executeQueryOrDie(conn_template1,
+ "SELECT datname, datallowconn "
+ "FROM pg_catalog.pg_database");
+
+ i_datname = PQfnumber(dbres, "datname");
+ i_datallowconn = PQfnumber(dbres, "datallowconn");
+
+ ntups = PQntuples(dbres);
+ for (dbnum = 0; dbnum < ntups; dbnum++)
+ {
+ char *datname = PQgetvalue(dbres, dbnum, i_datname);
+ char *datallowconn = PQgetvalue(dbres, dbnum, i_datallowconn);
+
+ if (strcmp(datname, "template0") == 0)
+ {
+ /* avoid restore failure when pg_dumpall tries to create template0 */
+ if (strcmp(datallowconn, "t") == 0)
+ pg_fatal("template0 must not allow connections,\n"
+ "i.e. its pg_database.datallowconn must be false\n");
+ }
+ else
+ {
+ /* avoid datallowconn == false databases from being skipped on restore */
+ if (strcmp(datallowconn, "f") == 0)
+ pg_fatal("All non-template0 databases must allow connections,\n"
+ "i.e. their pg_database.datallowconn must be true\n");
+ }
+ }
+
+ PQclear(dbres);
+
+ PQfinish(conn_template1);
+
+ check_ok();
+ }
/*
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
