launchpad-reviewers team mailing list archive

[Colin Watson <mp+455782@xxxxxxxxxxxxxxxxxx>]

Colin Watson has proposed merging ~cjwatson/launchpad:charm-db-update-fix-backend-connections into launchpad:master.

Commit message:
charm: Fix launchpad-db-update's connections to backend databases

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)

For more details, see:
https://code.launchpad.net/~cjwatson/launchpad/+git/launchpad/+merge/455782

The launchpad-db-update charm typically needs to connect directly to backend databases, bypassing pgbouncer, because it needs to be able to disable ordinary database access via pgbouncer before performing schema updates.  I discovered when testing this on staging that I hadn't quite got all the connection arrangements for this right.
-- 
Your team Launchpad code reviewers is requested to review the proposed merge of ~cjwatson/launchpad:charm-db-update-fix-backend-connections into launchpad:master.

diff --git a/charm/launchpad-db-update/config.yaml b/charm/launchpad-db-update/config.yaml
new file mode 100644
index 0000000..90cc41d
--- /dev/null
+++ b/charm/launchpad-db-update/config.yaml
@@ -0,0 +1,12 @@
+options:
+  backend_database_name:
+    type: string
+    description: >
+      Name of the database we are updating.  (This must be the name of the
+      database as known to PostgreSQL, which is not necessarily the same as
+      that exposed by pgbouncer.)
+    default: "launchpad_dev"
+  backend_database_user:
+    type: string
+    description: User name to use when connecting to backend databases.
+    default: "postgres"
diff --git a/charm/launchpad-db-update/reactive/launchpad-db-update.py b/charm/launchpad-db-update/reactive/launchpad-db-update.py
index e9c4b06..0bb9806 100644
--- a/charm/launchpad-db-update/reactive/launchpad-db-update.py
+++ b/charm/launchpad-db-update/reactive/launchpad-db-update.py
@@ -21,8 +21,10 @@ from ols import base, postgres
 from psycopg2.extensions import make_dsn, parse_dsn
 
 
-def any_dbname(dsn):
+def any_host_or_port_or_dbname(dsn):
     parsed_dsn = parse_dsn(dsn)
+    parsed_dsn["host"] = "*"
+    parsed_dsn["port"] = "*"
     parsed_dsn["dbname"] = "*"
     return make_dsn(**parsed_dsn)
 
@@ -43,8 +45,11 @@ def configure():
     db_admin = endpoint_from_flag("db-admin.master.available")
     db_admin_primary, _ = postgres.get_db_uris(db_admin)
     # We assume that this admin user works for any database on this host,
-    # which seems to be true in practice.
-    update_pgpass(any_dbname(db_admin_primary))
+    # which seems to be true in practice.  Indeed, since we need to bypass
+    # pgbouncer and contact backend databases directly, assume that it works
+    # for any host or port too so that we don't need to configure
+    # credentials for all the database hosts individually.
+    update_pgpass(any_host_or_port_or_dbname(db_admin_primary))
     config["db_admin_primary"] = strip_dsn_authentication(db_admin_primary)
 
     if is_flag_set("pgbouncer.master.available"):
diff --git a/charm/launchpad-db-update/templates/db-update.j2 b/charm/launchpad-db-update/templates/db-update.j2
index 4ca8e87..002933d 100755
--- a/charm/launchpad-db-update/templates/db-update.j2
+++ b/charm/launchpad-db-update/templates/db-update.j2
@@ -11,7 +11,9 @@ export LPCONFIG=launchpad-db-update
 {% if pgbouncer_primary -%}
 # Fastdowntime update, managing connections using pgbouncer.
 {{ code_dir }}/database/schema/full-update.py \
-    --pgbouncer='{{ pgbouncer_primary }}'
+    --pgbouncer='{{ pgbouncer_primary }}' \
+    --dbname='{{ backend_database_name }}' \
+    --dbuser='{{ backend_database_user }}'
 {% else -%}
 # We can't manage connections using pgbouncer in this environment.  Attempt
 # a simple schema upgrade, which may fail if anything has an active database
diff --git a/charm/launchpad-db-update/templates/preflight.j2 b/charm/launchpad-db-update/templates/preflight.j2
index ef2a88f..906938f 100755
--- a/charm/launchpad-db-update/templates/preflight.j2
+++ b/charm/launchpad-db-update/templates/preflight.j2
@@ -7,5 +7,7 @@
 set -e
 
 LPCONFIG=launchpad-db-update {{ code_dir }}/database/schema/preflight.py \
-    --skip-connection-check --pgbouncer='{{ pgbouncer_primary }}'
+    --skip-connection-check --pgbouncer='{{ pgbouncer_primary }}' \
+    --dbname='{{ backend_database_name }}' \
+    --dbuser='{{ backend_database_user }}'