Containerized PostgreSQL: options and limitations

For a long time, I used to dedicate a separate Postgresql instance to every application. This approach simplifies deployments and moving self sufficient application stacks from one Namespace / Cluster to another, it is a bit resource expensive and complicate the backups of the associated PersistentVolumeClaims. Anticipating on a multiplication of the namespaces on one of my clusters, I needed to mutualize the Postgres instance on each Namespace so it could act as a backend for all of the postgres compatible applications – not fan of the cluster-wide Database pattern: as soon as you restrict inter-namespace communication, things start to become quite complicated.

Official postgres

Postgres Official Image’s entrypoint loads and executes any *.sql *.sql.gz and *.sh. However, if you tried to use it, you probably noticed on the second boot that

PostgreSQL Database directory appears to contain a database; Skipping initialization                                                                                                                                                       │
│                                                                                                                                                                                                                                            │
│ 2025-04-05 10:43:02.612 UTC [1] LOG:  starting PostgreSQL 17.4 (Debian 17.4-1.pgdg120+2) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit                                                                         │
│ 2025-04-05 10:43:02.613 UTC [1] LOG:  listening on IPv4 address "0.0.0.0", port 5432

Developers justified this choice based on:

| Warning: scripts in /docker-entrypoint-initdb.d are only run if you start the container with a data directory that is empty; any pre-existing database will be left untouched on container startup. One common problem is that if one of your /docker-entrypoint-initdb.d scripts fails (which will cause the entrypoint script to exit) and your orchestrator restarts the container with the already initialized data directory, it will not continue on with your scripts.

While this is a good reason not to execute those scripts and I understand the common cautiousness about database initialization, the inability to add databases or change users and rights post first deployment remains an important operational obstacle to resource mutualization.

Bitnami and other images

I do not appreciate the extra layer of complexity added by bitnami image, nor do I appreciate it for other applications they package. From what I read, it does not solve the multi-user issue we are facing and my solution remains compatible with it.

PSQL scripting

Functional perimeter

Because these scripts will be executed on each start-up, we should not handle any form of suppression of data or users. However, the ability to change a user’s password is, in my case, an important feature. I think that most DB admins would be comfortable in automating the following tasks

Based on a file like

db_users.secret

some_user:h1sp@ssw0rd
other_user:h1Str0n9sp@ssw0rd

our scripts should be able to:

• Create a user with the associated password
• Create a database he owns named after him (like <the user>_db)
• Update the user’s password

and no more. By keeping usernames and databases names fixed, things will remain simple.

Scripting

For security reasons, I prefer to mount POSTGRES_PASSWORD and POSTGRES_USER as secrets in /run/secrets/postgresql and provide their paths with POSTGRES_USER_FILE and POSTGRES_PASSWORD_FILE (as preconized in the image documentation).

psql-entrypoint.sh

#!/bin/sh
set -e
cd "$(dirname "$0")"
export POSTGRES_USER=$(cat $POSTGRES_USER_FILE)
export POSTGRES_PASSWORD=$(cat $POSTGRES_PASSWORD_FILE)
export DBUSERS=/run/secrets/postgres/db_users.secret
./wait-for-postgres.sh
./init-user-db.sh
sleep infinity

In the same folder will live the db_users.secret. We can loop on this file and implement our logic with:

#!/bin/sh
# create databases based on a user:password
# flat file. Databases are named <username>_db
set -e
export PGPASSWORD=$POSTGRES_PASSWORD

while read line; do                 # for each line
  if [ "$line" != "" ]; then
   export username="${line%%:*}"      # characters before ':' are the username
   export password="${line#*:}"       # characters after ':' are the password
   export db_name="${username}_db"    # database is named after the username

   psql --host localhost --port 5432 --username "$POSTGRES_USER" <<EOF
   SELECT 'CREATE DATABASE $db_name'
   WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$db_name')\gexec
   
   DO
    \$do\$
    BEGIN
    IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '$username') THEN
      RAISE NOTICE 'Role $username already exists. Updating his password';
      ALTER USER $username ENCRYPTED PASSWORD '$password';
     ELSE
       CREATE USER $username WITH ENCRYPTED PASSWORD '$password';
       GRANT ALL PRIVILEGES ON DATABASE $db_name TO $username;
       GRANT CREATE ON SCHEMA public TO $username;
       ALTER DATABASE $db_name OWNER TO $username;
    END IF;
    END
    \$do\$;
EOF
fi
done<$DBUSERS

Finally, since this script will run asynchronously from the service’s entrypoint, we should wait until the service is up – i.e. able to answer a query like listing the databases (\l). This can be done with:

wait-for-postgres.sh

#!/bin/sh

set -o errexit
set -o nounset
set -o pipefail

export PGPASSWORD=$POSTGRES_PASSWORD
until psql --host localhost --port 5432 --username "$POSTGRES_USER" -c '\l'; do
  echo >&2 "$(date +%Y-%m-%dT%H:%M:%S) Postgres is unavailable - sleeping"
  sleep 1
