`scripts/fix-all-stale-dw-admin.sh`

153 lines
#!/usr/bin/env bash
# fix-all-stale-dw-admin.sh — idempotent fleet-wide repair of the stale dw_admin
# Postgres password across pm2 services on Kamatera.
#
# Root problem (2026-06): the dw_admin password was rotated, but many service
# .env files still carry the OLD password, so every DB query fails with
# `password authentication failed for user "dw_admin"`. (First seen breaking
# the Pairs-Well coordinates widget; same rot logged in memory
# dw-unified-backup-broken.md — nightly pg_dump 0-byte since 2026-06-13.)
#
# This script: for every pm2 process whose .env DATABASE_URL uses dw_admin,
#   1. test the CURRENT url with `SELECT 1` — if it AUTHENTICATES, skip (idempotent);
#   2. else build a candidate url with the CANONICAL password from secrets-manager
#      and test THAT with `SELECT 1`;
#   3. ONLY if the candidate authenticates: back up .env, swap *only* the password
#      segment, `pm2 restart NAME --update-env`, then re-verify (psql + healthz).
#   4. if neither the current nor the candidate authenticates, report STILL-FAILING
#      and touch nothing (it's not a stale-password problem — host down, db gone,
#      or canon itself wrong).
#
# DRY-RUN by default. Pass --apply to actually write + restart.
# The password is NEVER echoed; it only ever lives in process env vars.
#
# Usage (run ON Kamatera):
#   bash fix-all-stale-dw-admin.sh            # dry-run: show what's stale + the plan
#   bash fix-all-stale-dw-admin.sh --apply    # repair every stale service, one at a time

set -u

APPLY=0
[ "${1:-}" = "--apply" ] && APPLY=1

SECRETS_ENV="${SECRETS_ENV:-/root/Projects/secrets-manager/.env}"
# Fall back to the Mac2 path if the Kamatera one isn't present.
[ -f "$SECRETS_ENV" ] || SECRETS_ENV="$HOME/Projects/secrets-manager/.env"

red()  { printf '\033[31m%s\033[0m\n' "$*"; }
grn()  { printf '\033[32m%s\033[0m\n' "$*"; }
ylw()  { printf '\033[33m%s\033[0m\n' "$*"; }
mask() { sed -E 's#(://[^:]+:)[^@]+(@)#\1***\2#g'; }

command -v jq   >/dev/null 2>&1 || { red "jq not found"; exit 1; }
command -v psql >/dev/null 2>&1 || { red "psql not found"; exit 1; }
command -v pm2  >/dev/null 2>&1 || { red "pm2 not found"; exit 1; }

CANON="$(grep -m1 '^DW_ADMIN_PG_PASSWORD=' "$SECRETS_ENV" 2>/dev/null | cut -d= -f2-)"
if [ -z "${CANON:-}" ]; then
  red "FATAL: DW_ADMIN_PG_PASSWORD not found in $SECRETS_ENV — cannot proceed."
  exit 1
fi
export CANON

if [ "$APPLY" = "1" ]; then ylw "=== MODE: APPLY (will edit .env + pm2 restart stale services) ==="
else                        grn  "=== MODE: DRY-RUN (no writes) — pass --apply to repair ==="; fi
echo "canonical pw source: $SECRETS_ENV  (loaded, value hidden)"
echo

# Build "<name>\t<cwd>" for every pm2 process (one per line).
mapfile -t ROWS < <(pm2 jlist 2>/dev/null | jq -r '.[] | [.name, .pm2_env.pm_cwd] | @tsv')

OK=0; FIXED=0; WOULDFIX=0; SKIP=0; FAILED=0; SEEN_ENV=""

# Swap ONLY the password between "://dw_admin:" and the next "@". Password is
# treated as a literal string (no regex interpretation) by reading it from env.
build_url() {  # $1 = old url ; uses $CANON
  OLDURL="$1" python3 - <<'PY'
import os, re, sys
url = os.environ["OLDURL"]; pw = os.environ["CANON"]
m = re.match(r'^(.*?://dw_admin:)([^@]*)(@.*)$', url, re.S)
if not m:
    sys.exit(3)
sys.stdout.write(m.group(1) + pw + m.group(3))
PY
}

write_env() {  # $1 = envfile ; $2 = new url
  F="$1" NEWURL="$2" python3 - <<'PY'
import os
f = os.environ["F"]; nl = os.environ["NEWURL"]
out = []
for ln in open(f).read().splitlines():
    out.append("DATABASE_URL=" + nl if ln.startswith("DATABASE_URL=") else ln)
open(f, "w").write("\n".join(out) + "\n")
PY
}

