← back to Secrets Manager

rotate-dw-admin-full.sh

255 lines

#!/usr/bin/env bash
# rotate-dw-admin-full.sh — complete the dw_admin rotation (the FORWARD path).
# Finishes what ROTATION-PRESTAGE.md half-did on 2026-06-03 (prod ALTER ran but the
# fan + restart never did). Run FROM Mac2, in a console, supervised.
#
# WHAT IT DOES
#   1. Mints a new 32-char password (never echoed, never in argv/history).
#   2. ALTER ROLE dw_admin on Mac2 local PG AND prod Kamatera (each gated by y/N).
#   3. Fans the new value to Mac2 consumers via the canonical secrets-manager CLI.
#   4. On Kamatera: rewrites the dw_admin DSN password in-place wherever it lives
#      (app .env / ecosystem / pm2 dump), backing each file up first,
#      then restarts ONLY the apps actually affected, in batches of <=6
#      (NEVER `pm2 restart all` — that OOM-kills the daemon: 186 apps).
#   5. (only with --start-split-brain) starts the 2 split-brain workers
#      (am-recrawl, vendor-review-worker-enhanced) so they carry the new pw.
#   6. Verifies: 0 dw_admin auth failures, pm2 health, secrets-cli check.
#
# FAIL-CLOSED (TK-11480 repair, 2026-09-26): errexit + pipefail everywhere. Any
# failed stage STOPS the run and prints exactly which sides were already changed
# (e.g. "mac2_alter=yes prod_alter=no") so a half-rotation is never silent.
# Synthetic test: test/rotate-dw-admin-full.test.sh (no real host/DB/secret).
#
# SECRET HYGIENE (per Steve's standing rules)
#   The new pw exists only in: this process memory, the SCRAM hasher's stdin,
#   the secrets registry, and the rewritten consumer envs. It is NEVER printed
#   (unless --show-once), never an argv, never in shell history.
#
# USAGE
#   ./rotate-dw-admin-full.sh --dry-run            # print the plan, change NOTHING
#   ./rotate-dw-admin-full.sh                      # execute (confirms before each write)
#   ./rotate-dw-admin-full.sh --show-once          # also print the new pw ONCE at the end
#   ./rotate-dw-admin-full.sh --start-split-brain  # also start the 2 stopped workers
#
# ROLLBACK
#   Every rewritten file has a sibling *.pre-rot.<UTC-ts>.bak on Kamatera
#   (created exclusively, so a rerun never overwrites an earlier backup).
#   To revert prod dw_admin to the prior pw, re-run the 2026-06-03 recovery
#   (extract from any backup's DSN, ALTER via stdin). Mac2 PG + registry similarly.

set -Eeuo pipefail
# Never trace: xtrace would print the ALTER (and so the password) to scrollback.
set +o xtrace; unset SHELLOPTS BASH_XTRACEFD 2>/dev/null || true

DRY=0; SHOW=0; START_SB=0
for a in "$@"; do case "$a" in
  --dry-run) DRY=1 ;; --show-once) SHOW=1 ;; --start-split-brain) START_SB=1 ;;
  *) echo "unknown arg: $a" >&2; exit 2 ;;
esac; done

KAM="root@kamatera"
SECRETS_CLI="$HOME/Projects/secrets-manager/cli.js"
REG_KEY="PG_DW_ADMIN_PASSWORD"

say(){ printf '\n\033[1m%s\033[0m\n' "$*"; }
confirm(){ [ "$DRY" = 1 ] && { echo "[dry-run] would prompt: $1"; return 0; }
           local r=""; read -r -p "$1 [y/N] " r || true
           [ "$r" = y ] || { echo "aborted by operator at: $1"; report_state; exit 1; }; }

# Which sides have actually been changed — printed on ANY failure or abort.
DONE_MAC2=no; DONE_PROD=no; DONE_REG=no; DONE_FAN=no
report_state(){
  echo "STATE: mac2_alter=$DONE_MAC2 prod_alter=$DONE_PROD registry=$DONE_REG kamatera_fan=$DONE_FAN" >&2
  if [ "$DONE_MAC2$DONE_PROD" != nono ] && [ "$DONE_FAN" != yes ]; then
    echo "!! PARTIAL ROTATION: the role password changed but consumers were NOT all repointed." >&2
    echo "!! Do not walk away — rerun the remaining stage, or roll back per ROLLBACK above." >&2
  fi
}
trap 'rc=$?; echo "FAILED at line $LINENO (exit $rc)" >&2; report_state; exit $rc' ERR