done
echo >&2 "$(date +%Y-%m-%dT%H:%M:%S) Postgres is up - executing command"

Sidecar definition

Why a sidecar rather than a postStart?

I was tempted by using a postStart inside the main postgres container but abandoned this lead for 3 reasons.

First of all, the command executed in a postStart does not appears on the Pod’s logs. I found this black box hugely frustrating, piping does not work either and I struggled to execute multiple scripts which makes evolution of this provisioning nearly impossible. Together with the fact that this solution would not be container agnostic, I abandoned it to work on a more robust pattern: a sidecar container

A sidecar is a container living in the same pod as the main container (in our case: postgres) thus accessible through 127.0.0.1. This alpine based psql seemed the best option, for its compressed size is less than 6MB and we want to allocate the minimal amount of resources to this container.

All together

The 3 scripts shown above should live in the same ConfigMap

cm-postgres-db-manage.yml

apiVersion: v1
kind: ConfigMap
metadata:
  name: postgres-db-manage-cm
  namespace: tyr
data:
  init-user-db.sh: |
    #!/bin/sh
    # create databases based on a user:password
    # flat file. Databases are named <username>_db
    set -e
    export PGPASSWORD=$POSTGRES_PASSWORD

    while read line; do
       if [ "$line" != "" ]; then
        export username="${line%%:*}"
        export password="${line#*:}"
        export db_name="${username}_db"  

    psql --host localhost --port 5432 --username "$POSTGRES_USER" <<EOF
    SELECT 'CREATE DATABASE $db_name'
    WHERE NOT EXISTS (SELECT FROM pg_database WHERE datname = '$db_name')\gexec

    DO
     \$do\$
     BEGIN
     IF EXISTS (SELECT FROM pg_catalog.pg_roles WHERE rolname = '$username') THEN
       RAISE NOTICE 'Role $username already exists. Updating his password';
       ALTER USER $username ENCRYPTED PASSWORD '$password';
      ELSE
        CREATE USER $username WITH ENCRYPTED PASSWORD '$password';
        GRANT ALL PRIVILEGES ON DATABASE $db_name TO $username;
        GRANT CREATE ON SCHEMA public TO $username;
        ALTER DATABASE $db_name OWNER TO $username;
     END IF;
     END
     \$do\$;
    EOF
    fi
    done<$DBUSERS    

  wait-for-postgres.sh: |
    #!/bin/sh

    set -o errexit
    set -o nounset
    set -o pipefail

    export PGPASSWORD=$POSTGRES_PASSWORD
    until psql --host localhost --port 5432 --username "$POSTGRES_USER" -c '\l'; do
      echo >&2 "$(date +%Y-%m-%dT%H:%M:%S) Postgres is unavailable - sleeping"
      sleep 1
    done
    echo >&2 "$(date +%Y-%m-%dT%H:%M:%S) Postgres is up - executing command"    

  psql-entrypoint.sh: |
    #!/bin/sh
    set -e
    cd "$(dirname "$0")"
    export POSTGRES_USER=$(cat $POSTGRES_USER_FILE)
    export POSTGRES_PASSWORD=$(cat $POSTGRES_PASSWORD_FILE)
    export DBUSERS=/run/secrets/postgres/db_users.secret
    ./wait-for-postgres.sh
    ./init-user-db.sh
    sleep infinity    

We also need to load postgres user, password and the list of the additional user password using a Secret

secret-postgres.yml

apiVersion: v1
kind: Secret
metadata:
  name: postgres-secret
  namespace: tyr
data: ## Base64 of the files contents
  # Str0ng3stPostgresqlUs3rN@me
  pg_admin_user.secret: U3RyMG5nM3N0UG9zdGdyZXNxbFVzM3JOQG1lCg==
  # Str0ng3stPostgresqlPassw0rd
  pg_admin_password.secret: U3RyMG5nM3N0UG9zdGdyZXNxbFBhc3N3MHJkCg==
  # flat file containing user:password for DB initialization
  db_users.secret: a2V5Y2xvYWs6SzNjbDBAa1Bhc3Nzc2FQa0AwbGMzSwpkb2xpYmFycjpEMGwxcEBzc3NzQHAxbDBECg==

postgres-secret will be mounted in the two containers, postgres-db-manage-cm only in our sidecar, like is this Deployment

deploy-postgres.yml

apiVersion: apps/v1
kind: Deployment
metadata:
  name: postgres-deployment
  namespace: tyr
  labels:
    app: postgres
