Skip to content

Onset DB public exposure — RLS security pass (T5-1 Phase 1)

Status: STOP — Ryan review required before Phase 2 (public hostname)
Date: 2026-08-30
Authorization: T-4 closed (Gate T4-R pass); Ryan authorized T5-1 infra Phase 1 only
Target: VM 119 onset-db (192.168.1.102:8000 Kong, LAN-only today)
Scope: RLS / auth boundary audit before any public DNS or tunnel ingress for Supabase

Verdict

SAFE TO EXPOSE (RLS / patient-data boundary)

No anon-key or cross-user PostgREST leaks were found in live tests. Edge Functions reject unauthenticated invocation. Storage private bucket objects are not readable with anon key alone.

This verdict gates Phase 2 only. It does not authorize iOS pointer flip, archive, or submit.

Ryan must approve this report before Phase 2. Phase 2 still requires Cloudflare proxied ingress (orange cloud) — not present today.

Executive summary

Check Result
Public tables with RLS disabled 0 (all 54 public tables: RLS ON)
RLS policies (public) 76 (matches Gate B parity)
FK constraints public.* → auth.users 25 (audit doc previously cited 32; live count is 25)
Anon key, no JWT → patient tables 0 rows on all tested tables
Patient A JWT → patient B data 0 leaks (incl. user with 23 shared_checkins)
Storage private bucket anon access Denied (404 / empty list)
Storage public templates Intentionally public (18 objects, no PHI filenames)
Edge Functions anon POST 401 on all patient-facing functions (24 deployed)
CF proxy in path for onset-db Not yet (no public hostname; Phase 2 mandatory)

Non-blocking findings (hardening backlog): marketing tables with RLS on but zero policies (default deny — OK); scan-supplement / search-supplement return 500 before auth when service env ordering fails; auth-send-email returns config error body to unsigned callers; gateway verify_jwt = false on all functions (mitigated by in-function auth today).

1. Kong (:8000) surfaces and auth models

Self-hosted Supabase on VM 119 exposes the standard Kong routes via supabase-envoy on :8000:

