p24-infra Docs

n8n-db-password-rotation

3 min read

Playbook: n8n DB Password Rotation

Trigger

N8N_DB_PASSWORD (or bms4_n8n_db_password) rotated in secrets/n8n-bms4.env.sops without updating the actual PostgreSQL user — n8n crashes with:

password authentication failed for user "n8n"
Error: There was an error initializing DB

Confirm

ssh root@54.36.123.110 "docker logs bms-4-n8n-1 --tail=20 2>&1 | grep -i 'password\|DB\|crash'"
docker inspect bms-4-n8n-1 --format '{{.State.Health.Status}}'

Expected bad state: starting (health check loop) with password authentication failed in logs.

Fix

The PostgreSQL container uses local peer/trust auth for unix socket connections, so the password can be reset without knowing the old value:

ssh root@54.36.123.110 bash -s <<'REMOTE'
DB_PW=$(grep "^N8N_DB_PASSWORD=" /opt/p24-infra/bms-4/.env | cut -d= -f2-)
echo "Password length: $(echo -n "$DB_PW" | wc -c)"
docker exec bms-4-n8n-postgres-1 psql -U n8n -c "ALTER USER n8n WITH PASSWORD '$DB_PW';"
docker restart bms-4-n8n-1
REMOTE

Wait 20–30 seconds then confirm healthy:

ssh root@54.36.123.110 "docker inspect bms-4-n8n-1 --format '{{.State.Health.Status}}'"
# Expected: healthy

Why This Happens

The credential rotation script rotates N8N_DB_PASSWORD in the SOPS file and deploys the new value to /opt/p24-infra/bms-4/.env. However, the rotation script does not call ALTER USER on the Postgres container — it has no direct Postgres connection to bms-4’s n8n database. The next secrets-sync deploy picks up the new .env but n8n immediately fails to connect because the database user still has the old password.

Prevention

When rotating N8N_DB_PASSWORD:

  1. Rotate in SOPS as usual
  2. Before committing, run the ALTER USER fix above on bms-4
  3. Verify n8n is healthy, then commit + push

Long-term fix: Add an SSH step in secrets-sync.yml (bms-4 job) that detects when N8N_DB_PASSWORD changed and runs ALTER USER automatically. Tracked in issue #1549.

Notes

  • The Postgres container (bms-4-n8n-postgres-1) allows local connections without password (unix socket trust auth) — so psql -U n8n works without credentials from inside the container
  • n8n workers (bms-4-n8n-worker-*) connect via TCP and are unaffected while main is down; queued jobs resume automatically once main recovers
  • First observed and fixed: 2026-06-27 during mass credential rotation deployment

Audit Log — Log to infra_operations

After this operation completes, log it to the infra_operations audit table.

Python (Linux server — bms-4, vps-i1, vps-h1, or similar):

import sys
sys.path.insert(0, '/opt/p24-infra')
from scripts.lib.log_op import log_op
 
log_op(
    actor="claude",  # "radieu" for manual human ops, "claude" for agent
    op_type="credential_rotation",
    resource="N8N_DB_PASSWORD",
    result="success",  # "success" | "failed" | "skipped"
    detail="Scheduled rotation — n8n Postgres DB password changed and SOPS bms-4 updated",
    env="bms-4",
    gh_issue=2730,
)

PowerShell (Windows dev machine):

$env:SUPABASE_URL = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_URL=").ToString().Split("=",2)[1].Trim()
$env:SUPABASE_SERVICE_KEY = (Get-Content "C:\code_2026\p24-infra\.env.local" | Select-String "^SUPABASE_SERVICE_KEY=").ToString().Split("=",2)[1].Trim()
python -c "
import os, sys
sys.path.insert(0, 'C:/code_2026/p24-infra')
from scripts.lib.log_op import log_op
log_op('claude', 'credential_rotation', 'N8N_DB_PASSWORD', 'success', 'Scheduled rotation — n8n Postgres DB password changed and SOPS bms-4 updated', 'bms-4')
"
$env:SUPABASE_URL = ''; $env:SUPABASE_SERVICE_KEY = ''

Graph View

Backlinks