p24-infra Docs

n8n-supabase-vault-runtime-secrets

7 min read

Playbook: n8n Supabase-Vault runtime secrets (Vault as source of truth for Supabase-coupled workflows)

Reusable design-pattern for any n8n workflow that already writes to (or otherwise hard-depends on) our Supabase project and reads a leaf secret — an API password, OAuth client_secret, token, or connection string that is not itself the Supabase key. Such a workflow can read that leaf secret at runtime straight from vault.decrypted_secrets instead of from $env/SOPS, collapsing rotation from a multi-step SOPS→secrets-sync.yml→container-recreate cycle to a single UPDATE.

Origin: issues #4032 (ratified architecture) + #4033 (empirical discovery + this pattern registration). Trigger case: on 2026-07-12 the ATRAX account password rotated, the fleet-update workflows on bms-4 fired invalid_grant (#4021/#3941/#3942), and the SOPS→sync→recreate chain kept them broken for hours — even though those workflows already write their results into Supabase, so Vault reads add no new failure mode. Sibling design-pattern: the Redis singleton-lock #4008 (docs/playbooks/n8n/n8n-redis-singleton-lock.md).

When to use this pattern

Apply only if ALL of the following are true. If any is false → the secret stays in SOPS+age.

QuestionAnswer to apply
Does the workflow already have our Supabase project as a hard runtime dependency (it writes to / reads from mwkqmgadqnkkihjdeqsi and is dead without it)?yes
Is the secret a leaf secret (vendor password/OAuth secret/token), i.e. NOT the Supabase key itself?yes
Does the secret rotate often enough that the SOPS→sync→recreate latency is an operational pain?yes
Is the value read at a low rate (a few runs/day or a scheduled trigger), so a per-run Postgres SELECT is cheap?yes

If any is “no”, do not migrate — see When NOT to use.

Qualifying cases in this stack: the ATRAX-cluster workflows (CCx9UMdphmGficDX, AJ1px9uHIfbsriof, Wi3ZJcPw0YbxLXwa, akgnvueDkDPbLJ49, zdzyIQ8UBsDAF0wI, zTxZ7cIpJJ5uk4SG). Existing proof-of-pattern already in the repo: n8n-workflows/branding/oauth-token-refresh.json.

When NOT to use this pattern

  • The workflow is NOT Supabase-coupled. Reading Vault would add a brand-new Supabase runtime dependency to a workflow that does not have one — the opposite of what this pattern buys. These stay on SOPS: gh-actions-monitor (GH_TOKEN), github-auto-trigger, hu-sp-report-email, linkedin-*, telegram-infra-alerts, WAHA_Telegram_Handler, atrax-report-generator-webhook (its N8N_ATRAX_REPORT_SECRET is a webhook shared secret, not the OAuth creds).
  • The secret IS the Supabase key (chicken-and-egg — you cannot read Vault without it). The bootstrap Postgres credential (below) is the one secret that must remain in SOPS.
  • The secret belongs to a parked/other design track — e.g. the Pinbox24 credential stays on the #3826 CF Worker token-broker; do not fold it in (#4033 §5).
  • High-frequency reads where a per-run SELECT would dominate cost — design a cache first, or keep it in $env.

Design in one paragraph

Create a dedicated least-privilege Postgres role in the Supabase project that can SELECT only the target secret rows of vault.decrypted_secrets (RLS policy on vault.secrets, e.g. name LIKE 'ATRAX\_%'). Never reuse service_role — an RLS-bypass key in SOPS is a rename, not a security improvement. The role’s connection string is the one bootstrap secret that stays in SOPS (secrets/n8n-bms4.env.sops) — and since a qualifying workflow already talks to Supabase, this adds zero new secret material to the trust surface. Inside each qualifying workflow, a Postgres node runs SELECT name, decrypted_secret FROM vault.decrypted_secrets WHERE name IN (…) immediately before the node that consumes the secret; downstream expressions read {{ $node['Read Vault'].json.<key> }} instead of {{ $env.<KEY> }} (or instead of a hard-coded literal). The migrated secret then lives only in Vault — it is deleted from SOPS after cutover so there is exactly one source of truth. Rotation is a single UPDATE vault.secrets SET secret = :new WHERE name = :key, executed by the secret-manager role; the next scheduled run picks it up — no PR, no sync, no container recreate.

