Skip to content

SPRINT-7 DB public exposure — RLS security pass (T5-B Phase 1)

Status: STOP — Ryan review required. Do NOT expose api.last1.app.
Date: 2026-08-30
Authorization: T5-A closed; Ryan authorized T5-B Phase 1 only
Target: VM 103 last1app-db (192.168.1.103:8000 Kong, LAN-only today)
Pattern: Onset T5-1 Phase 1 (ONSET_DB_PUBLIC_EXPOSURE_RLS_SECURITY_PASS.md)

Verdict

BLOCK — not safe to expose

PostgREST returns veteran phone numbers, emails, org membership, and unit invite codes to the anon key without a user JWT. That is a hard stop before any public api.last1.app ingress.

Check-in row isolation for non-unit peers is PASS. Same-unit visibility and org-staff cohort rules appear intentional. The blocker is world-readable identity tables, not check_ins RLS for unrelated users.

Ryan must review this report (especially §3 cross-user/anon row counts) before Phase 2. Phase 2 (api.last1.app tunnel + CF proxy) is not authorized.

Executive summary

Check Result
Public tables with RLS disabled 0 (135/135 public base tables: RLS ON)
Zero-policy tables 0
Anon → check_ins / org SMS / last1_* data tables 0 rows (PASS)
Anon → users (phone/email) 43 rows (BLOCK)
Anon → user_profiles 6 rows incl. WIN staff emails + last1_identity_id (BLOCK)
Anon → organization_members 30 rows (BLOCK)
Anon → units (incl. invite_code) 2 rows (BLOCK)
Anon → organizations 11 rows (directory by design; includes contact emails)
Cross-user check_ins (non-unit) 0 rows (PASS)
Cross-user check_ins (same unit) 12 rows (intentional unit visibility)
Realtime publication 24 tables incl. check_ins, unit_messages, users
Realtime live WS RLS probe Inconclusive (tooling); PostgREST RLS parity assumed
Storage profile-photos Public bucket; profile images only (1 object)
Storage unit-chat-images Public flag but RLS-scoped SELECT; anon list 0 objects
Edge gateway verify_jwt Global false; cron/SMS functions callable anon → 200 (pre-exposure hardening)

Live test users (real data on R940): 43 public.users, 20 with check-in history, 75 total check_ins.

1. Kong (:8000) attack surface

Self-hosted Supabase on VM 103 exposes standard Kong routes on :8000 (today LAN / Tailscale only).

