Run init scripts after database creation

[carlcantprogram]

Hi,

I'd like to add a schema setup script to the init directory so that basic users, permissions etc are always available in the database. The init scripts fail as the database doesn't exist yet. (only the default postgres database exists at init time). The schema setup script would read the new database name from POSTGRESQL_DATABASE and run the schema setup in that database.

[commonquail]

I also have this problem. The image forces me to create a (user, password, database) but then I don't have a hook to modify that database just once so I can't really bootstrap it. I could make my own user and database, then bootstrap that database, but only by setting a remote admin password, which I'd rather avoid.

In particular, I'd like to run e.g.

CREATE ROLE migrations WITH CREATEROLE INHERIT;
REVOKE ALL ON DATABASE mydb FROM public;
GRANT CONNECT ON DATABASE mydb TO migrations;

before any other user gets access to the database.

I think that my specific obstacles could be solved by moving them from the init to the pre-start hook but that's not conceptually correct. I'd rather have a post-init hook that runs after creating database and user.

It came as a big surprise that init happens before behaviour that is very difficult to opt out of.

[commonquail]

Because of #325 I'm forced to create a custom image either way. Since I have to do that I've now also extracted run-postgresql from centos/postgresql-10-centos7@sha256:e5be79e98d93399de3f6b6d9554efed94648b7454cd1f96f17f5af6d4651eedc and extended it with a post-init hook after create_users. If maintainers are interested in this I can refine and submit it for inclusion.

[exhuma]

@commonquail Do you have any more information on your solution? From what I can tell by going through the docker-files, the OpenShift image is not based on the official Postgres images (I might be wrong here as the Dockerfiles are templated). So the usual init-scripts won't run anyway.

Either way, I also need some way to "bootstrap" the database before a container can be run.

I have migrations which run in the context of a container execution and require a postgres extension to be present. Installing the extension requires superuser privs in Postgres which the container does not have.

I can run this manually, but it would be way cleaner to have this properly initialise when a container is created and run.

[commonquail]

@exhuma I'm attaching to this comment a patch that loosely demonstrates two different approaches: the one I discuss in my previous comment, as well as another I discovered sometime after, that relies on exposed superuser access and OpenShift's pod-based lifecycle hooks. It includes samples for Docker Compose (for local development) and pieces of OpenShift object definitions and Jenkins integration.

At first I didn't consider the second option viable (I didn't want to expose superuser access) but then I realized that under my circumstances it didn't really matter. The database instance I'm creating is purely for local development or CI, not for production, and in production we intend to operate the database completely differently (namely, with much more manual intervention, including initial -- one-time -- setup). All those environments are already both tightly controlled as well as completely unimportant and unintegrated, so the worst thing a bad actor can do is wreck somebody's CI build. There is another downside, too: the files I can bind-mount with Docker Compose for trivial bootstrapping have to be mounted as config maps in OpenShift, which means they end up in a secondary serialization. Consequently, I end up having to maintain two copies of the same bootstrapping logic. Fortunately I can use something like

oc create --dry-run configmap cfg-pg-bootstrap --from-file=contrib/database/postgresql-post-init/ --output=json

to generate nearly the secondary serialization I need.

I've come to prefer the second approach, although I will say that OpenShift's pod-based lifecycle hooks are a royal pain to work with and have many severe limitations. Extending the base image is a much stabler approach but maintaining an image also has a rather high operational cost. I'd still greatly appreciate an upstream post-init hook.

From 8e661e9d63e9202d70cc5df8cd0b484f0bad21c7 Mon Sep 17 00:00:00 2001
From: Mikkel Kjeldsen <mikkel.kjeldsen@sallinggroup.com>
Date: Mon, 1 Jul 2019 09:12:25 +0200
Subject: [PATCH] Add local + CI database

Fork run-postgresql from centos/postgresql-10-centos7:1,
sha256:e5be79e98d93399de3f6b6d9554efed94648b7454cd1f96f17f5af6d4651eedc,
because it has no "post-init" support [1]. Add such a hook, with
semantics like the init hook, that runs immediately after creating the
database.

Alternatively, expose "postgres" superuser and delegate database + user
creation to custom 00-bootstrap.sh that runs in normal upstream init
hook and via "execNewPod" pod-based lifecycle hook [2].

