Rotate the gordon_bot DB role password

When to use: first deploy of a fresh cluster, scheduled 90-day rotation, or on suspected credential compromise.

The gordon_bot role is created by migration 0027_gordon_bot_role.sql with a placeholder password (placeholder_rotate_via_ansible). That placeholder exists only so the role can be created at migration time. No gordon-bot container should ever run with the placeholder in place.

When to rotate

• First deploy of a fresh cluster — replace the placeholder before any bot starts.
• Scheduled rotation — every 90 days, aligned with the Binance key rotation schedule.
• On suspected compromise — immediately, followed by a full audit of trading.bot_events and trading.order_intents for rows the old credential may have emitted.

Prerequisites

• Access to the Ansible vault on the ops workstation.
• Superuser (or gordon) Postgres credentials for srv-apps.
• A paused deploy window — gordon-manager should not be spawning new bots during rotation.

Procedure

1. Generate a new password.

   openssl rand -hex 32

   Do not reuse passwords across environments.

2. Update the Ansible vault. The key is trading.gordon_bot_db_password under homelab/group_vars/srv-apps/vault.yml:

   ansible-vault edit homelab/group_vars/srv-apps/vault.yml

   Update trading.gordon_bot_db_password to the new value.

3. Rotate in Postgres. Connect as gordon or postgres on srv-apps and run:

   ALTER ROLE gordon_bot WITH PASSWORD '<new-password>';

   This is cluster-scoped — one ALTER ROLE applies to all bots sharing the role.

   Via Docker:

   ssh srv-apps
   docker compose exec postgres psql -U gordon -d gordon \
     -c "ALTER ROLE gordon_bot WITH PASSWORD '<new-password>';"

4. Deploy the updated vault. Run the bot deploy playbook:

   ansible-playbook -i homelab/inventory/hosts.yml \
     homelab/playbooks/deploy-gordon-bot.yml --ask-vault-pass

   The playbook renders each bot's env file with the new password and restarts the bot containers via gordon-manager's reconciler.

5. Verify. After each bot restarts:

   • GET /healthz returns 200.
   • GET /readyz returns 200 after warmup and lease acquire.
   • trading.bot_events carries a role_probe_passed event with the expected bot_id and role: "gordon_bot".
   • GET /config shows database_url: "[REDACTED]" — no credential leakage.

   Check via curl from srv-apps:

   curl -fsS http://localhost:8084/healthz
   curl -fsS http://localhost:8084/readyz

Verify

ssh srv-apps
docker compose exec postgres psql -U gordon -d gordon -c "
  SET search_path = trading;
  SELECT bot_id, event_type, created_at
  FROM bot_events
  WHERE event_type = 'role_probe_passed'
  ORDER BY created_at DESC LIMIT 5;
"

Expected: one role_probe_passed row per bot, timestamped after the rotation.

Rollback

If rotation breaks bot startup:

1. Re-set the old password:

   ALTER ROLE gordon_bot WITH PASSWORD '<old-password>';

2. Re-run the deploy playbook with the vault rolled back to the previous revision:

   git revert <vault-commit>
   ansible-playbook -i homelab/inventory/hosts.yml \
     homelab/playbooks/deploy-gordon-bot.yml --ask-vault-pass

3. File a postmortem — a rotation that breaks startup usually points at an env-template drift or a missed bot in the inventory.

Related

• Migration 0027_gordon_bot_role.sql — role creation and RLS policies
• Migration 0028_gordon_bot_lease_holder_grant.sql — column-level UPDATE grant on bot_leases
• Bot warmup fallback — role probe is a warmup gate
• Incident response — operator token rotation