Security · §05

Security posture — red-team review

Branchfeat/v2 Commit38549fa AudienceJonny, Liam, eng

Security posture — red-team review

Pre-launch threat assessment ahead of the 5,000-founder issuance in Sierra Leone, December 2026. Independent red-team pass against the same commit. Conclusions overlap with — and sharpen — the engineering critique above.

Red team verdict · composite 1.4 / 5

Do not ship the founder cohort on this codebase

At least four trivially-exploitable critical paths exist before you reach the VC layer. The auth model is a 6-digit PIN behind an anon-key-shipped Postgres surface with handle → email enumeration and a self-bumpable identity lock. The launch event becomes a guaranteed loss-of-meaning incident the first time a competitor or journalist looks.

Posture score by domain

Domain	Score	Justification
Authentication	1 / 5	6-digit PIN, handle → email oracle, mailer auto-confirm, no MFA path, no per-account rate limit
Data protection	1 / 5	Open RLS on `pending_profiles`, anon SELECT on `public_profiles`, anon key shipped client-side, stale email cache in `profiles`
Infrastructure	2 / 5	Cloudflare front is fine; wrangler placeholders unset, no DNSSEC, no HSM for VC signing keys, no key custody plan
Supply chain	2 / 5	Pinned versions in pnpm-lock, but no Dependabot / Snyk / `pnpm audit` / SBOM; GitHub Packages publishing unimplemented
Operations	1 / 5	No tests, no PR gate, no observability, no Sentry, no audit log, no incident runbook

Top findings (12 of 20)

ID	Severity	Finding	Blast radius
F-01	Critical	Identity-lock bypass — `enforce_identity_lock` trigger lets the row through whenever `identity_lock_version > OLD.identity_lock_version`, and the UPDATE RLS policy lets the user write that field on their own row. The lock is theatre.	Permanence guarantee gone; founder handle/locale/country rewrite at will; any VC issued against a mutable PK is worthless
F-02	Critical	`pending_profiles` RLS `USING (true)` for anon SELECT + INSERT + UPDATE — every visitor can read and overwrite every in-flight registration	Founder cohort poisoning, identity hijack pre-lock, real-time funnel surveillance
F-03	Critical	`lookup_signin_email_by_handle` granted to `anon` — handle → recovery-email oracle, free phishing list generator	ATO at scale; phishing-list generation for entire user base; pairs with F-04 to remove every barrier to credential takeover
F-04	Critical	6-digit PIN as Supabase Auth password, anon-key client-side auth, IP-rotation-defeated rate limit (~100 RPS through residential pool)	ATO on any handle the attacker can enumerate — which is all of them
F-05	High	`mailer_autoconfirm = true` + anon-issued `<uuid>@anon.sporthead.id` synthetic emails	Pre-confirmed accounts at scale; founder/og number races; no email-validity check on real signups either
F-06	High	Founder-number assignment race — no advisory lock or SERIALIZABLE around `founder_number`	Whichever attacker wins the race takes F-0001; partial unique-index errors leak counter state
F-07	High	`handle_new_user` trusts client-supplied `raw_user_meta_data.pending_id` — forged signup can drain any pending row	Steal another user's draft identity at lock-in time
F-08	High	Anon key shipped in `NEXT_PUBLIC_*` static bundle — the anon role is the public internet's Postgres role	Every `GRANT TO anon` is a public grant. Audit every migration as if it were a SQL-injectable endpoint
F-09	High	`did:web:sporthead.id` + DNS still on GoDaddy + no DNSSEC	DNS hijack = total VC compromise; every issued credential becomes impersonatable
F-10	High	VC signing key custody undefined ("Cloudflare joint Jonny+Liam" — two humans with the same root token, no hardware quorum)	One phishing email at launch week and the issuer is gone forever
F-11	High	Single `IP_SALT` pepper on reserve worker → leak the salt and rainbow-table the IPv4 set	Re-identification of every reservation IP; trivially links emails to home networks
F-12	High	Wrangler `database_id` and `kv_namespace_id` placeholders `<TODO>`	First deploy either fails open or binds to the wrong resource

Eight more (F-13 to F-20) cover handle-vocab burn DoS via generate_sporthead_handle, public_profiles joinable to a re-identifying dataset, profiles.email cache divergence, XSS via trait filenames, CORS allowlist + subdomain takeover on sporthead-com.pages.dev, no PR gate, predictable Math.random() share tokens, and missing SBOM / Dependabot / pnpm audit. See research/2026-05-16_shotid_full-audit.md for the full list.

Walked kill chains

Chain 1 · trivial · minutes to compromise

Founder cohort poisoning at the Sierra Leone fight night

Extract anon key from the static bundle. Run SELECT * FROM pending_profiles ORDER BY created_at DESC continuously through PostgREST. When a popular registrant lands on the handle step, UPDATE pending_profiles SET sporthead_handle = '<your-handle>' WHERE pending_id = '<theirs>'. Open update RLS, no auth.uid() check. They press confirm. handle_new_user drains your handle text into their profile. Lock fires. Permanent.

Alternative path: forge a pending_id in your own signUp's raw_user_meta_data pointing at someone else's pending row. The trigger drains their state onto your auth.users row. You now own their handle, geo, avatar.

Or: mass-register F-0001..F-0500 yourself. No Turnstile on the journey entry — the only Turnstile is on the reservation worker, not the registration flow. Founder-number race means whichever client wins gets the prestige number.