[1] sclorg/postgresql-container#296
[2] https://docs.openshift.com/container-platform/3.11/dev_guide/deployments/deployment_strategies.html#pod-based-lifecycle-hook

---
 contrib/database/Dockerfile                   |   6 +
 contrib/database/postgresql-cfg/local.conf    |  11 +
 .../postgresql-post-init/00-bootstrap.sh      |  29 +++
 .../10-create-extensions.sql                  |   1 +
 .../20-add-group-migrations.sql               |  19 ++
 .../21-add-group-main.sql                     |  28 ++
 .../30-add-user-flyway.sql                    |   7 +
 .../postgresql-post-init/31-add-user-main.sql |   4 +
 contrib/database/run-postgresql               |  63 +++++
 contrib/docker-compose.yml                    |  24 ++
 openshift/Jenkinsfile                         |   6 +
 openshift/templates/catapult.json             | 239 ++++++++++++++++++
 .../V1__migrations_table_permissions.sql      |   7 +
 13 files changed, 444 insertions(+)
 create mode 100644 contrib/database/Dockerfile
 create mode 100644 contrib/database/postgresql-cfg/local.conf
 create mode 100755 contrib/database/postgresql-post-init/00-bootstrap.sh
 create mode 100644 contrib/database/postgresql-post-init/10-create-extensions.sql
 create mode 100644 contrib/database/postgresql-post-init/20-add-group-migrations.sql
 create mode 100644 contrib/database/postgresql-post-init/21-add-group-main.sql
 create mode 100644 contrib/database/postgresql-post-init/30-add-user-flyway.sql
 create mode 100644 contrib/database/postgresql-post-init/31-add-user-main.sql
 create mode 100755 contrib/database/run-postgresql
 create mode 100644 src/main/resources/db/migration/V1__migrations_table_permissions.sql

