p24-infra Docs

plan-3835-cf-worker-supabase-ci-tokens

12 min read

Plan: Short-lived Supabase credentials for CI — worker vs. lighter alternatives

Issue: #3835 Type: Code-change-design Status: Draft — iteration 1 Last updated: 2026-07-11 Author: Claude Opus 4.7 (AI-Dev, worker session on vps-h1) Related: #3825 (SUPABASE_ACCESS_TOKEN expiry rotation)

0. Summary — recommendation

Do NOT build a new Cloudflare Worker. Retire SUPABASE_ACCESS_TOKEN from CI entirely by switching both call sites to credentials that are already rotated, already scoped tighter, and already sitting in GH Secrets. The static PAT gets one CI consumer at a time removed until gh secret list no longer shows SUPABASE_ACCESS_TOKEN, at which point the secret is deleted and the two workflows carry zero Supabase Management API PAT exposure.

  • compliance-audit-due-check.yml — replace the Management API /database/query call with a PostgREST GET /rest/v1/dev_r_compliance_audits call using SUPABASE_SERVICE_ROLE_KEY (already in GH Secrets, already rotated on the standard schedule). One SELECT, no DDL — no reason it needs a superuser PAT.
  • credential-rotation.yml — promote the existing psycopg2 direct-DB fallback path in rotate_supabase_grafana_password() (scripts/rotate-credentials.py:544-559) to primary; drop the Management API branch. The DDL statement (ALTER ROLE grafana_readonly WITH PASSWORD …) runs against the same postgres superuser with SUPABASE_DB_HOST + SUPABASE_DB_PASSWORD, which are already GH Secrets consumed by this workflow.

The proxy-worker option is documented in §5 as a fallback if either migration proves unworkable (e.g. GH runners hit persistent SSL/SNI issues talking to the Supabase pooler), but the investigation below finds no reason to expect that.

1. Why the “CF Worker minting short-lived Supabase tokens” framing does not fit

