p24-infra Docs

2735-per-consumer-postgres-roles

3 min read

Design — Per-Consumer Postgres Roles for Supabase

Issue: #2735 · Status: Design (plan only) · Batch: K (#3338)

Eliminate SUPABASE_SERVICE_ROLE_KEY duplication by giving each consumer its own least-privilege Postgres role instead of sharing the god-mode service key.

1. Problem

SUPABASE_SERVICE_ROLE_KEY is duplicated across 5 SOPS files: et-operational-platform, brandpilot, n8n-bms4, whatsup, vps-h1. It bypasses RLS entirely. Consequences:

  • One key compromise forces atomic rotation across all 5 files + every live env + Vercel + containers — a high-blast-radius, error-prone operation.
  • No least privilege: a workflow that only needs p24_queue can read/write every table.
  • No per-consumer audit trail — all traffic is one role.

2. Target design — least-privilege roles

Each consumer connects via Postgres with a dedicated role and a scoped grant. Illustrative (audit in Phase 1 sets the real table list):

CREATE ROLE svc_n8n_queue LOGIN PASSWORD :'pw';
GRANT SELECT, INSERT, UPDATE ON p24_queue TO svc_n8n_queue;
 
CREATE ROLE svc_etop_backend LOGIN PASSWORD :'pw';
GRANT SELECT, INSERT ON registries, reg_records TO svc_etop_backend;

Each service holds only its role’s connection string (a scoped credential), not the service key.

3. Phased plan

PhaseWorkRisk
1. AuditFor each of the 5 consumers, capture the exact tables + operations used (query pg_stat_statements, grep service code for table names). Output: a consumer→tables matrix.none (read-only)
2. Design rolesDefine role hierarchy + grants from the Phase 1 matrix. Prefer table-level grants; use RLS policies where row scoping is needed.none (paper)
3. Create rolesMigration NNNN_per_consumer_roles.sql creating roles + grants (no consumer switched yet).low — additive
4. Cut overUpdate each service to use its Postgres connection string instead of the REST + service key. One consumer at a time, verify, then next.medium — per-service
5. Remove keyDrop SUPABASE_SERVICE_ROLE_KEY from consumer SOPS files once no consumer uses it. Keep it only where genuinely needed (e.g. admin/migrations).medium — irreversible per file

4. Consumers in scope

ConsumerSOPS fileLikely scope (confirm in Phase 1)
n8n (bms-4 + Cloud)secrets/n8n-bms4.env.sopsp24_queue, dispatch tables
et-operational-platformsecrets/et-operational-platform.env.sopsregistries, reg_records
brandpilotsecrets/brandpilot.env.sopsbrandpilot tables
whatsup / p24-wa-asecrets/whatsup.env.sopswa message/session tables
vps-h1 servicessecrets/vps-h1.env.sopsconfirm — may be removable

5. Risks & constraints

  • Connection method change (REST → direct Postgres) is the biggest per-service risk — connection pooling (pgBouncer/Supavisor), SSL, and prepared-statement mode must be validated per consumer.
  • PostgREST-dependent consumers may still need a JWT/anon path for RLS-scoped reads; direct Postgres is for service-to-service only.
  • All SOPS edits (Phases 4–5) are secret-manager work — out of scope for this dev-issue.
  • Migrations via mcp__claude_ai_Supabase__apply_migration; timestamp prefix date +%Y%m%d%H%M%S.

6. Out of scope for this doc

No role, grant, migration, or SOPS change is made by this issue. Deliverable is the phased plan + consumer inventory template.

Design-only deliverable. Related: docs/plans/plan-2427-rotate-secret-system.md, docs/policies/ credential-isolation, docs/supabase-operations.md.

Graph View