[#3025] automatic init of postgresql schema

10 files changed, 172 insertions, 29 deletions

diff --git a/src/lib/database/db_log.cc b/src/lib/database/db_log.cc
index 612d71653b..3fb45e4c1e 100644
--- a/src/lib/database/db_log.cc
+++ b/src/lib/database/db_log.cc
@@ -24,6 +24,7 @@ const int DB_DBG_TRACE_DETAIL = isc::log::DBGLVL_TRACE_DETAIL;
 const DbLogger::MessageMap db_message_map = {
     { DB_INVALID_ACCESS,                  DATABASE_INVALID_ACCESS },
 
+    { PGSQL_INITIALIZE_SCHEMA,            DATABASE_PGSQL_INITIALIZE_SCHEMA },
     { PGSQL_DEALLOC_ERROR,                DATABASE_PGSQL_DEALLOC_ERROR },
     { PGSQL_FATAL_ERROR,                  DATABASE_PGSQL_FATAL_ERROR },
     { PGSQL_START_TRANSACTION,            DATABASE_PGSQL_START_TRANSACTION },
diff --git a/src/lib/database/db_log.h b/src/lib/database/db_log.h
index 4f042507c1..3207d51a16 100644
--- a/src/lib/database/db_log.h
+++ b/src/lib/database/db_log.h
@@ -51,6 +51,7 @@ extern isc::log::Logger database_logger;
 enum DbMessageID {
     DB_INVALID_ACCESS,
 
+    PGSQL_INITIALIZE_SCHEMA,
     PGSQL_DEALLOC_ERROR,
     PGSQL_FATAL_ERROR,
     PGSQL_START_TRANSACTION,
diff --git a/src/lib/database/db_messages.cc b/src/lib/database/db_messages.cc
index bb55034cfa..d3781b31e1 100644
--- a/src/lib/database/db_messages.cc
+++ b/src/lib/database/db_messages.cc
@@ -17,6 +17,7 @@ extern const isc::log::MessageID DATABASE_PGSQL_COMMIT = "DATABASE_PGSQL_COMMIT"
 extern const isc::log::MessageID DATABASE_PGSQL_CREATE_SAVEPOINT = "DATABASE_PGSQL_CREATE_SAVEPOINT";
 extern const isc::log::MessageID DATABASE_PGSQL_DEALLOC_ERROR = "DATABASE_PGSQL_DEALLOC_ERROR";
 extern const isc::log::MessageID DATABASE_PGSQL_FATAL_ERROR = "DATABASE_PGSQL_FATAL_ERROR";
+extern const isc::log::MessageID DATABASE_PGSQL_INITIALIZE_SCHEMA = "DATABASE_PGSQL_INITIALIZE_SCHEMA";
 extern const isc::log::MessageID DATABASE_PGSQL_ROLLBACK = "DATABASE_PGSQL_ROLLBACK";
 extern const isc::log::MessageID DATABASE_PGSQL_ROLLBACK_SAVEPOINT = "DATABASE_PGSQL_ROLLBACK_SAVEPOINT";
 extern const isc::log::MessageID DATABASE_PGSQL_START_TRANSACTION = "DATABASE_PGSQL_START_TRANSACTION";
@@ -34,13 +35,14 @@ const char* values[] = {
     "DATABASE_INVALID_ACCESS", "invalid database access string: %1",
     "DATABASE_MYSQL_COMMIT", "committing to MySQL database",
     "DATABASE_MYSQL_FATAL_ERROR", "Unrecoverable MySQL error occurred: %1 for <%2>, reason: %3 (error code: %4).",
-    "DATABASE_MYSQL_INITIALIZE_SCHEMA", "Initializing the MySQL schema with command: kea-admin %1.",
+    "DATABASE_MYSQL_INITIALIZE_SCHEMA", "Initializing the MySQL schema with command: %1.",
     "DATABASE_MYSQL_ROLLBACK", "rolling back MySQL database",
     "DATABASE_MYSQL_START_TRANSACTION", "starting new MySQL transaction",
     "DATABASE_PGSQL_COMMIT", "committing to PostgreSQL database",
     "DATABASE_PGSQL_CREATE_SAVEPOINT", "creating a new PostgreSQL savepoint: %1",
     "DATABASE_PGSQL_DEALLOC_ERROR", "An error occurred deallocating SQL statements while closing the PostgreSQL lease database: %1",
     "DATABASE_PGSQL_FATAL_ERROR", "Unrecoverable PostgreSQL error occurred: Statement: <%1>, reason: %2 (error code: %3).",
+    "DATABASE_PGSQL_INITIALIZE_SCHEMA", "Initializing the PostgreSQL schema with command: %1.",
     "DATABASE_PGSQL_ROLLBACK", "rolling back PostgreSQL database",
     "DATABASE_PGSQL_ROLLBACK_SAVEPOINT", "rolling back PostgreSQL database to savepoint: $1",
     "DATABASE_PGSQL_START_TRANSACTION", "starting a new PostgreSQL transaction",
diff --git a/src/lib/database/db_messages.h b/src/lib/database/db_messages.h
index 3ddc1efc69..bb6a059a62 100644
--- a/src/lib/database/db_messages.h
+++ b/src/lib/database/db_messages.h
@@ -18,6 +18,7 @@ extern const isc::log::MessageID DATABASE_PGSQL_COMMIT;
 extern const isc::log::MessageID DATABASE_PGSQL_CREATE_SAVEPOINT;
 extern const isc::log::MessageID DATABASE_PGSQL_DEALLOC_ERROR;
 extern const isc::log::MessageID DATABASE_PGSQL_FATAL_ERROR;
+extern const isc::log::MessageID DATABASE_PGSQL_INITIALIZE_SCHEMA;
 extern const isc::log::MessageID DATABASE_PGSQL_ROLLBACK;
 extern const isc::log::MessageID DATABASE_PGSQL_ROLLBACK_SAVEPOINT;
 extern const isc::log::MessageID DATABASE_PGSQL_START_TRANSACTION;
diff --git a/src/lib/database/db_messages.mes b/src/lib/database/db_messages.mes
index 958b48aac6..3f4c09ea82 100644
--- a/src/lib/database/db_messages.mes
+++ b/src/lib/database/db_messages.mes
@@ -17,10 +17,6 @@ The code has issued a commit call.  All outstanding transactions will be
 committed to the database.  Note that depending on the MySQL settings,
 the committal may not include a write to disk.
 
-% DATABASE_MYSQL_INITIALIZE_SCHEMA Initializing the MySQL schema with command: kea-admin %1.
-This is logged before running the kea-admin command to automatically initialize the schema from Kea
-after getting the schema version initially failed. The full kea-admin command is shown.
-
 % DATABASE_MYSQL_FATAL_ERROR Unrecoverable MySQL error occurred: %1 for <%2>, reason: %3 (error code: %4).
 An error message indicating that communication with the MySQL database server
 has been lost.  If automatic recovery has been enabled,  then the server will
@@ -28,6 +24,10 @@ attempt to recover connectivity.  If not, then the server will exit with a
 non-zero exit code.  The cause of such an error is most likely a network issue
 or the MySQL server has gone down.
 
+% DATABASE_MYSQL_INITIALIZE_SCHEMA Initializing the MySQL schema with command: %1.
+This is logged before running the kea-admin command to automatically initialize the schema from Kea
+after getting the schema version initially failed. The full kea-admin command is shown.
+
 % DATABASE_MYSQL_ROLLBACK rolling back MySQL database
 The code has issued a rollback call.  All outstanding transaction will
 be rolled back and not committed to the database.
@@ -66,6 +66,10 @@ attempt to recover the connectivity.  If not, then the server will exit with a
 non-zero exit code.  The cause of such an error is most likely a network issue
 or the PostgreSQL server has gone down.
 
+% DATABASE_PGSQL_INITIALIZE_SCHEMA Initializing the PostgreSQL schema with command: %1.
+This is logged before running the kea-admin command to automatically initialize the schema from Kea
+after getting the schema version initially failed. The full kea-admin command is shown.
+
 % DATABASE_PGSQL_ROLLBACK rolling back PostgreSQL database
 The code has issued a rollback call.  All outstanding transaction will
 be rolled back and not committed to the database.
diff --git a/src/lib/dhcpsrv/pgsql_lease_mgr.cc b/src/lib/dhcpsrv/pgsql_lease_mgr.cc
index 65382a95e2..5f21fab9a9 100644
--- a/src/lib/dhcpsrv/pgsql_lease_mgr.cc
+++ b/src/lib/dhcpsrv/pgsql_lease_mgr.cc
@@ -1612,11 +1612,20 @@ PgSqlLeaseMgr::PgSqlLeaseTrackingContextAlloc::~PgSqlLeaseTrackingContextAlloc()
 // PgSqlLeaseMgr Constructor and Destructor
 
 PgSqlLeaseMgr::PgSqlLeaseMgr(const DatabaseConnection::ParameterMap& parameters)
-    : TrackingLeaseMgr(), parameters_(parameters), timer_name_("") {
+    : TrackingLeaseMgr(), parameters_(parameters) {
 
     // Check if the extended info tables are enabled.
     setExtendedInfoTablesEnabled(parameters);
 
+    // retry-on-startup?
+    bool const retry(parameters.count("retry-on-startup") &&
+                     parameters.at("retry-on-startup") == "true");
+
+    // retry-on-startup disabled. Ensure schema version with empty timer name / no retry.
+    if (!retry) {
+        ensureSchemaVersion();
+    }
+
     // Create unique timer name per instance.
     timer_name_ = "PgSqlLeaseMgr[";
     timer_name_ += boost::lexical_cast<std::string>(reinterpret_cast<uint64_t>(this));
@@ -1644,28 +1653,9 @@ PgSqlLeaseMgr::PgSqlLeaseMgr(const DatabaseConnection::ParameterMap& parameters)
     }
 #endif
 
-    // Validate schema version first.
-    std::pair<uint32_t, uint32_t> code_version(PGSQL_SCHEMA_VERSION_MAJOR,
-                                               PGSQL_SCHEMA_VERSION_MINOR);
-
-    std::string timer_name;
-    bool retry = false;
-    if (parameters.count("retry-on-startup")) {
-        if (parameters.at("retry-on-startup") == "true") {
-            retry = true;
-        }
-    }
+    // retry-on-startup enabled. Ensure schema version with timer name set / retries.
     if (retry) {
-        timer_name = timer_name_;
-    }
-
-    std::pair<uint32_t, uint32_t> db_version = getVersion(timer_name);
-    if (code_version != db_version) {
-        isc_throw(DbOpenError,
-                  "PostgreSQL schema version mismatch: need version: "
-                      << code_version.first << "." << code_version.second
-                      << " found version: " << db_version.first << "."
-                      << db_version.second);
+        ensureSchemaVersion();
     }
 
     // Create an initial context.
@@ -3044,6 +3034,16 @@ PgSqlLeaseMgr::getDescription() const {
     return (std::string("PostgreSQL Database"));
 }
 
+void
+PgSqlLeaseMgr::ensureSchemaVersion() const {
+    LOG_DEBUG(dhcpsrv_logger, DHCPSRV_DBG_TRACE_DETAIL, DHCPSRV_PGSQL_GET_VERSION);
+
+    IOServiceAccessorPtr ac(new IOServiceAccessor(&DatabaseConnection::getIOService));
+    DbCallback cb(&PgSqlLeaseMgr::dbReconnect);
+
+    return (PgSqlConnection::ensureSchemaVersion(parameters_, ac, cb, timer_name_));
+}
+
 std::pair<uint32_t, uint32_t>
 PgSqlLeaseMgr::getVersion(const string& timer_name) const {
     LOG_DEBUG(dhcpsrv_logger, DHCPSRV_DBG_TRACE_DETAIL, DHCPSRV_PGSQL_GET_VERSION);
diff --git a/src/lib/dhcpsrv/pgsql_lease_mgr.h b/src/lib/dhcpsrv/pgsql_lease_mgr.h
index d6bc94a212..553415feaa 100644
--- a/src/lib/dhcpsrv/pgsql_lease_mgr.h
+++ b/src/lib/dhcpsrv/pgsql_lease_mgr.h
@@ -676,6 +676,8 @@ public:
     /// @return Description of the backend.
     virtual std::string getDescription() const override;
 
+    void ensureSchemaVersion() const;
+
     /// @brief Returns backend version.
     ///
     /// @param timer_name The DB reconnect timer name.
diff --git a/src/lib/pgsql/Makefile.am b/src/lib/pgsql/Makefile.am
index b89ef34ee5..a7b4fae52b 100644
--- a/src/lib/pgsql/Makefile.am
+++ b/src/lib/pgsql/Makefile.am
@@ -1,6 +1,8 @@
 SUBDIRS = . testutils tests
 
-AM_CPPFLAGS = -I$(top_srcdir)/src/lib -I$(top_builddir)/src/lib
+AM_CPPFLAGS  =
+AM_CPPFLAGS += -DKEA_ADMIN=\"@prefix@/sbin/kea-admin\"
+AM_CPPFLAGS += -I$(top_srcdir)/src/lib -I$(top_builddir)/src/lib
 AM_CPPFLAGS += $(BOOST_INCLUDES) $(PGSQL_CPPFLAGS)
 
 AM_CXXFLAGS = $(KEA_CXXFLAGS)
diff --git a/src/lib/pgsql/pgsql_connection.cc b/src/lib/pgsql/pgsql_connection.cc
index ee98d54e6b..90df2c2916 100644
--- a/src/lib/pgsql/pgsql_connection.cc
+++ b/src/lib/pgsql/pgsql_connection.cc
@@ -6,6 +6,8 @@
 
 #include <config.h>
 
+#include <asiolink/io_service.h>
+#include <asiolink/process_spawn.h>
 #include <database/database_connection.h>
 #include <database/db_exceptions.h>
 #include <database/db_log.h>
@@ -29,6 +31,7 @@
 
 #include <sstream>
 
+using namespace isc::asiolink;
 using namespace std;
 
 namespace isc {
@@ -149,7 +152,7 @@ PgSqlConnection::getVersion(const ParameterMap& parameters,
     // Open the database.
     conn.openDatabaseInternal(false);
 
-    const char* version_sql =  "SELECT version, minor FROM schema_version;";
+    const char* version_sql = "SELECT version, minor FROM schema_version;";
     PgSqlResult r(PQexec(conn.conn_, version_sql));
     if (PQresultStatus(r) != PGRES_TUPLES_OK) {
         isc_throw(DbOperationError, "unable to execute PostgreSQL statement <"
@@ -166,6 +169,107 @@ PgSqlConnection::getVersion(const ParameterMap& parameters,
 }
 
 void
+PgSqlConnection::ensureSchemaVersion(const ParameterMap& parameters,
+                                     const IOServiceAccessorPtr& ac,
+                                     const DbCallback& cb,
+                                     const string& timer_name) {
+    pair<uint32_t, uint32_t> schema_version;
+    try {
+        schema_version = getVersion(parameters, ac, cb, timer_name);
+    } catch (DbOpenError const& exception) {
+        // Do nothing for open errors. We first need to establish a connection,
+        // and only afterwards can we initialize the schema if it still fails.
+        // Let it fail, or retry if retry is configured.
+    } catch (exception const& exception) {
+        // This may fail for a variety of reasons. We don't have to necessarily
+        // check for the error that is most common in situations where the
+        // database is not initialized which would sound something like
+        // "table schema_version does not exist". If the error had another
+        // cause, it will fail again during initialization or during the
+        // subsequent version retrieval and that is fine.
+        initializeSchema(parameters);
+
+        // Retrieve again because the initial retrieval failed.
+        schema_version = getVersion(parameters, ac, cb, timer_name);
+    }
+
+    // Check that the versions match.
+    pair<uint32_t, uint32_t> const expected_version(PGSQL_SCHEMA_VERSION_MAJOR,
+                                                    PGSQL_SCHEMA_VERSION_MINOR);
+    if (schema_version != expected_version) {
+        isc_throw(DbOpenError, "PostgreSQL schema version mismatch: expected version: "
+                                   << expected_version.first << "." << expected_version.second
+                                   << ", found version: " << schema_version.first << "."
+                                   << schema_version.second);
+    }
+}
+
+void
+PgSqlConnection::initializeSchema(const ParameterMap& parameters) {
+    if (parameters.count("readonly") && parameters.at("readonly") == "true") {
+        // The readonly flag is historically used for host backends. Still, if
+        // enabled, it is a strong indication that we should not meDDLe with it.
+        return;
+    }
+
+    // Convert parameters.
+    auto const tupl(toKeaAdminParameters(parameters));
+    vector<string> kea_admin_parameters(get<0>(tupl));
+    ProcessEnvVars const vars(get<1>(tupl));
+    kea_admin_parameters.insert(kea_admin_parameters.begin(), "db-init");
+
+    // Run.
+    IOServicePtr io_service(new IOService());
+    ProcessSpawn kea_admin(io_service, KEA_ADMIN, kea_admin_parameters, vars);
+    DB_LOG_INFO(PGSQL_INITIALIZE_SCHEMA).arg(kea_admin.getCommandLine());
+    pid_t const pid(kea_admin.spawn());
+    io_service->runOne();
+    if (kea_admin.isRunning(pid)) {
+        // TODO: implement synchronous process spawning. Otherwise kea-admin is not waited by the
+        // parent process, and it becomes a zombie, even though its work is finished. Uncomment the
+        // following throw when that is done.
+        // isc_throw(SchemaInitializationFailed, "kea-admin still running");
+    }
+    int const exit_code(kea_admin.getExitStatus(pid));
+    if (exit_code != 0) {
+        isc_throw(SchemaInitializationFailed, "Expected exit code 0. Got " << exit_code);
+    }
+}
+
+tuple<vector<string>, vector<string>>
+PgSqlConnection::toKeaAdminParameters(ParameterMap const& params) {
+    vector<string> result{"pgsql"};
+    ProcessEnvVars vars;
+    for (auto const& p : params) {
+        string const& keyword(p.first);
+        string const& value(p.second);
+
+        // These Kea parameters are the same as the kea-admin parameters.
+        if (keyword == "user" ||
+            keyword == "password" ||
+            keyword == "host" ||
+            keyword == "port" ||
+            keyword == "name") {
+            result.push_back("--" + keyword);
+            result.push_back(value);
+            continue;
+        }
+
+        // These Kea parameters do not have a direct kea-admin equivalent.
+        // But they do have a psql client environment variable equivalent.
+        // We pass them to kea-admin.
+        static unordered_map<string, string> conversions{
+            {"connect-timeout", "PGCONNECT_TIMEOUT"},
+            // {"tcp-user-timeout", "N/A"},
+        };
+        if (conversions.count(keyword)) {
+            vars.push_back(conversions.at(keyword) + "=" + value);
+        }
+    }
+    return make_tuple(result, vars);
+}
+
+void
 PgSqlConnection::prepareStatement(const PgSqlTaggedStatement& statement) {
     // Prepare all statements queries with all known fields datatype
     PgSqlResult r(PQprepare(conn_, statement.name, statement.text,
diff --git a/src/lib/pgsql/pgsql_connection.h b/src/lib/pgsql/pgsql_connection.h
index 6e7b4d8f0f..66e69aad5f 100644
--- a/src/lib/pgsql/pgsql_connection.h
+++ b/src/lib/pgsql/pgsql_connection.h
@@ -226,6 +226,9 @@ public:
     /// @brief Destructor
     virtual ~PgSqlConnection();
 
+    static std::tuple<std::vector<std::string>, std::vector<std::string>>
+    toKeaAdminParameters(ParameterMap const& params);
+
     /// @brief Get the schema version.
     ///
     /// @param parameters A data structure relating keywords and values
@@ -245,6 +248,29 @@ public:
                const DbCallback& cb = DbCallback(),
                const std::string& timer_name = std::string());
 
+    /// @brief Retrieve schema version, validate it against the hardcoded
+    ///     version, and attempt to initialize the schema if there is an
+    ///     error during retrieval.
+    ///
+    /// @param parameters A data structure relating keywords and values
+    ///     concerned with the database.
+    ///
+    /// @throw isc::db::ScehamInitializationFailed if the initialization fails
+    static void
+    ensureSchemaVersion(const ParameterMap& parameters,
+                        const IOServiceAccessorPtr& ac = IOServiceAccessorPtr(),
+                        const DbCallback& cb = DbCallback(),
+                        const std::string& timer_name = std::string());
+
+    /// @brief Initialize schema.
+    ///
+    /// @param parameters A data structure relating keywords and values
+    ///     concerned with the database.
+    ///
+    /// @throw isc::db::ScehamInitializationFailed if the initialization fails
+    static void
+    initializeSchema(const ParameterMap& parameters);
+
     /// @brief Prepare Single Statement
     ///
     /// Creates a prepared statement from the text given and adds it to the