diff --git a/contrib/database/Dockerfile b/contrib/database/Dockerfile
new file mode 100644
index 0000000..f0858b4
--- /dev/null
+++ b/contrib/database/Dockerfile
@@ -0,0 +1,6 @@
+FROM centos/postgresql-10-centos7:1
+
+COPY run-postgresql /usr/bin/
+
+COPY postgresql-cfg "${APP_ROOT}/src/postgresql-cfg"
+COPY postgresql-post-init "${APP_ROOT}/src/postgresql-post-init"
diff --git a/contrib/database/postgresql-cfg/local.conf b/contrib/database/postgresql-cfg/local.conf
new file mode 100644
index 0000000..e45622c
--- /dev/null
+++ b/contrib/database/postgresql-cfg/local.conf
@@ -0,0 +1,11 @@
+# Define some settings with values that are primarily suitable for the local
+# development environment.
+
+shared_preload_libraries = 'pg_stat_statements,auto_explain'
+
+auto_explain.log_min_duration = 500
+
+# With logging_collector on, stderr is internally redirected to a file and not
+# copied to stdout. Disable it to expose troubleshooting information, at the
+# risk of losing some events.
+logging_collector = off
diff --git a/contrib/database/postgresql-post-init/00-bootstrap.sh b/contrib/database/postgresql-post-init/00-bootstrap.sh
new file mode 100755
index 0000000..8118bc5
--- /dev/null
+++ b/contrib/database/postgresql-post-init/00-bootstrap.sh
@@ -0,0 +1,29 @@
+#!/bin/bash
+
+set -o errexit
+set -o xtrace
+
+db_exists()
+{
+    PGCONNECT_TIMEOUT=3 container-entrypoint psql -c '' 2>/dev/null
+}
+
+create_db()
+{
+    container-entrypoint createdb
+}
+
+seed_db()
+{
+    for sql in "$(dirname "${BASH_SOURCE[0]}")/"*.sql
+    do
+        container-entrypoint psql --variable ON_ERROR_STOP=1 --file="$sql"
+    done
+}
+
+main()
+{
+    db_exists || create_db && seed_db
+}
+
+main
diff --git a/contrib/database/postgresql-post-init/10-create-extensions.sql b/contrib/database/postgresql-post-init/10-create-extensions.sql
new file mode 100644
index 0000000..d3c6679
--- /dev/null
+++ b/contrib/database/postgresql-post-init/10-create-extensions.sql
@@ -0,0 +1 @@
+CREATE EXTENSION pg_stat_statements;
diff --git a/contrib/database/postgresql-post-init/20-add-group-migrations.sql b/contrib/database/postgresql-post-init/20-add-group-migrations.sql
new file mode 100644
index 0000000..00a4d8f
--- /dev/null
+++ b/contrib/database/postgresql-post-init/20-add-group-migrations.sql
@@ -0,0 +1,19 @@
+-- Create a new "migrations" role. Remove all permissions for everyone
+-- everywhere, then selectively grant "migrations" semi-cosmic powers so members
+-- can manage DDL and roles.
+--
+-- References:
+-- * https://stackoverflow.com/a/15868268
+-- * https://stackoverflow.com/a/24923877
+-- * https://tech.instacart.com/bootstrapping-postgres-users-cd594e5f28e9
+
+CREATE ROLE migrations WITH INHERIT;
+
+REVOKE ALL ON DATABASE mydb FROM public;
+REVOKE ALL ON SCHEMA public FROM public;
+
+GRANT CONNECT ON DATABASE mydb TO migrations;
+GRANT ALL ON SCHEMA public TO migrations WITH GRANT OPTION;
+GRANT ALL ON DATABASE mydb TO migrations WITH GRANT OPTION;
+
+ALTER ROLE migrations SET lock_timeout TO '10s';
diff --git a/contrib/database/postgresql-post-init/21-add-group-main.sql b/contrib/database/postgresql-post-init/21-add-group-main.sql
new file mode 100644
index 0000000..5432c64
--- /dev/null
+++ b/contrib/database/postgresql-post-init/21-add-group-main.sql
@@ -0,0 +1,28 @@
+-- Create a new "app" role with CRUD permissions all over PUBLIC. Ensure that
+-- any future objects created by "migrations" get these permissions but don't
+-- bother updating existing objects (there are none).
+--
+-- References:
+-- * https://stackoverflow.com/a/15868268
+-- * https://stackoverflow.com/a/24923877
+-- * https://tech.instacart.com/bootstrapping-postgres-users-cd594e5f28e9
+
+CREATE ROLE app WITH INHERIT;
+
+GRANT CONNECT, TEMPORARY ON DATABASE mydb TO app;
+GRANT USAGE ON SCHEMA public TO app;
+
+ALTER DEFAULT PRIVILEGES
+    FOR ROLE migrations
+    IN SCHEMA public
+    GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLES TO app;
+
+ALTER DEFAULT PRIVILEGES
+    FOR ROLE migrations
+    IN SCHEMA public
+    GRANT USAGE ON SEQUENCES TO app;
+
+ALTER DEFAULT PRIVILEGES
+    FOR ROLE migrations
+    IN SCHEMA public
+    GRANT EXECUTE ON FUNCTIONS TO app;
diff --git a/contrib/database/postgresql-post-init/30-add-user-flyway.sql b/contrib/database/postgresql-post-init/30-add-user-flyway.sql
new file mode 100644
index 0000000..1e34458
--- /dev/null
+++ b/contrib/database/postgresql-post-init/30-add-user-flyway.sql
@@ -0,0 +1,7 @@
+-- Create a new user "flyway" with database migration privileges. Change the
+-- user's role so object ownership belongs to the migration group instead of
+-- this user.
+
+CREATE ROLE flyway WITH INHERIT LOGIN PASSWORD 'flyway';
+GRANT migrations TO flyway;
+ALTER ROLE flyway SET role TO 'migrations';
diff --git a/contrib/database/postgresql-post-init/31-add-user-main.sql b/contrib/database/postgresql-post-init/31-add-user-main.sql
new file mode 100644
index 0000000..fe72118
--- /dev/null
+++ b/contrib/database/postgresql-post-init/31-add-user-main.sql
@@ -0,0 +1,4 @@
+-- Create a new user for general DB activity.
+
+CREATE ROLE mydbuser WITH LOGIN INHERIT PASSWORD 'mydbuser';
+GRANT app TO mydbuser;
diff --git a/contrib/database/run-postgresql b/contrib/database/run-postgresql
new file mode 100755
index 0000000..1516d0e
--- /dev/null
+++ b/contrib/database/run-postgresql
@@ -0,0 +1,63 @@
+#!/bin/bash
+
+export ENABLE_REPLICATION=${ENABLE_REPLICATION:-false}
+
+set -eu
+export_vars=$(cgroup-limits) ; export $export_vars
+
+source "${CONTAINER_SCRIPTS_PATH}/common.sh"
+
+set_pgdata
+
+process_extending_files \
+        "${APP_DATA}/src/postgresql-pre-start" \
+        "${CONTAINER_SCRIPTS_PATH}/pre-start"
+
+check_env_vars
+generate_passwd_file
+generate_postgresql_config
+
+# Is this brand new data volume?
+PG_INITIALIZED=false
+
+if [ ! -f "$PGDATA/postgresql.conf" ]; then
+  initialize_database
+  PG_INITIALIZED=:
+else
+  try_pgupgrade
+fi
+
+# Use insanely large timeout (24h) to ensure that the potential recovery has
+# enough time here to happen (unless liveness probe kills us).  Note that in
+# case of server failure this command still exists immediately.
+pg_ctl start -w --timeout 86400 -o "-h ''"
+
+# This is just a pedantic safety measure (the timeout above is unlikely to
+# happen), but `pt_ctl -w` is not reliable prior to PostgreSQL v10 where it
+# returns exit_status=0 even if the server is still starting.  For more info
+# see the issue#297 and
+# https://www.postgresql.org/message-id/CAB7nPqSJs85wK9aknm%3D_jmS6GnH3SQBhpzKcqs8Qo2LhEg2etw%40mail.gmail.com
+pg_isready
+
+if $PG_INITIALIZED ; then
+    process_extending_files \
+        "${APP_DATA}/src/postgresql-init" \
+        "${CONTAINER_SCRIPTS_PATH}/init"
+    migrate_db
+    create_users
+    # Extend the base image with a custom post-init hook so we can make changes
+    # to the database and user just created.
+    process_extending_files \
+        "${APP_DATA}/src/postgresql-post-init" \
+        "${CONTAINER_SCRIPTS_PATH}/post-init"
+fi
+
+process_extending_files \
+    "${APP_DATA}/src/postgresql-start" \
+    "${CONTAINER_SCRIPTS_PATH}/start"
+
+pg_ctl stop
+
+unset_env_vars
+echo "Starting server..."
+exec postgres "$@"
diff --git a/contrib/docker-compose.yml b/contrib/docker-compose.yml
index c5488c3..8f45cc1 100644
--- a/contrib/docker-compose.yml
+++ b/contrib/docker-compose.yml
@@ -23,3 +23,27 @@ services:
       timeout: 5s
       start_period: 10s
       retries: 3
