p24-infra Docs

supabase-secrets-management-gaps

7 min read

Supabase Secrets Management — Gap Analysis

Date: 2026-06-28 Author: Claude (p24-infra session) Status: Active — implementation in progress Related playbooks:

Supabase ref:

Key architectural finding

The Supabase management API fully supports programmatic project API key lifecycle management:

OperationEndpointAuth
List keysGET /v1/projects/{ref}/api-keysSUPABASE_ACCESS_TOKEN (sbp_…)
Create keyPOST /v1/projects/{ref}/api-keysSUPABASE_ACCESS_TOKEN + scope secrets:write
Delete keyDELETE /v1/projects/{ref}/api-keys/{id}SUPABASE_ACCESS_TOKEN + scope secrets:write

Full value is returned in the POST response — one-time reveal, like GitHub PATs. No Playwright or dashboard access needed for key rotation. SUPABASE_ACCESS_TOKEN is already in secrets/monitoring.env.sops and GH Secrets.

This means: one authorized management token can bootstrap and rotate all project API keys. Per-component key isolation (Gap 1) is fully automatable.

Context

Analysis performed 2026-06-28 against current state of:

  • supabase-access-token-rotation.md, n8n/n8n-supabase-credential-rotation.md, static-api-key-incident-rotation.md
  • password-rotation-procedures.md, secrets-sync.yml, secrets-rotation-log.md

Incident reference: 2026-06-27 GPS fleet freeze — 12h outage caused by a Supabase key rotation that updated SOPS but left 5 of 6 n8n credentials with the revoked key.

Gap 1 — No per-component key isolation

Severity: HIGH | Effort: MEDIUM | Blast radius: all consumers

Current state

One SUPABASE_SERVICE_ROLE_KEY (sb_secret_…) shared by:

  • 6 n8n credentials on bms-4 (3× supabaseApi + 3× httpHeaderAuth)
  • et-operational-platform Next.js backend (Vercel)
  • brandpilot Next.js backend (Vercel)
  • monitoring scripts on vps-i1 (cost-exporter, infra_operations log)
  • CI/CD workflows (secrets-sync.yml, credential-rotation.yml)

One SUPABASE_ANON_KEY (sb_publishable_…) shared by:

  • et-operational-platform frontend
  • n8n credentials supabase-dev-r + supabase-anon-apikey

Supabase recommendation

“We recommend creating separate secret keys for each backend component”

Gap

A single compromised or rotated key breaks all consumers simultaneously. Missing any one consumer (proven: 2026-06-27) causes multi-hour outage.

Proposed target state

Key name in SupabaseSOPS varConsumerScope
p24-n8nSUPABASE_SERVICE_KEY_N8Nbms-4 n8n (6 credentials)service_role
p24-vercelSUPABASE_SERVICE_KEY_VERCELet-operational-platform + brandpilotservice_role
p24-monitoringSUPABASE_SERVICE_KEY_MONITORINGvps-i1 scripts, CI/CDservice_role
p24-anonSUPABASE_ANON_KEYall frontends, public APIanon (publishable)

Migration approach

Fully automatable via management API — see supabase-service-key-rotation.md. No dashboard session required. Migration order: create new keys → distribute per-consumer → verify each consumer → delete old shared key (after all verified).

Status

Done (2026-06-28) — 3 named keys created via management API (p24_n8n_20260628, p24_vercel_20260628, p24_monitoring_20260628). SOPS updated (n8n-bms4 + monitoring + brandpilot). All consumers migrated via secrets-sync. Old shared keys (default + n8n_service_key_2_kapibara_supabase) deleted. n8n credentials auto-updated by sync-n8n-supabase-creds.py. PRs #1955 #1956 #1959.

Gap 2 — n8n Supabase credential sync not automatic

Severity: HIGH | Effort: LOW | Root cause of 2026-06-27 outage

Current state

secrets-sync.yml bms-4 job:

  1. Deploys .env from SOPS ✅
  2. Restarts n8n containers ✅
  3. Runs sync-n8n-credentials.py — syncs Mezmo credential only ✅
  4. Does NOT run sync-n8n-supabase-creds.py ❌

sync-n8n-supabase-creds.py exists and is correct but must be triggered manually. Playbook says “always run after rotating” — manual = forgettable.

Gap

Every Supabase key rotation goes through SOPS/secrets-sync silently to n8n. n8n reads credentials from its own encrypted PostgreSQL vault, not from .env.

Fix

Add one step to secrets-sync.yml bms-4 job after n8n is ready:

python3 /opt/p24-infra/scripts/sync-n8n-supabase-creds.py --dry-run
python3 /opt/p24-infra/scripts/sync-n8n-supabase-creds.py

Script is idempotent (patches only credentials where key differs from SOPS).

Status

Pending — tracked as part of supabase-service-key-rotation.md Track B.

Gap 3 — Root cause analysis comes after rotation, not before

Severity: MEDIUM | Effort: LOW | Process alignment

Current state

static-api-key-incident-rotation.md Step 6 (last): Root cause analysis. Rotation is completed before understanding why exposure happened.

Supabase recommendation

“Make sure you have fully considered the situation and have remediated the root cause of the suspicion or vulnerability first” “We recommend using the OWASP Risk Rating Methodology to determine the appropriate response urgency”

Fix