Components (canonical shapes — see #4032 §3 for full SQL)

  1. Dedicated role + RLS — CREATE ROLE svc_n8n_vault_<scope> LOGIN; GRANT USAGE ON SCHEMA vault
    • GRANT SELECT ON vault.decrypted_secrets; RLS POLICY … ON vault.secrets FOR SELECT USING (name LIKE '<SCOPE>\_%' ESCAPE '\'). Put the policy on vault.secrets (the underlying table), not the SECURITY DEFINER view. Each new scope (ATRAX, then Pinbox, …) gets its own role + policy row — never widen an existing predicate across trust boundaries.
  2. Vault rows — SELECT vault.create_secret('<value>', '<KEY>', '<description>'), seeded with the freshly rotated value (the seed IS the rotation). Values entered by secret-manager via a session-only channel — never through chat, a PR, or a committed file.
  3. Bootstrap credential — one …_VAULT_PG_CONN=postgresql://svc_n8n_vault_<scope>:<pw>@<pooler>:6543/postgres?sslmode=require key in secrets/n8n-bms4.env.sops. Synced to the bms-4 n8n postgres credential by bms-4/sync-n8n-credentials.py.
  4. n8n read node — a first-party Postgres node; WHERE name IN (…) trimmed to exactly the keys that workflow needs (do not pull rows the role would have to RLS-filter needlessly).

Migration runbook (per workflow)

  1. Confirm live vs export. The repo n8n-workflows/*.json may lag the live workflow. Diff against the live n8n API on bms-4 (server/n8n-admin op) before editing — some workflows read $env, some hard-code the OAuth grant (#4033 §2.1); the before-state changes the edit.
  2. Ensure the role + Vault rows (steps 1–3 above) exist first — a workflow edited to read Vault before the rows exist fails on first run.
  3. Set the workflow active: false. Insert the Read Vault Postgres node before the consumer node; repoint the consumer’s expressions from $env/literal → {{ $node['Read Vault'].json.<key> }}. All other nodes byte-identical.
  4. Test off-schedule (webhook/manual trigger): Vault node returns the expected rows; the token/auth node returns 200; the downstream Supabase write still succeeds.
  5. Export the workflow JSON back into n8n-workflows/ (versioning — the definition otherwise lives only in live n8n) and set active: true only if the test is green.
  6. After 3 clean production runs, remove the migrated key(s) from every SOPS file that carries them (canary-decrypt before git add), and update the dev_r_services source-of-truth column.

Rotation, going forward (the whole point)

-- secret-manager role, one line, no PR/sync/recreate:
UPDATE vault.secrets SET secret = :new_value, updated_at = NOW() WHERE name = 'ATRAX_PASSWORD';

Propagation window = time to the next scheduled run (bounded, acceptable — the vendor portal honours only one credential at a time, so a shorter window has no operational value). Audit every write (who / when / which key — never the value) via the role’s own audit log / RLS trail.

Guards (do not skip)

  • Bootstrap key stays in SOPS, and “recreate-not-restart” still applies to it — docker restart does not reload env_file; recreate the n8n container when the bootstrap key changes (lesson #3688). Migrated leaf secrets no longer need any container action to rotate.
  • Zero drift: a migrated secret lives in exactly one place. Delete it from SOPS post-cutover; do not leave a SOPS copy “just in case” — that recreates the dual-source problem this pattern kills.
  • Least privilege: dedicated role, scoped RLS, never service_role.
  • No plaintext in git: never commit a secret value — not in a workflow JSON, not in a migration file, not in a doc. If an exported workflow JSON already hard-codes a value, that is a leak: rotate, scrub, and purge history (secret-manager + human — see docs/playbooks/static-api-key-incident-rotation.md).
  • Fallback (escape hatch): if Vault is unavailable during a rotation, temporarily re-add the key to SOPS + secrets-sync.yml — i.e. today’s path. Keep this documented for the 24 h after cutover.

Checklist — reviewer / self-review

Before merging a workflow that uses this pattern:

  • The workflow is genuinely Supabase-coupled (writes/reads mwkqmgadqnkkihjdeqsi) — not just “would be convenient”.
  • A dedicated Postgres role with a scoped RLS policy is used — never service_role.
  • The bootstrap …_VAULT_PG_CONN is the only new SOPS key; all migrated leaf secrets end up only in Vault.
  • WHERE name IN (…) lists exactly the keys this workflow reads.
  • Off-schedule test is green (Vault rows returned, token 200, Supabase write ok) before active: true.
  • The edited workflow JSON is re-exported into n8n-workflows/ and contains no secret value.
  • Post-cutover: key removed from every SOPS file carrying it; dev_r_services updated.
  • Rotation drill performed: one UPDATE vault.secrets propagates to the next run with no container action.

See also

  • docs/plans/plan-4032-n8n-supabase-vault-runtime-secrets.md — ratified architecture (role/RLS/node/SOPS-removal).
  • docs/plans/plan-4033-n8n-vault-runtime-discovery-addendum.md — empirical discovery, scope corrections, plaintext-in-git finding, Pinbox24 resolution.
  • n8n-workflows/branding/oauth-token-refresh.json — existing working Vault read/write from n8n.
  • docs/playbooks/n8n/n8n-redis-singleton-lock.md — sibling n8n design-pattern (#4008).
  • docs/playbooks/static-api-key-incident-rotation.md — rotation + leak remediation.
  • standards/domains/n8n.md — n8n credential conventions.

Graph View

Backlinks