healthz_of() {  # $1 = cwd -> echo HEALTH_URL from .deploy.conf if present
  [ -f "$1/.deploy.conf" ] && grep -m1 '^HEALTH_URL=' "$1/.deploy.conf" | cut -d= -f2-
}

for row in "${ROWS[@]}"; do
  NAME="${row%%$'\t'*}"
  CWD="${row#*$'\t'}"
  ENVF="$CWD/.env"
  [ -f "$ENVF" ] || continue

  URL="$(grep -m1 '^DATABASE_URL=' "$ENVF" 2>/dev/null | cut -d= -f2-)"
  [ -n "$URL" ] || continue
  case "$URL" in *dw_admin:*) ;; *) continue ;; esac   # only password'd dw_admin urls

  # de-dupe: several pm2 apps may share one .env
  case "$SEEN_ENV" in *"|$ENVF|"*) continue ;; esac
  SEEN_ENV="$SEEN_ENV|$ENVF|"

  # 1) current password good?  -> idempotent skip
  if psql "$URL" -tAc 'SELECT 1' >/dev/null 2>&1; then
    grn "OK     $NAME  ($ENVF)"; OK=$((OK+1)); continue
  fi

  # 2) build + test the canonical-password candidate
  NEWURL="$(build_url "$URL")" || { red "SKIP   $NAME  — unparseable DATABASE_URL shape"; SKIP=$((SKIP+1)); continue; }
  if [ "$NEWURL" = "$URL" ]; then
    red "FAIL   $NAME  — already on canonical pw but auth still fails (host/db issue, not stale pw)"; FAILED=$((FAILED+1)); continue
  fi
  if ! psql "$NEWURL" -tAc 'SELECT 1' >/dev/null 2>&1; then
    red "FAIL   $NAME  — canonical pw ALSO fails (host down / db gone / canon wrong) — leaving untouched"; FAILED=$((FAILED+1)); continue
  fi

  # 3) candidate authenticates — stale password confirmed
  if [ "$APPLY" = "0" ]; then
    ylw "STALE  $NAME  — WOULD fix ($ENVF) + pm2 restart"; WOULDFIX=$((WOULDFIX+1)); continue
  fi

  BAK="$ENVF.bak.$(date +%Y%m%d-%H%M%S)"
  cp "$ENVF" "$BAK"
  write_env "$ENVF" "$NEWURL"
  # verify the write took and now authenticates before bouncing the process
  CHK="$(grep -m1 '^DATABASE_URL=' "$ENVF" | cut -d= -f2-)"
  if ! psql "$CHK" -tAc 'SELECT 1' >/dev/null 2>&1; then
    red "ERROR  $NAME  — post-write psql failed; rolling back"; cp "$BAK" "$ENVF"; FAILED=$((FAILED+1)); continue
  fi
  pm2 restart "$NAME" --update-env >/dev/null 2>&1
  sleep 2
  HU="$(healthz_of "$CWD")"
  if [ -n "$HU" ]; then
    CODE="$(curl -s -o /dev/null -m 15 -w '%{http_code}' "$HU")"
    if [ "$CODE" = "200" ]; then grn "FIXED  $NAME  — restarted, healthz 200 ($BAK)"; FIXED=$((FIXED+1))
    else ylw "FIXED? $NAME  — restarted + db OK, but healthz=$CODE ($HU) — check app log"; FIXED=$((FIXED+1)); fi
  else
    grn "FIXED  $NAME  — restarted, db auth OK (no HEALTH_URL to probe) ($BAK)"; FIXED=$((FIXED+1))
  fi
done

echo
echo "──────── summary ────────"
echo "already OK : $OK"
[ "$APPLY" = "0" ] && echo "stale      : $WOULDFIX  (run with --apply to fix)" || echo "fixed      : $FIXED"
echo "failed     : $FAILED  (host/db/canon issue — NOT touched)"
echo "skipped    : $SKIP  (unparseable url shape)"
[ "$FAILED" -gt 0 ] && ylw "Note: 'failed' services have a non-password problem — investigate before any further action."
[ "$APPLY" = "0" ] && [ "$WOULDFIX" -gt 0 ] && echo "Next: re-run with  --apply  to repair the $WOULDFIX stale service(s), one at a time."
exit 0