command -v openssl >/dev/null || { echo "need openssl" >&2; exit 1; }
command -v node    >/dev/null || { echo "need node"    >&2; exit 1; }
[ -f "$SECRETS_CLI" ] || { echo "missing $SECRETS_CLI" >&2; exit 1; }

say "dw_admin FULL rotation $([ "$DRY" = 1 ] && echo '(DRY RUN — no changes)')"

# ---- 1. mint -------------------------------------------------------------
NEW_PG="$(openssl rand -base64 48 | tr -dc 'A-Za-z0-9' | head -c 32)"
[[ "$NEW_PG" =~ ^[A-Za-z0-9]{32}$ ]] || { echo "pw-gen failed" >&2; exit 1; }
echo "[1] minted a new 32-char password (hidden)"

# Hash CLIENT-SIDE into a SCRAM-SHA-256 verifier (the exact format PG stores in
# pg_authid). Postgres accepts a pre-hashed verifier as-is, so the ALTER text that
# reaches the server — and any log_statement/pg_stat_statements capture — never
# contains the plaintext. The pw goes to python on STDIN (never argv).
PG_VERIFIER="$(printf '%s' "$NEW_PG" | python3 -c '
import sys,os,hmac,hashlib,base64
pw=sys.stdin.read().encode(); salt=os.urandom(16); it=4096
sp=hashlib.pbkdf2_hmac("sha256",pw,salt,it)
ck=hmac.new(sp,b"Client Key",hashlib.sha256).digest()
sk=hmac.new(sp,b"Server Key",hashlib.sha256).digest()
b=lambda x: base64.b64encode(x).decode()
print(f"SCRAM-SHA-256${it}:{b(salt)}${b(hashlib.sha256(ck).digest())}:{b(sk)}")
')"
[[ "$PG_VERIFIER" =~ ^SCRAM-SHA-256\$4096:[A-Za-z0-9+/=]+\$[A-Za-z0-9+/=]+:[A-Za-z0-9+/=]+$ ]] \
  || { echo "SCRAM verifier generation failed" >&2; exit 1; }
alter_sql(){ printf "ALTER ROLE dw_admin PASSWORD '%s';\n" "$PG_VERIFIER"; }

# ---- 2. Mac2 local PG ----------------------------------------------------
say "[2] ALTER ROLE dw_admin on Mac2 local PG"
if [ "$DRY" = 1 ]; then
  echo "[dry-run] alter_sql | psql -X -q -d postgres -f -   (superuser = \$USER; adjust -U if needed)"
else
  confirm "  apply to Mac2 local PG (psql -d postgres as \$USER)?"
  alter_sql | psql -X -q -d postgres -v ON_ERROR_STOP=1 -f -
  DONE_MAC2=yes; echo "  Mac2: ALTER ok"
fi

# ---- 3. prod Kamatera PG -------------------------------------------------
say "[3] ALTER ROLE dw_admin on prod Kamatera"
if [ "$DRY" = 1 ]; then
  echo "[dry-run] alter_sql | ssh $KAM 'sudo -n -u postgres psql -X -q -d postgres -f -'"
else
  confirm "  apply to PROD Kamatera?"
  alter_sql | ssh "$KAM" "sudo -n -u postgres psql -X -q -d postgres -v ON_ERROR_STOP=1 -f -"
  DONE_PROD=yes; echo "  prod: ALTER ok"
fi

# ---- 4. fan to Mac2 consumers via secrets-manager ------------------------
say "[4] route new pw to Mac2 consumers (secrets-manager import-paste, stdin)"
if [ "$DRY" = 1 ]; then
  echo "[dry-run] printf '$REG_KEY=<new>' | node $SECRETS_CLI import-paste"
else
  printf '%s=%s\n' "$REG_KEY" "$NEW_PG" | node "$SECRETS_CLI" import-paste
  DONE_REG=yes; echo "  registry + Mac2 .envs updated"
fi