spec:
  replicas: 1
  selector:
    matchLabels:
      app: postgres
  template:
    metadata:
      labels:
        app: postgres
    spec:
      containers:
        - name: postgres-configurator  # our sidecar
          image: alpine/psql:17.4      # we run the same version as postgres
          env:
            - name: POSTGRES_USER_FILE
              value: /run/secrets/postgres/pg_admin_user.secret
            - name: POSTGRES_PASSWORD_FILE
              value: /run/secrets/postgres/pg_admin_password.secret
          command:
            - sh
            - -c
            - /opt/db-scripts/psql-entrypoint.sh
          resources:
            requests: # Minimal resources
              cpu: 0.5m
              memory: 4Mi
            limits:
              cpu: 1m
              memory: 8Mi
          volumeMounts:
            - mountPath: /opt/db-scripts # 
              name: initdb
            - mountPath: /run/secrets/postgres # SECRET (shared with postgres)
              name: postgres-secret
              readOnly: true
        - name: postgres  # main service container
          image: postgres:17.4
          ports:
            - name: sql
              containerPort: 5432
          resources:
            requests:
              cpu: 20m
              memory: 64Mi
            limits:
              cpu: 100m
              memory: 128Mi
          volumeMounts:
            - mountPath: /var/lib/postgresql/data
              name: postgres-pvc
            - mountPath: /run/secrets/postgres # SECRET (shared with psql)
              name: postgres-secret
              readOnly: true
          env:
            - name: PGDATA
              value: /var/lib/postgresql/data/pgdata
            - name: POSTGRES_USER_FILE
              value: /run/secrets/postgres/pg_admin_user.secret
            - name: POSTGRES_PASSWORD_FILE
              value: /run/secrets/postgres/pg_admin_password.secret
      volumes:
        - name: postgres-pvc        # part not detailed 
          persistentVolumeClaim:    # in this tutorial
            claimName: postgres-pvc
        - name: postgres-secret
          secret:
            secretName: postgres-secret
        - name: initdb
          configMap:
            defaultMode: 0711
            name: postgres-db-manage-cm

Usage

To add a new database associated with a new user, or to update a user’s password, I just need to

• edit db_users.secret from secret-postgres.yml
• kubectl apply
• delete the existing pod for the changes to apply

With a db_users.secret containing

keycloak:K3cl0@kPassssaPk@0lc3K
dolibarr:D0l1p@ssss@p1l0D

To see these lines in the sidecar’s logs is nerdy satisfaction I could not live without.

│ psql: error: connection to server at "localhost" (::1), port 5432 failed: Connection refused                                                                                                                                               │
│     Is the server running on that host and accepting TCP/IP connections?                                                                                                                                                                                                                                                                                                                                      │
│ 2025-04-05T10:43:02 Postgres is unavailable - sleeping                                                                                                                                                                                     │
│                                                                                             List of databases                                                                                                                              │
│             Name             |            Owner            | Encoding | Locale Provider |  Collate   |   Ctype    | Locale | ICU Rules |                        Access privileges                                                          │
│ -----------------------------+-----------------------------+----------+-----------------+------------+------------+--------+-----------+-----------------------------------------------------------------                                  │
│  Str0ng3stPostgresqlUs3rN@me | Str0ng3stPostgresqlUs3rN@me | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |                                                                                                   │
│  dolibarr_db                 | dolibarr                    | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =Tc/dolibarr                                                   +                                  │
│                              |                             |          |                 |            |            |        |           | dolibarr=CTc/dolibarr                                                                             │
│  keycloak_db                 | keycloak                    | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =Tc/keycloak                                                   +                                  │
│                              |                             |          |                 |            |            |        |           | keycloak=CTc/keycloak                                                                             │
│  postgres                    | Str0ng3stPostgresqlUs3rN@me | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           |                                                                                                   │
│  template0                   | Str0ng3stPostgresqlUs3rN@me | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/"Str0ng3stPostgresqlUs3rN@me"                               +                                  │
│                              |                             |          |                 |            |            |        |           | "Str0ng3stPostgresqlUs3rN@me"=CTc/"Str0ng3stPostgresqlUs3rN@me"                                   │
│  template1                   | Str0ng3stPostgresqlUs3rN@me | UTF8     | libc            | en_US.utf8 | en_US.utf8 |        |           | =c/"Str0ng3stPostgresqlUs3rN@me"                               +                                  │
│                              |                             |          |                 |            |            |        |           | "Str0ng3stPostgresqlUs3rN@me"=CTc/"Str0ng3stPostgresqlUs3rN@me"                                   │
│ (6 rows)                                                                                                                                                                                                                                   │
│                                                                                                                                                                                                                                            │
│ 2025-04-05T10:43:03 Postgres is up - executing command                                                                                                                                                                                     │
│ NOTICE:  Role keycloak already exists. Updating his password                                                                                                                                                                               │
│ DO                                                                                                                                                                                                                                         │
│ NOTICE:  Role dolibarr already exists. Updating his password                                                                                                                                                                               │
│ DO

Cheers (ﾉ◕ヮ◕)ﾉ*:･ﾟ✧