Skip to content

SPRINT-7 T5-B — RLS remediation plan (DRAFT ONLY)

Status: APPLIED 2026-08-30 — Phase 1 re-run SAFE TO EXPOSE (rerun report)
Date: 2026-08-30
Prior: SPRINT7_DB_PUBLIC_EXPOSURE_RLS_SECURITY_PASS.md (Phase 1 BLOCK)
Target: VM 103 last1app-db (192.168.1.103:8000)

Executive summary

Phase 1 found RLS ON everywhere, but four identity tables use USING (true) (or equivalent world read) policies inherited from hosted migrations. Anon PostgREST reads 43 phones/emails, 6 staff profiles, 30 org memberships, and 2 unit invite codes.

This plan proposes scoped replacements grounded in how RadioCheck iOS and last1.app actually query Supabase (code audit via sprint clone repos Last-1-Enterprises/RadioCheck, Last-1-Enterprises/last1.app). last1.app server routes use service role and bypass RLS; proposals focus on anon + authenticated JWT clients.

Apply order: B1 → B3 → B2 → B4 (B4 needs companion RPC + small app patch for invite join) → B5 (Edge, parallel but before public ingress).

After Ryan applies: re-run Phase 1 only (scripts/last1app/t5-1-rls-security-pass.sh). Do not start Phase 2 until updated report says SAFE TO EXPOSE.

Code audit sources

Repo Role Auth to Supabase
RadioCheck (iOS) Squad check-ins, units, org roster Anon key + synced Last1 JWT (lib/supabase.ts, lib/auth.ts)
last1.app (monolith) Org admin, staff validation, RC dashboard Service role (server/supabase.ts) — not gated by these policies
radiocheck-web Dashboard org picker JWT; reads own organization_members only

Sensitive writes on users already go through patch_users_row / sync_user_from_last1 RPCs where RLS was tight (lib/radioCheckUserWrites.ts).

B1 — public.users (phones/emails world-readable)

What the app actually needs

Access pattern Who Columns / use Code
Own profile Authenticated self Full row (cadence, unit_id, call_sign, phone, email) hooks/useUnit.ts (.eq('id', user.id)), lib/auth.ts, user store refresh
Same-unit squad roster Unit members Full row today (select('*')) for roster, leaderboard, check-in queue embed hooks/useUnit.ts L47–51, hooks/useCheckIns.ts L36–38 (target_user:users!target_user_id(*)), hooks/useCheckIns.ts L269–272
Org member roster (VSO) Org members UI selects safe columns only (id, display_name, call_sign, rank, xp, profile_photo_url) but PostgREST embed requires row-level SELECT hooks/useOrganization.ts L49–56
Contact discovery Authenticated inviter Matched contacts (phone → user); not a table scan RPC find_contacts_on_app(p_phones) (SECURITY DEFINER) — hooks/useUnitInvites.ts
Signup dedup Pre/post auth Email / call_sign existence check lib/auth.ts L70–74, L206–209 — may break if world read removed without RPC (see below)
Org staff RC dashboard last1.app admins Member call_sign / display via service role last1.app/server/routes.ts — unaffected

Intentional product behavior to preserve: same-unit check-in visibility already works via check_ins RLS; squad mates must remain readable to each other. Cross-unit users (e.g. Ryan → review@) must stay 0 rows.

Ryan decision (org phones): Org co-member SELECT on users grants row-level access (including phone/email) even if the UI only displays call_sign. Options:

  • A (recommended for minimal app change): allow org co-member SELECT with explicit acceptance that org veterans see each other's contact fields.
  • B (tighter): org roster via SECURITY DEFINER RPC returning safe columns only; requires iOS app change to stop embedding users on organization_members.

This draft proposes A plus a backlog item for B.

Current policies (live VM 103, 2026-08-30)

-- Policy: "Anon can read users"
FOR SELECT USING (true);

-- Policy: "Users are viewable by everyone"
FOR SELECT USING (true);

-- (Keep) "Users can insert own record" — WITH CHECK (auth.uid() = id)
-- (Keep) "Users can update own record" — USING (auth.uid() = id)
-- (Keep) "Service role has full access to users" — ALL for service_role

Origin: 20260202_1609_004_last1_auth_integration.sql ("Anon can read users" / "Users are viewable by everyone").

Proposed migration (draft SQL)

Step 1 — helper functions (mirror existing is_member_of_unit pattern):