# ---- 5. fan to Kamatera (in-place DSN rewrite) + targeted batched restart -
# Why not a heredoc: ssh has ONE stdin, and it must carry the password alone.
# So the (secret-free) script moves into the ssh command, quoted with %q.
# Assumes root's login shell on Kamatera is bash (it is; a change fails loudly).
# The remote program travels as the ssh COMMAND (it contains no secret); the
# password travels alone on ssh STDIN. (The old version fed both a pipe and a
# heredoc to the same stdin — the heredoc won, so the remote read the script
# text as the "password".)
read -r -d '' REMOTE_FAN <<'REMOTE' || true
set -Eeuo pipefail
IFS= read -r NEW_PG                       # pw arrives on stdin — never in argv
[[ "$NEW_PG" =~ ^[A-Za-z0-9]{32}$ ]] || { echo "remote: bad pw on stdin" >&2; exit 3; }
export NEW_PG DWROT_TS="$(date -u +%Y%m%dT%H%M%SZ)"
# 5a. rewrite every file carrying a dw_admin DSN; record which dirs changed.
#     Any failed write exits non-zero -> errexit stops BEFORE any restart.
CHANGED_DIRS="$(python3 - <<'PY'
import os,re,sys,shutil
new=os.environ["NEW_PG"]; ts=os.environ["DWROT_TS"]
roots=["/root/DW-Agents","/root/public-projects","/root/Projects","/etc/environment","/root/.pm2/dump.pm2"]
pat=re.compile(r'(postgres(?:ql)?://dw_admin:)[^@]+(@)')
files=set()
for r in roots:
    if os.path.isfile(r): files.add(r)
    elif os.path.isdir(r):
        for dp,_,fs in os.walk(r):
            if "node_modules" in dp or "/.git/" in dp or dp.endswith("/.git"): continue
            for f in fs:
                if f.endswith((".env",".local",".js",".cjs",".json")): files.add(os.path.join(dp,f))
dirs=set(); errs=0
for f in sorted(files):
    try: s=open(f,encoding="utf-8",errors="surrogateescape").read()
    except Exception: continue
    if "dw_admin:" not in s: continue
    ns=pat.sub(lambda m: m.group(1)+new+m.group(2), s)
    if ns==s: continue
    try:
        bak=f"{f}.pre-rot.{ts}.bak"
        with open(bak,"x",encoding="utf-8",errors="surrogateescape") as b: b.write(s)   # exclusive: never clobber
        shutil.copymode(f,bak)
        tmp=f"{f}.dwrot-tmp"
        with open(tmp,"w",encoding="utf-8",errors="surrogateescape") as t: t.write(ns)
        shutil.copymode(f,tmp); os.replace(tmp,f)
        dirs.add(os.path.dirname(f))
    except Exception as e:
        errs+=1; sys.stderr.write(f"REWRITE FAILED {f}: {e}\n")
sys.stderr.write(f"rewrote files in {len(dirs)} dirs\n")
print("\n".join(sorted(dirs)))
sys.exit(1 if errs else 0)
PY
)"
# 5b. restart ONLY online apps whose cwd relates to a changed dir, <=6 per batch.
#     CHG is passed in the ENVIRONMENT (the old version appended it as an argv,
#     so python never saw it and zero apps restarted). Broad parent cwds never
#     match "downward", so an app running from /root can't sweep the whole box.
#     Captured via plain command substitution (NOT `done < <(...)`): errexit +
#     pipefail cannot see a failure inside process substitution, so a dead
#     `pm2 jlist` would silently mean "0 apps restarted" and a false-green DONE.
PM2_JSON="$(pm2 jlist)" || { echo "pm2 jlist failed — refusing to guess restart targets" >&2; exit 6; }
TARGET_LIST="$(printf '%s' "$PM2_JSON" | CHG="$CHANGED_DIRS" python3 -c '
import sys,json,os
BROAD={"/","/root","/root/Projects","/root/public-projects","/root/DW-Agents","/etc","/root/.pm2"}
dirs=[d for d in os.environ.get("CHG","").splitlines() if d]
for p in json.load(sys.stdin):
    e=p["pm2_env"]
    if e.get("status")!="online": continue
    cwd=(e.get("pm_cwd","") or "").rstrip("/")
    if not cwd: continue
    hit=any(cwd==d or cwd.startswith(d+"/") for d in dirs if d not in BROAD) or \
        (cwd not in BROAD and any(d.startswith(cwd+"/") for d in dirs))
    if hit: print(p["name"])
