Create a postgres image that mocks the empty azure db

When provisioning the jore4 database in Azure, we also fill it up with some initial data. E.g. create db users for microservices
HSLdevcom · Oct 8, 2021 · f35e9a0 · f35e9a0
1 parent 28d25bb
commit f35e9a0
Show file tree

Hide file tree

Showing 12 changed files with 317 additions and 2 deletions.
diff --git a/.github/workflows/docker-build.yml b/.github/workflows/docker-build.yml
@@ -21,6 +21,7 @@ jobs:
         run: |
           docker pull $IMAGE_NAME:latest || echo "Previous image not found"
           docker pull $IMAGE_NAME:mapmatching || echo "Previous image not found"
+          docker pull $IMAGE_NAME:azuredbmock || echo "Previous image not found"
 
       - name: Build docker images
         run: |
@@ -31,6 +32,13 @@ jobs:
             -t $IMAGE_NAME:latest \
             -t $IMAGE_NAME:mapmatching-$COMMIT_ID ./mapmatching
 
+          docker build \
+            --cache-from=$IMAGE_NAME:latest \
+            --cache-from=$IMAGE_NAME:azuredbmock \
+            -t $IMAGE_NAME:azuredbmock \
+            -t $IMAGE_NAME:latest \
+            -t $IMAGE_NAME:azuredbmock-$COMMIT_ID ./azuredbmock
+
       - name: Login to Docker Hub
         uses: docker/login-action@v1
         with:
@@ -40,10 +48,12 @@ jobs:
       - name: Push images tagged with git commit details to Docker Hub
         run: |
           docker push $IMAGE_NAME:mapmatching-$COMMIT_ID
+          docker push $IMAGE_NAME:azuredbmock-$COMMIT_ID
 
       - name: Push rest of the tags to Docker Hub
         if: ${{ github.ref == 'refs/heads/main' }}
         run: |
+          docker push $IMAGE_NAME:azuredbmock
           docker push $IMAGE_NAME:mapmatching
           docker push $IMAGE_NAME:latest
 
@@ -67,6 +77,8 @@ jobs:
               '-e POSTGRES_DB="$POSTGRES_DB" -e POSTGRES_USER="$POSTGRES_USER"
               -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" -e
               DIGIROAD_ROUTING_DUMP_VERSION="$DIGIROAD_ROUTING_DUMP_VERSION"'
+            # no secrets are needed here
+            secretMapping: ""
             dbQuery:
               # verifies that we have all the tables in routing schema
               "SELECT table_name FROM information_schema.tables WHERE
@@ -78,6 +90,33 @@ jobs:
               dr_linkki_vertices_pgr
               dr_pysakki
 
+              (3 rows)
+          # azuredbmock image
+          - dockerImage: azuredbmock
+            envMapping:
+              '-e POSTGRES_DB="$POSTGRES_DB" -e POSTGRES_USER="$POSTGRES_USER"
+              -e POSTGRES_PASSWORD="$POSTGRES_PASSWORD" -e
+              SECRET_STORE_BASE_PATH="/run/secrets"'
+            # secrets are bound as volumes because `docker run` does not support secrets
+            secretMapping:
+              "-v
+              $(pwd)/test-secrets/db-auth-username:/run/secrets/db-auth-username:ro
+              -v
+              $(pwd)/test-secrets/db-auth-password:/run/secrets/db-auth-password:ro
+              -v
+              $(pwd)/test-secrets/db-jore3importer-username:/run/secrets/db-jore3importer-username:ro
+              -v
+              $(pwd)/test-secrets/db-jore3importer-password:/run/secrets/db-jore3importer-password:ro"
+            dbQuery:
+              # finds all users in the database
+              "SELECT usename FROM pg_catalog.pg_user ORDER BY usename;"
+            expectedOutput: |
+              usename
+              ------------------
+              authusername
+              importerusername
+              user
+
               (3 rows)
 
     steps:
@@ -89,7 +128,7 @@ jobs:
 
       - name: Start up postgresql docker container
         run: |
-          docker run -d --rm -p 6432:5432 --name postgres ${{ matrix.envMapping }} $IMAGE_NAME:${{ matrix.dockerImage }}-$COMMIT_ID
+          docker run -d --rm -p 6432:5432 --name postgres ${{ matrix.envMapping }} ${{ matrix.secretMapping }} $IMAGE_NAME:${{ matrix.dockerImage }}-$COMMIT_ID
 
       - name: Verify that postgres database is up and can be connected to
         uses: HSLdevcom/jore4-tools/github-actions/healthcheck@healthcheck-v1