CREATE OR REPLACE FUNCTION public.shares_unit_with(p_user_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.unit_memberships um_self
    JOIN public.unit_memberships um_peer
      ON um_peer.unit_id = um_self.unit_id
    WHERE um_self.user_id = auth.uid()
      AND um_peer.user_id = p_user_id
  )
  OR EXISTS (
    SELECT 1
    FROM public.users me
    JOIN public.users peer ON peer.unit_id = me.unit_id
    WHERE me.id = auth.uid()
      AND peer.id = p_user_id
      AND me.unit_id IS NOT NULL
  );
$$;

REVOKE ALL ON FUNCTION public.shares_unit_with(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.shares_unit_with(uuid) TO authenticated;

CREATE OR REPLACE FUNCTION public.shares_organization_with(p_user_id uuid)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1
    FROM public.organization_members om_self
    JOIN public.organization_members om_peer
      ON om_peer.organization_id = om_self.organization_id
    WHERE om_self.user_id = auth.uid()
      AND om_peer.user_id = p_user_id
  );
$$;

REVOKE ALL ON FUNCTION public.shares_organization_with(uuid) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.shares_organization_with(uuid) TO authenticated;

Step 2 — replace world-read policies:

DROP POLICY IF EXISTS "Anon can read users" ON public.users;
DROP POLICY IF EXISTS "Users are viewable by everyone" ON public.users;

CREATE POLICY "Users can read own row"
  ON public.users FOR SELECT
  USING (auth.uid() = id);

CREATE POLICY "Users can read squadmates in shared units"
  ON public.users FOR SELECT
  USING (public.shares_unit_with(id));

CREATE POLICY "Users can read co-members in shared organizations"
  ON public.users FOR SELECT
  USING (public.shares_organization_with(id));

Step 3 — signup helpers (if email/call_sign pre-check breaks in QA):

CREATE OR REPLACE FUNCTION public.rc_user_exists_by_email(p_email text)
RETURNS boolean
LANGUAGE sql STABLE SECURITY DEFINER SET search_path = public
AS $$
  SELECT EXISTS (
    SELECT 1 FROM public.users u
    WHERE lower(trim(u.email)) = lower(trim(p_email))
  );
$$;
-- GRANT EXECUTE TO authenticated; wire auth.ts to RPC if direct SELECT fails

Re-tests (Phase 1 script + manual)

Test Expected after fix
Anon GET /rest/v1/users?select=id,phone,email 0 rows
Ryan JWT → all users >0 but only own + same-unit + same-org cohort (not all 43 unrelated)
Ryan JWT → users?id=eq.<review@ user_id> (different unit) 0 rows
Ryan JWT → users?unit_id=eq.<shared unit> or squadmate ids ≥1 rows (same-unit PASS preserved)
Ryan JWT → organization_members embed users(...) for WIN rows with user embed (org roster still works)
iOS: unit roster + pending check-ins screen Manual smoke on device/staging
iOS: org members tab Manual smoke

B2 — public.user_profiles (staff emails, last1_identity_id)

What the app actually needs

Client Reads user_profiles? Scope
RadioCheck iOS No direct .from('user_profiles') Identity via sync_user_from_last1 RPC / Edge
radiocheck-web No —
last1.app server Yes Service role — staff onboarding, is_system_admin, staff roles (server/last1OAuthRoutes.ts, server/validation-engine/staff-validation.ts, server/storage.ts)

JWT clients do not need world read. Staff directory for org admins is served through last1.app API (service role), not PostgREST from mobile.

Optional future: org staff viewing peer staff_role / email_verified via PostgREST join — would need org-scoped SELECT (similar to B3), not USING (true).

Current policy (live)

-- Policy: "Profiles are viewable by everyone"
FOR SELECT USING (true);

-- (Keep) "Users can update own profile"
FOR UPDATE USING (user_id::text = auth.uid()::text);

Origin: 20260203_173000_enable_rls_security_fix.sql.

Proposed migration (draft SQL)

DROP POLICY IF EXISTS "Profiles are viewable by everyone" ON public.user_profiles;

CREATE POLICY "Users can read own profile"
  ON public.user_profiles FOR SELECT
  USING (user_id = auth.uid());

-- Optional — only if a JWT client must read org staff profiles directly:
CREATE POLICY "Org co-members can read staff profiles in shared org"
  ON public.user_profiles FOR SELECT
  USING (
    EXISTS (
      SELECT 1
      FROM public.organization_members om_self
      JOIN public.organization_members om_peer
        ON om_peer.organization_id = om_self.organization_id
      WHERE om_self.user_id = auth.uid()
        AND om_peer.user_id = user_profiles.user_id
    )
  );

