`scripts/db-setup.sh`

32 lines
#!/usr/bin/env bash
# Idempotent + self-healing: create the bubbesblock role+db, (re)write .env with a
# fresh known password, apply schema. Runs ON the Kamatera box (uses sudo -u postgres).
set -euo pipefail
PROJ="$(cd "$(dirname "$0")/.." && pwd)"
PW="$(openssl rand -hex 20)"

# role (create or reset password to the known value) — SQL via stdin, not argv
sudo -u postgres psql -v ON_ERROR_STOP=1 -q <<SQL
DO \$do\$ BEGIN
  IF EXISTS (SELECT FROM pg_roles WHERE rolname='bubbesblock') THEN
    ALTER ROLE bubbesblock LOGIN PASSWORD '${PW}';
  ELSE
    CREATE ROLE bubbesblock LOGIN PASSWORD '${PW}';
  END IF;
END \$do\$;
SQL

# database (owned by the role)
sudo -u postgres psql -tAc "SELECT 1 FROM pg_database WHERE datname='bubbesblock'" | grep -q 1 \
  || sudo -u postgres createdb -O bubbesblock bubbesblock

# app env (600), password never echoed to stdout
umask 077
printf 'DATABASE_URL=postgres://bubbesblock:%s@127.0.0.1:5432/bubbesblock\nPORT=9825\n' "$PW" > "$PROJ/.env"
chmod 600 "$PROJ/.env"

# schema
set -a; . "$PROJ/.env"; set +a
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -q -f "$PROJ/db/schema.sql"
echo "DB ready: $(psql "$DATABASE_URL" -tAc "SELECT current_database()||' / '||current_user")"