Row Level Security

Row level security (RLS) is the database-side authorization layer. Combined with the least-privilege grant model, it ensures that even if a PostgREST request with a browser-role JWT reached the database, it could not read or write sensitive rows.

The RLS strategy

public / anon / authenticated        service_role (the API)
        │                                    │
        │  revoked from every table          │  narrow SELECT/INSERT/UPDATE/
        │  and every rpc_* function          │  EXECUTE grants only
        ▼                                    ▼
   RLS enabled, zero policies         RLS bypassed (service role)
   → deny everything                 → API is the only data path

• Tables are RLS-enabled with zero policies (or a single narrow exception documented below). Supabase's default grants would otherwise leak rows to anon/authenticated; enabling RLS with no policies makes those roles see an empty table even if a grant is ever misconfigured.
• Grants are stripped and re-added narrowly. Each migration revokes all from public, anon, authenticated, service_role and re-grants exactly what the API needs (e.g. select, insert for events; select, insert, update for punishments; never DELETE on retention tables).
• Function EXECUTE is service_role-only (migration 016 lockdown), so PostgREST browser roles cannot call any rpc_* function.

Authenticated vs anonymous access

Role	Table access	RPC access
anon	none (RLS + revoked grants)	none (EXECUTE revoked)
authenticated (browser JWT)	none on production tables; one narrow exception on training_session_members	none
service_role (API client)	narrow per-table grants	EXECUTE on the named RPCs
postgres (owner)	full	n/a

There is no anonymous data path in the application: the only anonymous-adjacent surface is the liveness route (GET /) and the OAuth entry points, which touch no tables.

Table policies

The deliberate exceptions to "zero policies":

training_session_members — own-membership SELECT

-- A browser user may SELECT only their own membership rows.
-- This is the basis for Realtime private-channel authorization.

An authenticated user can see exactly the (session_id, agent_id, role) rows where agent_id equals their JWT sub. This tells the Realtime server which channels they may join without exposing any training content.

Realtime private channels — realtime.messages policies

Private channel authorization for training:session:<uuid> and training:user:<agent_id> is implemented as RLS on realtime.messages (migrations 023, 026, 027):

• Receive policies require session membership (training_session_members) for session channels, or sub = agent_id for personal user channels.
• Migration 027 permits the Realtime server's claims-less authorization-probe INSERT (the probe runs before the JWT is loaded) while preserving the member/owner checks for real writes. The probe branch grants no table reads.
• The policies are recreated idempotently (026) with topic-column checks so default-deny cannot silently regress.

These policies mean a browser JWT can join a session channel only if it is a member of that session, and a user channel only for their own ID — regardless of what client code attempts.

Training policies

Training tables (training_scenarios, training_sessions, training_events, training_notes, training_injections, training_session_scenarios) are RLS-enabled with service-role-only data access. The API is the only writer/reader; browser JWTs get the single membership SELECT above and nothing else. Realtime delivery is authorized by the realtime.messages policies, not by table access.

Event / audit protections

events, admin_audit_log, and admin_health_history are protected three ways:

1. Grants: service_role gets SELECT+INSERT only (no UPDATE/DELETE).
2. RLS: enabled, zero policies.
3. Triggers: UPDATE/DELETE/TRUNCATE raise an exception (append-only).

A browser role cannot read the streams (no grant, no policy); a compromised API client still cannot rewrite history (no UPDATE grant, trigger backup).

Claim guards

The concurrency-critical guards are row locks inside the RPCs, not RLS:

• rpc_claim_action / rpc_discipline_claim — FOR UPDATE SKIP LOCKED, so two bot ticks can never claim the same queue row.
• rpc_report_action — the report row is locked before the status guard, so two racing actions cannot both pass.
• rpc_complete_action / rpc_discipline_complete / rpc_punishment_revoke — locked status guards before terminal transitions.

RLS does not protect these (the service role bypasses RLS by design); the locks + in-transaction re-checks do.

Why service/API authorization and database authorization both matter

The API is the primary authorization boundary — it authenticates the caller and checks clearance, rank, allowlist, and assignment before any data access. The database layer exists because the API is a single process with a powerful credential:

• Defense in depth: if a route forgets a check, or a future endpoint misuses the service key, the RPCs still validate whitelists and authority, and RLS still denies browser roles.
• Least privilege: the service key can do exactly what the application needs — nothing more (no DELETE on retention tables, no EXECUTE on functions the app doesn't call, no table access the app doesn't use).
• The browser never holds data credentials. Sessions are API-side; the only Supabase credential a browser ever sees is the public anon key, which RLS renders useless for data access.

Treat RLS/grant changes as security changes: they require a migration and documentation update (see Maintenance).

Related reading

• RPC layer
• Migrations
• Threat model