Surface Path prefix Auth model Patient-data risk
GoTrue /auth/v1/* Sign-up/sign-in/OAuth; requires apikey header (anon or service JWT as apikey) Issues user JWTs; no direct row access
PostgREST /rest/v1/* RLS-enforced per request JWT (Authorization: Bearer <user JWT>) or anon role when no JWT Primary data boundary — must pass RLS
Storage /storage/v1/* Bucket public flag + storage service checks; RLS on storage.objects (no visible pg_policies; API enforces) File access; private bucket tested
Edge Functions /functions/v1/* Gateway JWT verification off (verify_jwt = false); each function validates auth internally Privileged ops if misconfigured
Realtime /realtime/v1/* WebSocket; probe returned 503 (not exposed/healthy on this stack) Low today
Studio / meta Not on public Kong patient path Internal N/A

Smoke (LAN, anon apikey):

Path HTTP
/auth/v1/health 200
/rest/v1/ 403
/storage/v1/bucket 200
/functions/v1/hello 401
/realtime/v1/websocket 503

2. RLS policy audit

2.1 Baseline

  • 54 public base tables: RLS enabled on all (relrowsecurity = true).
  • 76 policies in public (Gate B parity).
  • 25 foreign keys from public → auth.users.
  • 10 policies grant service_role full/manage access (expected for backend/cron/edge).

2.2 Patient-data tables (policy presence)

All core patient/clinic share tables have multiple scoped policies using auth.uid(), consent flags on practitioner_patients, and/or is_clinic_portal_user():

Table Policies Notes
practitioner_patients 4 practitioner_patients_select_shield; clinic scoped
shared_checkins 4 Patient own + clinic consented select
shared_lab_results 4 Patient own + clinic consented select
shared_adherence 2 Consent-gated clinic/patient paths
data_share_events 2 auth.uid() = patient_app_user_id
protocol_assignments 1 Scoped
clinic_protocols 1 is_clinic_portal_user()
onset_lab_results 1 User scoped
onset_protocol_* 1 each User scoped
patient_last1_identities 4 Link table scoped

2.3 Intentionally broader read paths (not leaks)

Table / policy Role Assessment
public_protocols / public_fitness_protocols anon, authenticated Published catalog only (is_published = true)
contact_submissions INSERT public Contact form insert only
clinic_notifications service_role Backend only (USING true)

2.4 Marketing tables (RLS on, zero policies)

10 marketing email tables have RLS enabled and 0 policies:

marketing_subscribers, marketing_broadcasts, marketing_sequences, marketing_sequence_steps, marketing_sequence_enrollments, marketing_broadcast_recipients, marketing_send_queue, marketing_confirm_send_log, marketing_tags, marketing_subscriber_tags

Behavior: PostgreSQL default-deny when RLS is on and no policy matches.

Live test: anon and authenticated JWT → 0 rows on marketing_subscribers, marketing_broadcasts, marketing_sequences.

Recommendation (non-blocking): add explicit service_role-only policies for operability clarity; not required for SAFE verdict.

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

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

3.1 Anon key, no user JWT

Table HTTP Rows Leak?
practitioner_patients 200 0 No
shared_checkins 200 0 No
shared_lab_results 200 0 No
shared_adherence 200 0 No
protocol_assignments 200 0 No
clinic_protocols 200 0 No
onset_lab_results 200 0 No
data_share_events 200 0 No
patient_shares 200 0 No
onset_protocol_items 200 0 No
onset_user_preferences 200 0 No
practitioners 200 0 No
patient_last1_identities 200 0 No
marketing_subscribers 200 0 No

3.2 Cross-user JWT isolation

Test Result
alex.rivera@demo JWT → all patient tables Own practitioner_patients row only; 0 foreign patient_app_user_id
hello@unicornryda.com JWT → shared_checkins 23 rows, all patient_app_user_id = hello
alex.rivera@demo JWT → shared_checkins 0 rows (cannot see hello's 23 check-ins)
curryrg@gmail.com JWT → clinic share tables via PostgREST 0 rows (clinic portal uses service role server-side on VM 118, not browser anon PostgREST — acceptable)

Result: POSTGREST_ISOLATION=PASS

Repro script: scripts/onset/t5-1-rls-security-pass.sh

4. Storage bucket policies

Bucket Public Objects PHI assessment
templates Yes 18 Marketing/protocol template PNGs (templates/posts/*.png); public GET 200 — intentional
clinic-support-attachments No 2 Portal screenshots; not in public bucket

Tests:

Call Result
Anon list clinic-support-attachments HTTP 200, 0 objects
Anon GET private object path HTTP 400/404 "Object not found" (no bytes leaked)
Anon GET storage/v1/object/public/templates/posts/1.png HTTP 200, 95 KB PNG

Note: pg_policies shows 0 rows for storage schema; storage.objects has RLS enabled. Enforcement is via Storage API + bucket flags — tested OK.

5. Edge Function auth (24 functions)

Deployed on VM 119 (/opt/supabase/docker/volumes/functions/):

admin-dashboard, apple-link, auth-send-email, client-event, daily-insight, delete-account, fitness-extract, generate-protocol, google-link, lab-intelligence, last1-connect-assertion, last1-link, last1-unlink, parse-lab, popular-branded-products, practitioner-notifications, protocol-extract, protocol-import, resolve-branded-product, scan-label, scan-supplement, scan-supplement-label, search-supplement, simulate-protocol-lab

(+ hello smoke target)

Gateway

All functions: verify_jwt = false in supabase/config.toml — JWT checked inside function code (documented pattern).

Anon POST {} probe

Function HTTP Notes
All patient/clinic AI/auth functions 401 Unauthorized
protocol-import, fitness-extract 401 Requires x-protocol-import-secret
practitioner-notifications 401 Requires service bearer + notify secret
admin-dashboard 401 Requires admin secret
auth-send-email 500 GoTrue webhook; fails without Standard Webhooks signature + secret
scan-supplement, search-supplement 500 "Server misconfigured" when env check runs before auth path — no data returned

Auth patterns confirmed in source:

  • User JWT via supabase.auth.getUser() — iOS-facing AI/event functions
  • Shared secrets — x-protocol-import-secret, x-notify-secret, x-onset-admin, SEND_EMAIL_HOOK_SECRET (webhook)
  • Link functions — Apple/Google/Last1 linking with bearer validation

Non-blocking: reorder scan-supplement / search-supplement to return 401 before env misconfig 500; restrict auth-send-email error detail on unsigned requests.

6. Rate limiting / abuse posture (pre-exposure)

Control Current state Phase 2 requirement
Public hostname for onset-db None (LAN + Tailscale only) Required
Cloudflared tunnel onset-edge on VM 118 routes clinic.theonset.app only Add second hostname → http://192.168.1.102:8000
Cloudflare proxied (orange cloud) Not in path for Supabase Mandatory at Phase 2 for WAF + rate limits
GoTrue rate limits Self-hosted defaults Monitor after exposure
PostgREST max_rows 1000 (config.toml) Accept

Phase 1 conclusion: CF proxy cannot be confirmed on onset-db today because the endpoint does not exist. Phase 2 must not go live without proxied CNAME.

Recommended hostname (unchanged from T-1/T5-0): onset-db.last1.enterprises as second ingress on existing onset-edge tunnel (avoid coupling patient API to theonset.app marketing zone unless Ryan prefers onset-api.theonset.app).

7. Findings summary

Hard blockers (must fix before exposure)

None identified.

Recommendations (non-blocking)

  1. Add explicit service_role-only policies on marketing tables (clarity).
  2. Fix scan/search supplement functions to fail 401 before 500 on missing env.
  3. Tighten auth-send-email unsigned error response (generic 401).
  4. After Phase 2 cutover, re-run §3 tests against public hostname (same RLS, new path).
  5. Optional: enable gateway verify_jwt = true where functions only use user JWT (defense in depth).

Out of scope (unchanged)

  • iOS Production.xcconfig pointer — not flipped
  • Hosted Supabase rollback net — untouched
  • Clinic sovereign stack — untouched
  • Other product edges (lastvet-edge, last1id-edge, realoutcomes-edge) — untouched

8. Ryan decision gate

Action Owner
Review this report Ryan
Reply T5-1 Phase 2 go to authorize public hostname + tunnel ingress Ryan
Phase 2 deliverable TIER_4_ONSET_T5-1_INFRA_REPORT.md (public endpoint + public smoke)
After Phase 2 Separate T5-1-iOS go for pointer flip + archive + submit

STOP. Do not create public onset-db DNS or tunnel ingress until Ryan approves this RLS pass.

References