Insert Step 0c into static-api-key-incident-rotation.md before Step 1:

  • Root cause hypothesis (< 5 min)
  • Is the exposure vector still active?
  • OWASP risk rating: Likelihood × Impact → response urgency
  • Remediate vector before generating new key

Status

Done — Step 0c added to static-api-key-incident-rotation.md.

Gap 4 — No dedicated Supabase project API key rotation playbook

Severity: MEDIUM | Effort: LOW | Procedure gap

Current state

Three documents cover partial aspects but no canonical rotation procedure exists for SUPABASE_SERVICE_ROLE_KEY + SUPABASE_ANON_KEY with all consumers enumerated.

The 2026-06-27 incident was partly caused by partial execution — no single document listed all consumers and required steps in sequence.

Fix

Created docs/playbooks/supabase-service-key-rotation.md covering:

  • Consumer inventory
  • Management API programmatic key creation
  • Per-component key isolation migration plan
  • Full rotation steps with consumer order
  • Mandatory n8n sync step
  • Mandatory dev_r_services update
  • Pre-deletion verification gate

Status

Done — playbook created.

Gap 5 — Supabase audit log not monitored

Severity: MEDIUM | Effort: HIGH | Detection gap

Current state

Incident trigger list includes “audit log shows calls from unexpected IP” but no system actively monitors Supabase audit logs.

Gap

Unauthorized service_role API calls may go undetected until downstream effects appear (GPS staleness, data corruption, unexpected billing).

Proposed fix (deferred)

Option A: n8n scheduled workflow polls Supabase management API for unusual auth events. Option B: Edge Function log drain into Mezmo with Alertmanager rule.

Status

Deferred — requires design issue. Known gap, accepted risk for now.

Gap 6 — 6 n8n Supabase credentials across 2 types

Severity: MEDIUM | Effort: MEDIUM | Complexity risk

Current state

3× supabaseApi type + 3× httpHeaderAuth type for Supabase. Dual-type complexity caused partial sync in 2026-06-27 incident.

Credential IDNameTypeKey kind
8bVcpRNbyLOHJDrUSupabase accountsupabaseApisb_secret_
1pvpQp7GfpAxNFIhSupabase - kapibarasupabaseApisb_secret_
rFL6zCStG02XlFGKsupabase-dev-rsupabaseApisb_publishable_
cMpMbkDFAJzC3O4fsupabaseKapbaraAuthBearerhttpHeaderAuthsb_secret_
aMxMREZhtabQV3Z7supabase-anon-apikeyhttpHeaderAuthsb_publishable_
ZNuDXmNs3nT58L6psupabase-service-role-keyhttpHeaderAuthsb_secret_

Fix

When per-component keys are created (Gap 1 migration), consolidate n8n to:

  • 1 credential for p24-n8n service key (supabaseApi type where possible)
  • 1 credential for anon/publishable key Reduce from 6 to 2–3. Audit which httpHeaderAuth creds can migrate to supabaseApi.

Status

Planned — part of Gap 1 migration. No standalone action yet.

Gap 7 — dev_r_services rotation tracking not enforced

Severity: MEDIUM | Effort: LOW | Audit accuracy

Current state

password-rotation-procedures.md documents UPDATE dev_r_services SET last_rotated... but it is not a mandatory enforced step — omitting it leaves stale expiry dates that cause premature or missed rotation alerts.

Fix

Added as mandatory step (not optional post-step) in supabase-service-key-rotation.md.

Status

Done — included in new rotation playbook.

Gap 8 — No pre-deletion verification gate

Severity: LOW | Effort: LOW | Safety net

Current state

Playbook instructs “revoke old key only after confirming new value works everywhere” but provides no structured checklist of consumers to verify before deletion. Supabase: “Deleting a secret key is irreversible.”

Fix

Added explicit consumer verification checklist to supabase-service-key-rotation.md as a mandatory gate before the delete step.

Status

Done — included in new rotation playbook.

Implementation Summary

GapSeverityStatus
1. No per-component key isolationHIGHDone — p24_n8n/p24_vercel/p24_monitoring created 2026-06-28, old keys deleted, PRs #1955 #1959
2. n8n sync not automaticHIGHDone — secrets-sync.yml auto-runs sync-n8n-supabase-creds.py, PR #1955
3. RCA after rotationMEDIUMDone — Step 0c added to incident playbook
4. No service key rotation playbookMEDIUMDone — supabase-service-key-rotation.md created
5. Supabase audit log not monitoredMEDIUMDeferred — known gap, future issue
6. 6 n8n creds / 2 typesMEDIUMPlanned — part of Gap 1 migration
7. dev_r_services tracking unenforcedMEDIUMDone — added to new rotation playbook
8. No pre-deletion gateLOWDone — added to new rotation playbook

Change sequencing — no-stop deployment

Track A — Documentation only (zero risk): Gaps 3, 4, 7, 8 — playbook files only. No infrastructure touch.

Track B — secrets-sync.yml (low risk, one CI run): Gap 2 — adds idempotent SSH step to bms-4 job. n8n stays running. Prerequisite: confirm sync-n8n-supabase-creds.py present at path on bms-4.

Track C — Key isolation migration (medium risk, planned): Gap 1 — creates named keys via management API, migrates consumers, deletes old shared key last. Order: monitoring scripts → Vercel → n8n → delete old key. Old key stays active until last consumer confirmed. No service interruption. Full procedure: supabase-service-key-rotation.md §Per-component migration.

Graph View

Backlinks