Surface Path Auth model Sensitive-data risk
GoTrue /auth/v1/* Anon or service JWT as apikey; user JWT from sign-in Issues tokens; no direct table reads
PostgREST /rest/v1/* RLS per Authorization JWT (anon role if absent) Primary boundary — failed for identity tables
Storage /storage/v1/* Bucket public flag + storage.objects RLS Profile photos public; unit chat scoped
Edge Functions /functions/v1/* Gateway JWT off (FUNCTIONS_VERIFY_JWT=false); in-function auth varies Cron/SMS endpoints reachable
Realtime /realtime/v1/* WebSocket + JWT; uses same RLS as DB for postgres_changes Exposed; subscribes to 24 tables

LAN smoke (anon apikey, 2026-08-30):

Path HTTP Notes
/auth/v1/health 200
/auth/v1/settings 200 Providers: phone/twilio, google, apple (T5-A)
/rest/v1/ 403 Root denied
/storage/v1/bucket 200 Lists buckets
/functions/v1/hello 500 Staging artifact absent
/realtime/v1/websocket 400 Upgrade required (service up, not 503)

22 Edge Functions deployed (+ main, _shared): see §6.

2. RLS policy audit

2.1 Baseline

  • 135 public base tables: RLS enabled on all.
  • 0 tables with RLS off.
  • 0 tables with RLS on and zero policies.

2.2 Sensitive tables (requested scope)

Table RLS Policies Assessment
check_ins ON 8 Scoped: target user, unit members, org staff cohort
org_check_ins ON 2 Anon 0 rows
users ON 5 USING (true) ×2 — world-readable phones/emails
user_profiles ON 2 USING (true) — staff emails, last1_identity_id
units ON 4 Broad SELECT; exposes invite_code
org_sms_threads / org_sms_messages ON 2 each Anon 0 rows
unit_messages ON 2 Anon 0 rows
organizations ON 3 Public directory (is_demo filter)
organization_members ON 3 USING (true) on SELECT
beneficiaries ON 1 Anon 0 rows
last1_* (9 tables audited) ON 1–3 each Anon 0 rows on all tested
sms_outbound / sms_check_ins ON 2 Anon 0 rows

Migration origin (intentional on hosted, incompatible with public anon exposure):

  • 20260202_1609_004_last1_auth_integration.sql — "Anon can read users" / "Users are viewable by everyone"
  • 20260203_173000_enable_rls_security_fix.sql — "Profiles are viewable by everyone"
  • 20260421234500_silo_demo_units_for_apple_review.sql — org/unit public SELECT for demo silo

2.3 Permissive USING (true) policies (hard-block subset)

Table Policy Risk
users Anon can read users All veteran phone numbers
users Users are viewable by everyone Duplicate world read
user_profiles Profiles are viewable by everyone Staff email, Last1 identity bridge
organization_members Members can view org memberships Maps users ↔ orgs + roles

3. Cross-user / anon live tests (HARD GATE)

Executed on VM 103 against http://127.0.0.1:8000 with production anon key from /opt/supabase/docker/.env and JWTs minted with stack JWT_SECRET (same verification as GoTrue).

3.1 Anon key, no user JWT

Table Rows returned Leak?
check_ins 0 No
org_check_ins 0 No
org_sms_threads 0 No
org_sms_messages 0 No
unit_messages 0 No
direct_messages 0 No
last1_organizations 0 No
last1_programs 0 No
last1_beneficiaries 0 No
sms_outbound 0 No
users 43 YES — phones/emails
user_profiles 6 YES — PII
organization_members 30 YES
units 2 YES — invite codes
organizations 11 Directory (by design)

Sample anon users payload (redacted pattern): includes +18186932921, +13232136573, michelle@warriorsinneed.org, etc.

3.2 Cross-user JWT tests (real accounts)

Test Result Pass?
User A c6f84fd2-… (+18186932921, Ryan) JWT → check_ins WHERE target_user_id = User B 169082e7-… (review@radiocheck.app) 0 rows PASS
User B JWT → check_ins WHERE target_user_id = User A 0 rows PASS
User A JWT → check_ins WHERE target_user_id = User C a3cbfcca-… (jrdelacruz, same unit 8c022fa7-…) 12 rows By design (unit member visibility)
User A JWT → all check_ins (limit 100) 39 rows, 0 foreign to allowed unit cohort PASS
User A JWT → users table 43 rows (all users' phones/emails) FAIL
Anon → users 43 rows FAIL

Specific wording (Ryan-requested format):

User Ryan Curry (c6f84fd2-ba39-4fec-828b-aa2287507ed6, +18186932921) with valid JWT attempted to read user review@radiocheck.app's (169082e7-8183-469f-9008-826cc705e1b5) check_ins → 0 rows returned. PASS.

User Ryan Curry with valid JWT attempted to read user jrdelacruz.a@gmail.com's (a3cbfcca-7a12-45cd-a544-7026f27dcd9f, same unit) check_ins → 12 rows returned. Intentional (shared unit H&S_ISC).

Anon key (no JWT) attempted to read check_ins → 0 rows. PASS.

Anon key attempted to read users (phone/email) → 43 rows. BLOCK.

3.3 Warriors In Need (organizations)

Test Rows Notes
Anon → organizations 11 Includes WIN + demo/smoke orgs; contact_email visible
Ryan JWT → WIN 7b975ed9-373e-4874-b8b1-88e1950027b5 1 Expected (Ryan is org admin)
Anon → organization_members 30 Exposes user↔org mapping without auth

Cross-org denial on check-ins: unrelated users outside unit/cohort rules → 0 rows (PASS). Org directory and membership tables are not scoped to membership for SELECT.

4. Realtime RLS

4.1 Exposure

  • Realtime WebSocket endpoint responds (HTTP 400 without Upgrade, not 503).
  • supabase_realtime publication includes 24 tables, including:
  • check_ins, unit_messages, unit_message_reads, unit_typing_status
  • users, organizations, org_check_ins, org_sms_messages
  • direct_messages, silence_alerts, sms_check_ins, etc.

RadioCheck iOS subscribes to check-in and unit thread channels over Realtime.

4.2 RLS parity

Supabase Realtime postgres_changes enforces the same RLS as PostgREST for the subscribed role/JWT.

Inference Basis
Cross-user non-unit check-in streams should be blocked PostgREST check_ins cross-user tests 0 rows
Same-unit check-in visibility PostgREST returns unit peers' rows by policy
users channel risk If app subscribes to users changes, world-readable RLS would broadcast profile/phone updates broadly

4.3 Live WebSocket probe

Attempted Phoenix phx_join on check_ins filter target_user_id=eq.<review> via wscat (SSH tunnel to VM 103). No reliable response captured (tooling/timeouts). Not a PASS sign-off for Realtime in isolation.

Recommendation before exposure: after fixing §3 blockers, re-run Realtime subscription test with user A JWT on user B's filter and confirm no change events (or join error).

5. Storage

Bucket DB public Objects Anon access test
profile-photos true 1 (…/profile.jpg) GET 200 (148 KB) — expected public profile image
unit-chat-images true 0 Anon list → []; SELECT policy requires unit membership folder

storage.objects RLS policies:

  • Unit members can view unit chat images — SELECT scoped to caller's users.unit_id folder
  • Profile upload/update/delete scoped to auth.uid() folder

Assessment: Profile photos intentionally public (no PHI beyond avatar). Unit chat bucket flag is public but object RLS blocks anon listing/reads today (no objects present). Re-test after unit chat media exists.

6. Edge Function gateway auth (T2-1)

Global: FUNCTIONS_VERIFY_JWT=false (all functions). T2-1 documented per-function models (SPRINT7_T2-1_REPORT.md).

Anon POST {} probe (2026-08-30):

Function HTTP Auth behavior
delete-account 401 In-function JWT ✓
quick-check-in 400 Before auth (action validation)
stripe-checkout / stripe-portal 400 Body validation before auth
sync-user-from-last1 401 HMAC ✓
revoke-from-last1 401 HMAC ✓
check-in-proof-events 401 Cron secret ✓
stripe-webhook 400 Missing signature ✓
sms-webhook (form, no sig) 403 Twilio signature ✓
sms-cron 200 No gateway auth; runs SMS pipeline (sent:0)
org-sms-cron 200 No gateway auth
sms-checkin 200 Twilio XML path (review before public)
verify-org-email 200 Returns {success:false} (no JWT gate)
trigger-check-ins / silence-engine 500 Missing APNs (no data leak)

Pre-exposure requirement: restrict cron/internal functions to LAN/cron bearer or enable gateway JWT per T2-1 recommendations before api.last1.app (not a PostgREST RLS blocker, but must be fixed for public Edge exposure).

7. Hard blockers (must fix before Phase 2)

# Finding Evidence
B1 users world-readable (anon + any authenticated JWT) Anon 43 rows with phone/email
B2 user_profiles world-readable Anon 6 rows; WIN staff emails + last1_identity_id
B3 organization_members world-readable Anon 30 rows
B4 units expose invite codes to anon Anon 2 rows incl. invite_code=BC727972

Required remediation (policy direction)

  1. Drop or replace USING (true) on users, user_profiles, organization_members with auth.uid()-scoped (or unit/org-scoped) SELECT.
  2. Restrict units SELECT: hide invite_code from anon/authenticated non-members (view or column-level guard).
  3. Re-run §3 tests until anon users = 0 and cross-user phone reads = 0.
  4. Harden Edge cron endpoints before public ingress (parallel track).

Non-blockers (documented)

  • organizations public directory (11 rows) — product choice; confirm WIN contact_email exposure acceptable or narrow columns.
  • Same-unit check_ins visibility (12 rows Ryan→JRD) — intentional squad feature.
  • GoTrue SMTP still deferred (T5-A).
  • Realtime WS probe inconclusive — re-test after B1–B4 fixed.

8. Ryan decision gate

Action Owner
Review this report (especially §3 row counts) Ryan
Authorize policy migration to fix B1–B4 Ryan
Re-run Phase 1 after fixes → expect SAFE TO EXPOSE Agent
Reply T5-B Phase 2 go only after updated PASS report Ryan

STOP. Do not create public api.last1.app DNS/tunnel ingress until blockers are remediated and Ryan approves a SAFE TO EXPOSE verdict.

References