diff --git a/.github/workflows/test-docker-compose.yml b/.github/workflows/test-docker-compose.yml
@@ -1,4 +1,4 @@
-name: Test whether the dockerized version works (with secrets)
+name: Test whether the dockerized versions work (with secrets)
 
 on:
   push:
@@ -27,3 +27,14 @@ jobs:
           command:
             'psql -h localhost -p 6432 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c
             "SELECT ''OK'';"'
+
+      - name: Verify that azuredbmock database is up and can be connected to
+        uses: HSLdevcom/jore4-tools/github-actions/healthcheck@healthcheck-v1
+        env:
+          POSTGRES_USER: username
+          POSTGRES_DB: testdb
+          PGPASSWORD: password
+        with:
+          command:
+            'psql -h localhost -p 7432 -U "$POSTGRES_USER" -d "$POSTGRES_DB" -c
+            "SELECT ''OK'';"'
diff --git a/README.md b/README.md
@@ -36,3 +36,52 @@ Note that you may also use secrets as a substitute to environment variables.
 E.g. `postgres-user`, `postgres-password` and `postgres-db` secrets are exposed
 as `POSTGRES_USER`, `POSTGRES_PASSWORD` and `POSTGRES_DB` environment variables
 and can be used to set up the database.
+
+## `jore4-postgres:azuredbmock`
+
+Extends the original
+[postgis/postgis](https://hub.docker.com/r/postgis/postgis/) docker image.
+
+The purpose of this image is to provide a dockerised postgresql instance with a
+similar experience of what the initial Azure database instance is like right
+after being
+[provisioned and configured by ansible](https://github.com/HSLdevcom/jore4-deploy#setting-up-database-users).
+E.g. we are creating db users.
+
+This image is not responsible for setting up the jore4 database schema, that is
+done by the [jore4-hasura image](https://github.com/HSLdevcom/jore4-hasura)
+
+Ports:
+
+The default TCP port `5432` is used as usual.
+
+Volumes:
+
+The `/docker-entrypoint-initdb.d` volume may be used to execute additional
+migration scripts on top of what are used by this image. Don't be surprised
+however that the built-in migrations used by this image will appear in the
+mapped directory on your host machine as the image internally also uses the
+`docker-entrypoint-initdb.d` directory for executing its own migrations.
+
+Environment variables:
+
+| Environment variable   | Example            | Description                             |
+| ---------------------- | ------------------ | --------------------------------------- |
+| SECRET_STORE_BASE_PATH | /mnt/secrets-store | Directory containing the docker secrets |
+
+Secrets:
+
+| Secrets                   | Example         | Description                                                       |
+| ------------------------- | --------------- | ----------------------------------------------------------------- |
+| db-auth-username          | dbauth          | Name of the database user for the authentication microservice     |
+| db-auth-password          | \*\*\*          | Password of the database user for the authentication microservice |
+| db-jore3importer-username | dbjore3importer | Name of the database user for the jore3 importer microservice     |
+| db-jore3importer-password | \*\*\*          | Name of the database user for the jore3 importer microservice     |
+
+All other environment variables are the same as as in `postgis/postgis`'s base
+image, [postgres](https://registry.hub.docker.com/_/postgres/)
+
+Note that you may also use secrets as a substitute to environment variables.
+E.g. `postgres-user`, `postgres-password` and `postgres-db` secrets are exposed
+as `POSTGRES_USER`, `POSTGRES_PASSWORD` and `POSTGRES_DB` environment variables
+and can be used to set up the database.
diff --git a/azuredbmock/00-initialize.sql b/azuredbmock/00-initialize.sql
@@ -0,0 +1,7 @@
+
+-- Initializations, which are needed locally, but not in the cloud / prod environments,
+-- go here.
+
+-- These users are created from the jore4-deploy repository in cloud environments.
+CREATE USER xxx_db_auth_username_xxx PASSWORD 'xxx_db_auth_password_xxx';
+CREATE USER xxx_db_jore3importer_username_xxx PASSWORD 'xxx_db_jore3importer_password_xxx';
diff --git a/azuredbmock/Dockerfile b/azuredbmock/Dockerfile
@@ -0,0 +1,21 @@
+# Builder docker image.
+FROM postgis/postgis:12-3.1
+
+# install curl
+RUN apt-get update && \
+  apt-get install curl -y
+
+# download script for reading docker secrets
+RUN curl -o /tmp/read-secrets.sh "https://raw.githubusercontent.com/HSLdevcom/jore4-tools/main/docker/read-secrets.sh"
+
+COPY 00-initialize.sql /migrations/00-initialize.sql
+COPY replace-placeholders-in-sql-schema-migrations.sh /scripts/replace-placeholders-in-sql-schema-migrations.sh
+
+COPY entrypoint.sh /usr/local/bin/
+
+ENTRYPOINT ["entrypoint.sh"]
+CMD ["postgres"]
+
+HEALTHCHECK --interval=30s --timeout=5s \
+  # have to source the secrets here as well, otherwise the POSTGRES_USER and/or POSTGRES_DB variables might not exist
+  CMD source /tmp/read-secrets.sh && pg_isready -h localhost -U "${POSTGRES_USER}" -d "${POSTGRES_DB}"
diff --git a/azuredbmock/entrypoint.sh b/azuredbmock/entrypoint.sh
@@ -0,0 +1,21 @@
+#!/bin/bash
+
+set -Eeuo pipefail
+
+# read the secrets to environment variables
+source /tmp/read-secrets.sh
+
+# copy the migrations scripts to the docker-entrypoint-initdb.d folder so that the postgres entrypoint
+# executes them (together with the other migrations that might have been mapped as a volume)
+mkdir -p /docker-entrypoint-initdb.d
+cp /migrations/* /docker-entrypoint-initdb.d/
+
+# replace placeholders with secrets within the all migration SQL scripts
+SECRET_STORE_BASE_PATH="${SECRET_STORE_BASE_PATH:-/run/secrets}"
+REPLACE_PLACEHOLDERS_SCRIPT='/scripts/replace-placeholders-in-sql-schema-migrations.sh'
+MIGRATIONS_DIR="/docker-entrypoint-initdb.d/"
+"${REPLACE_PLACEHOLDERS_SCRIPT}" "${SECRET_STORE_BASE_PATH}" "${MIGRATIONS_DIR}"
+
+# call the original entrypoint to continue execution
+echo 'Start postgres'
+exec /usr/local/bin/docker-entrypoint.sh "$@"
diff --git a/azuredbmock/replace-placeholders-in-sql-schema-migrations.sh b/azuredbmock/replace-placeholders-in-sql-schema-migrations.sh
@@ -0,0 +1,136 @@
+#!/bin/sh
+#
+# Replace placeholders in SQL schema migrations.
+#
+# Docker or Kubernetes secrets contain the PostgreSQL users we need in the SQL
+# schema migrations. As placeholders in the SQL schema migrations, we use
+# mangled Docker secret filenames with bracket delimiters. This script replaces
+# the placeholders with the contents of the Docker secrets at runtime.
+#
+# E.g. the username stored in a secret named
+# "db-jore3importer-username" is used with the placeholder
+# "xxx_db_jore3importer_username_xxx" within the SQL schema migration
+# files.
+
+set -eu
+
+[ "$#" -ne 2 ] && {
+  echo 1>&2 "Usage: $(basename "$0") RUNTIME_SECRETS_DIR RUNTIME_MIGRATIONS_DIR"
+  exit 1
+}
+
+secrets_dir="$1"
+migrations_dir="$2"
+
+# Choose delimiters that allow the placeholders to act as valid SQL identifiers
+# for any static analysis purposes.
+delimiter_alnum='xxx'
+start_delimiter="${delimiter_alnum}_"
+end_delimiter="_${delimiter_alnum}"
+placeholder_characters='0-9A-Za-z_'
+
+cleanup() {
+  [ -z "${check_duplicate_list+dummy}" ] || rm -f "${check_duplicate_list}"
+  [ -z "${sed_script_file+dummy}" ] || rm -f "${sed_script_file}"
+}
+trap cleanup EXIT
+
+check_duplicate_list="$(mktemp)"
+sed_script_file="$(mktemp)"
+
+# Create a script file for sed to replace the placeholders.
+write_to_sed_script_file() {
+  secret_path="$1"
+  # As `basename -z` is not supported by the available shell, we cannot handle
+  # newlines in filenames with this approach. We do not translate the newline
+  # inserted by basename.
+  placeholder="$(
+    basename "${secret_path}" \
+      | tr -C "${placeholder_characters}\n" '_'
+  )"
+
+  # Check for the delimiter in the secret name.
+  if echo \
+    "${placeholder}" \
+    | grep \
+      -q \
+      -e "${delimiter_alnum}"; then
+    cat <<EOM 1>&2
+The basename for secret
+${secret_path}
+should not contain the alphanumeric part of the delimiter:
+${delimiter_alnum}
+EOM
+    exit 4
+  fi
+
+  # Check for collisions of secrets.
+  if grep \
+    -q \
+    -x \
+    -e "${placeholder}" \
+    "${check_duplicate_list}"; then
+    cat <<EOM 1>&2
+The basename for secret
+${secret_path}
+is not unique enough. The basenames of the secrets should differ by alphanumeric
+characters.
+EOM
+    exit 2
+  fi
+  echo "${placeholder}" >>"${check_duplicate_list}"
+
+  echo "s|${start_delimiter}${placeholder}${end_delimiter}|$(cat "${secret_path}")|g" \
+    >>"${sed_script_file}"
+}
+
+# Replace the placeholders with sed and check for leftover placeholders.
+replace_and_check() {
+  migration_path="$1"
+  sed \
+    -f "${sed_script_file}" \
+    -i \
+    "${migration_path}"
+  if leftover_placeholders="$(
+    grep \
+      -o \
+      -h \
+      -E \
+      -e "${start_delimiter}[${placeholder_characters}]+${end_delimiter}" \
+      "${migration_path}"
+  )"; then
+    sorted_leftovers="$(echo "${leftover_placeholders}" | sort -u)"
+    cat <<EOM 1>&2
+The migration file
+${migration_path}
+contains some placeholders for which there are no corresponding secrets. The
+offending placeholders are:
+${sorted_leftovers}
+EOM
+    exit 3
+  fi
+}
+
+echo "Starting to replace placeholders in SQL schema migration files."
+
+# As `export -f` is not supported by the available shell, executing the shell
+# functions with `find` does not work.
+
+# As `read -d ''` is not supported by the available shell, `find -print0` cannot
+# be used here.
+
+# Loop over secrets.
+find "${secrets_dir}" -type f \
+  | LC_ALL=C sort \
+  | while IFS= read -r file; do
+    write_to_sed_script_file "${file}"
+  done
+
+# Loop over SQL schema migrations.
+find "${migrations_dir}" -type f -name '*.sql' \
+  | LC_ALL=C sort \
+  | while IFS= read -r file; do
+    replace_and_check "${file}"
+  done
+
+echo "Replacing placeholders done."
diff --git a/docker-compose.yml b/docker-compose.yml
@@ -19,6 +19,25 @@ services:
     volumes:
       - "./test-migrations/test.sql:/docker-entrypoint-initdb.d/test.sql"
 
+  azuredbmock:
+    build:
+      context: "./azuredbmock"
+    container_name: azuredbmock
+    restart: "unless-stopped"
+    networks:
+      - jore4
+    ports:
+      - "127.0.0.1:7432:5432"
+    secrets:
+      - postgres-user
+      - postgres-password
+      - db-auth-username
+      - db-auth-password
+      - db-jore3importer-username
+      - db-jore3importer-password
+    environment:
+      - POSTGRES_DB=testdb
+
 networks:
   jore4:
 
@@ -29,3 +48,11 @@ secrets:
     file: ./test-secrets/postgres-password
   postgres-db:
     file: ./test-secrets/postgres-db
+  db-auth-username:
+    file: ./test-secrets/db-auth-username
+  db-auth-password:
+    file: ./test-secrets/db-auth-password
+  db-jore3importer-username:
+    file: ./test-secrets/db-jore3importer-username
+  db-jore3importer-password:
+    file: ./test-secrets/db-jore3importer-password
diff --git a/test-secrets/db-auth-password b/test-secrets/db-auth-password
@@ -0,0 +1 @@
+authpassword
diff --git a/test-secrets/db-auth-username b/test-secrets/db-auth-username
@@ -0,0 +1 @@
+authusername
diff --git a/test-secrets/db-jore3importer-password b/test-secrets/db-jore3importer-password
@@ -0,0 +1 @@
+importerpassword
diff --git a/test-secrets/db-jore3importer-username b/test-secrets/db-jore3importer-username
@@ -0,0 +1 @@
+importerusername