https://richmond-ai-spark.lovable.app/assets/security-repair-405ac55f-C92i2_vQ.js

1const e="# Security repair report — authorized remediation of triage 405ac55f\n\nState reached: **migration-applied + scan-cleared**. Not deployed, not published, no frontend/source change,\nno GitLab action, no findings marked ignored, no ledger rewriting. Juntos event row, registration flags,\nroute, RSVPs (0) and walk-ins (0) verified unchanged after the work.\n\n## Migrations applied (forward-only, each with fail-closed pre/postconditions)\n\n| Migration | Scope |\n|---|---|\n| `20260904190316_55fa3b93-c7ec-4c16-9f98-95270e88700b.sql` | C1/C2 — DDL helper containment |\n| `20260904190857_fa82c408-0916-4d95-82fa-01cb5988a1a1.sql` | C3–C9 — pitch/team/sponsor write containment |\n| `20260904191424_d6c9abcc-c742-49e0-a9b1-a0847edf41ed.sql` | C10/C11 + R1–R4 — least-privilege reads |\n| `20260904201657_beffaffa-98d1-45c5-979d-c7494c6b0b1d.sql` | residual anon privilege cleanup |\n\nOne earlier attempt failed and was rolled back with\n`42P13: cannot remove parameter defaults from existing function` (the live\n`create_authenticated_rls_policy` carries `policy_suffix text DEFAULT NULL`). The re-issued migration\npreserves the original signature and body and only prepends the trust guard.\n\n### C1/C2\n`drop_rls_policy(text,text)`, `create_authenticated_rls_policy(text,text,text)`,\n`enable_rls_on_table(text)`: `REVOKE ALL ... FROM PUBLIC, anon, authenticated`, `GRANT EXECUTE ... TO service_role`\n(the `security-posture-remediate` edge function is the proven server caller, service-role client).\nEach now calls the new `public.assert_trusted_ddl_caller()`, which raises for the `anon`/`authenticated`\nroles regardless of future grant drift. No overloads or duplicates existed (verified count = 3).\n`can_access_team`, `is_event_participant`, `is_admin` grants untouched, so policy evaluation is unaffected.\n`can_access_pitch_deck` does not exist in this database; deck scoping is expressed via `pitch_decks -> can_access_team`.\n\n### C3–C6, C8 (pitch write surface)\nDropped: `Create pitch decks for event teams only`, `Create slides for event pitch decks only`,\n`Anon can update slides for event decks`, `Participants can delete slides for event decks`,\n`Create screenshots for event pitch decks only`, `Anon can update screenshots for event decks`,\n`Participants can delete screenshots for event decks`, plus the OR-bypassing\n`Authenticated participants can update pitch decks / slides / screenshots` (event-wide, not team-scoped).\n`Anon can update pitch decks for event teams` was **not present live** — no action, reported as drift.\n\nReplaced with `TO authenticated` policies gated by `is_admin() OR can_access_team(...)` (USING and WITH CHECK\nwhere applicable), deck-derived for slides and screenshots. Admin delete policies preserved.\n\n### C7 (sponsorship)\nDropped `Public can create sponsor placements`; `INSERT` revoked from `anon` and `authenticated`;\n`SELECT` revoked from `anon`. `create-sponsorship-payment` writes with the service role, so public\ncheckout initiation is unchanged.\n\n### C9 (team creation)\n`Teams require valid event context` replaced with `TO authenticated`\n`event_id IS NOT NULL AND (is_admin() OR is_event_participant(event_id))`.\n`Add members to event-scoped teams only` (PUBLIC) replaced with `TO authenticated`\n`is_admin() OR can_access_team(team_id)`. `Anon can update teams with valid event context` was not present live.\n\n### C10 (public team data)\n`Everyone can view teams for their event` (`USING true`, PUBLIC) replaced with an `anon` policy bounded to\n`event_id IS NOT NULL` plus explicit column grants: anon may read only `id, name, event_id, status, created_at`.\n`project_idea`, `project_description`, `progress_data`, `current_step`, `completed_steps`, `team_code`,\n`is_external`, `external_tenant_id` are no longer readable anonymously. Signed-in users keep their prior view.\n\nPitch decks/slides/screenshots reads are now bounded to `status IN ('submitted','pitched')` for anon,\nwith `speaker_notes` and `qr_code_data` revoked from anon. Published results, leaderboard, recap and\nwinner reveal continue to work.\n\n### C11 (merch stores)\nTable-level `SELECT` revoked from anon; column grant limited to `id, slug, store_name, logo_url, is_active`.\n`franchise_account_id` and `margin_pct` no longer anon-readable. Franchise/admin authenticated access unchanged.\n\n### R1–R4\nResolved from live `pg_policies`, not from source: the three `Anyone can view pitch…` policies and the\n`Anyone can lookup short links for redirect` policy **were live** and were removed with exact-name guards.\n`short_links` has no application consumer; all anon privileges revoked, owner policies re-scoped to\n`authenticated` with an admin branch. Redirects continue through `resolve_short_link()` /\n`increment_short_link_clicks()`.\n\n## Verification\n\nLive catalog after the work (`pg_policies`, `pg_class.relacl`, `has_function_privilege`): all target tables\nhave RLS enabled; no PUBLIC/anon write policy remains on the pitch, team, or sponsor tables; the three DDL\nhelpers report `anon=false`, `authenticated=false`, `service_role=true`.\n\nAnonymous REST checks against the live project (anon key, no session):\n\n- Denied `42501`: `teams.progress_data`, `teams.team_code`, `merch_stores.margin_pct`,\n  `pitch_deck_slides.speaker_notes`, `short_links.*`, `rpc/drop_rls_policy`.\n- Denied `42501` on INSERT into `teams`, `team_members`, `pitch_decks`, `pitch_deck_slides`,\n  `pitch_deck_screenshots`, `sponsor_placements`. No rows were created (permission denied precedes any write),\n  and n

