PacketStation/deploy/postgres/initdb/01-roles.sh

#!/usr/bin/env bash
# Runs once, when the postgres container starts on an empty pgdata volume.
# Creates the two login roles and the packetstation database.
#   ps_migrate  owns the database and its schema. Migrations run as this role.
#   ps_app      may connect. Table grants come from the migrations (0001).
# Passwords are read from the compose secret files, never from the environment or argv.
# The official image may source this file instead of executing it, so the body runs in a
# subshell and leaves the image's own shell options alone.

ps_create_roles() (
  set -euo pipefail

  for name in postgres_migrate_password postgres_app_password; do
    f="/run/secrets/$name"
    if [[ ! -r "$f" ]]; then
      echo "01-roles: secret $name is missing or unreadable" >&2
      exit 1
    fi
    if [[ ! -s "$f" ]]; then
      echo "01-roles: secret $name is empty" >&2
      exit 1
    fi
    if [[ "$(wc -l < "$f")" -gt 1 ]] || grep -q $'\r' "$f"; then
      echo "01-roles: secret $name must be one line with no carriage return" >&2
      exit 1
    fi
  done

  # psql reads the passwords itself through backtick \set, so they never appear on a
  # command line. :'name' quotes each value as a SQL literal.
  psql -v ON_ERROR_STOP=1 --no-psqlrc --username "${POSTGRES_USER:-postgres}" --dbname postgres <<'SQL'
\set migrate_pw `cat /run/secrets/postgres_migrate_password`
\set app_pw `cat /run/secrets/postgres_app_password`

CREATE ROLE ps_migrate LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD :'migrate_pw';
CREATE ROLE ps_app     LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE NOREPLICATION PASSWORD :'app_pw';
\unset migrate_pw
\unset app_pw

CREATE DATABASE packetstation OWNER ps_migrate ENCODING 'UTF8' TEMPLATE template0;
REVOKE ALL ON DATABASE packetstation FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE packetstation TO ps_app;  -- TEMP: the feed COPYs into temp tables

\connect packetstation
-- PostgreSQL 15+ already denies CREATE on public to PUBLIC. Make it explicit and give
-- the schema to the migrate role.
REVOKE ALL ON SCHEMA public FROM PUBLIC;
ALTER SCHEMA public OWNER TO ps_migrate;
SQL

  echo "01-roles: created ps_migrate, ps_app and database packetstation"
)

ps_create_roles