init-db.sh

#!/bin/bash
set -euo pipefail
# --- Create databases and roles ---
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" <<-EOSQL
  -- Resource DB + user
  CREATE DATABASE "${RESOURCE_DATASOURCE_NAME}";
  CREATE ROLE "${RESOURCE_DATASOURCE_USERNAME}" LOGIN PASSWORD '${RESOURCE_DATASOURCE_PASSWORD}';
  ALTER DATABASE "${RESOURCE_DATASOURCE_NAME}" OWNER TO "${RESOURCE_DATASOURCE_USERNAME}";
  GRANT ALL PRIVILEGES ON DATABASE "${RESOURCE_DATASOURCE_NAME}" TO "${RESOURCE_DATASOURCE_USERNAME}";

  -- Auth (Keycloak) DB + user
  CREATE DATABASE "${AUTH_DATASOURCE_NAME}";
  CREATE ROLE "${AUTH_DATASOURCE_USER}" LOGIN PASSWORD '${AUTH_DATASOURCE_PASSWORD}';
  ALTER DATABASE "${AUTH_DATASOURCE_NAME}" OWNER TO "${AUTH_DATASOURCE_USER}";
  GRANT ALL PRIVILEGES ON DATABASE "${AUTH_DATASOURCE_NAME}" TO "${AUTH_DATASOURCE_USER}";

  -- Ooz DB + user
  CREATE DATABASE "${OOZ_DATASOURCE_NAME}";
  CREATE ROLE "${OOZ_DATASOURCE_USERNAME}" LOGIN PASSWORD '${OOZ_DATASOURCE_PASSWORD}';
  ALTER DATABASE "${OOZ_DATASOURCE_NAME}" OWNER TO "${OOZ_DATASOURCE_USERNAME}";
  GRANT ALL PRIVILEGES ON DATABASE "${OOZ_DATASOURCE_NAME}" TO "${OOZ_DATASOURCE_USERNAME}";

  -- Decks DB + user
  CREATE DATABASE "${DECKS_DATASOURCE_NAME}";
  CREATE ROLE "${DECKS_DATASOURCE_USERNAME}" LOGIN PASSWORD '${DECKS_DATASOURCE_PASSWORD}';
  ALTER DATABASE "${DECKS_DATASOURCE_NAME}" OWNER TO "${DECKS_DATASOURCE_USERNAME}";
  GRANT ALL PRIVILEGES ON DATABASE "${DECKS_DATASOURCE_NAME}" TO "${DECKS_DATASOURCE_USERNAME}";
EOSQL

# --- Schema ownership + sane privileges (RESOURCE) ---
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "${RESOURCE_DATASOURCE_NAME}" <<-EOSQL
  -- Make the app user own the default schema *before* anything creates tables
  ALTER SCHEMA public OWNER TO "${RESOURCE_DATASOURCE_USERNAME}";

  -- Allow creating objects; (optional) lock down PUBLIC if you want stricter security
  GRANT USAGE, CREATE ON SCHEMA public TO "${RESOURCE_DATASOURCE_USERNAME}";
  -- REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- uncomment if you want only your user to create objects

  -- If any objects already exist, make sure privileges are correct (harmless if empty at init)
  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO "${RESOURCE_DATASOURCE_USERNAME}";
  GRANT USAGE,  SELECT, UPDATE          ON ALL SEQUENCES IN SCHEMA public TO "${RESOURCE_DATASOURCE_USERNAME}";
EOSQL

# --- Schema ownership + sane privileges (AUTH / Keycloak) ---
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "${AUTH_DATASOURCE_NAME}" <<-EOSQL
  ALTER SCHEMA public OWNER TO "${AUTH_DATASOURCE_USER}";
  GRANT USAGE, CREATE ON SCHEMA public TO "${AUTH_DATASOURCE_USER}";
  -- REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- optional hardening

  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO "${AUTH_DATASOURCE_USER}";
  GRANT USAGE,  SELECT, UPDATE          ON ALL SEQUENCES IN SCHEMA public TO "${AUTH_DATASOURCE_USER}";
EOSQL

# --- Schema ownership + sane privileges (OOZ) ---
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "${OOZ_DATASOURCE_NAME}" <<-EOSQL
  -- Make the app user own the default schema *before* anything creates tables
  ALTER SCHEMA public OWNER TO "${OOZ_DATASOURCE_USERNAME}";

  GRANT USAGE, CREATE ON SCHEMA public TO "${OOZ_DATASOURCE_USERNAME}";
  -- REVOKE CREATE ON SCHEMA public FROM PUBLIC;  -- optional hardening

  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO "${OOZ_DATASOURCE_USERNAME}";
  GRANT USAGE,  SELECT, UPDATE          ON ALL SEQUENCES IN SCHEMA public TO "${OOZ_DATASOURCE_USERNAME}";
EOSQL

# --- Schema ownership + sane privileges (DECKS) ---
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" -d "${DECKS_DATASOURCE_NAME}" <<-EOSQL
  ALTER SCHEMA public OWNER TO "${DECKS_DATASOURCE_USERNAME}";

  GRANT USAGE, CREATE ON SCHEMA public TO "${DECKS_DATASOURCE_USERNAME}";

  GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES    IN SCHEMA public TO "${DECKS_DATASOURCE_USERNAME}";
  GRANT USAGE,  SELECT, UPDATE          ON ALL SEQUENCES IN SCHEMA public TO "${DECKS_DATASOURCE_USERNAME}";
EOSQL