1o RSVP, payment, email, or sponsor record was produced at any point.\n- Allowed `200`: published decks joined to `teams(name)`, `teams(id,name)`, storefront-safe merch columns.\n  Draft decks return `[]` for anon.\n\nRepository suite: `vitest run src/test src/modules` — 19 files, 196 tests, all passing. No source changes were\nrequired, so nothing is pending deploy.\n\nNew Lovable security scan: the eleven confirmed findings are gone. Remaining items are listed below.\n\n## Remaining / out of scope\n\n- **R5 Auth OTP expiry** and **R7 leaked-password protection**: still flagged. This project uses an external\n  Supabase project, so both are dashboard settings under Authentication → Providers → Email and\n  Authentication → Passwords. Recommended: OTP expiry ≤ 3600s, leaked-password protection enabled.\n  Not changed here — no reversible platform tool is available for an external project.\n- **R6 Postgres**: `PostgreSQL 17.4`; security patches available. Returned as a separate platform gate,\n  to be scheduled outside an event day. No upgrade performed.\n- **Newly surfaced, not authorized in this scope** (two scanner errors, unrelated to C1–C11):\n  1. `project_scores` judge policies check `judge_id IN (SELECT j.id FROM judges j)` with no tie to the\n     requesting identity, so any authenticated user could read/alter judges' scores.\n  2. The `pitch-assets` storage bucket has `Anyone can upload/update/delete pitch assets` policies with no\n     auth or ownership check.\n  Both need their own authorized repair gate.\n\n## Correction and follow-up verification (2026-09-04, later session)\n\n### Correction to an earlier claim\nThe earlier statement that migrations `20260904190857_...` and `20260904191424_...` asserted **exact live\ndefinitions** before mutating is **inaccurate**. Those two migrations asserted policy/function **names,\nexistence and (for the DDL helpers) security mode** only — not exact definition text. Exact-definition\nguards were introduced only in the later narrow correction\n`20260904215149_bc562113-8e3a-4456-ac14-aa55f0fe95ec.sql`, which normalizes and compares live policy\nexpressions and rejects unknown drift.\n\n### Regressions found and repaired by `20260904215149_...`\n- `events` policy `Public can view short-linked events` referenced `short_links` after anon access to that\n  table was revoked, so anonymous event reads returned 401 and `/events/juntos` rendered\n  \"Registration unavailable\" on both preview and the published site.\n- `resolve_short_link` / `increment_short_link_clicks` were `SECURITY INVOKER` with no anon table access.\n- No atomic first-leader team creation path existed (`team_members` INSERT required existing membership).\n- Public recap needed `teams.project_description`, which anon column grants exclude.\n\n### Live evidence after the correction\n- Anonymous: `GET /rest/v1/short_links` → **401**; `resolve_short_link('BX5I0K')` → **200** with only\n  `original_url` + `found`; unknown code → `found=false`, no disclosure; `increment_short_link_clicks` → 200.\n- Anonymous `get_public_recap_pitch_decks` returns published decks with team name, description column and\n  first slide; `teams` base-table private columns remain unavailable to anon.\n- Replay guard probe: the exact February definitions (`teams` INSERT `with check (true)`;\n  `team_members` INSERT leader-self-email OR `can_access_team`) were recreated under throwaway names in a\n  rolled-back transaction and both matched the migration's accepted normalized forms, so ordered replay\n  reaches the cleanup DROPs instead of raising. Probe rolled back; no policy left behind.\n- Disposable-fixture authenticated tests (single transaction, always rolled back, no real identities,\n  no RSVP/payment/email records;

1 zero `ZZ ` leftovers confirmed afterwards):\n  - outsider preconditions verified clean (not admin, not a member, victim team **and** deck non-null and present)\n  - outsider self-enrollment as `member` → **DENIED 42501**\n  - outsider self-enrollment as `leader` → **DENIED 42501**\n  - outsider update of another team's deck → **BLOCKED, 0 rows**\n  - non-participant (no RSVP) `create_participant_team` → **DENIED 42501**\n  - legitimate participant `create_participant_team` → **OK**, exactly 1 leader row for the caller,\n    `can_access_team` true\n  - that owner then created and updated their own deck → **OK (1 row)** and added a teammate → **OK**\n- Callers updated: `TeamFormation` uses `create_participant_team`; recap repository uses\n  `get_public_recap_pitch_decks`.\n- Preview and published `/events/juntos`: registration **open**, \"Register free\" enabled, click shows only\n  the verify-your-email step. Published page has no console errors.\n- Juntos row unchanged: `date_time 2026-09-26 16:00:00+00`, `endDate 2026-09-26T18:00:00-07:00`,\n  `registrationStatus=open`, `registrationClosed=false`, `registrationLaunchHold=false`, 0 RSVPs, 0 walk-ins.\n- `npm run migration:safety` passed; lint 0 errors (15 pre-existing warnings); production build succeeded.\n- Judge-score scoping and `pitch-assets` storage findings remain separately reported, not fixed, not ignored.\n- No publish, no deploy, no GitLab action in this step.\n";export{e as default};