Snapshot current almost-working version of rpki-sql-setup,

before whacking it in a slightly different direction.

svn path=/branches/tk705/; revision=6254

3 files changed, 63 insertions, 34 deletions

diff --git a/buildtools/debian-skeleton/rpki-ca.postinst b/buildtools/debian-skeleton/rpki-ca.postinst
index 866183af..18fef863 100644
--- a/buildtools/debian-skeleton/rpki-ca.postinst
+++ b/buildtools/debian-skeleton/rpki-ca.postinst
@@ -50,7 +50,7 @@ setup_rpki_conf() {
 
 setup_sql() {
     #rpki-sql-setup --mysql-defaults /etc/mysql/debian.cnf create
-    rpki-sql-setup --postgresql-root-username postgres create
+    rpki-sql-setup --debug --verbose --postgresql-root-username postgres create
 }
 
 setup_bpki() {
diff --git a/buildtools/debian-skeleton/rpki-ca.prerm b/buildtools/debian-skeleton/rpki-ca.prerm
index 69689870..2754ed43 100644
--- a/buildtools/debian-skeleton/rpki-ca.prerm
+++ b/buildtools/debian-skeleton/rpki-ca.prerm
@@ -37,7 +37,7 @@ case "$1" in
 	# since that's where we find out whether this is a purge.
 
 	#rpki-sql-setup --mysql-defaults /etc/mysql/debian.cnf script-drop /etc/rpki/drop_databases.sql
-	rpki-sql-setup --postgresql-root-username postgres script-drop /etc/rpki/drop_databases.sql
+	rpki-sql-setup --debug --verbose --postgresql-root-username postgres script-drop /etc/rpki/drop_databases.sql
 
 	# Clean up our cron jobs.
 
diff --git a/ca/rpki-sql-setup b/ca/rpki-sql-setup
index 94e9d3ae..863e86ee 100755
--- a/ca/rpki-sql-setup
+++ b/ca/rpki-sql-setup
@@ -145,32 +145,59 @@ class PostgreSQL_Driver(Abstract_Driver):
         else:
             self._pw = None
         if self.args.verbose:
-            print "Initialized PostgreSQL driver, pw {!r}".format(pw)
+            print "Initialized PostgreSQL driver, pw {!r}".format(self._pw)
 
     def _execute(self, *sql_commands):
-        try:
-            pid = None if self._pw is None else os.fork()
+        if self.args.verbose:
+            print "PostgreSQL driver commands:"
+            for sql_command in sql_commands:
+                print " ", sql_command
+        pid = None if self._pw is None else os.fork()
+        if self.args.verbose:
+            print "PostgreSQL driver fork {}".format(pid)
+        if pid == 0:
+            if self.args.verbose:
+                print "PostgreSQL driver setuid({0.pw_gid})/setgid({0.pw_uid})".format(self._pw)
+                sys.stdout.flush()
+            os.setgid(self._pw.pw_gid)
+            os.setuid(self._pw.pw_uid)
+        if pid == 0 or pid is None:
+            if self.args.verbose:
+                print "PostgreSQL driver opening connection to database {}".format(self.args.postgresql_root_database)
+                sys.stdout.flush()
+
+            # Trusty supplies psychopg2 2.4.x, which is too old for
+            # with-based transactions, so do it the old fashioned way.
+
+            try:
+                db = cur = None
+                db = self.driver.connect(database = self.args.postgresql_root_database)
+                db.autocommit = True
+                cur = db.cursor()
+                for sql_command in sql_commands:
+                    if self.args.verbose:
+                        print "PostgreSQL driver executing command {!r}".format(sql_command)
+                        sys.stdout.flush()
+                    cur.execute(sql_command)
+            finally:
+                if cur is not None:
+                    cur.close()
+                if db is not None:
+                    db.close()
+
+        if pid == 0:
             if self.args.verbose:
-                print "PostgreSQL driver fork {}".format(pid)
-            if pid == 0:
-                os.setgid(self._pw.pw_gid)
-                os.setuid(self._pw.pw_uid)
-            if not pid:
-                with self.driver.connect(database = self.args.postgresql_root_database) as db:
-                    with db.cursor() as cur:
-                        for sql_command in sql_commands:
-                            if self.args.verbose:
-                                print "PostgreSQL driver executing {!r}".format(sql_command)
-                            cur.execute(command)
-            if pid == 0:
-                os._exit(0)
-            elif pid:
-                os.waitpid(pid, 0)
-        except Exception as e:
+                print "PostgreSQL driver done with command group"
+                sys.stdout.flush()
+            sys.exit(0)
+        elif pid:
             if self.args.verbose:
-                print "PostgreSQL driver exception {!s}".format(e)
-            if pid == 0:
-                os._exit(1)
+                print "PostgreSQL driver waiting for pid {}".format(pid)
+                sys.stdout.flush()
+            pid, status = os.waitpid(pid, 0)
+            if self.args.verbose:
+                print "PostgreSQL driver pid {} returned status {:x}".format(pid, status)
+                sys.stdout.flush()
 
     def _accessible_test(self, udb):
         self.driver.connect(database = udb.database, user = udb.username , password = usb.password).close()
@@ -186,18 +213,20 @@ class PostgreSQL_Driver(Abstract_Driver):
     # serious about using PostgreSQL on Debian and cleaning up after ourselves.
 
     def create(self, udb):
-        #
+
         # CREATE ROLE doesn't take a IF NOT EXISTS modifier, but we can fake it using plpgsql.
         # http://stackoverflow.com/questions/8092086/create-postgresql-role-user-if-it-doesnt-exist
-        #
-        self._execute('''
-                        DO $$ BEGIN
-                          IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE usename = '{0.username}') THEN
-                            CREATE ROLE {0.username} LOGIN PASSWORD '{0.password}';
-                          END IF;
-                        END $$
-                      '''.format(udb),
-                      "CREATE DATABASE IF NOT EXISTS {0.database} OWNER {0.username}".format(udb))
+
+        create_role = '''\
+            DO $$ BEGIN
+                IF NOT EXISTS (SELECT * FROM pg_catalog.pg_user WHERE usename = '{0.username}') THEN
+                    CREATE ROLE {0.username} LOGIN PASSWORD '{0.password}';
+                END IF;
+            END $$'''
+
+        create_database = "CREATE DATABASE {0.database} OWNER {0.username}"
+
+        self._execute(create_role.format(udb), create_database.format(udb))
 
     def drop(self, udb):
         self._execute("DROP DATABASE IF EXISTS {0.database}".format(udb))