B8 — Auth/Identity (Backend): Adversarial BACKEND Audit

Status: Archived Date: 2026-07-11

Scope: profiles table + RLS (00002, 00011, 00051), profile-visibility gating (00212/00221/00228/00260), field-protection triggers (00066/ 00067/00074), consent ledger (00156/00169/00171/00173/00184), account lifecycle (delete_account_atomic 00186, anonymize_withdrawn_ profiles 00157/00184, 00115, 00219, cross-ref 00322), push-token lifecycle (00019/00165/00175, supabase/functions/send-push, supabase/functions/check-push-receipts), nationality (00228), PIPA access-audit (pi_access_log, record_pi_access). Cross-referenced docs/audits/blocks/U7-auth-profile-dm.md (UI-side findings) and docs/audits/blocks/B1-clubs-backend.md (ghost-membership finding this audit extends into the session domain).

Method: Direct migration read (00002→00386, every migration touching profiles/user_consent/pi_access_log/push-token columns) + live adversarial probes against a freshly-reset local Postgres (npx supabase db reset, HEAD = 00386, verified via npx supabase migration list --local) using docker exec supabase_db_twomore-v2 psql to simulate an unrelated authenticated caller (SET LOCAL ROLE authenticated + request.jwt.claim.sub) issuing the exact raw .from('profiles').select(...) queries the adapter (packages/app/src/adapters/supabase/profile.supabase.ts) issues — not static migration reading alone (the dead-theme-file lesson: the adapter's own inline comment claiming RLS enforcement was falsified only by actually running the query as a second user). Also probed profiles_update/field_protection_triggers coverage with real UPDATE statements, pg_proc.proconfig for search_path hygiene, and has_function_privilege('anon', …) for the anon-EXECUTE leak class B5 independently found elsewhere. No code modified; two throwaway auth.users/ profiles rows were inserted into the local dev DB for the probes only (inert on reset, no production or repo artifact touched).

---

Executive-severity ranking

#	Finding	Severity
1	profiles_select RLS is USING (true) — zero row/column-level protection. profile_visibility (00212) is enforced ONLY inside the get_public_profile_view RPC, used by exactly one adapter method. Every other read path (findByIds, findByClub, getPlayerStats, findTopByRating, searchByDisplayName, getRegionalLeaderboard/Ranking) issues a raw table select that bypasses it — and since RLS has no predicate at all, any authenticated user can read phone, kakao_id, push_token, birth_year, and all 3 PIPA consent timestamps for every other user via a direct PostgREST call, independent of app code. Empirically reproduced live.	CRITICAL
2	profiles_update RLS allows the row owner to write any column on their own row; field_protection_triggers (00067) only reverts 7 of them. trust_tier, singles_elo/doubles_elo/singles_rd/doubles_rd, rating_confidence, rating_source, kakao_id, nationality_code, total_sessions_confirmed, global_attendance_rate are all self-writable via raw PATCH — a complete bypass of the Phase 6 trust-tier and rating-integrity machinery. Empirically reproduced live (elo_rating correctly reverted; the 9 listed columns did not).	CRITICAL
3	delete_account_atomic/anonymize_withdrawn_profiles never touch rsvps, dues, session_payments, elo_history, manner_tags, attendance_records, member_reports, DM tables, or signals — extends B1's club_members ghost-row finding into the session domain. Migration 00322's own header comment names this exact gap ("account deletion … is handled separately") but no migration ever implements the separate handling — a withdrawn user's future CONFIRMED RSVPs never release, permanently occupying session capacity.	CRITICAL (extends B1 #1/#2, self-documented, never closed)
4	The marketing_push consent-conflation U7 flagged (#3, generic push toggle silently grants/revokes marketing consent) has zero current server-side consequence — no code path anywhere sends an actual marketing-tagged push ((광고) prefix: 0 hits repo-wide; send-push never branches on user_consent state) — but the §50(8) 2-year re-confirm cron (00171) is already running against a consent ledger that may already contain grants users never knowingly made. Dormant landmine, not yet a live violation.	HIGH
5	record_pi_access — the PIPA §30 access-audit RPC — is fully dead code (zero client call sites; only reference is the generated types file). The only pi_access_log writes that ever happen are the one baked directly into delete_account_atomic. No other PI-viewing flow is ever logged.	MEDIUM
6	can_view_profile_field(p_viewer_id, p_target_id, p_field) takes p_viewer_id as a caller-supplied argument instead of resolving auth.uid() internally (unlike every sibling function in this block) — any authenticated caller can probe whether two other unrelated users share a club or an accepted friendship (boolean relationship-graph leak). Low severity (boolean only, no field value), but a design outlier worth closing alongside #1/#2.	MEDIUM
7	nationality_code (00228) has no protect-trigger and no UI field (matches U7 #14's "no picker by design"), but the raw-API write path is real and unguarded — a self-forgery vector that's inert today (flag rendering hidden, everyone defaults 'KR') but pre-seeds a defect for the promised verified-IdP path, which has no "immutable once verified-source-populated" pattern the way birth_year correctly got in 00186.	LOW-MEDIUM (latent, roadmap-adjacent)
8	Systemic anon-EXECUTE default-ACL leak (root-caused in B5) reproduces on 2 private-schema functions in this block (expire_stale_marketing_consents, protect_consent_timestamps) — not independently exploitable: PostgREST only exposes public/graphql_public schemas (supabase/config.toml:13), and every public-schema B8 function correctly shows anon_exec = false.	LOW (confirms B5's finding, not new exposure)
9	user_consent, pi_access_log, and profiles never received the explicit data-API GRANT this project's own convention requires (same class as B1 finding #7) — inert today because every policy is scoped TO authenticated/TO service_role, but drift risk if default privileges are ever tightened.	LOW (hygiene)
10	Verified CLEAN / works well (see below) — consent-ledger append-only integrity, birth-year immutability + under-14 gate, push-token stale-token cleanup, SECDEF hygiene across the entire block.	INFO

---

1. CRITICAL — profiles_select RLS enforces nothing; profile_visibility is RPC-only, not RLS

Evidence — the policy itself (supabase/migrations/00011_rls_indexes_triggers.sql:100-103):

CREATE POLICY profiles_select ON public.profiles
  FOR SELECT
  TO authenticated
  USING (true);

No later migration ever narrows this (grep -rn "profiles_select\|ALTER POLICY.*profiles" supabase/migrations/*.sql → only the one CREATE POLICY, never redefined). Live confirmation: SELECT relrowsecurity FROM pg_class WHERE relname='profiles' → t (RLS is on), and \dp public.profiles shows the policy text verbatim on the live HEAD-00386 database — RLS is active but its only predicate is a no-op.

Evidence — the adapter's false claim (packages/app/src/adapters/supabase/profile.supabase.ts:15-18):

// PUBLIC: cross-user reads — omits PII fields (phone, push_token, birth_year, consent audit columns)
// Field-level visibility (gender, region) is enforced server-side by the profile_visibility RLS predicate
const PUBLIC_PROFILE_COLUMNS = '... gender, region, district, play_regions, ...' as const;

There is no profile_visibility RLS predicate anywhere in the migration history — the only server-side enforcement of profile_visibility (00212) is inside public.get_public_profile_view(uuid), a SECDEF RPC used by exactly one adapter method, findPublicView. Every other adapter method that reads cross-user profiles (findByIds, findByClub, getPlayerStats, findTopByRating, searchByDisplayName, getRegionalLeaderboard, getRegionalRanking) issues a raw .from('profiles').select(PUBLIC_PROFILE_COLUMNS) — and PUBLIC_PROFILE_COLUMNS includes gender, region, district, play_regions, none of which are visibility-gated at that layer.

Live reproduction (fresh db reset to HEAD 00386):

1. Created two throwaway users A (1111…) and B (2222…), not club-mates, no friendship.
2. Set A's profile: gender='female', region='seoul', district='gangnam', phone='+821012345678', kakao_id=99887766, push_token='ExponentPushToken[SECRET_TOKEN_XYZ]', birth_year=1995, all four profile_visibility keys set to 'private'.
3. As user B (SET LOCAL ROLE authenticated; set_config('request.jwt.claim.sub', B)), ran the exact raw select the adapter issues:

SELECT id, phone, kakao_id, push_token, birth_year, terms_accepted_at, privacy_accepted_at, marketing_consent_at
FROM public.profiles WHERE id = '<A>';

Result: full unmasked values returned — phone=+821012345678, kakao_id=99887766, push_token=ExponentPushToken[SECRET_TOKEN_XYZ], birth_year=1995, all 3 consent timestamps populated. Same for gender/region/district via the PUBLIC_PROFILE_COLUMNS-shaped select.

4. Compared against the intended gate: SELECT get_public_profile_view('<A>') as user B correctly returned gender/region/district: null — proving the RPC path itself is sound, it's simply not the path any list/leaderboard/club-roster screen uses.

Impact: this is not a theoretical gap — findByClub backs every club member-list screen, getPlayerStats/leaderboards back 기록, findByIds backs any bulk profile hydrate. Every one of them, plus a trivial curl against the PostgREST endpoint with any signed-in user's JWT (not even an admin token — any registered account), exposes every user's real phone number, Kakao numeric ID, Expo push token, and birth year — the exact 4 fields 00212's own header comment calls "NEVER public (always self-only, hardcoded)." This is a live PIPA §23/§24 sensitive-information exposure and a push-token exfiltration vector, reachable by any signed-up user with no privilege escalation required.

Fix shape: narrow profiles_select itself — either (a) split into two policies (self full-row USING(auth.uid()=id) + others restricted to a hard column allowlist via a companion view/RPC-only pattern, since Postgres RLS can't do column-level masking directly), or (b) the cleaner path given the codebase already has get_public_profile_view: revoke broad profiles_select for non-self rows and force all cross-user reads through SECDEF RPCs (extend get_public_profile_view or add bulk siblings for findByIds/findByClub/leaderboards per the project's own Protocol A "bulk sibling" convention), never a raw table select for another user's row. Needs a migration + adapter rewrite of the 6 listed methods.

---

2. CRITICAL — Self-service forgery of trust tier, singles/doubles rating, and Kakao/nationality identity fields

Evidence — the update policy (00011:106-110):

CREATE POLICY profiles_update ON public.profiles
  FOR UPDATE TO authenticated
  USING ((select auth.uid()) = id)
  WITH CHECK ((select auth.uid()) = id);

No column restriction — a row owner may write any column via a raw .from('profiles').update({...}).eq('id', self).

Evidence — what's actually protected (00067_field_protection_triggers.sql:12-19, live-confirmed as the only relevant trigger on profiles):

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'
  );

Only these 7 columns are reverted on mismatch. The live trigger inventory on profiles (pg_trigger, HEAD 00386) confirms no other trigger touches trust_tier, singles_elo/doubles_elo/singles_rd/doubles_rd, rating_confidence, rating_source, kakao_id, nationality_code, total_sessions_confirmed, or global_attendance_rate. trg_profiles_sync_rating_state (private.sync_rating_state) only derives rating_state/display_tier_sub/rating_source (self_assessed→intra_club monotonic bump) from whatever elo_rating/elo_matches_played/singles_matches_played currently hold — it never reverts a directly-forged value.

Live reproduction (same session as finding #1, as the row owner — no cross-user access needed, this is a self-service escalation):

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 6 values persisted exactly as set

UPDATE public.profiles SET elo_rating = 9999 WHERE id = '<self>';
-- → reverted to 1200 by protect_profiles_computed (control — proves the mechanism works, just has an incomplete column list)

UPDATE public.profiles SET kakao_id = 555555 WHERE id = '<self>';
-- → persisted

Impact: any authenticated user can, via a single raw PATCH:

• Self-promote trust_tier to 'veteran' — the top of the Phase 6 trust ladder (private.recalculate_trust_tier, docs/specifications/attendance-and-reviews.md) — bypassing every session-history/manner-tag/attendance input the real function weighs, and unlocking any trust-tier-gated recruitment filter or feature.
• Self-inflate singles_elo/doubles_elo/rating_confidence, corrupting leaderboards (U5 already flagged leaderboard integrity from the UI side; this is the backend root cause available to any user, not just a display bug), the "rating honesty" display-tier check (00229), and any future rating-gated matchmaking.
• Rewrite rating_source to 'verified_tournament' (no value-allowlist enforcement beyond the CHECK constraint on the column, if any — the sync trigger doesn't block arbitrary same-write values), falsely badging a self-assessed rating as tournament-verified.
• Set kakao_id to an arbitrary value on their own row. kakao_id carries a UNIQUE constraint, so this cannot silently take over another live account, but it can pre-emptively squat a victim's real Kakao numeric ID before that person ever signs up/links Kakao — the moment the real owner attempts Kakao OIDC sign-in, the app-side profile-linking write (if any exists at that layer) would collide against the squatted row. (Not fully traced end-to-end here — flagged for the Phase 8c OAuth wiring plan to close explicitly, since kakao_id write access should never be client-reachable at all; it's an OIDC-verified identity attribute, not a user preference.)

Fix shape: extend protect_profiles_computed's column list to cover every server-computed/identity-verified column (trust_tier, singles_elo, singles_rd, doubles_elo, doubles_rd, rating_confidence, rating_source, rating_state, kakao_id, total_sessions_confirmed, global_attendance_rate) — mechanical, mirrors the existing pattern exactly, one migration.

---

3. CRITICAL — Account deletion misses every downstream table except profiles/user_consent/pi_access_log (extends B1, self-documented gap never closed)

Evidence: live \sf public.delete_account_atomic and \sf private.anonymize_withdrawn_profiles on HEAD 00386 match 00186/00184 verbatim — no later migration touches either function. delete_account_atomic writes only profiles (deleted_at/push_token), user_consent (withdrawal rows), pi_access_log (one audit row). anonymize_withdrawn_profiles writes only profiles (PII null-out), user_preferences (DELETE), signal_consent_log (DELETE).

Every one of the following tables has user_id UUID NOT NULL REFERENCES public.profiles(id) ON DELETE CASCADE and is never referenced by either function: rsvps (00006), dues (00009), elo_history (00008), manner_tags/attendance_records/member_reports (00086), plus session_payments, DM threads/messages, and signals. The ON DELETE CASCADE clauses are inert in practice because profiles.id is never hard-deleted (profiles rows are permanent by explicit design — "anonymize, don't drop… deleting profile rows would cascade into matches/RSVPs/scores," 00157:8-13).

The gap is self-documented and was never closed. 00322_audit_batch13_release_seats_on_leave.sql:22-24, in its own header:

"Scope: FUTURE sessions only … This fires on the leave/kick path (a real DELETE of a club_members row); account deletion is soft-delete (delete_account_atomic sets deleted_at, 00186) and does not delete club_members, so it is handled separately."

grep across every migration after 00322 for any trigger or function keyed off profiles.deleted_at beyond the two known ones (zero_pii_on_soft_delete, discovery-summary read-side filters) returns nothing RSVP/dues/session_payment-related — "handled separately" was never implemented.

Impact: identical failure mode to B1 finding #1/#2, but in the session domain, which B1 did not audit (out of its scope). A user who deletes their account while holding a confirmed RSVP on a future session:

• Keeps occupying that seat forever — no release, no waitlist promotion (the only RSVP-release code path, private.release_member_future_seats_on_leave, fires exclusively on a club_members DELETE, which account deletion never performs).
• Can hold a session artificially at max-capacity/locked, blocking real members from joining, indefinitely.
• Their unpaid dues rows stay owed to a '탈퇴한 회원' sentinel forever (same "immortal unpaid dues" pattern U8/B5 already flagged for ex-members generally — this is the account-deletion-specific instance of it).
• Pending session_payments holds self-heal only via the time-based hold-expiry cron (B3/B5's territory) — not via anything account-deletion-aware.

Fix shape: either (a) have delete_account_atomic itself run the equivalent of release_member_future_seats_on_leave across all the user's active club memberships and future RSVPs before/alongside the soft-delete, or (b) add a trigger on profiles.deleted_at (NULL→value) that fans out the same release logic. Needs coordination with B1's fix for the club_members ghost-row problem — likely the same migration should close both, since B1's proposed AFTER UPDATE OF profiles.deleted_at trigger (deactivate club_members) is the natural place to also invoke the RSVP release.

---

4. HIGH — Marketing-consent conflation (U7 #3) is dormant today but the ledger is already tainted

U7 found the generic "푸시 알림" toggle silently grants/revokes the marketing_push PIPA purpose. This audit traces the server-side consequence: there isn't one, yet. grep -rn "(광고)" supabase/functions/ packages/ → 0 hits; send-push/index.ts never reads user_consent or branches on marketing_push state — it only gates on signal_preferences (in-app/push/severity/quiet-hours). No code path in the repository sends an actual marketing-tagged push.

The §50(8) 2-year re-confirmation cron (private.expire_stale_marketing_consents, 00171) is nonetheless already running daily against whatever user_consent rows exist for marketing_push/marketing_push_night — meaning any grant rows the mislabeled generic toggle has already written (U7's finding, live in production per the toggle's shipped state) sit in the audit ledger as if they were informed, specifically-labeled marketing consent. The moment a real marketing-push send path ships and reads this ledger as its gate, it will be sending to users who never knowingly opted into marketing — a live PIPA §22 violation from day one of that feature, not a bug introduced then.

Fix shape: matches U7's proposed fix (strip marketing_push writes from the generic toggle) — flagging here specifically so the eventual marketing-push-send implementation task also includes a one-time ledger audit/re-consent pass for any marketing_push grants whose collection_method='settings_toggle' predate the toggle fix, since backend-side the grants already exist and look legitimate.

---

5. MEDIUM — PIPA §30 access-audit RPC is dead code

Evidence: public.record_pi_access(p_access_type, p_scope, p_purpose, p_user_agent) (definition read live, HEAD 00386) forces actor_user_id = target_user_id = auth.uid() — self-logging only, correctly scoped, correctly SECDEF-hardened (search_path='', anon_exec=false). But grep -rn "record_pi_access|recordPiAccess" packages/app/src packages/features returns zero call sites outside generated.types.ts (the auto-generated Supabase type definitions file, not a real invocation). The only rows pi_access_log ever receives are the single insert baked directly into delete_account_atomic.

Impact: the audit-trail infrastructure required for PIPA §30 (recording who accessed whose personal information, for what purpose) exists as schema + RPC but is wired to exactly one event (account deletion). Any other PI-adjacent access — an admin viewing a member's phone/consent history via get_user_consents(p_user_id)'s dev-superuser branch, a future customer-support tool, or (per finding #1) any of the currently-unintended cross-user PII reads — leaves no audit trail at all.

---

6. MEDIUM — can_view_profile_field takes the viewer as a caller-supplied argument

Evidence: public.can_view_profile_field(p_viewer_id uuid, p_target_id uuid, p_field text) (00212) does not call auth.uid() internally; the caller supplies p_viewer_id directly as an RPC argument. Every sibling function in this block (get_public_profile_view, grant_consent, revoke_consent, get_user_consents, delete_account_atomic) instead resolves v_user := auth.uid() itself and rejects/ignores mismatches. Live grant check confirms it's authenticated-only (anon_exec=false), so this requires a real login, but any logged-in user can call it with an arbitrary p_viewer_id naming two other people and learn whether they share a club or an accepted friendship (a boolean, not the underlying field value).

Impact: low severity (boolean-only relationship-graph probe, not a data leak of the gated field itself) but a real design inconsistency worth closing in the same pass as #1/#2 — the fix is mechanical (drop the p_viewer_id parameter, resolve auth.uid() inside, update the one call site in get_public_profile_view).

---

7. LOW-MEDIUM — nationality_code has no write protection, pre-seeding a forgery vector for the verified-IdP path

Evidence: 00228_profiles_nationality.sql adds nationality_code TEXT NOT NULL DEFAULT 'KR' with an ISO-3166-1-alpha-2 format CHECK but no protect-trigger coverage and no entry in UpdateProfileInput (confirmed by U7 #14 — "no picker by design"). The migration's own comment states: "in PRODUCTION the value is DERIVED from the country's officially-recognized identity provider at signup … never free-form." Today this is inert (everyone defaults 'KR', ATP flag rendering is hidden) — but unlike birth_year, which got an explicit "immutable after first set" trigger in the same audit-adjacent migration series (00186), nationality_code has no equivalent lock. Any raw PATCH can set it today with zero consequence (matches finding #2's broader "self-write unprotected identity columns" pattern) — and there's no mechanism today that would prevent a user from overwriting a future verified-IdP-populated value with an arbitrary self-declared one.

Fix shape: when the verified-IdP onboarding work lands, add the same BEFORE UPDATE immutable-after-verified-set pattern 00186 used for birth_year; until then, low-priority (no live feature reads or trusts this column beyond identity display, which is 'KR' for 100% of accounts today).

---

8. LOW — Systemic anon-EXECUTE leak (B5's finding) reproduces here, not independently exploitable

Evidence: has_function_privilege('anon', …, 'EXECUTE') sweep across every SECDEF function in this block:

private.expire_stale_marketing_consents()   anon_exec = t
private.protect_consent_timestamps()        anon_exec = t
public.can_view_profile_field(...)          anon_exec = f
public.delete_account_atomic()              anon_exec = f
public.get_public_profile_view(...)         anon_exec = f
public.get_user_consents(...)               anon_exec = f
public.grant_consent(...)                   anon_exec = f
public.record_pi_access(...)                anon_exec = f
public.revoke_consent(...)                  anon_exec = f

The two private-schema functions are anon-executable at the Postgres grant level (same post-00209 ALTER DEFAULT PRIVILEGES gap B5 root-caused as a 41-function app-wide issue), but supabase/config.toml:13 scopes PostgREST's exposed schemas to ["public", "graphql_public"] only — private-schema functions are not reachable via supabase.rpc() or any REST endpoint regardless of their Postgres-level grant. Every public-schema function in this block correctly shows anon_exec = false.

Impact: none independently — confirms B5's finding is systemic (present here too) but this block's actually-reachable surface is clean. Fold into B5's tracked fix rather than filing separately.

---

9. LOW (hygiene) — user_consent/pi_access_log/profiles predate the explicit-GRANT convention

Evidence: \dp public.user_consent and \dp public.pi_access_log (live, HEAD 00386) both show anon=arwdDxtm (full table-level CRUD) alongside authenticated/service_role — no explicit GRANT ... TO authenticated statement exists for either table in the migration history (same pattern B1 finding #7 flagged for clubs/club_members/invites/guest_applications). Currently inert: user_consent's only policies are TO authenticated, pi_access_log's only policy is TO service_role — anon matches zero policies so RLS returns zero rows regardless of the raw table grant. profiles (00002) predates the convention by the widest margin of any table in the codebase.

Fix shape: one hygiene migration adding explicit GRANT SELECT, INSERT, UPDATE ON public.profiles TO authenticated; / GRANT SELECT, INSERT ON public.user_consent TO authenticated; / GRANT INSERT ON public.pi_access_log TO service_role; — mechanical, no behavior change, matches B1's proposed fix shape.

---

Verified CLEAN / works well (evidence)

• Consent ledger append-only integrity is real, not just documented. user_consent has no UPDATE/DELETE RLS policy at all (Postgres RLS default-denies both — confirmed via \dp), so withdrawal/supersession can only ever happen via a new INSERT row. The partial unique index uq_active_consent ON (user_id, purpose_id) WHERE state='granted' makes "one active grant per purpose" a DB-layer guarantee, not an application convention — race-condition-proof for concurrent grant attempts.
• Backdating is blocked, not just discouraged. private.protect_consent_timestamps (live-read) hard-rejects any terms_accepted_at/privacy_accepted_at rewrite once set (RAISE EXCEPTION), and allows marketing_consent_at to go value→NULL (withdrawal) or NULL→value (grant) but blocks value→different-value (no silent backdating of an existing grant).
• Under-14 signup gate + birth-year immutability is real DB enforcement, not just an onboarding-screen check (private.reject_under14_birth_year, 00186) — blocks both the initial under-14 INSERT and any later attempt to change an already-set birth_year.
• Push-token stale-cleanup is correctly wired. Both send-push/index.ts:428-446 (inline, on DeviceNotRegistered ticket error) and the dedicated check-push-receipts cron (00175, on DeviceNotRegistered receipt) null the bad token — no dangling-token accumulation.
• delete_account_atomic's own scope is internally correct and atomic: single transaction, auth.uid() required, pushtoken nulled immediately (no further sends during the 30-day grace window via zero_pii_on_soft_delete), consent withdrawal rows appended for every currently-granted purpose, one pi_access_log audit row written — the _implementation of what it does is sound; the finding (#3) is entirely about what it never touches.
• SECDEF hygiene is clean across the entire block — all 12 functions checked carry search_path="", matching CLAUDE.md's SECDEF-hygiene rule; no search_path=public drift found here (contrast B5's finding of 20 such functions elsewhere in the codebase — none of them are in this block).
• anonymize_withdrawn_profiles's column inventory is verified correct against the live schema (own migration comment claims this after fixing a previous 42703-erroring version in 00161) — reproduced live: the function runs without error on HEAD 00386 against real column names.

---

Adversarial probes run (explicit list)

1. Can any authenticated user read any profile fully? REPRODUCED — finding #1. Simulated an unrelated authenticated user (no club, no friendship) reading another user's phone/kakao_id/push_token/birth_year/consent timestamps and gender/region/district (all set to profile_visibility: 'private') via the exact raw-select shape the adapter issues; all fields returned unmasked. Cross-checked the intended get_public_profile_view RPC path correctly masks the same fields for the same viewer — proving the gate exists but is bypassable, not absent everywhere.
2. Is profile_visibility actually enforced in RLS? REPRODUCED — no. Confirmed via direct read of every CREATE POLICY ... profiles statement in the migration history (only 3 policies ever exist: select/update/self-insert, none reference profile_visibility) plus the live probe above.
3. PII exposure via joins (signals/DM/attendance leaking display_name of private profiles)? Not reproduced as a distinct bug — displayName/avatarUrl are explicitly documented as always-visible identity (00212), so their appearance in joins is by design, not a gap. The real leak is the other fields (finding #1), which are exposed identically whether read via a join or a direct select, since RLS has no predicate either way.
4. Can a user self-elevate server-computed trust/rating fields? REPRODUCED — finding #2. Set trust_tier='veteran', singles_elo/doubles_elo=3000, rating_confidence=100 on own row via raw UPDATE; all persisted. Control probe (elo_rating=9999) correctly reverted, proving the protection mechanism exists but has an incomplete column list rather than being entirely absent.
5. kakao_id squatting? Confirmed self-writable (persisted in probe); full cross-account collision path not traced end-to-end (would require tracing the Kakao OIDC linking write, which lives in Supabase Auth's own auth.identities, likely orthogonal to profiles.kakao_id) — flagged as a needs-closing-before-Phase-8c item rather than a fully reproduced exploit.
6. delete_account_atomic completeness — which tables does it miss? Enumerated via FK grep (REFERENCES public.profiles(id) ... ON DELETE CASCADE): rsvps, dues, elo_history, manner_tags, attendance_records, member_reports, plus (not FK-swept individually but confirmed absent from both functions' bodies) session_payments, DM tables, signals. Cross-referenced against 00322's self-documented "handled separately" claim — confirmed never implemented. Finding #3.
7. Anonymization cron coverage vs GDPR/PIPA erasure? anonymize_withdrawn_profiles's column list verified against live schema (runs clean on HEAD 00386); scope is profiles + user_preferences + signal_consent_log only — same gap as #3 for every other PII-adjacent table (e.g. does a withdrawn user's manner_tags.giver_id-authored comment text, if any exists as free text — checked, manner_tags is tag-enum-only, no free text — carry PII forward? No, clean on that specific sub-question).
8. Soft-delete grace-window PII exposure? zero_pii_on_soft_delete nulls push_token immediately on the deleted_at transition (verified live definition) — but every other PII field (phone, kakao_id, gender, region, birth_year) stays populated for the full 30-day grace window, and — per finding #1 — remains readable by any other authenticated user for that entire window via the same RLS gap, not just self.
9. Push token registration RLS / cross-user overwrite? profiles_update is self-row-only (auth.uid()=id), so updatePushToken(userId, token) cannot target another user's row even if a compromised client passed an arbitrary userId — RLS blocks it regardless of the client-supplied parameter. Safe.
10. Stale token cleanup / receipt handling? Verified CLEAN (see works-well section) — both inline and cron paths null DeviceNotRegistered tokens.
11. Nationality/verified-IdP server-side assumptions? Traced nationality_code's CHECK constraint (format-only, no source-trust concept) and confirmed no write-protection trigger exists — finding #7.
12. SECDEF hygiene + GRANT gaps across every function touching profiles/user_consent/pi_access_log/push tokens: pg_proc.proconfig sweep (12 functions, all search_path="") + has_function_privilege('anon', ...) sweep (2 private-schema functions anon-executable but unreachable via PostgREST's exposed-schema config; all public-schema functions correctly anon_exec=false) + \dp on profiles/user_consent/pi_access_log (all three predate the explicit-GRANT convention, none currently exploitable). Findings #8, #9.
13. Consent forgery/backdating? Attempted conceptually against grant_consent/revoke_consent (both resolve auth.uid() internally, cannot act on another user's behalf) and against protect_consent_timestamps (blocks backdating of terms_accepted_at/privacy_accepted_at/marketing_consent_at at the trigger level) — confirmed clean, no forgery path found.
14. Withdrawal actually stops sends? push_token is nulled synchronously on withdrawal (zero_pii_on_soft_delete), so push delivery physically cannot continue post-deletion. Marketing-specific consent withdrawal has no send path to verify against at all today (finding #4) — the withdrawal mechanism itself works, there's just nothing yet gated on it.

---

Canon / rule cross-references

• CLAUDE.md SECDEF hygiene ("SET search_path = '' + REVOKE EXECUTE FROM PUBLIC + explicit GRANT") — fully complied with across all 12 functions read/swept in this block (probe #12). No drift found here, in contrast to B5's broader 20-function search_path=public finding elsewhere in the codebase.
• CLAUDE.md "every new public table needs an explicit data-API GRANT" — violated by profiles/user_consent/pi_access_log (finding #9), same class as B1 finding #7; not currently exploitable, hygiene debt only.
• PIPA §21/§30/§37/§50(8) — §21 (anonymize-on-withdrawal) and §37 (right-to-opt-out) mechanisms are real and DB-enforced for what they cover; §30 (access-audit trail) infrastructure exists but is unused outside one call site (finding #5); §50(8) 2-year marketing re-confirm cron is running correctly but against a ledger with a known, still-live contamination source (finding #4, cross-ref U7 #3).
• "No brittle foundation" / single-source-of-truth — profile_visibility's single enforcement point (get_public_profile_view) not being the single read path is exactly this anti-pattern: the security-relevant logic exists once, correctly, but most call sites don't route through it.
• Cross-reference to B1: finding #3 here is the session-domain twin of B1 findings #1/#2 (club-domain ghost rows on account deletion) — both trace back to the same root design choice (profiles rows are permanent, cascades never fire) and should likely be closed in the same migration/wiring-plan pass.