B7 — Platform Plumbing (Backend) Adversarial Audit
Status: Archived Date: 2026-07-11 Scope: edge functions (supabase/functions/*), pg_cron inventory (00102_pg_cron_config_table.sql + live cron.job), signals residuals, telemetry PII, storage buckets, and the full-schema explicit-GRANT convention audit. Primary mandate (owner-flagged CRITICAL LEAD from B5): enumerate the full ACL blast radius of the post-00209 SECDEF anon-EXECUTE leak app-wide — not just re-cite B5's count, but find the mechanism and rank every live instance by exploitability.
Method: Live probes against a freshly-reset local Postgres (docker exec supabase_db_twomore-v2 psql, HEAD = 00386), not static migration reading alone — aclexplode(proacl)/aclexplode(relacl) over pg_proc/pg_class, pg_default_acl, pg_get_functiondef on every anon-executable SECDEF body to check for internal auth.uid() guards, cron.job table dump, pg_policies on storage.objects, and SET ROLE anon; SELECT ... live re-enactment of the app_config grant to prove/disprove exploitability rather than assume from the ACL alone. Cross-referenced B1–B6 (esp. B5's CRITICAL LEAD and B6's ingest-venues fail-open finding) so nothing here duplicates without adding new evidence.
Summary table
| # | Finding | Severity | Verified | | --- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------- | ------------------------------------------------------------------ | ------------------------------- | -------------------------------------------- | | 1 | Root cause of the systemic SECDEF anon-EXECUTE leak found: Supabase's platform bootstrap sets ALTER DEFAULT PRIVILEGES … GRANT ALL ON FUNCTIONS TO anon,authenticated,service_role,postgres as explicit per-role default-ACL entries, not a grant routed through the PUBLIC pseudo-role. REVOKE … FROM PUBLIC (used 96× across migrations) is therefore a no-op against anon — it never touches anon's own explicit default-ACL row. Only REVOKE … FROM PUBLIC, anon (00209's idiom) actually works. | CRITICAL (root cause) | pg_default_acl dump + 96-instance grep | | 2 | Full current blast radius: 40 of 139 (29%) public-schema SECURITY DEFINER functions are anon-EXECUTE-able right now. Of those, 5 have zero internal auth guard and are directly, unauthenticated-exploitable (venue-pipeline mass-write RPCs); the rest fail safe on auth.uid() IS NULL or are unreachable trigger functions. Full ranked list below. | CRITICAL | aclexplode + per-function body read | | 3 | ingest_venues_batch, enrich_venues_batch, apply_venue_coords, record_venue_observations, resolve_venue_facts are directly callable by anon via PostgREST RPC with zero auth check, bypassing ingest-venues's edge-function token gate entirely (B6's CRITICAL finding). Fixing the edge function's if (token && …) bug (B6's recommendation) does not close this — the RPC is a second, unguarded front door straight into the venues table. | CRITICAL (escalates B6 #1) | Live pg_get_functiondef, no auth.uid() anywhere | | 4 | Storage: avatars insert own / avatars update own RLS policies have zero ownership check — the qual/with-check is literally bucket_id = 'avatars', applied to role authenticated. Combined with the world-readable listing (avatars read, also authenticated, bucket_id='avatars' only) and a fully client-constructed, guessable avatars/${userId}-${epoch}.${ext} path, any signed-in user can enumerate and overwrite/deface any other user's avatar image. | CRITICAL | Live pg_policies + client upload-path read | | 5 | app_config — the table holding the plaintext service_role_key and cron_secret used by every net.http_post cron job — has the full Supabase default table grant (arwdDxtm) to both anon and authenticated. Currently inert only because RLS is enabled with zero policies (deny-all). One future permissive policy, or one ALTER TABLE … DISABLE ROW LEVEL SECURITY, instantly leaks the service-role key to the public internet. | HIGH (latent) | information_schema.role_table_grants + SET ROLE anon live test | | 6 | 82 of 88 public-schema tables (93%) carry the unmodified Supabase default ACL (anon+authenticated full arwdDxtm) — only 6 tables (administrative_divisions, profile_play_regions, surface_catalog, venue_courts, venue_media, venues) have an explicit narrowed GRANT per the AGENTS.md convention. Extends B1's/B5's individually-flagged tables to the full schema count. Currently safe only because RLS is universally enabled with ≥1 policy on every table except app_config/signals_archive. | HIGH (convention, not proven live) | Full pg_class.relacl sweep | | 7 | ingest-venues edge function's token gate is fail-open (if (token && …) skips the check entirely when INGEST_TOKEN is unset) — re-confirmed live, matches B6 #1 exactly; no sibling edge function repeats this exact inversion (all 4 weather crons + check-push-receipts/send-push correctly use if (secret.length === 0 | | provided !== secret)). | CRITICAL (= B6 #1, re-verified) | Source read, all 7 secret-gated fns compared | | 8 | seed-scenario's auth guard is "permissive by default": with SEED_ALLOWED_UIDS unset, any Bearer token including the public anon key is accepted as long as ENV !== 'production'. The only backstop against a shared preview/pre-prod Supabase project is a correctly-set ENV secret — self-documented as an accepted tradeoff, but no enforced fail-safe default exists. | MEDIUM-HIGH | Source read | | 9 | check_rate_limit_for_key is anon-executable with fully attacker-controlled p_key/p_action/p_max_calls/p_window_seconds and no identity binding — a caller who knows or guesses another user's rate-limit key (e.g. a phone number used for OTP) can trip/exhaust that key's limit as a targeted DoS, or pass an inflated p_max_calls to defeat rate limiting on their own abusive traffic. | MEDIUM | Live pg_get_functiondef | | 10 | Zero automated regression guard exists anywhere in the repo for SECDEF ACL hygiene — no pgTAP test, no CI step, nothing greps aclexplode(proacl). Every fix to date (00209, 00217, 00218, 00386) has been a manual, reactive, one-function-at-a-time patch; the 96-instance REVOKE … FROM PUBLIC-only anti-pattern (finding #1) will keep re-opening functions with every future CREATE OR REPLACE. | HIGH (preventive gap) | Repo-wide grep for aclexplode/pgTAP | | 11 | Cron: generate_dues_reminders() (job 25, 0 21 * * *, active) still runs daily writing to the confirmed-dead club_alerts table (B5 #13, re-verified: club_alerts row count = 0, its hooks were explicitly removed "Round 14 — zero UI consumers", port/adapter kept only for a future bell-badge). No new finding, cross-referenced. | LOW (dead cron, cross-ref B5) | cron.job dump + hooks/index.ts comment | | 12 | 27 active pg_cron jobs total, full inventory below. No duplicate/orphaned schedules found beyond #11. Cadence is sane throughout (weather batching every 15min staggered across 6 batches, */5 payment-hold expiry matching the 10-min hold window, */15 match auto-call matching the 30s edit-debounce). check_cron_health() runs every 15 min as a self-monitor. | INFO (clean) | Full cron.job dump |
1. CRITICAL (root cause) — the systemic anon-EXECUTE leak mechanism
This is the answer to the CRITICAL LEAD. pg_default_acl on the live DB:
schema | defaclrole | objtype | defaclacl
public | supabase_admin | f | {postgres=X/supabase_admin,anon=X/supabase_admin,authenticated=X/supabase_admin,service_role=X/supabase_admin}
public | postgres | f | {postgres=X/postgres,anon=X/postgres,authenticated=X/postgres,service_role=X/postgres}Supabase's project bootstrap ran (once, at provisioning, outside the migrations folder) something equivalent to:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON FUNCTIONS TO anon, authenticated, service_role, postgres;This creates explicit, named-role default-ACL rows — anon gets X (EXECUTE) directly, not by virtue of being a member of PUBLIC. Every CREATE FUNCTION (and every DROP FUNCTION; CREATE FUNCTION, or any signature-changing replace that forces a drop-recreate) in public picks this up automatically and silently.
00209_revoke_anon_execute_secdef.sql correctly diagnosed this in 2026 and did the double fix: a one-time DO $$ ... REVOKE EXECUTE ... FROM PUBLIC, anon $$ loop over every existing SECDEF function, plusALTER DEFAULT PRIVILEGES IN SCHEMA public REVOKE EXECUTE ON FUNCTIONS FROM PUBLIC for future-proofing. But that ALTER DEFAULT PRIVILEGES … REVOKE … FROM PUBLIC only strips the PUBLIC pseudo-role's row from the default-ACL bucket — it does not touch the separate, already-present anon=X row in the same bucket (confirmed: that row is still live today, post-00209, per the dump above). So every SECDEF function created after 00209 still auto-gets anon EXECUTE, and 00209's future-proofing clause never worked for anon.
Worse: dozens of individual feature migrations independently tried to lock down their own new functions using the idiom:
REVOKE ALL ON FUNCTION public.some_fn(...) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.some_fn(...) TO service_role;This idiom never worked, for the exact same reason — anon's grant was never routed through PUBLIC in the first place, so REVOKE ... FROM PUBLIC revokes a grant that was never the one protecting anything. Count:
$ grep -rE "REVOKE (ALL|EXECUTE) ON FUNCTION public\.[a-z_]+\([^)]*\) FROM PUBLIC;" supabase/migrations/*.sql | wc -l
96
$ grep -rE "REVOKE (ALL|EXECUTE) ON FUNCTION public\.[a-z_]+\([^)]*\) FROM (PUBLIC, ?anon|PUBLIC, ?anon, ?authenticated)" supabase/migrations/*.sql | wc -l
1396 migrations use the broken idiom; only 13 use the correct one. This is not a handful of forgotten functions — it's a near-universal, copy-pasted anti-pattern across the migration history. Direct proof on the venue pipeline (finding #3 below): every one of ingest_venues_batch's 4 defining migrations (00334, 00335, 00341, 00342) includes exactly this REVOKE ALL … FROM PUBLIC; GRANT … TO service_role; pair, with clear service-role-only intent, and every one of them silently failed to achieve it.
Fix pattern going forward: every REVOKE … FROM PUBLIC on a function must be REVOKE … FROM PUBLIC, anon (mirror 00209's own idiom, not the 96-instance one). A single remediation migration should re-run 00209's DO $$ loop (idempotent, safe to re-run) rather than hand-patch 40 individual functions one at a time (00386's approach, correct for a hotfix but not a strategy for 40).
2. CRITICAL — the full current anon-EXECUTE blast radius (40 functions, ranked)
Query (the definitive enumeration the task asked for):
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;40 rows (of 139 total public SECDEF functions — 29%). Zero rows for grantee PUBLIC specifically (00209's ALTER DEFAULT PRIVILEGES did correctly strip that one row; the leak is 100% via the separate anon row). mark_session_payment_paid_via_portone (B5's original CRITICAL, 00386 hotfix) is confirmed no longer in this list — the hotfix holds.
Every function's pg_get_functiondef was pulled and checked for an internal auth.uid() guard. Classification:
2a. Directly exploitable — no internal guard, no confirmed reachability limit (CRITICAL, see #3 below)
ingest_venues_batch, enrich_venues_batch, apply_venue_coords, record_venue_observations, resolve_venue_facts — zero auth.uid() reference anywhere in the body. Detailed in finding #3.
2b. Guarded but should not be anon-reachable (defense-in-depth violation, not currently exploitable)
All of these open with IF auth.uid() IS NULL THEN RAISE EXCEPTION 'Not authenticated' (or an equivalent WHERE user_id = auth.uid() that naturally excludes a NULL caller), verified individually:
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 (hardcoded dev-account UUID check, doubly safe), get_blocked_users, get_club_admin_snapshot, get_club_growth_analytics (both delegate to is_club_admin(p_club_id), itself auth.uid()-scoped), 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_venue_correction, toggle_dm_reaction, unblock_user.
submit_match_score deserves a specific note: it delegates entirely to private.submit_match_score, which does check club_members.role IN ('owner','admin','match_director') AND user_id = auth.uid() before mutating — confirmed anon-safe (an anon caller's auth.uid() is NULL, which matches zero membership rows). This is a different concern from B4's already-filed finding (no status guard against rewriting a COMPLETED match) — that's an authorized-caller abuse, not an anon one.
2c. Unreachable regardless of grant — trigger 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 — all RETURNS trigger. Postgres refuses to invoke a trigger-return-type function directly (ERROR: trigger functions can only be called as triggers) regardless of ACL. Dead attack surface, but still worth revoking for hygiene / to stop misleading future audits.
2d. Intentionally public-shaped (discovery/share surfaces, no PII, correctly no auth check)
get_club_discovery_summaries, get_public_share_preview, is_public_discoverable_club — these read only pre-filtered discoverable/public data (club discovery listings, KakaoTalk share previews). Reasonable to be anon-reachable by design; still technically violate 00209's blanket "no anon on any SECDEF" policy and should either be formally exempted (documented) or converted to SECURITY INVOKER + RLS so the exemption is structural rather than an ACL that looks identical to a mistake.
2e. Utility, no identity binding, low-severity abuse surface
check_rate_limit_for_key — see finding #9.
3. CRITICAL — venue-pipeline RPCs are a second, unguarded front door (escalates B6 #1)
B6 flagged ingest-venues's edge-function token check as fail-open (if (token && req.headers.get('x-ingest-token') !== token) — skips the whole block when INGEST_TOKEN is unset). That finding assumed the edge function is the only path to ingest_venues_batch. It is not.
Live pg_get_functiondef for all 5 venue-pipeline write RPCs shows zeroauth.uid() reference, zero role check, zero anything — the entire security model for these functions is "the ACL is service_role-only," exactly like mark_session_payment_paid_via_portone before 00386. And per finding #1/#2, that ACL premise is false: all 5 are currently anon-EXECUTE.
-- ingest_venues_batch(p_rows jsonb) — INSERT ... ON CONFLICT (import_hash) DO UPDATE
-- enrich_venues_batch(p_rows jsonb) — UPDATE venues SET telephone/coords/fee_schedule/...
-- apply_venue_coords(p_rows jsonb) — UPDATE venues SET latitude/longitude
-- record_venue_observations(p_rows jsonb) — INSERT venue_source_observations
-- resolve_venue_facts(p_venue_ids uuid[]) — recomputes venues.* from observations + venue_correctionsConcretely, today, any client holding only the public anon key can:
POST /rest/v1/rpc/ingest_venues_batchwith an arbitrary JSONB array to insert or upsert (byimport_hashcollision) fabricated venues directly into the live directory — no token, no login, bypassesingest-venuesentirely.POST /rest/v1/rpc/enrich_venues_batch/apply_venue_coordsto overwritetelephone,latitude/longitude,fee_schedule,operating_hours,capabilitieson any existing venue byid.POST /rest/v1/rpc/record_venue_observationsto inject arbitraryprovider/payloadrows intovenue_source_observations, then callresolve_venue_factsto have the resolver's majority-vote/agreement logic fold the forged observations into the venue's resolved facts — a data-poisoning vector distinct from and worse than B6's already-flaggedvenue_correctionsRLS gap, since it operates on the raw observation ledger the resolver trusts as ground truth.
B6's recommended fix (patch the edge function's token check) does not close this. The correct fix is the same idiom as 00386: REVOKE ALL … FROM PUBLIC, anon, authenticated; GRANT EXECUTE … TO service_role; on all 5 functions, in addition to (not instead of) fixing the edge-function token check.
4. CRITICAL — storage: any signed-in user can deface any other user's avatar
storage.objects RLS policies on the avatars bucket (public: true, 5MB limit, image/jpeg|png|webp):
avatars insert own | INSERT | {authenticated} | (no USING) | with_check: (bucket_id = 'avatars')
avatars update own | UPDATE | {authenticated} | (bucket_id = 'avatars') | with_check: (bucket_id = 'avatars')
avatars read | SELECT | {authenticated} | (bucket_id = 'avatars')The policy names ("insert own", "update own") assert an ownership scope that the actual qual/with_check expressions do not implement — there is no (storage.foldername(name))[1] = auth.uid()::text or owner = auth.uid() check anywhere. Compare to the correctly-scoped siblings on the same table: club media insert admin checks club_members role via split_part(objects.name,'/',1) = club_id; dm media storage insert checks is_dm_participant(...). The avatars bucket has no equivalent.
Client-side, packages/app/src/presentation/hooks/mutations/use-upload-avatar.ts:24-25 constructs the storage path entirely client-side and predictably:
const fileName = `${userId}-${Date.now()}.${fileExt}`;
const filePath = `avatars/${fileName}`;The file's own doc comment (lines 15-18) states the intended contract: "Authenticated write access (RLS policy allowing INSERT/UPDATE for auth.uid())" — the policy that was supposed to enforce this was never written; only the bucket-membership check shipped.
Concrete exploit path, no admin/service-role needed:
- Any authenticated user calls
SELECTagainst theavatarsbucket (avatars readpolicy grants this to the wholeauthenticatedrole,bucket_id='avatars'only) to list/enumerate existing avatar object names — including a target victim's exact<uuid>-<epoch>.<ext>key. - That same user calls storage
.update()on that exact key with arbitrary image bytes. Theavatars update ownpolicy's with-check isbucket_id = 'avatars'— passes trivially, no ownership match required. - The victim's
profiles.avatarUrlalready points at that object (public bucket, stable public URL) — the defacement is live app-wide immediately, with no write to any other table required.
This is broadly exploitable (any of the app's real signed-in users, not a privileged role) and needs the same fix pattern as the well-scoped club-media/dm-media policies: add an ownership predicate, e.g. (storage.foldername(name))[1] = auth.uid()::text provided the upload path is changed to embed auth.uid() as the first path segment (not just as a filename substring, which is not RLS-checkable without a regex the way club-gallery-media's split_part(name,'/',1) pattern does it).
5. HIGH (latent) — app_config (holds the plaintext service_role key + cron secret) has anon+authenticated table grants
00102_pg_cron_config_table.sql created public.app_config (key/value store for supabase_url, service_role_key, cron_secret — read by every net.http_post-based cron job via get_app_config()) with the comment "No public policies — only service_role and postgres can read." That's true for RLS policies (zero exist), but the table-level GRANT was never narrowed:
SET ROLE anon;
SELECT * FROM public.app_config; -- returns 0 rows (not an error)
RESET ROLE;information_schema.role_table_grants for app_config:
postgres: INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
service_role: INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
anon: INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER
authenticated: INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGERLive-verified currently safe — RLS is enabled (relrowsecurity = true) with zero policies, which is deny-all for any non-owner role, confirmed by the SET ROLE anon probe above returning 0 rows rather than erroring. But this is one mistake away from a full service-role-key leak: a single future migration that adds any permissive policy on app_config (e.g. someone innocently adding an admin-read policy without checking the pre-existing grant), or a stray ALTER TABLE app_config DISABLE ROW LEVEL SECURITY, would immediately hand anon the plaintext service_role_key and cron_secret — full-admin credentials, no login required. Given the table's contents, it should carry an explicit, narrow GRANT SELECT ON public.app_config TO service_role; with everything else revoked from anon/authenticated, exactly like the convention CLAUDE.md already mandates for product tables — this table is the highest-value target in the schema for exactly that convention.
signals_archive is in the same "RLS-enabled, zero policies" bucket (deny-all, currently safe) but holds no comparable secret — lower priority.
6. HIGH — explicit data-API GRANT convention: 82/88 tables non-compliant
AGENTS.md/CLAUDE.md require: "every new public table needs an explicit data-API GRANT ... never rely on the Supabase default-privilege auto-grant." Full-schema sweep:
SELECT count(*) FROM pg_class WHERE relnamespace='public'::regnamespace AND relkind='r'; -- 88
-- tables where anon has ANY privilege in relacl (i.e. still on the unmodified default):
SELECT count(*) FROM pg_class c WHERE relkind='r' AND EXISTS (
SELECT 1 FROM aclexplode(c.relacl) a JOIN pg_roles r ON r.oid=a.grantee WHERE r.rolname='anon'
); -- 82Only 6 tables have an explicit, narrowed GRANT: administrative_divisions, profile_play_regions, surface_catalog, venue_courts, venue_media, venues. The other 82 — including every table B1/B5 already individually flagged (clubs, club_members, invites, guest_applications, attendance_records, dues, manner_tags) plus everything else in the schema (sessions, matches, rsvps, session_payments, club_growth_events, client_errors, etc.) — carry the raw Supabase default: full arwdDxtm (select/insert/ update/delete/truncate/references/trigger) to both anon and authenticated.
Currently not a proven live exploit: cross-checked every table for RLS status — relrowsecurity = true with ≥1 policy on 86 of 88 tables (the 2 exceptions are app_config and signals_archive, both deny-all, covered above). So for DML reachable via PostgREST (SELECT/INSERT/ UPDATE/DELETE), RLS is the actual gate everywhere, and RLS is present. TRUNCATE/REFERENCES/TRIGGER are not RLS-filtered at all, but are also not exposed by PostgREST's REST/RPC surface (no raw-SQL passthrough to anon/authenticated), so that slice of the over-broad grant is currently inert.
This is nonetheless a real structural gap, for the same reason finding #1 matters: the ACL is the only thing standing between "safe today" and "one RLS policy bug away from a full table leak." 93% non-compliance means the convention is not being followed as a matter of course — it is being followed only on the handful of newest venue-directory tables where an audit-driven author remembered to add it. Recommend either (a) closing the gap schema-wide with a bulk migration (mirrors 00209's approach for functions), or (b) explicitly downgrading the convention from "every table" to "tables carrying secrets/PII" if the team decides RLS-as-primary-gate is an acceptable permanent posture — but the current state is neither: the rule is written as absolute, and compliance is 7%.
7. Edge functions — full inventory
| Function | Auth gate | Fail-open risk | Notes |
|---|---|---|---|
check-push-receipts | x-cron-secret, cronSecret.length===0 || provided!==secret | None — fails closed | service_role only |
enrich-venues | x-ingest-token, !expected || token!==expected | None — fails closed | Correctly written; contrast with sibling below |
export-user-data | Authorization Bearer forwarded to a user-scoped Supabase client | None — 400 if missing | RLS-scoped per caller |
fetch-holidays | none (public read proxy, data.go.kr key stays server-side) | Cost-abuse only (no rate limit found) | No DB writes; LOW |
fetch-living-weather | x-cron-secret, cronSecret.length===0 || provided!==secret | None — fails closed | |
fetch-medium-forecast | same pattern | None — fails closed | |
fetch-weather | same pattern | None — fails closed | |
fetch-weather-warnings | same pattern | None — fails closed | |
ingest-venues | x-ingest-token, if (token && provided!==token) | FAIL-OPEN when INGEST_TOKEN unset (= B6 #1, re-confirmed) | Also see finding #3 — RPC-level bypass regardless |
portone-webhook | Standard-Webhooks HMAC-SHA256 signature + 5-min replay guard | None — correct pattern for a webhook | --no-verify-jwt correct (PortOne sends no Supabase JWT) |
search-venues | rate-limited via user JWT / service-role fingerprint wrapper; reclassify mode requires exact service-key Bearer match | Fails closed (503) if rate-limit infra unavailable | General search intentionally public |
seed-scenario | ENV==='production' block + permissive-by-default Bearer accept unless SEED_ALLOWED_UIDS set | See finding #8 | Dev/QA tool, self-documented tradeoff |
send-push | x-cron-secret, cronSecret.length===0 || provided!==secret | None — fails closed | |
simulate-activity | exact service-key Bearer match | None — fails closed (401) | |
static-map | none (NCP image proxy, keys stay server-side) | Cost-abuse only (no rate limit found) | No DB writes; LOW |
verify-portone-payment | user JWT validated via admin.auth.getUser(token) | None | Independently re-verifies with PortOne server-side |
No function besides ingest-venues repeats the if (secret && …) inversion — it is an isolated instance, not a pattern, among the secret-gated functions. fetch-holidays and static-map have no rate limiting at all (worth a LOW note — both proxy paid/quota-limited external APIs and could be hammered for cost abuse, though neither writes to the DB or leaks anything beyond quota).
8. pg_cron — full inventory (27 active jobs)
| Job | Schedule | Target | Note |
|---|---|---|---|
| 24 | */2 * * * * | send-push | |
| 25 | 0 21 * * * | generate_dues_reminders(); generate_session_reminders() | First half dead — writes to zero-consumer club_alerts (= B5 #13) |
| 26 | 0 0 * * * | simulate-activity?mode=daily | |
| 37 | 0 19 * * * | signals_archive_sweep() | |
| 39 | 0 0 * * * | signals_tick_dues() | |
| 40 | 15 * * * * | auto_advance_session_statuses() | |
| 41 | 15 19 * * * | signals_purge_365d_batched() | |
| 43 | 15 18 * * * | anonymize_withdrawn_profiles() | |
| 44 | 0 17 * * * | expire_stale_marketing_consents() | |
| 45 | */20 * * * * | check-push-receipts | |
| 56 | */15 * * * * | check_cron_health() | self-monitor |
| 58 | 17 4 * * * | prune_telemetry() | |
| 81 | 30 18 * * * | purge_archived_clubs() | |
| 82 | */5 * * * * | check_auto_call(id) for in_progress matches idle >30s | cadence matches the 30s edit-debounce |
| 89 | 20 * * * * | fetch-weather-warnings | |
| 90 | 23 * * * * | fetch-living-weather | |
| 91 | 0 22 * * * | fetch-medium-forecast (evening) | |
| 92 | 0 10 * * * | fetch-medium-forecast (morning) | |
| 93–98 | :15/:18/:21/:24/:27/:30 at 2,5,8,11,14,17,20,23 | fetch-weather batches 0–5 | intentional stagger, not duplicates |
| 99 | 45 * * * * | fetch-weather?mode=aqi-refresh | |
| 101 | */5 * * * * | expire_unpaid_session_holds() | matches the 10-min hold window granularity |
| 102 | 0 * * * * | tick_session_refund_obligations() | |
| 103 | */15 * * * * | signals_tick_session_starting_soon() | |
| 104 | 30 20 * * * | signals_tick_rsvp_deadline_soon() |
All 27 are active = true. No duplicate or orphaned schedules beyond the already-known-dead half of job 25 (cross-ref B5, not re-filed). Cadences are all internally consistent with the windows they enforce. All net.http_post cron jobs correctly source service_role_key/cron_secret from get_app_config() rather than inlining secrets in the job body — good practice, but see finding #5 for the table those secrets live in.
9. Signals residuals / telemetry / storage — remaining scope
club_alertsbridge: confirmed dead end-to-end.packages/app/src/presentation/hooks/index.ts:65-67self-documents: "Club Alerts: hooks removed in Round 14 — zero UI consumers since v2 inception. Port + adapter + entity remain (registry.clubAlerts) so the bell-badge UI can wire fresh hooks when it ships." Live row count = 0. Matches B5 #13 exactly; not re-filed as new, just independently re-verified from the client side (B5 verified from the cron/DB side).- Telemetry PII: spot-checked
client_errorscolumns (app_version,app_dist,platform,route,error_message,error_stack,component_stack,context jsonb,severity) and grepped client error-reporting call sites for email/phone/name literals written intocontext— none found in the sampled call sites. This was a targeted spot-check, not an exhaustive walk of everysignals_emit/client_errorsinsert call site; treat as directionally clean rather than a completed sweep. yarn debug:logs:scripts/debug-logs.mjsexists and is wired to the documented command; not exercised end-to-end in this audit (no finding either way).- Storage —
dm-media/club-gallery-media/club-media: all correctly scoped (is_dm_participant(...),is_club_admin(...)viasplit_part(name,'/',1), or role-checkedclub_membersjoin). Only theavatarsbucket has the ownership gap (finding #4). - No pgTAP/CI regression guard for any of this — see finding #10 in the summary table; the single highest-leverage prevention item from this entire audit is adding one pgTAP test that asserts
SELECT count(*) FROM pg_proc p JOIN aclexplode(...) ... WHERE prosecdef AND rolname IN ('anon','PUBLIC') = 0(with an explicit, reviewed allowlist for the 2d "intentionally public" functions) and wiring it into the same CI gate as the RLS test suite undersupabase/tests/.
Adversarial probes run
- Live
aclexplode(proacl)over every SECDEF function inpublic, cross-joined topg_roles, filtered toanon/PUBLICgrantees — the definitive enumeration, not a static-text guess. - Pulled
pg_get_functiondeffor all 40 anon-executable functions and grepped each body forauth.uid()presence, then manually read the ~15 functions without it to confirm guard-vs-no-guard classification (not just presence/absence of the string). pg_default_acldump to find the actual mechanism, not just observe the symptom — this is what let me correct/extend B5's framing from "the ALTER DEFAULT PRIVILEGES fix has a gap" to "the fix targets the wrong ACL row entirely, and 96 migrations independently made the same mistake."- Grepped every migration that last defined each of the 5 unguarded venue RPCs to confirm the
REVOKE FROM PUBLIC-without-anon idiom was present at creation time (00334/00341/00342/00347/00358/00370/00379/00382/00383) — proves this isn't drift from a since-fixed state, it never worked. SET ROLE anon; SELECT * FROM public.app_config;— live re-enactment rather than trusting the RLS-policy-count inference, to positively confirm the current-safe / latent-risk split.- Full
pg_class.relaclsweep across all 88 public tables, cross-checked againstpg_policiespresence per table to separate "convention violation" from "proven live exploit." - Read the actual client upload code (
use-upload-avatar.ts) against the storage RLS policy text to confirm the avatars gap is reachable from the real app's own code path, not just theoretically malformed SQL. - Compared all 7 secret-gated edge functions' guard conditions side by side to confirm
ingest-venuesis an isolated inversion, not a pattern (directly answers the task's "are there siblings?" question — no).
Canon violations
- AGENTS.md SECDEF discipline ("every SECURITY DEFINER function MUST ... REVOKE EXECUTE FROM PUBLIC + explicit GRANT") — violated by 40 live functions; the stated remediation pattern itself (
REVOKE ... FROM PUBLIC) is insufficient on this Supabase project and should be corrected in the canon doc toREVOKE ... FROM PUBLIC, anonexplicitly, or future authors will keep reproducing finding #1. - CLAUDE.md/AGENTS.md explicit data-API GRANT convention for new tables — 7% compliance schema-wide (finding #6).
packages/app/src/domainPII rule ("never write PII values into JSONB columns") — spot-checked clean, not exhaustively verified (see §9).