+
+  mydb:
+    container_name: my-db
+    image: centos/postgresql-10-centos7:1
+    ports:
+      - "${DB_HOST_PORT}:5432"
+    volumes:
+      - "./database/postgresql-cfg:/opt/app-root/src/postgresql-cfg:ro"
+      - "./database/postgresql-post-init:/opt/app-root/src/postgresql-post-init:ro"
+    environment:
+      # Tell image not to create any extra database or user automatically.
+      # Unfortunately requires granting remote access to superuser but that is
+      # acceptable locally and in CI.
+      POSTGRESQL_ADMIN_PASSWORD: "postgres"
+      # Then set any libpq env vars necessary to create database; password not
+      # used on localhost.
+      PGDATABASE: "mydb"
+    healthcheck:
+      # pg_isready reports "ready" before database restarts.
+      test: container-entrypoint psql --quiet "$$PGDATABASE" mydbuser -c "SELECT 1"
+      interval: 2m
+      timeout: 5s
+      start_period: 20s
+      retries: 3
diff --git a/openshift/Jenkinsfile b/openshift/Jenkinsfile
index 25f96b2..8ea21ca 100644
--- a/openshift/Jenkinsfile
+++ b/openshift/Jenkinsfile
@@ -52,6 +52,12 @@ node('app-builder') {
             'JAVA_HOME=/usr/lib/jvm/java-11',
             'SPRING_PROFILES_ACTIVE=ci',
     ]) {
+        stage('await database') {
+            bbnotify.notifying('await database', env.RUN_DISPLAY_URL, {
+                openshiftVerifyService(svcName: 'postgresql')
+            })
+        }
+
         stage('await solr') {
             bbnotify.notifying('await solr', env.RUN_DISPLAY_URL, {
                 openshiftVerifyService(svcName: 'solr')
diff --git a/openshift/templates/catapult.json b/openshift/templates/catapult.json
index 3c8d9e9..5fac6da 100644
--- a/openshift/templates/catapult.json
+++ b/openshift/templates/catapult.json
@@ -19,6 +19,31 @@
         "spring.profiles.active": "ci"
       }
     },
+    {
+      "apiVersion": "v1",
+      "kind": "ConfigMap",
+      "metadata": {
+        "name": "cfg-pg-bootstrap"
+      },
+      "data": {
+        "00-bootstrap.sh": "#!/bin/bash\n\nset -o errexit\nset -o nounset\nset -o xtrace\n\ndb_exists()\n{\n    PGCONNECT_TIMEOUT=3 container-entrypoint psql -c '' 2>/dev/null\n}\n\ncreate_db()\n{\n    container-entrypoint createdb\n}\n\nseed_db()\n{\n    for sql in \"$(dirname \"${BASH_SOURCE[0]}\")/\"*.sql\n    do\n        container-entrypoint psql --variable ON_ERROR_STOP=1 --file=\"$sql\"\n    done\n}\n\nmain()\n{\n    db_exists || create_db && seed_db\n}\n\nmain",
+        "10-create-extensions.sql": "CREATE EXTENSION pg_stat_statements;\n",
+        "20-add-group-migrations.sql": "-- Create a new \"migrations\" role. Remove all permissions for everyone\n-- everywhere, then selectively grant \"migrations\" semi-cosmic powers so members\n-- can manage DDL and roles.\n--\n-- References:\n-- * https://stackoverflow.com/a/15868268\n-- * https://stackoverflow.com/a/24923877\n-- * https://tech.instacart.com/bootstrapping-postgres-users-cd594e5f28e9\n\nCREATE ROLE migrations WITH INHERIT;\n\nREVOKE ALL ON DATABASE mydb FROM public;\nREVOKE ALL ON SCHEMA public FROM public;\n\nGRANT CONNECT ON DATABASE mydb TO migrations;\nGRANT ALL ON SCHEMA public TO migrations WITH GRANT OPTION;\nGRANT ALL ON DATABASE mydb TO migrations WITH GRANT OPTION;\n\nALTER ROLE migrations SET lock_timeout TO '10s';\n",
+        "21-add-group-main.sql": "-- Create a new \"app\" role with CRUD permissions all over PUBLIC. Ensure that\n-- any future objects created by \"migrations\" get these permissions but don't\n-- bother updating existing objects (there are none).\n--\n-- References:\n-- * https://stackoverflow.com/a/15868268\n-- * https://stackoverflow.com/a/24923877\n-- * https://tech.instacart.com/bootstrapping-postgres-users-cd594e5f28e9\n\nCREATE ROLE app WITH INHERIT;\n\nGRANT CONNECT, TEMPORARY ON DATABASE mydb TO app;\nGRANT USAGE ON SCHEMA public TO app;\n\nALTER DEFAULT PRIVILEGES\n    FOR ROLE migrations\n    IN SCHEMA public\n    GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLES TO app;\n\nALTER DEFAULT PRIVILEGES\n    FOR ROLE migrations\n    IN SCHEMA public\n    GRANT USAGE ON SEQUENCES TO app;\n\nALTER DEFAULT PRIVILEGES\n    FOR ROLE migrations\n    IN SCHEMA public\n    GRANT EXECUTE ON FUNCTIONS TO app;\n",
+        "30-add-user-flyway.sql": "-- Create a new user \"flyway\" with database migration privileges. Change the\n-- user's role so object ownership belongs to the migration group instead of\n-- this user.\n\nCREATE ROLE flyway WITH INHERIT LOGIN PASSWORD 'flyway';\nGRANT migrations TO flyway;\nALTER ROLE flyway SET role TO 'migrations';\n",
+        "31-add-user-main.sql": "-- Create a new user \"mydbuser\" for general DB activity.\n\nCREATE ROLE mydbuser WITH LOGIN INHERIT PASSWORD 'mydbuser';\nGRANT app TO mydbuser;\n"
+      }
+    },
+    {
+      "apiVersion": "v1",
+      "kind": "ConfigMap",
+      "data": {
+        "local.conf": "# Define some settings with values that are primarily suitable for the local\n# development environment.\n\nshared_preload_libraries = 'pg_stat_statements,auto_explain'\n\nauto_explain.log_min_duration = 500\n\n# With logging_collector on, stderr is internally redirected to a file and not\n# copied to stdout. Disable it to expose troubleshooting information, at the\n# risk of losing some events.\nlogging_collector = off\n"
+      },
+      "metadata": {
+        "name": "cfg-pg-conf"
+      }
+    },
     {
       "kind": "ImageStream",
       "apiVersion": "v1",
@@ -290,6 +315,42 @@
                         "key": "client-secret"
                       }
                     }
+                  },
+                  {
+                    "name": "DB_APP_USER",
+                    "valueFrom": {
+                      "secretKeyRef": {
+                        "name": "my-db",
+                        "key": "app-user"
+                      }
+                    }
+                  },
+                  {
+                    "name": "DB_APP_PASSWORD",
+                    "valueFrom": {
+                      "secretKeyRef": {
+                        "name": "my-db",
+                        "key": "app-password"
+                      }
+                    }
+                  },
+                  {
+                    "name": "DB_FLYWAY_USER",
+                    "valueFrom": {
+                      "secretKeyRef": {
+                        "name": "my-db",
+                        "key": "flyway-user"
+                      }
+                    }
+                  },
+                  {
+                    "name": "DB_FLYWAY_PASSWORD",
+                    "valueFrom": {
+                      "secretKeyRef": {
+                        "name": "my-db",
+                        "key": "flyway-password"
+                      }
+                    }
                   }
                 ]
               }