Recommendation: start with self-only SELECT; add org policy only if QA finds a broken JWT path.

Re-tests

Test Expected
Anon GET /rest/v1/user_profiles?select=* 0 rows
Ryan JWT → all user_profiles 1 row (own) unless org policy added
last1.app staff validation / WIN admin routes unchanged (service role)

B3 — public.organization_members (full org/role map)

What the app actually needs

Access Who Code
List members of my org(s) Org member hooks/useOrganization.ts L49–56 — .eq('organization_id', resolvedOrgId)
Own membership rows Self radiocheck-web/src/pages/Dashboard.tsx — .eq('user_id', userId)
Join / leave / admin CRUD Self or org admin hooks/useOrganization.ts, RPC join_organization
last1.app org admin Service role server/routes.ts, staff-validation.ts

No anon or global JWT need. A user should see memberships only for organizations they belong to (plus their own user_id rows across orgs).

Current policy (live)

-- Policy: "Members can view org memberships"
FOR SELECT USING (true);

(INSERT/DELETE policies are already scoped; live DELETE policy has a known typo om.organization_id = om.organization_id — fix separately if delete misbehaves.)

Proposed migration (draft SQL)

DROP POLICY IF EXISTS "Members can view org memberships" ON public.organization_members;

CREATE POLICY "Members can view memberships in their organizations"
  ON public.organization_members FOR SELECT
  USING (
    user_id = auth.uid()
    OR EXISTS (
      SELECT 1
      FROM public.organization_members om
      WHERE om.organization_id = organization_members.organization_id
        AND om.user_id = auth.uid()
    )
  );

Re-tests

Test Expected
Anon GET /rest/v1/organization_members 0 rows
Ryan JWT → all organization_members only rows for orgs Ryan belongs to (not all 30)
Ryan JWT → WIN org members list + user embed ≥1 rows with nested users
Unrelated user JWT → WIN organization_id=eq.<win> 0 rows unless member
iOS org tab / radiocheck-web dashboard Manual smoke

B4 — public.units (invite_code exposed)

What the app actually needs

Access Who Needs invite_code? Code
Unit detail / settings Unit member or leader Yes (share code with squad) hooks/useUnit.ts L31–35 select('*')
My squads list Member / leader / creator Often yes on unit card hooks/useUserUnits.ts L52–57
Join by code Authenticated non-member Lookup by code once, then join lib/joinUnitByInvite.ts L10–15, hooks/useUnit.ts L119–124
Managed units Leader Yes hooks/useManagedUnits.ts
Demo silo (Apple review) Demo user Scoped demo units can_view_demo_data() already in live policy

Problem: Current "Units are viewable by everyone" lets anon enumerate all units + invite_code (BC727972, etc.). Tight RLS on units breaks join-by-code unless lookup goes through a SECURITY DEFINER RPC (cannot express "filter by invite_code in query" safely in RLS alone).

Current policy (live)

-- Policy: "Units are viewable by everyone"
FOR SELECT USING (
  ((COALESCE(is_demo, false) = false) AND (demo_visibility_user_id IS NULL))
  OR (demo_visibility_user_id = auth.uid())
  OR can_view_demo_data()
);

Note: demo clause is correct; breadth is wrong (any role including anon passes when not demo-gated).

Proposed migration (draft SQL)

Step 1 — member-scoped unit SELECT (no invite enumeration):

DROP POLICY IF EXISTS "Units are viewable by everyone" ON public.units;

CREATE POLICY "Unit members and leaders can view their units"
  ON public.units FOR SELECT
  USING (
    public.is_member_of_unit(id)
    OR leader_id = auth.uid()
    OR created_by = auth.uid()
    OR (
      ((COALESCE(is_demo, false) = false) AND (demo_visibility_user_id IS NULL))
      OR (demo_visibility_user_id = auth.uid())
      OR public.can_view_demo_data()
    )
  );

Step 2 — invite join RPC (replaces client table scan):

CREATE OR REPLACE FUNCTION public.join_unit_by_invite_code(p_invite_code text)
RETURNS public.units
LANGUAGE plpgsql SECURITY DEFINER SET search_path = public
AS $$
DECLARE
  v_unit public.units;
  v_uid uuid := auth.uid();
  v_code text := upper(trim(p_invite_code));