')"
TARGETS=()
while IFS= read -r a; do [ -n "$a" ] && TARGETS+=("$a"); done <<< "$TARGET_LIST"
echo "affected apps: ${#TARGETS[@]}"
FAILED_RESTARTS=()
restart_batch(){ pm2 restart "$@" --update-env >/dev/null 2>&1 || FAILED_RESTARTS+=("$@"); }
batch=()
for a in ${TARGETS[@]+"${TARGETS[@]}"}; do
  batch+=("$a")
  if [ "${#batch[@]}" -ge 6 ]; then restart_batch "${batch[@]}"; sleep 4; batch=(); fi
done
if [ "${#batch[@]}" -gt 0 ]; then restart_batch "${batch[@]}"; fi
# 5c. split-brain workers only when explicitly requested (--start-split-brain).
if [ "${DWROT_START_SB:-0}" = 1 ]; then pm2 start am-recrawl vendor-review-worker-enhanced; fi
pm2 save >/dev/null
if [ "${#FAILED_RESTARTS[@]}" -gt 0 ]; then
  echo "RESTART FAILED for: ${FAILED_RESTARTS[*]} (all other batches ran)" >&2; exit 4
fi
echo "remote fan + targeted restart done"
REMOTE

say "[5] fan to prod Kamatera (rewrite dw_admin DSNs in place, restart affected apps <=6/batch)"
if [ "$DRY" = 1 ]; then
  echo "[dry-run] pw via ssh stdin (script as ssh command); restart only apps whose cwd relates to a rewritten file"
  if [ "$START_SB" = 1 ]; then echo "[dry-run] would also pm2 start am-recrawl vendor-review-worker-enhanced"; fi
else
  confirm "  rewrite prod app envs + restart affected dw_admin apps (batched)?"
  printf '%s\n' "$NEW_PG" | ssh "$KAM" "DWROT_START_SB=$START_SB bash -c $(printf '%q' "$REMOTE_FAN")"
  DONE_FAN=yes
fi

# ---- 6. verify -----------------------------------------------------------
read -r -d '' REMOTE_VERIFY <<'REMOTE' || true
set -uo pipefail
LOG=/var/log/postgresql/postgresql-14-main.log
sleep 12
f=$(awk -v c="$(date -u -d "12 seconds ago" "+%Y-%m-%d %H:%M:%S" 2>/dev/null)" '/authentication failed for user "dw_admin"/ && $1" "$2 > c {n++} END{print n+0}' "$LOG" 2>/dev/null || echo 0)
echo "  dw_admin auth failures (last 12s): $f  (want 0)"
pm2 jlist | python3 -c "import sys,json;from collections import Counter;print('  pm2:',dict(Counter(p['pm2_env'].get('status') for p in json.load(sys.stdin))))"
sudo -n -u postgres psql -X -d dw_unified -tAc "SELECT '  dw_unified rows: '||count(*) FROM products;" 2>&1 | head -1
[ "${f:-0}" = 0 ]
REMOTE

say "[6] verify"
if [ "$DRY" = 1 ]; then
  echo "[dry-run] would: check 0 dw_admin auth failures, pm2 health, secrets-cli check"
else
  ssh "$KAM" "bash -c $(printf '%q' "$REMOTE_VERIFY")" </dev/null \
    || { echo "VERIFY FAILED: dw_admin auth failures seen after rotation" >&2; report_state; exit 5; }
  node "$SECRETS_CLI" check 2>&1 | grep -iE "dw_admin|PG_DW|FAIL|VALID" | head -5 || true
fi

trap - ERR
say "DONE.  After verifying 0 failures + apps online:"
echo "  • flip CNCP prod flag (server.js:1718 status:'compromised' -> 'ok')"
echo "  • the box is now on a FRESH password (compromised-era pw retired)"
if [ "$SHOW" = 1 ]; then printf '  new dw_admin pw (record in your vault, then clear): %s\n' "$NEW_PG"; fi
unset NEW_PG PG_VERIFIER