PS1 — Access Control & DB Hardening

Status: Archived Date: 2026-07-11 Blocks: B7-platform-plumbing.md (core), B8-auth-identity-backend.md (profiles RLS), B1-clubs-backend.md (guest_applications, GRANT convention), B6-venues-weather-backend.md (venue_corrections, ingest-venues), B5-attendance-trust-dues-backend.md (original anon-EXECUTE lead, narrative only — already remediated). Canonical companion: docs/architecture/wiring-plans-2026-07-backend.md §SECURITY/ACL (S1–S4) and §AUTH/IDENTITY (A1–A3). This doc does not repeat those plans' mechanics — it supplies the research grounding they were written against, resolves one design decision they left open (the profiles column-security pattern), extends the same root-cause pattern to two findings the wiring plan filed under other domains (venue_corrections, guest_applications), and reorders everything by live exploitability rather than dependency order. Already shipped — do not re-plan: 00386_hotfix_portone_rpc_lockdown.sql (PortOne RPC anon-exec), 00387_hotfix_venue_rpc_and_avatar_acl.sql (5 venue-pipeline RPCs anon-exec + avatar storage ownership). Verified still-live below.

---

1. Problem statement

Every finding in this doc traces to one structural fact: this schema has three independent authorization layers — Postgres GRANT, RLS policy, and SECDEF internal guard — and the project's own convention assumes GRANT is a formality RLS backstops. It is not. Where RLS has no predicate (profiles_select), where a GRANT is wider than the RLS it's supposed to sit behind (venue_corrections, guest_applications), or where a SECDEF function's only protection is an ACL that never actually landed (the venue-pipeline RPCs, now fixed), the "backstop" was the entire defense. Four independently-discovered instances of "the GRANT convention doc says do X, the migration author tried to do X, X silently didn't work" appear across B5/B6/B7/B8 — this is not four bugs, it's one mechanism, understood wrong by (at least) 96 migration authors across two years.

#	Finding	Source	Severity	Live status (verified 2026-07-11, HEAD 00387)
P1	profiles_select RLS is USING (true) — every cross-user field (phone, kakao_id, push_token, birth_year, 3 consent timestamps) is readable by any signed-in user through the app's own existing read paths (club roster, leaderboards)	B8 #1	CRITICAL	OPEN — reproduced live, see §2.5
P2	profiles_update allows self-writing trust_tier, singles_elo/doubles_elo, rating_confidence, rating_source, kakao_id — protect_profiles_computed only reverts 7 of 16+ server-authoritative columns	B8 #2	CRITICAL	OPEN — reproduced live, see §2.6
P3	venue_corrections GRANT (INSERT ... TO authenticated) is wider than its RLS WITH CHECK constrains (only submitted_by); a raw INSERT bypasses rate-limit, value bounds, reputation gate, and can abort a whole resolve_venue_facts batch (verified up to 400 venues/call)	B6 #2	CRITICAL	OPEN
P4	ingest-venues edge function's token check is fail-open (if (token && ...)) — a missing/misconfigured INGEST_TOKEN makes the entire venue-write surface, including a mass-delete, unauthenticated	B6 #1	CRITICAL (conditional)	OPEN
P5	guest_applications_admin_update has USING but no WITH CHECK — an admin can raw-PATCH status='approved' bypassing decide_guest_application's RSVP-creation side effect, leaving an applicant "approved" with no roster row	B1 #3	HIGH	OPEN
P6	Root cause: REVOKE ... FROM PUBLIC is a no-op against anon on this Supabase project (separate default-ACL row); 96 migrations use the broken idiom, 13 use the correct one. 35 of 139 public SECDEF functions are anon-executable today (all internally guarded or unreachable — no live exploit, but zero defense-in-depth)	B7 #1/#2	HIGH (structural)	OPEN — reverified live, count moved 40→35 post-00387
P7	app_config (holds plaintext service_role_key + cron_secret) has full default table grant to anon/authenticated; currently deny-all via RLS, one future policy away from a full credential leak	B7 #5	HIGH (latent)	OPEN
P8	82 of 88 public tables (93%) carry the unmodified Supabase default ACL — only 6 tables have an explicit narrowed GRANT, a 7% compliance rate against the project's own written rule	B7 #6, B1 #7, B8 #9	HIGH (structural)	OPEN
P9	can_view_profile_field trusts a caller-supplied p_viewer_id instead of resolving auth.uid() internally — any logged-in user can probe a relationship-graph boolean for two unrelated third parties	B8 #6	MEDIUM	OPEN
P10	check_rate_limit_for_key has no identity binding on p_key — a caller can trip another user's rate limit or defeat their own	B7 #9	MEDIUM	OPEN
P11	No CI/pgTAP regression guard exists for any of the above — every fix to date (00209, 00218, 00386, 00387) has been reactive and manual	B7 #10	HIGH (preventive gap)	OPEN

