Multi-database/schema initialisation

[isedwards]

Initialise multiple databases/schemas from docker compose with a script like: https://stackoverflow.com/a/46668342

Or, implement something along the lines of:

Modify the init.sql script and the Docker Compose file to use environment variables for the passwords:

CREATE USER user1 WITH PASSWORD :user1_password;
CREATE DATABASE db1;
GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;

CREATE USER user2 WITH PASSWORD :user2_password;
CREATE DATABASE db2;
GRANT ALL PRIVILEGES ON DATABASE db2 TO user2;

In this script, :user1_password and :user2_password are placeholders for the actual passwords.

2. Modify the Docker Compose file:

services:
  postgres:
    image: postgres:13
    volumes:
      - postgres-db-volume:/var/lib/postgresql/data
      - ./docker-entrypoint-initdb.d:/docker-entrypoint-initdb.d
    environment:
      POSTGRES_DB: keycloak
      POSTGRES_USER: keycloak
      POSTGRES_PASSWORD: initpassword
      USER1_PASSWORD: yourpassword1
      USER2_PASSWORD: yourpassword2

In the environment or env_file section, define the actual passwords for users.

Note: Unfortunately, PostgreSQL does not support using variables directly in the SQL scripts like above. To resolve this, we'd need to use a bash script to create the SQL command.

Create a bash script named init.sh in your docker-entrypoint-initdb.d folder with content like this:

#!/bin/bash

set -e

psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE USER user1 WITH PASSWORD '$USER1_PASSWORD';
    CREATE DATABASE db1;
    GRANT ALL PRIVILEGES ON DATABASE db1 TO user1;

    CREATE USER user2 WITH PASSWORD '$USER2_PASSWORD';
    CREATE DATABASE db2;
    GRANT ALL PRIVILEGES ON DATABASE db2 TO user2;
EOSQL

The PostgreSQL Docker image runs .sh scripts that are found in /docker-entrypoint-initdb.d directory, as well as .sql scripts. Here, it replaces the USER1_PASSWORD and USER2_PASSWORD with the actual environment variables you set in the docker-compose.yml file.