SPRINT-7 T5-B Phase 1 — RLS security pass (POST-REMEDIATION)¶

Date: 2026-08-30 (re-run 23:25 UTC)
Status: SAFE TO EXPOSE (Phase 1) — Ryan review before Phase 2
Target: VM 103 last1app-db (192.168.1.103:8000)
Remediation: B1 Option A applied + B2–B5 + org_members recursion hotfix
Prior (pre-fix): SPRINT7_DB_PUBLIC_EXPOSURE_RLS_SECURITY_PASS.md (BLOCK)

Verdict¶

SAFE TO EXPOSE (Phase 1 gate)¶

Identity-table anon leaks are closed. JWT reads are scoped (own + same-unit + same-org co-members). Cross-org / cross-unit denial holds. Edge cron endpoints reject anon POST.

Phase 2 (api.last1.app tunnel + CF proxy) is NOT authorized in this message. Ryan reads row counts below first.

Applied fixes (2026-08-30)¶

Fix	What changed
B1 users (Option A)	Dropped `USING(true)` policies. SELECT: own row + `shares_unit_with()` + `shares_organization_with()`. Added `rc_user_exists_by_email`, `rc_call_sign_taken` RPCs.
B2 user_profiles	Dropped world-read. Self-only SELECT (`user_id::uuid = auth.uid()`).
B3 organization_members	Membership-scoped SELECT via `is_member_of_organization()` (SECURITY DEFINER; fixes 42P17 recursion).
B4 units	Member/leader/creator/demo-scoped SELECT. Added `join_unit_by_invite_code` RPC.
B5 Edge	`sms-cron` + `org-sms-cron` require `Authorization: Bearer $RADIOCHECK_CRON_SECRET`.
iOS (archive clone)	`joinUnitByInvite.ts`, `useUnit.ts` → RPC join; `auth.ts` → email/call_sign RPCs.

SQL: scripts/last1app/migrations/20260830120000_t5b_rls_identity_hardening.sql
Hotfix: scripts/last1app/migrations/20260830121500_t5b_fix_org_members_rls_recursion.sql

1. Anon + no JWT — identity tables (HARD GATE)¶

Table	Rows returned	Expected	Result
`users`	0	0	PASS
`user_profiles`	0	0	PASS
`organization_members`	0	0	PASS
`units`	0	0	PASS

(Pre-fix: 43 / 6 / 30 / 2 respectively — all BLOCK.)

Other anon probes (unchanged PASS): check_ins, org_check_ins, org_sms_*, unit_messages, last1_* → 0 rows each.

Anon organizations: 11 rows — public org directory by design (not an identity leak gate).

2. Cross-user JWT — isolation vs Option A¶

Test users (minted JWTs):

Role	User	ID
User A	Ryan (+18186932921)	`c6f84fd2-ba39-4fec-828b-aa2287507ed6`
User B (isolated)	+15203532111	`1aaaedbb-51d4-49ff-9193-aff3eb9d8751`
WIN co-member A	ryan@warriorsinneed.org (example pair)	used for Option A proof
WIN peer	michelle@warriorsinneed.org	`a1ecc736-e0aa-4922-9483-3197c86de3cc`

2.1 Must be zero (no shared org/unit)¶

Test	Rows	Pass?
User A JWT → `users?id=eq.<User B>` (phone/email)	0	PASS
User A JWT → `check_ins?target_user_id=eq.<User B>`	0	PASS
User A JWT → unrelated-org user row	0	PASS
User A JWT → all `organization_members`	0 (vs 30 total in DB)	PASS

2.2 Must NOT be world-readable¶

Test	Rows	Pass?
User A JWT → `users` (limit 100)	4 of 43 total users	PASS (not all-users leak)

2.3 Option A — org co-members see contact (by design)¶

Test	Rows	Pass?
WIN org member JWT → co-member `users` row (phone/email)	1	PASS

Org 7b975ed9-373e-4874-b8b1-88e1950027b5 (Warriors In Need): co-member read confirmed.

2.4 Same-unit visibility preserved¶

Test	Rows	Pass?
User A JWT → same-unit squadmate `users` row	1	PASS
User A JWT → same-unit squadmate `check_ins`	4	PASS
User A JWT → all `check_ins` (incl. unit peers)	39	By design

3. Edge functions — anon POST¶

Function	HTTP	Expected	Result
`sms-cron`	401	401	PASS
`org-sms-cron`	401	401	PASS

Body: {"error":"Unauthorized"}

4. Artifacts¶

Artifact	Path
Re-run JSON	VM 103 `/tmp/last1app-rls-pass-post-t5b.json`
Re-run script	`scripts/last1app/t5-1-rls-security-pass.sh`
Apply script	`scripts/last1app/apply-t5b-rls-remediation.sh`
iOS patches (audit clone)	`/tmp/sprint7-audit/RadioCheck/lib/joinUnitByInvite.ts`, `hooks/useUnit.ts`, `lib/auth.ts`

STOP¶

Phase 1 re-run: SAFE TO EXPOSE.

Ryan: review row counts above. Do not authorize Phase 2 (api.last1.app public ingress) until you reply T5-B Phase 2 go.

Note: iOS RPC join + signup RPC changes are in the audit clone only; ship with tonight's RadioCheck archive before cutover testing on device.