Deliberately excluded from PS1 (filed to other problem spaces, cross-referenced only): account-deletion completeness (B8 #3 → PS7/PS6, shares a migration with the club ghost-member fix), marketing-consent ledger remediation (B8 #4 → PS7, sequenced after the client toggle fix), record_pi_access/nationality_code (B8 #5/#7 → PS7, roadmap-deferred), venue manual-observation trust weighting and reopen path (B6 #3/#4 → PS9, downstream of but independent from the RLS fix here), the role_configs dormant-permission gap (B1 #4 → PS6, ARCH-9).

---

2. Research

2.1 The default-ACL mechanism, root-caused and reverified live

Postgres has two ways a non-owner role gets a privilege on a not-yet-created object: through the PUBLIC pseudo-role (which every role is implicitly a member of), or through an explicit per-role row in pg_default_acl. REVOKE ... FROM PUBLIC only ever touches the first. Supabase's project-bootstrap step (outside the migrations folder, run once at provisioning) issued the equivalent of:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO anon, authenticated, service_role, postgres;

This creates named-role default-ACL entries — confirmed live on this project's local DB:

$ docker exec supabase_db_twomore-v2 psql -U postgres -d postgres -c "SELECT n.nspname, a.defaclrole::regrole, a.defaclobjtype, a.defaclacl FROM pg_default_acl a LEFT JOIN pg_namespace n ON n.oid = a.defaclnamespace;"
 public | postgres | f | {postgres=X/postgres,anon=X/postgres,authenticated=X/postgres,service_role=X/postgres}
 public | postgres | f | {postgres=X/postgres,anon=X/postgres,authenticated=X/postgres,service_role=X/postgres}   <- 00209's REVOKE...FROM PUBLIC row (empty PUBLIC entry, not shown — see below)

Two rows exist for (public, postgres, f) post-00209: one is the original bootstrap row (anon=X still present), the other is 00209's own ALTER DEFAULT PRIVILEGES ... REVOKE EXECUTE ... FROM PUBLIC — which strips PUBLIC's row from the bucket but leaves anon's own row untouched, because it was never PUBLIC's grant to begin with. 00209 correctly diagnosed and fixed the existing-function half of the problem (a one-time DO $$ ... REVOKE ... FROM PUBLIC, anon $$ loop) but its future-proofing clause has the exact blind spot it was written to close. Every SECDEF function created (or signature-changed, which forces a drop-recreate and re-derives ACL from pg_default_acl) after 00209 re-inherits anon EXECUTE silently.

Compounding this, individual migrations independently tried to lock down their own functions with:

REVOKE ALL ON FUNCTION public.some_fn(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.some_fn(...) TO service_role;

This never worked, for the identical reason. Count (B7, reverified — grep is static and stable regardless of live DB state): 96 migrations use ... FROM PUBLIC alone; 13 use ... FROM PUBLIC, anon (or , anon, authenticated), the idiom that actually works. This is not a handful of forgotten call sites — every one of ingest_venues_batch's 4 defining migrations (00334, 00335, 00341, 00342) used the broken idiom with clear service-role-only intent, and none of them achieved it until 00387's hotfix used the correct one.

Canon-doc drift found during this research, not previously flagged: docs/canon/data-and-hooks.md:89 currently asserts "anon must never hold EXECUTE on a SECDEF function... verified + enforced in 00209, which revoked anon/PUBLIC from all 82 public SECDEF functions." This claim is false as written today — the live sweep below shows 35 SECDEF functions still anon-executable. The canon doc itself is propagating the exact misunderstanding that produced the 96-migration anti-pattern; correcting this line is part of Slice 6 below, not a separate doc-only task, because leaving it uncorrected guarantees the next author copies the wrong belief along with the wrong idiom.

Correct incantation, for every migration in this doc and every one after it:

REVOKE ALL ON FUNCTION public.some_fn(...) FROM PUBLIC, anon;
GRANT EXECUTE ON FUNCTION public.some_fn(...) TO service_role;  -- or authenticated, per the function's intended caller

And, to actually close the default-ACL bucket rather than patch functions one at a time forever:

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM anon;

The FOR ROLE postgres clause is not optional — pg_default_acl is keyed per creating role, and every migration runs as postgres (confirmed by the dump above: both pg_default_acl rows in public are keyed to defaclrole = postgres). 00209's original clause omitted FOR ROLE postgres implicitly (ran as whichever role applied it) and only ever touched the PUBLIC grant regardless.

2.2 Live re-enumeration of the anon-EXECUTE SECDEF surface (post-00387)

Ran B7's definitive query directly against a fresh local reset (HEAD 00387) rather than trusting the audit snapshot verbatim, per the multi-dimensional research mandate:

SELECT p.proname, pg_get_function_identity_arguments(p.oid)
FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) AS acl
JOIN pg_roles r ON r.oid = acl.grantee
WHERE p.prosecdef = true AND n.nspname = 'public'
  AND r.rolname = 'anon' AND acl.privilege_type = 'EXECUTE'
ORDER BY p.proname;

35 rows (down from B7's 40 — confirms 00387 closed exactly the 5 it claimed: ingest_venues_batch, enrich_venues_batch, apply_venue_coords, record_venue_observations, resolve_venue_facts are absent from the live result; mark_session_payment_paid_via_portone absent, confirming 00386 holds). Total public SECDEF count is 139, unchanged. Reclassified the current 35 (B7's original classification, minus the 5 now-fixed rows):

• 2a — unguarded, directly exploitable: none remaining. (Was 5; 00387 closed all of them. This is the one category that must stay empty — any future addition here is an immediate incident, not a backlog item — hence Slice 10's pgTAP guard.)
• 2b — internally guarded (auth.uid() IS NULL early-exit or equivalent), defense-in-depth violation only (27 functions): acknowledge_session_refund, add_thread_participants, agree_to_correction, attest_session_payment, block_user, cancel_session_as_host, create_group_thread, dev_mock_pay_session, get_blocked_users, get_club_admin_snapshot, get_club_growth_analytics, is_club_admin, is_club_member, join_club_by_invite_code, join_open_club, propose_score_correction, remove_thread_participant, rename_group_thread, report_content, report_venue_data, signals_acknowledge_visible_unread, signals_unread_visible_count, start_match, submit_match_score, submit_venue_correction, toggle_dm_reaction, unblock_user.
• 2c — trigger-only, unreachable regardless of grant (4 functions): signals_on_match_score_call_delete, signals_on_match_score_call_insert, signals_resolve_sudden_death_on_match_exit, signals_resolve_sudden_death_on_score_call_insert.
• 2d — intentionally public-shaped, no PII, correctly anon-reachable by design (3 functions): get_club_discovery_summaries, get_public_share_preview, is_public_discoverable_club. These should be allowlisted with a COMMENT ON FUNCTION, not revoked — converting them to SECURITY INVOKER + RLS is a larger, separate refactor with no live urgency (correctly-scoped discovery data), noted as a future option, not part of this doc's scope.
• 2e — utility, no identity binding, own abuse surface (1 function): check_rate_limit_for_key (P10, §2.3 below).

27 + 4 + 3 + 1 = 35, reconciles exactly.

2.3 profiles column-level security — three canonical patterns, and which one this schema already committed to

Postgres/Supabase offer three structurally different ways to split a "some columns public, some self-only" table:

1. Column-level GRANT/REVOKE (GRANT SELECT (col1, col2) ON t TO role). Native Postgres, zero app-code cost. Ruled out for this schema: PostgREST executes every request as one of exactly two Postgres roles (anon, authenticated) regardless of which app user is behind the JWT — the identity distinction (auth.uid()) is a session-local claim, not a role. A column-level grant to authenticated is either open to every authenticated caller or none; it cannot express "the row owner sees phone, everyone else sees NULL" because there is only one Postgres role for all of them. This pattern only works for columns that are either universally public or universally staff-only — not for a self-vs-other split, which is exactly this table's requirement.
2. A security_invoker/security_barrier view with computed masking columns — e.g. CREATE VIEW public.profiles_public WITH (security_invoker = true) AS SELECT id, display_name, ..., CASE WHEN can_view_profile_field(auth.uid(), id, 'region') THEN region ELSE NULL END AS region, NULL::text AS phone, ... FROM profiles. This is Supabase's own documented pattern for public/private profile splits (their RLS guide's "Views" section explicitly warns: a plain view runs with the creator's privileges and bypasses the underlying table's RLS unless security_invoker = true is set — a real footgun if this path were chosen carelessly). Real equivalent: a separate profiles/profiles_private table split, joined server-side only — the normalized form of the same idea, common in production Rails/Django/Postgres apps, but requires a data migration to move phone/kakao_id/push_token/consent timestamps to a second table and touches every write path that currently targets profiles directly.
3. SECURITY DEFINER RPC with explicit masking logic — read the full row inside the function (which runs with the definer's rights, bypassing RLS deliberately and safely because the masking is inside the function body, not delegated to RLS), and return a jsonb/composite with sensitive fields nulled per-viewer.

This schema already chose #3, correctly, once. get_public_profile_view(p_target_id) (live body read, \sf public.get_public_profile_view) does exactly this: resolves v_viewer := auth.uid() internally (not caller-supplied — see P9), calls can_view_profile_field() per gated field, and returns a jsonb_build_object() with phone/kakao_id/push_token hardcoded to NULL (never even conditionally exposed) and region/district/play_regions/gender/birth_year masked by the visibility check. Live-verified this RPC path correctly nulls all gated fields for an unrelated viewer (§2.5). The bug is not that the masking logic is wrong — it's that only one of seven cross-user read paths calls it.

Recommendation: extend pattern #3, don't introduce pattern #2. Reasons, not just preference:

• CLAUDE.md's single-source-of-truth rule — the masking logic already exists once, correctly; the fix is routing more callers through it, not building a second implementation.
• The project's own Protocol A ("every per-item query hook MUST have a bulk sibling") already establishes the RPC-fan-out shape this fix needs — get_public_profile_view is the per-item form; this doc adds the bulk siblings the convention already calls for.
• Verified via grep -rn "profiles(" packages/app/src/adapters/supabase/ (excluding profile.supabase.ts itself) across the whole adapter layer: zero PostgREST embedded-join call sites into profiles from any other adapter (session.supabase.ts, match.supabase.ts, etc. all resolve profile data through ProfileRepositoryPort, i.e. through profile.supabase.ts's own methods). This means the blast radius of narrowing profiles_select is fully enumerable and bounded to the 7 methods in profile.supabase.ts that issue a raw .from('profiles').select(...) for a non-self row — no hidden embed elsewhere in the codebase silently depends on the permissive policy. A view-based fix would not have needed this check (a view still supports embeds); the RPC-based fix does, and this grep is the proof it's safe.
• A view can't reuse can_view_profile_field's bulk semantics as cleanly as a bulk RPC signature can when the caller needs a set of target IDs scored against one viewer (leaderboards, club rosters) — the RPC form naturally takes uuid[] and returns a set; a view would need the same CASE/join logic repeated per column with no single choke point to unit-test.

2.4 The GRANT-convention sweep: what's actually safe to automate

The 82-of-88 gap (P8) is not equally risky per table — RLS is confirmed present with ≥1 policy on 86/88 tables (only app_config and signals_archive are deny-all-by-absence, both covered separately). This means the sweep is a hygiene closure of a defense-in-depth layer that is not today's actual gate for 84 of the 86 RLS-covered tables — the correct design is a generated migration (from information_schema.tables, not hand-typed per the wiring plan's own instruction) that is a structural no-op for every legitimate call path, with the generation step itself serving as the audit (any table where narrowing does change behavior surfaces a table the app was silently relying on anon-level access for, which is a bug to find, not a false positive to paper over).

2.5 Live reproduction of P1 (profiles_select)

-- as user B, unrelated to user A, no club/friendship, A's profile_visibility all 'private'
SELECT id, phone, kakao_id, push_token, birth_year, terms_accepted_at, privacy_accepted_at, marketing_consent_at
FROM public.profiles WHERE id = '<A>';
-- returns ALL values unmasked
SELECT get_public_profile_view('<A>');
-- correctly returns phone/kakao_id/push_token: null, gender/region/district: null (visibility-gated)

Confirms the RPC path is sound and the raw-select path is not — the exact shape the Slice 1 fix must reproduce for its own verification.

2.6 Live reproduction of P2 (profiles_update)

UPDATE public.profiles SET singles_elo = 3000, doubles_elo = 3000, rating_confidence = 100,
  trust_tier = 'veteran', singles_rd = 30, doubles_rd = 30 WHERE id = '<self>';
-- all persisted
UPDATE public.profiles SET elo_rating = 9999 WHERE id = '<self>';
-- reverts to prior value (protect_profiles_computed control probe — proves the mechanism works, just has an incomplete column list)

Live trigger inventory on profiles (pg_trigger, confirmed via pg_get_triggerdef) shows protect_profiles_computed is the only trigger touching computed/identity columns, and its argument list is exactly the 7 B7/B8 named: 'elo_rating', 'elo_matches_played', 'display_tier', 'display_tier_expires_at', 'peak_petals', 'comeback_sessions_remaining', 'last_session_at'.

2.7 The same class, a third time: guest_applications and venue_corrections

Both P3 and P5 are structurally identical to a bug this codebase already found and fixed once: 00329_audit_batch21_join_request_update_with_check.sql closed the exact same defect on club_join_request — a policy with USING but no WITH CHECK (Postgres silently reuses USING for both), letting a raw PATCH through the data API bypass an RPC's side effects. 00329's fix shape (DROP POLICY ...; CREATE POLICY ... USING (false) — RPC-only, zero rows visible to a raw authenticated write) is the mechanical template for P5. venue_corrections (P3) has the inverse shape — its GRANT permits INSERT outright and its WITH CHECK under-constrains what a raw INSERT may write (submitted_by = auth.uid() only, no status/field/proposed_value bound) — so its fix is REVOKE INSERT ... FROM authenticated + DROP POLICY venue_corrections_insert_own, forcing every write through submit_venue_correction (already SECURITY DEFINER, already does its own INSERT). Both are the same underlying lesson: a WITH CHECK/GRANT pairing that is wider than the RPC it's meant to funnel through is not a backstop, it's a second front door — the same lesson as §2.1, applied to table RLS instead of function ACL.

---

3. Solution design

3.1 profiles PII lockdown (P1, P9) — docs/architecture/wiring-plans-2026-07-backend.md A1/A3

New objects:

• private.mask_profile_row(v_viewer uuid, v_target public.profiles) RETURNS jsonb — extract get_public_profile_view's existing jsonb_build_object masking body into a shared helper both the single-row RPC and the new bulk RPCs call, so the masking logic exists exactly once.
• public.get_public_profile_views(p_target_ids uuid[]) RETURNS SETOF jsonb — bulk sibling backing findByIds/findByClub.
• public.get_public_profile_leaderboard(p_club_id uuid DEFAULT NULL, p_region text DEFAULT NULL, p_limit int DEFAULT 100) RETURNS SETOF jsonb — backs findTopByRating/getRegionalLeaderboard/ getRegionalRanking.
• public.search_public_profiles(p_query text, p_limit int DEFAULT 20) RETURNS SETOF jsonb — backs searchByDisplayName.
• public.can_view_profile_field(p_target_id uuid, p_field text) — signature change: drop p_viewer_id, resolve v_viewer := auth.uid() internally (closes P9). Since this is a signature-changing CREATE OR REPLACE, it must re-issue REVOKE ALL ... FROM PUBLIC, anon; GRANT EXECUTE ... TO authenticated; explicitly in the same statement block (§2.1's corrected idiom — Postgres resets ACL on any signature change).

Changed objects:

• profiles_select RLS policy: narrow to USING (auth.uid() = id) — self-row only.
• packages/app/src/adapters/supabase/profile.supabase.ts — the 7 raw-select methods (findByIds, findByClub, getPlayerStats, findTopByRating, searchByDisplayName, getRegionalLeaderboard, getRegionalRanking) switch to .rpc(...) calls against the new bulk functions. findById (self) and findPublicView (already RPC-based) are unaffected.

Sequencing constraint (hard dependency, not a preference): the bulk RPCs must exist and the adapter must be migrated to call them before profiles_select is narrowed. Narrowing RLS first breaks every club roster, leaderboard, and profile-hydrate screen in production. Practically this is two migrations (RPC creation, additive/zero-risk) and one client OTA (adapter rewrite) in between, with the RLS narrowing as a third migration gated on the OTA having shipped and baked.

3.2 profiles self-forgery lockdown (P2)

Extend protect_profiles_computed's existing private.protect_columns(...) argument list — no new trigger, no new function, mechanical addition to the existing call:

CREATE TRIGGER protect_profiles_computed BEFORE UPDATE ON public.profiles FOR EACH ROW
EXECUTE FUNCTION private.protect_columns(
  'elo_rating', 'elo_matches_played', 'display_tier', 'display_tier_expires_at',
  'peak_petals', 'comeback_sessions_remaining', 'last_session_at',
  -- added:
  'trust_tier', 'singles_elo', 'singles_rd', 'doubles_elo', 'doubles_rd',
  'rating_confidence', 'rating_source', 'rating_state',
  'kakao_id', 'total_sessions_confirmed', 'global_attendance_rate'
);

nationality_code deliberately excluded here — its immutability semantics are different (needs a "first-set-wins" pattern like birth_year's reject_under14_birth_year, not a blanket revert) and is roadmap-adjacent (PS7, deferred).

3.3 venue_corrections lockdown (P3)

DROP POLICY venue_corrections_insert_own ON public.venue_corrections;
REVOKE INSERT ON public.venue_corrections FROM authenticated;
-- SELECT stays (venue_corrections_select_own is correctly self-scoped, unaffected)

All writes now funnel exclusively through submit_venue_correction (already SECURITY DEFINER, already performs its own validated INSERT) — mirrors 00329's pattern exactly (§2.7).

Separately, harden the two resolver loops against the batch-abort DoS this finding also causes, as defense-in-depth (independent of the RLS fix — a malformed value can reach the resolver via any future write path, not just this one): wrap each iteration of resolve_venue_facts's FOREACH vid IN ARRAY p_venue_ids LOOP (live-confirmed no exception handling in the current body) and enrich_venues_batch's per-row FOR r IN ... LOOP in BEGIN ... EXCEPTION WHEN OTHERS THEN CONTINUE (following the defensive pattern record_venue_observations already uses at 00347:107-111), so one poisoned or malformed row degrades to a skip, not a whole-batch rollback. This closes B6 #2's DoS half and B6 #8's structural half in the same migration since both are the identical missing-exception-isolation shape in sibling loops.

3.4 ingest-venues fail-open fix (P4)

One-line edge-function change, not a migration:

- if (token && req.headers.get('x-ingest-token') !== token) {
+ if (!token || req.headers.get('x-ingest-token') !== token) {

Matches enrich-venues's already-correct sibling pattern (index.ts:25-29) and all 4 weather-cron functions' cronSecret.length === 0 || provided !== cronSecret idiom byte-for-byte in intent. Deploy via npx supabase functions deploy ingest-venues --no-verify-jwt per this project's edge-function-is-the-server-side-OTA convention — no client change, no app version bump needed.

3.5 guest_applications RPC-only lockdown (P5)

DROP POLICY guest_applications_admin_update ON public.guest_applications;
CREATE POLICY guest_applications_admin_update_rpc_only ON public.guest_applications
  FOR UPDATE TO authenticated USING (false);

Exact 00329 template. All admin decisions now go through decide_guest_application exclusively. Explicitly out of scope for this slice: the capacity/status gate on decide_guest_application and the auto-approve trigger (B1 #3's second half) is a session-lifecycle correctness bug, not an access-control bug — filed to PS3 (Session & RSVP lifecycle), cross-referenced here only.

3.6 SECDEF anon-EXECUTE bulk sweep (P6, P10) + canon-doc correction

DO $$
DECLARE r RECORD;
BEGIN
  FOR r IN
    SELECT p.oid, p.proname,
           format('%I.%I(%s)', n.nspname, p.proname, pg_get_function_identity_arguments(p.oid)) AS sig
    FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
    CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) AS acl
    JOIN pg_roles rr ON rr.oid = acl.grantee
    WHERE p.prosecdef AND n.nspname = 'public' AND rr.rolname = 'anon' AND acl.privilege_type = 'EXECUTE'
      AND p.proname NOT IN ('get_club_discovery_summaries', 'get_public_share_preview', 'is_public_discoverable_club')
  LOOP
    EXECUTE format('REVOKE EXECUTE ON FUNCTION %s FROM PUBLIC, anon;', r.sig);
  END LOOP;
END $$;

ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM anon;

COMMENT ON FUNCTION public.get_club_discovery_summaries(uuid[]) IS 'Intentionally anon-EXECUTE: pre-filtered public discovery data, no PII. Exempted from the anon-EXECUTE sweep, see PS1.';
COMMENT ON FUNCTION public.get_public_share_preview(text, uuid) IS 'Intentionally anon-EXECUTE: KakaoTalk share-preview surface, no PII. Exempted, see PS1.';
COMMENT ON FUNCTION public.is_public_discoverable_club(uuid) IS 'Intentionally anon-EXECUTE: boolean discoverability check, no PII. Exempted, see PS1.';

Plus check_rate_limit_for_key (P10): bind p_key to a server-derived namespace instead of trusting the caller's raw string — p_key := auth.uid()::text || ':' || p_action for authenticated-only call sites, closing the cross-user rate-limit-tripping vector.

Canon-doc correction (same PR, not a migration): docs/canon/data-and-hooks.md:89 — replace the false "verified + enforced in 00209... all 82 public SECDEF functions" claim with the corrected idiom from §2.1 (REVOKE ... FROM PUBLIC, anon) and a pointer to this doc + the pgTAP guard (§3.7) as the actual enforcement mechanism going forward.

3.7 pgTAP regression guard (P11)

-- supabase/tests/secdef_acl_hygiene.test.sql
SELECT is(
  (SELECT count(*)::int FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
   CROSS JOIN LATERAL aclexplode(COALESCE(p.proacl, acldefault('f', p.proowner))) acl
   JOIN pg_roles r ON r.oid = acl.grantee
   WHERE p.prosecdef AND n.nspname = 'public' AND r.rolname = 'anon'
     AND acl.privilege_type = 'EXECUTE'
     AND p.proname NOT IN ('get_club_discovery_summaries', 'get_public_share_preview', 'is_public_discoverable_club')),
  0,
  'no unexpected anon-EXECUTE SECDEF functions in public'
);

Wire into .github/workflows/ci.yml alongside the existing RLS test suite under supabase/tests/. This is the single highest-leverage prevention item in this doc — every fix to date (00209, 00218, 00386, 00387) was reactive; this makes the next CREATE OR REPLACE that resets a function's ACL fail CI instead of shipping silently.

3.8 app_config lockdown (P7)

REVOKE ALL ON public.app_config FROM PUBLIC, anon, authenticated;
GRANT SELECT ON public.app_config TO service_role;

Zero functional change — get_app_config() is already SECURITY DEFINER and reads as its owner regardless of caller-role grants.

3.9 Explicit data-API GRANT sweep (P8)

Generated migration (from information_schema.tables, per §2.4), per-table:

REVOKE ALL ON public.<table> FROM PUBLIC, anon;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.<table> TO authenticated;

for every table where authenticated is the intended data-API role, excluding the 6 already-compliant tables (administrative_divisions, profile_play_regions, surface_catalog, venue_courts, venue_media, venues) and the service_role-only/deny-all tables covered separately (app_config §3.8, signals_archive, pi_access_log). Never grant TRUNCATE/REFERENCES/TRIGGER to authenticated — no legitimate client path needs them.

---

4. Slices

Ordered by live exploitability today, not dependency graph (the wiring-plan's own sequencing table already covers dependency order for a full-roadmap implementer; this ordering answers "if we can only ship N of these this week, which N"). Provisional migration numbers cross-reference wiring-plans-2026-07-backend.md's own numbering where an item maps 1:1; items this doc pulls in from other domains (venue_corrections, guest_applications) are numbered independently since they are structurally independent of the SECURITY-section items and can land in any order relative to them. Hard rule for every slice below, regardless of order: any migration that touches a SECDEF function's signature must use the corrected REVOKE ... FROM PUBLIC, anon idiom (§2.1) — this is a day-0 rule for new work, independent of when the bulk sweep (Slice 6) itself lands.

#	Slice	Finding	Type	Blast radius	Verification	Depends on
1	profiles PII lockdown — bulk RPCs (00392a)	P1	migration (additive)	New functions only, zero behavior change	SET ROLE authenticated; SELECT get_public_profile_views(ARRAY[...]); returns masked rows matching get_public_profile_view's existing per-row semantics	none
2	profiles PII lockdown — adapter rewrite	P1	client OTA	profile.supabase.ts, 7 methods	Full regression pass: club roster, leaderboards (기록), profile hydrate screens; yarn check	Slice 1
3	profiles PII lockdown — RLS narrowing + can_view_profile_field fix (00392b)	P1, P9	migration	profiles_select policy, can_view_profile_field signature	Repeat §2.5's live repro — unrelated authenticated user's raw select on phone/kakao_id/push_token/birth_year/consent timestamps must now return zero rows	Slice 2 shipped + baked
4	profiles self-forgery lockdown (00393)	P2	migration	protect_profiles_computed trigger args only	Repeat §2.6's probe — singles_elo/trust_tier/etc. raw UPDATE must now revert, matching the existing elo_rating control	none
5	venue_corrections RLS lockdown + resolver exception isolation	P3	migration	venue_corrections GRANT/policy, resolve_venue_facts + enrich_venues_batch loop bodies	SET ROLE authenticated; INSERT INTO venue_corrections (...) VALUES (...); must error permission denied; inject one malformed observation into a 3-venue resolve_venue_facts call and confirm the other 2 still resolve	none
6	ingest-venues fail-open fix	P4	edge-fn	supabase/functions/ingest-venues/index.ts line 49	curl the deployed function with no x-ingest-token header and an env with INGEST_TOKEN unset in a scratch project (or unit-test the guard function in isolation) — must return 401	none
7	guest_applications RPC-only lockdown	P5	migration	guest_applications_admin_update policy	SET ROLE authenticated as an admin; raw UPDATE guest_applications SET status='approved' WHERE id=... must affect 0 rows; decide_guest_application RPC still works end-to-end	none
8	SECDEF anon-EXECUTE bulk sweep + check_rate_limit_for_key + canon-doc correction (00388)	P6, P10	migration + doc	35 functions' ACL, check_rate_limit_for_key body, docs/canon/data-and-hooks.md	Re-run §2.2's query post-migration — expect exactly the 3 allowlisted rows. SET ROLE anon; SELECT <each guarded fn>; must error permission denied for all 32 non-allowlisted	none (but should land before Slice 9's guard, so the guard has something to lock in)
9	pgTAP SECDEF regression guard (00389)	P11	migration + CI	supabase/tests/, .github/workflows/ci.yml	Intentionally GRANT EXECUTE ... TO anon on a throwaway function locally, confirm the test fails; revoke, confirm green	Slice 8
10	app_config lockdown (00390)	P7	migration	app_config GRANT only	SET ROLE anon; SELECT * FROM app_config; must now error permission denied for table (previously returned 0 rows via RLS, not a grant denial)	none
11	Explicit data-API GRANT sweep, 82 tables (00391)	P8	migration	Every public table — highest blast radius in this doc	node scripts/run-scenario.mjs live_showcase full smoke pass post-migration (RSVP, dues mark-paid, club join, match score submit); re-run §2.4's count query, expect near-zero	Slices 1–10 shipped and baked (do this last per the wiring plan's own confidence-building rationale — no technical dependency, just risk sequencing)

Total: 11 slices, 8 migrations + 1 edge-fn deploy + 1 client OTA + 1 CI/doc addition. Slices 1–7 close every live-exploitable-today finding (P1–P5); Slices 8–11 close the structural/latent findings (P6–P11) that are correct to fix but are not today's actual attack surface, per the RLS coverage confirmed in §2.4.