@@ -368,6 +429,184 @@
         },
         "wildcardPolicy": "None"
       }
+    },
+    {
+      "apiVersion": "v1",
+      "kind": "DeploymentConfig",
+      "metadata": {
+        "labels": {
+          "app": "db-runtime",
+          "component": "postgresql"
+        },
+        "name": "postgresql"
+      },
+      "spec": {
+        "replicas": 1,
+        "strategy": {
+          "activeDeadlineSeconds": 21600,
+          "recreateParams": {
+            "post": {
+              "execNewPod": {
+                "command": [
+                  "/bin/sh",
+                  "/opt/bootstrap/00-bootstrap.sh"
+                ],
+                "containerName": "postgresql",
+                "env": [
+                  {
+                    "name": "PGHOST",
+                    "value": "postgresql"
+                  },
+                  {
+                    "name": "PGUSER",
+                    "value": "postgres"
+                  },
+                  {
+                    "name": "PGPASSWORD",
+                    "value": "postgres"
+                  },
+                  {
+                    "name": "PGDATABASE",
+                    "value": "mydb"
+                  }
+                ],
+                "volumes": [
+                  "volume-pg-bootstrap"
+                ]
+              },
+              "failurePolicy": "Abort"
+            },
+            "timeoutSeconds": 600
+          },
+          "resources": {},
+          "type": "Recreate"
+        },
+        "template": {
+          "metadata": {
+            "labels": {
+              "app": "db-runtime",
+              "component": "postgresql"
+            }
+          },
+          "spec": {
+            "containers": [
+              {
+                "env": [
+                  {
+                    "name": "POSTGRESQL_ADMIN_PASSWORD",
+                    "value": "postgres"
+                  }
+                ],
+                "image": "openshift/postgresql:10",
+                "imagePullPolicy": "IfNotPresent",
+                "livenessProbe": {
+                  "exec": {
+                    "command": [
+                      "bash",
+                      "-c",
+                      "timeout 1s psql \"$POSTGRESQL_DATABASE\" -c 'SELECT 1'"
+                    ]
+                  },
+                  "initialDelaySeconds": 120
+                },
+                "name": "postgresql",
+                "ports": [
+                  {
+                    "containerPort": 5432,
+                    "protocol": "TCP"
+                  }
+                ],
+                "readinessProbe": {
+                  "exec": {
+                    "command": [
+                      "bash",
+                      "-c",
+                      "timeout 1s psql \"$POSTGRESQL_DATABASE\" -c 'SELECT 1'"
+                    ]
+                  },
+                  "initialDelaySeconds": 10
+                },
+                "resources": {},
+                "terminationMessagePath": "/dev/termination-log",
+                "volumeMounts": [
+                  {
+                    "mountPath": "/opt/bootstrap",
+                    "name": "volume-pg-bootstrap",
+                    "readOnly": true
+                  },
+                  {
+                    "mountPath": "/opt/app-root/src/postgresql-cfg",
+                    "name": "volume-pg-conf",
+                    "readOnly": true
+                  }
+                ]
+              }
+            ],
+            "dnsPolicy": "ClusterFirst",
+            "restartPolicy": "Always",
+            "securityContext": {},
+            "terminationGracePeriodSeconds": 30,
+            "volumes": [
+              {
+                "configMap": {
+                  "defaultMode": 420,
+                  "name": "cfg-pg-bootstrap"
+                },
+                "name": "volume-pg-bootstrap"
+              },
+              {
+                "configMap": {
+                  "defaultMode": 420,
+                  "name": "cfg-pg-conf"
+                },
+                "name": "volume-pg-conf"
+              }
+            ]
+          }
+        },
+        "test": false,
+        "triggers": [
+          {
+            "type": "ConfigChange"
+          },
+          {
+            "type": "ImageChange",
+            "imageChangeParams": {
+              "automatic": true,
+              "containerNames": [
+                "postgresql"
+              ],
+              "from": {
+                "kind": "ImageStreamTag",
+                "name": "postgresql:10",
+                "namespace": "openshift"
+              }
+            }
+          }
+        ]
+      }
+    },
+    {
+      "apiVersion": "v1",
+      "kind": "Service",
+      "metadata": {
+        "name": "postgresql"
+      },
+      "spec": {
+        "ports": [
+          {
+            "name": "server",
+            "port": 5432,
+            "protocol": "TCP",
+            "targetPort": 5432
+          }
+        ],
+        "selector": {
+          "component": "postgresql"
+        },
+        "sessionAffinity": "None",
+        "type": "ClusterIP"
+      }
     }
   ],
   "parameters": [
diff --git a/src/main/resources/db/migration/V1__migrations_table_permissions.sql b/src/main/resources/db/migration/V1__migrations_table_permissions.sql
new file mode 100644
index 0000000..52974df
--- /dev/null
+++ b/src/main/resources/db/migration/V1__migrations_table_permissions.sql
@@ -0,0 +1,7 @@
+-- Look, don't touch.
+--
+-- We can't do this during bootstrap because the table doesn't exist before
+-- executing this migration, and if we cached the result of the migration we
+-- would still need the migration to exist.
+REVOKE ALL ON TABLE flyway_schema_history FROM app;
+GRANT SELECT ON flyway_schema_history TO app;
-- 
2.22.0

[commonquail]

Here is the actual post-init hook change, against e42c1d7:

From 4a4c2f49a34311ce813a4a2c695b94a32e14cdc7 Mon Sep 17 00:00:00 2001
From: Mikkel Kjeldsen <mikkel.kjeldsen@sallinggroup.com>
Date: Mon, 1 Jul 2019 09:49:21 +0200
Subject: [PATCH] Create postgresql-post-init hook

This hook can be used to manipulate the newly created database and user,
for instance to change privileges.
---
 src/root/usr/bin/run-postgresql                      |  5 +++++
 .../usr/share/container-scripts/postgresql/README.md | 12 ++++++++++--
 2 files changed, 15 insertions(+), 2 deletions(-)

diff --git a/src/root/usr/bin/run-postgresql b/src/root/usr/bin/run-postgresql
index 2367e57..1516d0e 100755
--- a/src/root/usr/bin/run-postgresql
+++ b/src/root/usr/bin/run-postgresql
@@ -45,6 +45,11 @@ if $PG_INITIALIZED ; then
         "${CONTAINER_SCRIPTS_PATH}/init"
     migrate_db
     create_users
+    # Extend the base image with a custom post-init hook so we can make changes
+    # to the database and user just created.
+    process_extending_files \
+        "${APP_DATA}/src/postgresql-post-init" \
+        "${CONTAINER_SCRIPTS_PATH}/post-init"
 fi
 
 process_extending_files \
diff --git a/src/root/usr/share/container-scripts/postgresql/README.md b/src/root/usr/share/container-scripts/postgresql/README.md
index a5e9e65..f56f445 100644
--- a/src/root/usr/share/container-scripts/postgresql/README.md
+++ b/src/root/usr/share/container-scripts/postgresql/README.md
@@ -294,10 +294,18 @@ server is running.  For re-deployments scenarios with persistent data
 directory, the scripts are not sourced (no-op).
 
 
+##### `postgresql-post-init/`
+
+Same semantics as `postgresql-init/`, except that these scripts are
+sourced _after_ the `POSTGRESQL_USER` user has been created as an owner of the
+`POSTGRESQL_DATABASE` database.
+
+
 ##### `postgresql-start/`
 
-Same sematics as `postgresql-init/`, except that these scripts are
-always sourced (after `postgresql-init/` scripts, if they exist).
+Same semantics as `postgresql-init/`, except that these scripts are
+always sourced (after `postgresql-init/` and `postgresql-post-init/` scripts,
+if they exist).
 
 
 ----------------------------------------------
-- 
2.22.0

[exhuma]

I actually found a much easier approach which worked for me:

The official PostgreSQL image does not work well in OpenShift, but the ones provided by RedHat in the default ImageStream work well.

They also offer a similar way to initialise the DB using scripts in an "init" folder, but it is slightly different from the official PostgreSQL image. In this case, the tool s2i is needed. After that, one only has to do the following:

• Create a folder with another subfolder called postgresql-init
• That folder should contain .sh files which are executed when the PG container comes up from the image.
• Create a new image using: s2i build . <openshift-host>/openshift/postgresql:9.6 <openshift-host>/<openshift-namespace>/<imagestream>

This will make the new image available in <imagestream>. That image will have the initialisations baked in an will execute if a container is started from this image.

It works quite well for me.

To get back to @carlcantprogram's initial question, the users could be created in one such init-script as they are globals to the cluster. After that, the permissions will work during migrations.

[pkubatrh]

One other workaround you can use for "post-init like" behaviour is writing a start script, that checks the PG_INITIALIZED variable, which is set to something other than false when the postgresql datadir is first initialized during the run of the container.

I do not think this is documented anywhere as this was not the intended use for it when it was introduced to the start script, but we might change this if it proves useful to have.

[cloudbustinguk]

How about something like this - a bit low-tech, but did the job for me. (Assuming a Deployment and not DeploymentConfig - swap out resources/labels as necessary):

1. Create a configMap to hold your SQL

oc create cm the-db-initsql --from-file=postgres_init.sql

2. Mount the configMap in as a volume. Beware - this will cause a redeployment.

oc set volume deploy/the-db --add --overwrite -t configmap --name=the-db-initsql --configmap-name=the-db-initsql -m /some/path/postgres_init.sql --sub-path=postgres_init.sql

3. Now execute the SQL via psql in the container:

oc exec $(oc get pods -ldeployment=the-db --no-headers -o name) -- /usr/bin/psql -f /some/path/postgres_init.sql

You'll note that these commands are both using oc, so you can embed this in CI/CD without too much faffing around.

HINT: Before running step 3, add a sleep of 30 seconds or so in, to allow enough time for the re-deployments to happen (from running oc set volume ...) - otherwise, the exec will fail.

[carlcantprogram]

Hi all, just checking back in on this, it has been several years since I worked on openshift. My solution was the same as @exhuma in the end. I used S2I to customise the default image with some init scripts and logic.

[aushacker]

I found a simple workaround, specify a dummy database name for POSTGRESQL_DATABASE and then create the real database in the postgresql-init/initialize.sh script. This works, even with ephemeral storage.

[cvgaviao]

Hello, is there any easy way to run an initialization script without having to touch S2I.
Perhaps, just mounting a ConfigMap containing the script to be run, for example ?

[wbkoetsier]

@cvgaviao a ConfigMap is readonly. This is what I did:

I add a volume to my deployment that I mount at the postgresql-init directory. Here's the trick:

volumes:
  - name: name-of-your-volume
    emptyDir: {}

Then I create an init-container:

initContainers:
  - name: pgsql-init
    image: busybox-or-some-other-image-that-works-for-you
    command: ["/bin/sh", "-c"]
    args:
    - |
      cat <<EOF > /path/to/postgresql-init/initialise.sh
      #!/bin/sh
      echo add your commands here
      psql -c "DROP TABLE students;" # little Bobby Tables, we call him
      echo all done
      EOF
      chmod +x /path/to/postgresql-init/initialise.sh
    volumeMounts:
    - name: name-of-your-volume
      mountPath: /path/to/postgresql-init/

In my logs and terminal I can see that the init script really did execute 👍

[sheecegardezi]

see this for hints on chaining scripts https://stackoverflow.com/questions/48069718/creating-pg-cron-extension-within-docker-entrypoint-initdb-d-fails/69495491#69495491