The issue phrases the ask as “fetch a short-lived token from our Cloudflare worker at call time.” Two facts make that specific design unavailable:

  1. Supabase PATs cannot be exchanged for short-lived credentials. The Management API (api.supabase.com/v1/*) accepts a PAT (sbp_…) as a Bearer token. There is no /oauth/token exchange endpoint, no OIDC federation, and no scoped short-lived token endpoint. PATs are created browser-only (app.supabase.com/account/tokens), do not expire, and are not revocable via API — only via the browser. Confirmed against Supabase’s public Management API docs and the existing rotation playbook (docs/playbooks/supabase-access-token-rotation.md — labelled Tier 3 manual-only for exactly this reason).

  2. Therefore a CF Worker in this role can only be a proxy, not a token minter. The worker would hold the long-lived PAT itself as a CF secret and expose an authenticated endpoint that the CI callers hit; the worker forwards to api.supabase.com server-side and returns only the result. The “short-lived credential” would be a worker-issued auth token (e.g. HMAC, or the P24_AUTOMATION_KEY pattern from p24-auth-worker), not a Supabase token. This does reduce blast radius (PAT never touches CI logs/env), but is a proxy pattern — call it that plainly.

Both facts are load-bearing for the alternatives below, because they shift the question from “how do we mint short-lived tokens” to “how do we keep the PAT out of CI at all”.

2. Current state — what the two consumers actually do

Grepped SUPABASE_ACCESS_TOKEN across the whole repo. Total consumer surface:

FileLine(s)OperationReal requirement
.github/workflows/credential-rotation.yml107Sets env var for the rotatorSee below
scripts/rotate-credentials.py533-541POST /v1/projects/{ref}/database/query — ALTER ROLE grafana_readonly WITH PASSWORD '…'Postgres superuser DDL
scripts/rotate-credentials.py544-559Existing fallback: direct psycopg2 using SUPABASE_DB_HOST + SUPABASE_DB_PASSWORDSame DDL, same superuser
.github/workflows/compliance-audit-due-check.yml20Sets env var for the checkerSee below
scripts/compliance-audit-due-check.py38, 56-71POST /v1/projects/{ref}/database/query — SELECT … FROM dev_r_compliance_audits …Read one public table

Two calls total across the entire repo. Both hit POST /v1/projects/{ref}/database/query. That single Management API endpoint runs arbitrary SQL against the Supabase project’s postgres database as a superuser — it is a thin remote wrapper over psql, not a scoped compliance API.

Neither call needs the arbitrary-SQL breadth that endpoint gives them:

  • The compliance check runs one SELECT against a single public table. PostgREST exposes exactly that (GET /rest/v1/dev_r_compliance_audits?select=…&order=…) using the same SUPABASE_SERVICE_ROLE_KEY we already use in every worker.
  • The credential rotator runs one ALTER ROLE DDL. That does require superuser — but the existing psycopg2 fallback already runs it as postgres with the current DB password. That fallback is only “fallback” today because Management API was chosen first (comment on L531-532: “avoids direct psycopg2 which can’t send SNI on some OpenSSL builds, causing ENOIDENTIFIER on the hstgr runner”). The comment is about the hstgr server-side rotator, not the GH Actions ubuntu-latest runner where this workflow runs — the SNI issue has never been reproduced on GH’s runner OpenSSL build.

The GH Secret itself was validated as human-action overdue rotation in #3825 and the administration.env.sops copy was found stale. So the current state is: static PAT in GH Secrets, rarely rotated, broader scope than the two consumers actually need, and one of the two SOPS copies is already known-stale.

3. Option A (recommended) — Retire SUPABASE_ACCESS_TOKEN from CI

Change each consumer to a credential that already meets the least-privilege requirement.

3.1 compliance-audit-due-check.py — PostgREST + SUPABASE_SERVICE_ROLE_KEY

Rewrite supabase_query() (scripts/compliance-audit-due-check.py:55-71) to hit PostgREST directly. The current SQL:

SELECT DISTINCT ON (scope) id, scope, audit_type, next_audit_due, result, ts
FROM dev_r_compliance_audits WHERE next_audit_due IS NOT NULL
ORDER BY scope, ts DESC

Becomes:

GET /rest/v1/dev_r_compliance_audits
    ?select=id,scope,audit_type,next_audit_due,result,ts
    &next_audit_due=not.is.null
    &order=scope.asc,ts.desc

DISTINCT ON (scope) needs to be reproduced client-side: after the fetch, keep the first row per scope (rows arrive already ordered by scope ASC, ts DESC, so the first per group is the freshest — same semantics as DISTINCT ON). This is a 4-line Python loop.

Env changes to the workflow:

# Remove:
SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
SUPABASE_PROJECT_REF: mwkqmgadqnkkihjdeqsi
 
# Add:
SUPABASE_URL: ${{ secrets.SUPABASE_URL }}
SUPABASE_SERVICE_KEY: ${{ secrets.SUPABASE_SERVICE_KEY }}

Both replacement secrets already exist in the repo (used by credential-rotation.yml L85-86).

RLS check required. dev_r_compliance_audits needs SELECT policy for service_role (which is the default — service_role bypasses RLS). Verify before shipping with a one-off GET /rest/v1/dev_r_compliance_audits?select=id&limit=1. If RLS blocks the read, add a migration granting service_role explicit SELECT.

3.2 rotate-credentials.py — psycopg2 as primary, drop Management API branch

The current code at scripts/rotate-credentials.py:533-559 is:

access_token = _env('SUPABASE_ACCESS_TOKEN')
if access_token:
    resp = requests.post(f'https://api.supabase.com/v1/projects/{project_ref}/database/query', ...)
    if resp.status_code not in (200, 201): return False
else:
    # Fallback: direct psycopg2 (requires SUPABASE_DB_HOST + SUPABASE_DB_PASSWORD)
    if not SUPABASE_DB_HOST or not SUPABASE_DB_PASSWORD: return False
    ...
    conn = psycopg2.connect(host=SUPABASE_DB_HOST, port=5432, ...)

Change to: try psycopg2 first; keep the Management API path only as an explicit fallback gated on PSYCOPG2_FALLBACK_TO_PAT=true env var (default false). Or simpler — delete the Management API branch entirely and rely on psycopg2. The workflow already exports both SUPABASE_DB_HOST and SUPABASE_DB_PASSWORD (.github/workflows/credential-rotation.yml L87-88), so the direct path is fully configured today.

Bootstrap concern — chicken-and-egg? SUPABASE_DB_PASSWORD rotates in the same script. It does not: rotate_supabase_grafana_password() rotates the grafana_readonly role, not postgres. postgres’s password is a separate credential (currently manual per docs/playbooks/mongodb-credential-rotation.md-adjacent Supabase docs) and does not rotate in this weekly job. So there is no self-referential loop.

Env changes to the workflow:

# Remove:
SUPABASE_ACCESS_TOKEN: ${{ secrets.SUPABASE_ACCESS_TOKEN }}
# Keep the existing SUPABASE_DB_HOST + SUPABASE_DB_PASSWORD as-is.

3.3 Retire the GH Secret

Once both PRs merge and one green run of each workflow lands with the new path:

gh secret delete SUPABASE_ACCESS_TOKEN --repo radieu/p24-infra

Also update secrets/administration.env.sops per the direction in #3825 — either rotate to a fresh PAT for local developer use, or remove the key entirely and repoint developer scripts to secrets/role-secret-manager.env.sops::ROLE_SECRET_MANAGER_SUPABASE_ACCESS_TOKEN (that decision is out of scope of this issue).

3.4 Cost / risk of Option A

DimensionImpact
New infranone
New secretsnone — reuses SUPABASE_URL / SUPABASE_SERVICE_KEY / SUPABASE_DB_PASSWORD
Blast radius reductionPAT deleted from GH Secrets — full removal, not just proxied
Ongoing rotation burdenone fewer credential to rotate (Tier 3 manual → gone)
Rollbackrevert 2 PRs; PAT is deletable but not un-createable, so re-create manually if needed
RiskRLS on dev_r_compliance_audits blocks service_role SELECT (mitigation §3.1 — check first)
Riskpsycopg2 SSL/SNI issue on GH ubuntu-latest — no evidence it exists on this runner, but keep one revert-ready fallback commit that restores the Management API branch

4. Option B — Scoped-secret gate (no code change, no worker)

If §3 is rejected (e.g. someone wants to preserve the arbitrary-SQL capability of the Management API for future ad-hoc CI needs), the lightest possible mitigation is:

  1. Move SUPABASE_ACCESS_TOKEN from repo-level GH Secrets to a GH Environment (e.g. named supabase-management).
  2. Configure the environment with required_reviewers: [radieu] — every workflow run that consumes it requires manual approval in the Actions UI.
  3. Reference the env in both workflows: 
    jobs:
  rotate:
    environment: supabase-management
    ...

Trade-off: eliminates unattended CI blast-radius (a compromised repo write cannot exfiltrate the PAT without a human clicking Approve), at the cost of every scheduled run requiring a manual click. For a weekly rotation job that is already partially manual, this is workable. For a daily compliance-due check, it becomes annoying quickly.

Not recommended if §3 is achievable, but worth noting as the minimum-change option.

5. Option C — Cloudflare Worker proxy (fallback)

Only build this if §3 is unworkable. Sketch of what it would look like — deliberately kept minimal because we should not build it.

5.1 Shape

Directory: infra-src/supabase-mgmt-proxy/ (mirrors p24-auth-worker/ layout).

Endpoints:

MethodPathAuthPurpose
GET/healthnoneDeploy smoke test
POST/db/queryBearer SUPABASE_MGMT_PROXY_KEYBody: {sql: string, params?: any[]} → proxies to POST api.supabase.com/v1/projects/{ref}/database/query, returns 502 on upstream failure.

CF secrets:

  • SUPABASE_ACCESS_TOKEN — the real PAT, held only on Cloudflare
  • SUPABASE_PROJECT_REF — mwkqmgadqnkkihjdeqsi
  • SUPABASE_MGMT_PROXY_KEY — pre-shared, matches the p24-auth-worker P24_AUTOMATION_KEY pattern
  • ALLOWED_STATEMENT_PREFIXES (optional) — comma-separated allowlist, e.g. SELECT ,ALTER ROLE grafana_readonly

Deploy workflow: deploy-supabase-mgmt-proxy.yml, mirrors deploy-p24-auth-worker.yml (source secrets from secrets/monitoring.env.sops — the same SOPS file that used to hold this PAT before the 2026-07-05 migration to role-secret-manager.env.sops).

5.2 CI migration

Both consumers switch from POST api.supabase.com/v1/projects/{ref}/database/query to POST https://supabase-mgmt.radieu.workers.dev/db/query with Bearer SUPABASE_MGMT_PROXY_KEY (a fresh short-scope worker-issued secret in GH Secrets). Same JSON body shape.

The GH Secret SUPABASE_ACCESS_TOKEN is deleted (fully removed from CI). Blast-radius reduction: a compromised GH runner env leaks only the proxy key, which is scoped to the allowed-statement prefix list, not the full Management API.

5.3 Why NOT build this (recap)

  • Two consumers, both trivially reachable without the Management API.
  • Two long-lived credentials to rotate (SUPABASE_ACCESS_TOKEN and SUPABASE_MGMT_PROXY_KEY) instead of the one we’re trying to remove.
  • Two new deploy paths (deploy-supabase-mgmt-proxy.yml, worker rebuilds).
  • Add a dev_r_services row + ops doc per CLAUDE.md’s new-service compliance rules — real work.
  • The p24-auth-worker precedent solves a different problem: n8n workflows call it thousands of times/day; adding a proxy layer amortises. CI runs it 8 times/week (7 daily compliance + 1 weekly rotation). No amortisation.

6. Files to change (Option A — recommended)

Actual code changes are follow-ups; this plan issue produces the design only. Filed here so the implementer knows the target set.

FileWhat changesWhy
scripts/compliance-audit-due-check.pyRewrite supabase_query() to hit PostgREST; client-side DISTINCT ON emulation; drop SB_TOKEN; add SUPABASE_URL + SUPABASE_SERVICE_KEY env reads§3.1
.github/workflows/compliance-audit-due-check.ymlSwap SUPABASE_ACCESS_TOKEN → SUPABASE_URL + SUPABASE_SERVICE_KEY env vars§3.1
scripts/rotate-credentials.pyIn rotate_supabase_grafana_password(): reorder branches so psycopg2 is tried first; delete the Management API branch (or keep behind an env-gated flag for one release cycle)§3.2
.github/workflows/credential-rotation.ymlRemove the SUPABASE_ACCESS_TOKEN env line§3.2
gh secret delete SUPABASE_ACCESS_TOKEN (operational, not a file change)Deferred until both PRs above merge and one green run of each workflow completes§3.3
docs/playbooks/credential-automation-registry.mdRemove the “Supabase Management API” row referencing SUPABASE_ACCESS_TOKEN as the admin credential once the workflows no longer use it; keep the row for the token if administration.env.sops retains a developer-local copy§3.3

files_to_change: scripts/compliance-audit-due-check.py, .github/workflows/compliance-audit-due-check.yml, scripts/rotate-credentials.py, .github/workflows/credential-rotation.yml, docs/playbooks/credential-automation-registry.md

File-overlap signature: scripts/compliance-audit-due-check.py, scripts/rotate-credentials.py, .github/workflows/compliance-audit-due-check.yml, .github/workflows/credential-rotation.yml, docs/playbooks/credential-automation-registry.md

7. Regression risks

  • dev_r_compliance_audits RLS blocking service_role SELECT — Mitigation: pre-check with a one-off REST call before merging §3.1; if blocked, ship an RLS grant migration in the same PR.
  • Client-side DISTINCT ON semantics drift — Mitigation: emit both the SQL and REST result sets in a one-off dry run, diff them, verify identical row set before deleting the SQL path.
  • psycopg2 SSL/SNI on GH ubuntu-latest — Mitigation: run the weekly rotation once against the new path via workflow_dispatch with dry_run=true before deleting the Management API branch. If ENOIDENTIFIER surfaces, escalate to a “keep both, prefer psycopg2” pattern rather than deleting the Management API path outright.
  • SUPABASE_DB_PASSWORD freshness — Same secret both workflow versions consume; if the password stored in GH Secrets has drifted from Supabase’s current value, psycopg2 will 401. Detectable in the same dry run.
  • Losing arbitrary-SQL capability from CI — This is the intended trade-off, not a regression. If a future need for arbitrary SQL from CI arises, re-open the question then, not now — YAGNI applies.

8. Manual test checklist (for the implementation PRs)

  • curl -sf -H "apikey: $SVC" -H "Authorization: Bearer $SVC" 'https://mwkqmgadqnkkihjdeqsi.supabase.co/rest/v1/dev_r_compliance_audits?select=id,scope,audit_type,next_audit_due,ts&order=scope.asc,ts.desc&limit=5' — non-empty JSON array (RLS check)
  • python3 scripts/compliance-audit-due-check.py locally with SUPABASE_URL + SUPABASE_SERVICE_KEY set — produces same “due” list as one prior successful run (compare to last workflow run’s logs)
  • gh workflow run compliance-audit-due-check.yml --repo radieu/p24-infra after PR merge — green run, same behaviour
  • gh workflow run credential-rotation.yml --repo radieu/p24-infra -f dry_run=true after PR merge — reaches rotate_supabase_grafana_password and prints [DRY RUN] SUPABASE_GRAFANA_PASSWORD without HTTP calls to api.supabase.com
  • Real run (Monday 06:00 UTC cron) — grafana_readonly password actually rotates via psycopg2 path; verify by looking at logs for the ALTER ROLE execution and confirming the dev_r_services.last_rotated row updates
  • gh secret list --repo radieu/p24-infra | grep -v SUPABASE_ACCESS_TOKEN after retirement — token no longer listed

9. Out of scope

  • Rotating or deleting the administration.env.sops copy of SUPABASE_ACCESS_TOKEN — that is #3825’s job. This issue only removes the CI consumers.
  • Migrating any other Supabase-consuming CI paths that don’t currently exist. If new CI code wants to hit the Management API in the future, revisit §5 (the proxy worker) then, not now.
  • The p24-auth-worker architecture itself — referenced only as the design pattern for §5.
  • The role-secret-manager.env.sops copy of the same PAT — that consumer is the secret-manager role’s own script runs, not CI, and is scoped tighter already.

10. Decision points for reviewer

  1. Accept the framing shift (CF Worker → retire the credential entirely)? If the “we want a worker” instinct is real (e.g. blast-radius theatre for auditors), Option C is documented and builds cleanly; but this plan recommends against it because the two consumers don’t need arbitrary SQL.
  2. Delete or gate the Management API branch in rotate-credentials.py? Deleting is cleaner; gating (behind PSYCOPG2_FALLBACK_TO_PAT=true) keeps one release cycle of safety net at the cost of leaving the PAT-shaped code alive.
  3. Retire the GH Secret timing: delete immediately after both PRs merge and pass one run, or wait one full weekly rotation cycle to confirm? Recommended: wait one cycle.

Auto-generated by worker agent (Claude Opus 4.7, session 5f8d1262) on 2026-07-11. Review, iterate up to 3 cycles per CLAUDE.md’s plan workflow, then follow the plan iteration workflow to convert accepted decisions into implementation issues.

Graph View