geointel/scripts/rotate_postgres_password.sh

#!/usr/bin/env bash
set -euo pipefail

ROOT="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
CONTAINER="geointel"
ENV_FILE="$ROOT/.env"
RESTART="false"

usage() {
  cat <<'EOF'
Usage: bash scripts/rotate_postgres_password.sh [options]

Generates a random 64-character hexadecimal password, atomically updates
GEOINTEL_POSTGRES_PASSWORD in the operator .env file, changes the matching
PostgreSQL role, and never prints the secret.

Options:
  --container NAME
  --env-file PATH
  --restart-all-in-one   Recreate the managed all-in-one container and wait
                         for fail-closed readiness
EOF
}

while [ "$#" -gt 0 ]; do
  case "$1" in
    --container) CONTAINER="$2"; shift 2 ;;
    --env-file) ENV_FILE="$2"; shift 2 ;;
    --restart-all-in-one) RESTART="true"; shift ;;
    --help|-h) usage; exit 0 ;;
    *) echo "Unknown argument: $1" >&2; usage >&2; exit 2 ;;
  esac
done

for required in docker openssl python3; do
  command -v "$required" >/dev/null 2>&1 || {
    echo "Missing required command: $required" >&2
    exit 2
  }
done
if [ "$(docker inspect -f '{{.State.Running}}' "$CONTAINER" 2>/dev/null || true)" != "true" ]; then
  echo "Container '$CONTAINER' is not running." >&2
  exit 3
fi

ENV_FILE="$(python3 -c 'import pathlib,sys; print(pathlib.Path(sys.argv[1]).expanduser().resolve())' "$ENV_FILE")"
if [ ! -f "$ENV_FILE" ]; then
  echo "Environment file does not exist: $ENV_FILE" >&2
  exit 3
fi
DB_USER="$(docker exec "$CONTAINER" sh -c 'printf %s "${POSTGRES_USER:-${GEOINTEL_POSTGRES_USER:-geointel}}"' )"
if ! [[ "$DB_USER" =~ ^[A-Za-z_][A-Za-z0-9_]{0,62}$ ]]; then
  echo "Unsafe PostgreSQL role name." >&2
  exit 3
fi

NEW_PASSWORD="$(openssl rand -hex 32)"
if [ "${#NEW_PASSWORD}" -ne 64 ]; then
  echo "Password generator returned an unexpected result." >&2
  exit 3
fi
export GEOINTEL_ROTATED_DATABASE_PASSWORD="$NEW_PASSWORD"
BACKUP_ENV="${ENV_FILE}.password-rotation-backup.$$"
cp -- "$ENV_FILE" "$BACKUP_ENV"
chmod 600 "$BACKUP_ENV"

restore_env_on_failure() {
  if [ -f "$BACKUP_ENV" ]; then
    mv -f -- "$BACKUP_ENV" "$ENV_FILE"
  fi
}
trap restore_env_on_failure EXIT

python3 - "$ENV_FILE" <<'PY'
import os
import pathlib
import stat
import sys
import tempfile

path = pathlib.Path(sys.argv[1])
password = os.environ["GEOINTEL_ROTATED_DATABASE_PASSWORD"]
lines = path.read_text(encoding="utf-8").splitlines()
key = "GEOINTEL_POSTGRES_PASSWORD"
replacement = f"{key}={password}"
updated = False
result = []
for line in lines:
    if line.startswith(f"{key}="):
        if not updated:
            result.append(replacement)
            updated = True
        continue
    result.append(line)
if not updated:
    result.append(replacement)

mode = stat.S_IMODE(path.stat().st_mode)
with tempfile.NamedTemporaryFile(
    "w",
    encoding="utf-8",
    dir=path.parent,
    prefix=f".{path.name}.",
    delete=False,
) as handle:
    handle.write("\n".join(result) + "\n")
    temporary = pathlib.Path(handle.name)
temporary.chmod(mode)
temporary.replace(path)
PY

printf "ALTER ROLE %s PASSWORD '%s';\n" "$DB_USER" "$NEW_PASSWORD" \
  | docker exec -i "$CONTAINER" psql \
      -X -v ON_ERROR_STOP=1 -U "$DB_USER" -d postgres >/dev/null

rm -f -- "$BACKUP_ENV"
trap - EXIT
unset NEW_PASSWORD GEOINTEL_ROTATED_DATABASE_PASSWORD
echo "PostgreSQL password rotated without exposing the generated secret."

if [ "$RESTART" = "true" ]; then
  (
    cd "$ROOT"
    bash deploy/unraid/run-dockerman-container.sh
  )
  for attempt in $(seq 1 90); do
    health="$(
      docker inspect -f '{{if .State.Health}}{{.State.Health.Status}}{{else}}{{.State.Status}}{{end}}' \
        "$CONTAINER" 2>/dev/null || true
    )"
    if [ "$health" = "healthy" ]; then
      echo "Recreated container is healthy after attempt $attempt."
      exit 0
    fi
    if [ "$health" = "unhealthy" ] || [ "$health" = "exited" ] || [ "$health" = "dead" ]; then
      echo "Recreated container entered terminal state: $health" >&2
      docker logs --tail 120 "$CONTAINER" >&2 || true
      exit 4
    fi
    sleep 2
  done
  echo "Recreated container did not become healthy." >&2
  docker logs --tail 120 "$CONTAINER" >&2 || true
  exit 4
fi

echo "Recreate the container before starting new application work."