`ROTATION-PRESTAGE.md`

89 lines
# dw_admin Rotation — Pre-Stage (corrected 2026-06-09)

> **No secret VALUES here.** Console-only steps are Steve's; Claude runs the
> no-secret parts (local fan + verify). Goal: a tight, uninterrupted window so
> the 2026-06-03 half-fire does NOT repeat.

---

## ⚠️ Why the last attempt (2026-06-03) HALF-FIRED — now fixed

Pre-flight on 2026-06-09 found the route-back fan was a **silent no-op** for
dw_admin — three stacked bugs:

1. The `PG_DW_ADMIN_PASSWORD` route lived at routes.json **top-level**, but
   `cli.js` only reads `ROUTES.services[key]` → the route was invisible → the
   fan wrote **only** master+desktop and skipped all 8 destinations.
2. Even when found, the destinations are `type:"env_file"` — and `fanOut` had
   **no `env_file` branch** → silently skipped.
3. Even if written, the fan set a **bare `*_PASSWORD` var**, but every consumer
   connects via a full **`DATABASE_URL` DSN** (pw embedded inline). The bare var
   is read by nothing. So `ALTER` changed the real pw, the fan "succeeded," and
   every app restarted still holding the OLD pw in `DATABASE_URL` → FATAL auth.

**Fixed (commit on this repo, 2026-06-09) — all in cli.js, routes.json UNCHANGED:**
- `routeFor(key)` now falls back to top-level routes, not just `.services` — so
  `PG_DW_ADMIN_PASSWORD` (and ~21 OTHER invisible top-level routes: SHOPIFY_ORDERS_TOKEN,
  TWILIO_*, GOOGLE_DRIVE_*, the ANTHROPIC_* keys…) actually fan.
- `fanOut` gained an `env_file` branch (the dw_admin dests' type — previously unhandled).
- New `rewriteDsnPassword()` + a code-driven `DSN_REWRITE` map (`PG_DW_ADMIN_PASSWORD →
  {dsn:DATABASE_URL, user:dw_admin}`): the fan now **surgically rewrites the password
  inside `DATABASE_URL`** for `dw_admin` in each of the 8 dests (URL-encoding `+ / =`),
  in addition to setting the bare var.
- Proven on temp .env: DSN rewritten+encoded, comments+other keys preserved, route
  visible (8 dests), routes.json byte-identical to HEAD.

**Still NOT auto-covered: prod.** The 8 fan dests are all **local Mac2**. The
~40 Kamatera apps read their OWN `DATABASE_URL` (pm2 env). The fan does not
touch prod — see step 3b, which is manual + SSH (Steve).

---

## Console steps — keep 2 → 3 → 4 ONE uninterrupted block

**Before you start:** open a second shell already `ssh root@45.61.58.125` and
locate prod's DATABASE_URL source so 3b is instant:
```
ssh root@45.61.58.125 "grep -rl 'dw_admin:' /root/.pm2/dump.pm2 /root/.env /root/**/ecosystem.config.js 2>/dev/null | head"
```
Keep the OLD pw recovery line staged (revert target lives in `/root/.pm2/dump.pm2`).

1. **Mint** a URL-safe ≥20-char pw (avoid raw `+ / =` for the manual prod edit):
   `openssl rand -hex 24`  (or `openssl rand -base64 24 | tr -d '+/='`)
2. **ALTER ROLE on BOTH** databases:
   - Prod: `ssh root@45.61.58.125 'sudo -u postgres psql -c "ALTER ROLE dw_admin WITH PASSWORD '\''<NEW>'\'';"'`
   - Local Mac2: `psql -U postgres -c "ALTER ROLE dw_admin WITH PASSWORD '<NEW>';"`
3. **Route back the new pw:**
   - **3a — Local (Claude can run this):**
     `printf 'PG_DW_ADMIN_PASSWORD=<NEW>' | node ~/Projects/secrets-manager/cli.js import-paste`
     → rewrites `DATABASE_URL` in all 8 local dests + sets the bare var. Confirm
     the output shows `[DSN DATABASE_URL×1]` for each (NOT `⚠ NOT FOUND`).
   - **3b — Prod (Steve, SSH):** update the prod `DATABASE_URL` source found
     above with the new pw inline, e.g. if it's an env/ecosystem file:
     `ssh root@45.61.58.125` → edit so `DATABASE_URL=postgresql://dw_admin:<NEW>@127.0.0.1:5432/dw_unified...`
     (URL-encode any `+ / =`). If the live value lives only in `dump.pm2`, the
     reliable refresh is `pm2 delete all && pm2 start <ecosystem> && pm2 save`
     after the ecosystem file carries `process.env.DATABASE_URL` (already
     env-first on the rotation-prestage branch).
4. **Restart consumers:**
   - Local: `pm2 restart wallco-ai-9905`
   - Prod: `ssh root@45.61.58.125 'pm2 restart all --update-env'` (or the
     delete+start from 3b if env didn't refresh).
5. **Verify (Claude can run):**
   - `node ~/Projects/secrets-manager/cli.js check`
   - `ssh root@45.61.58.125 'pm2 ls'` → all `online`, no restart loops
   - `curl -s -o /dev/null -w "%{http_code}" https://designerwallcoverings.com`
   - **If any dw_admin app is FATAL → REVERT immediately:** re-ALTER prod to the
     OLD pw, `pm2 restart all --update-env`, and stop to debug. Do NOT leave it
     half-applied.

## Naming note
The route key + fan key is **`PG_DW_ADMIN_PASSWORD`** (the one cli.js reads).
secrets `.env` also carries a stray `DW_ADMIN_PG_PASSWORD` and the old doc
referenced `DW_ADMIN_DB_PASSWORD` — both dead aliases; always fan
`PG_DW_ADMIN_PASSWORD`.

## After rotation → Kamatera clear
- Flip cncp prod flag (`server.js` `status:'compromised'` → `ok`).
- Outstanding (same incident, separate creds): Gemini key #1, key #2.