Detection probability: zero. No audit log on pending_profiles UPDATE, no alert on burst founder assignments, no observability anywhere.

Chain 2 · trivial · ~5 min to harvest 5,000 emails

Handle harvest → phishing campaign at scale

Vocabulary tables are anon-readable. Cartesian product of sporthead_prefixes × sporthead_suffixes × 10..99 gives a wordlist of plausible handles. For each candidate, call lookup_signin_email_by_handle. Non-null return = registered + locked + here's their real recovery email.

Even the synthetic-email branch is a feature: when the email is <uuid>@anon.sporthead.id, the handle is now confirmed live → watchlist target. Use as scope for credential stuffing.

Send a "SHOT support: identity-lock recovery required, set a new PIN" phishing mail from a lookalike domain. Capture submitted PIN. Replay against signInWithPassword. With the email leaked you don't even need to brute-force PIN — but if you do: 10⁶ space, ~100 RPS through 200 rotating residential proxies, expected hit within 2,000 attempts. ATO in under a minute per target.

Chain 3 · medium today · critical the moment any verifier renders avatars

Avatar CDN manipulation at verification time

The Supabase Storage traits bucket is anon-readable. Trait storage_path values are in trait_definitions, anon-SELECTable. Avatar URLs are predictable, no signature.

When (not if) a verifier — federation portal, partner app — renders an avatar alongside a presented VC, they fetch a path the issuer doesn't authenticate. Compromise the storage bucket once (anon write needs an RLS bug; the more likely path is a leaked service-role key from the deploy workflow) and substitute the helmet trait file. Every downstream verifier renders the substituted image.

Combined with did:web:sporthead.id over GoDaddy without DNSSEC — an attacker who hijacks the apex domain controls both the JWKS and the avatar layer. Signature verifies. Total identity substitution.

Chain 4 · high feasibility once federation-router ships

Federation tenant lateral movement

Compromise one federation admin account (no MFA in scope today, phishing trivial). Federation-router resolves Host → theme via KV; federations.vanity_domain is unique. Race condition: register id.sporthead.id as a federation vanity in the millisecond before the platform claims it. Federation content now served on the canonical identity surface.

public_profiles is anon-readable. Federation-admin (when built) sees every Sport Head across every federation. Default scope creep means a compromised SLFA operator sees WAB members. Cross-tenant breach by intended design.

KV cache poisoning: if the federation admin can edit theme_key and the value flows into a script-src or asset URL, a malicious theme bundle exfils session storage from every visitor to that vanity domain.

Compliance and regulatory call-outs

UK Children's Code (Age Appropriate Design Code): no age gate, no DoB collection. If a single 12-year-old in Manchester registers, you're in breach. ICO has fined for this.
GDPR right to erasure: identity_locked_at + enforce_identity_lock are designed to make profile fields immutable. Article 17 erasure requests override this. Build a soft_erase_profile SECURITY DEFINER path that tombstones the row.
COPPA (US under-13): Sierra Leone registrants may include US persons via diaspora. Any under-13 data without verifiable parental consent → FTC jurisdiction asserts.
Geo PII linkage: UK postcode + handle + email in one profiles row is a re-identifying dataset. Treat profiles as a high-sensitivity store: encrypt geo_unit_raw_input at the column level with a Cloudflare-held KEK; log every SELECT.
W3C VC permanence vs erasure: once a VC is issued and held, you cannot recall the holder's copy. Document the policy: revocation-list-only (VC stays in wallets, reads as revoked) is the only honest answer.

What to fix this week vs next quarter

This week · non-negotiable

Five fixes before any external demo

Patch enforce_identity_lock — remove the identity_lock_version > OLD bypass; gate on auth.role() = 'service_role'.
Lock pending_profiles RLS. Drop the three USING (true) policies. Move writes behind a SECURITY DEFINER RPC that verifies an HMAC-signed pending token (not the client UUID).
Revoke EXECUTE on lookup_signin_email_by_handle from anon. Sign-in moves to a Cloudflare Worker that returns either a session or a generic failure — never the email.
Set the two <TODO> wrangler resource IDs. Verify secrets are actually set with wrangler secret list.
Disable mailer_autoconfirm. Require email confirmation OR a Turnstile-gated signUp path.

Next quarter · before December

Six harder fixes before the launch event

Replace 6-digit PIN with 8 digits + memorable passphrase, or PIN + WebAuthn device. Add per-account lockout.
Move VC signing keys to HSM or Cloudflare Keyless with hardware quorum custody. DNSSEC on sporthead.id. Migrate registrar off GoDaddy.
Observability: Sentry on frontend, structured logs on workers, Supabase audit log shipped to a warehouse, alerts on signup rate / founder_number gap / RLS denial rate.
CI: PR-required tests, pnpm audit in CI, Dependabot, branch protection on main with required reviews and signed commits.
Threat-model the OIDC issuer before writing it. RFC 9700 / OAuth 2.1 BCP. Don't invent your own protocol — or buy Hydra and skip the threat model.
External pentest against a staging mirror of prod. No exceptions.

Architectural takeaway: the codebase is honest about half of these findings in migration comments. The team named the risk in migration 07 and migration 09 and shipped anyway. The fix is not more comments. Pull every GRANT ... TO anon behind a Cloudflare Worker that authenticates the caller (Turnstile + per-IP token) before reaching Postgres. That single architectural change neutralises seven of the twenty findings above. Do it before the next merge to main.