BEGIN
  IF v_uid IS NULL THEN
    RAISE EXCEPTION 'Not authenticated';
  END IF;
  IF v_code = '' THEN
    RAISE EXCEPTION 'Missing invite code';
  END IF;

  SELECT * INTO v_unit
  FROM public.units u
  WHERE upper(u.invite_code) = v_code
    AND u.status = 'active'
  LIMIT 1;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Invalid invite code';
  END IF;

  INSERT INTO public.unit_memberships (user_id, unit_id, role)
  VALUES (v_uid, v_unit.id, 'member')
  ON CONFLICT (user_id, unit_id) DO NOTHING;

  PERFORM public.patch_users_row(v_uid, jsonb_build_object('unit_id', v_unit.id));

  RETURN v_unit;
END;
$$;

REVOKE ALL ON FUNCTION public.join_unit_by_invite_code(text) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION public.join_unit_by_invite_code(text) TO authenticated;

Step 3 — companion app change (RadioCheck repo, not applied here):

File Change
lib/joinUnitByInvite.ts Replace .from('units').ilike('invite_code', ...) with supabase.rpc('join_unit_by_invite_code', { p_invite_code: code })
hooks/useUnit.ts joinUnit Same RPC

Leaders still read invite_code via member-scoped SELECT on units.

Re-tests

Test Expected
Anon GET /rest/v1/units?select=id,invite_code 0 rows
Ryan JWT → all units only units Ryan belongs to / leads (not all 2+)
Anon / random JWT → enumerate invite codes 0 rows
Authenticated RPC join_unit_by_invite_code('VALID') success; membership created
Authenticated RPC with garbage code error, no leak of other codes
iOS: join squad + unit home shows code for leaders Manual smoke

B5 — Edge Functions: sms-cron / org-sms-cron anon POST → 200

What the app actually needs

These are pg_cron / internal workers, not user-facing endpoints. They use service role inside the function. They must not be callable from the public internet without a shared secret.

Reference implementation: check-in-proof-events already gates on Authorization: Bearer <RADIOCHECK_CRON_SECRET> (RadioCheck/supabase/functions/check-in-proof-events/index.ts — clone path; live on VM 103).

Current state: sms-cron/index.ts and org-sms-cron/index.ts have no inbound auth (T2-1 probe: anon POST → 200, SMS pipeline runs). RADIOCHECK_CRON_SECRET is already in edge .env (T2-1 vault).

Proposed change (draft — function code, not DB policy)

Add to top of handler in both functions (mirror check-in-proof-events):

const cronSecret = Deno.env.get('RADIOCHECK_CRON_SECRET')?.trim();
const inbound = req.headers.get('Authorization')?.replace(/^Bearer\s+/i, '').trim() ?? '';
if (!cronSecret || inbound !== cronSecret) {
  return json({ error: 'Unauthorized' }, 401);
}

pg_cron: Ensure scripts/last1app/t2-1-activate-crons.sh (or equivalent) sends Authorization: Bearer $RADIOCHECK_CRON_SECRET when calling http://192.168.1.103:8000/functions/v1/sms-cron and .../org-sms-cron.

Optional hardening: Cloudflare/WAF deny /functions/v1/sms-cron and /functions/v1/org-sms-cron except cron source IP (belt-and-suspenders).

Re-tests

Test Expected
Anon POST /functions/v1/sms-cron {} 401
Anon POST /functions/v1/org-sms-cron {} 401
POST with Authorization: Bearer $RADIOCHECK_CRON_SECRET 200 (or 500 if Twilio/APNs missing — not 401)
pg_cron job after activation Still 200 with secret header

Suggested migration file (for Ryan to apply)

Create in RadioCheck repo (example name only — do not apply from agent):

supabase/migrations/20260830120000_t5b_rls_identity_hardening.sql

Contents: B1 helpers + policies, B2, B3, B4 RPC + policies in one transaction. Deploy Edge auth patch separately or in same release branch.

Apply checklist (Ryan)

# Action Owner
1 Review org co-member phone exposure decision (B1 option A vs B) Ryan
2 Apply SQL migration on VM 103 (fresh morning; snapshot/backup first) Ryan
3 Deploy Edge cron auth patch + verify pg_cron headers Ryan
4 Merge RadioCheck iOS RPC join change (B4) if included in release Ryan
5 Re-run t5-1-rls-security-pass.sh → expect anon leaks 0 Agent (after Ryan says go)
6 Manual iOS smoke: squad roster, check-ins, org tab, join-by-code Ryan
7 If PASS → authorize Phase 2 separately (T5-B Phase 2 go) Ryan

STOP

No policies were changed in this session. This document is analysis + proposed SQL only.

Do not expose api.last1.app until Phase 1 re-run passes after remediation.

References