Supabase Row Level Security (RLS) Data Isolation

RLS is enforced at the database, keyed off the caller's verified JWT. The Worker chooses which client to use, and that choice decides whether RLS applies:

                 Authorization: Bearer <user JWT>
iOS ──REST──▶ Worker (jose verifies sig + iss + aud) ──┐
                                                       │
        ┌──────────────────────────────────────────────┴─────────────────────────┐
        │                                                                          │
        ▼  userClient(anon key + user JWT)                    serviceClient(service_role) ▼
   PostgREST runs as role=authenticated, sub=<uid>       PostgREST runs as role=service_role
        │                                                                          │
        ▼                                                                          ▼
 ┌───────────────────────────────────────────┐                   ┌──────────────────────────┐
 │ Postgres — RLS ENFORCED, evaluated per row  │                   │ RLS BYPASSED               │
 │   using ( (select auth.uid()) = owner_id )  │                   │ system operations ONLY:    │
 │   with check ( (select auth.uid()) = owner_id )                 │ anon sign-in, profile init,│
 │ → caller sees / writes ONLY its own rows    │                   │ public-table upsert, cleanup│
 └───────────────────────────────────────────┘                   └──────────────────────────┘

Defense in depth — three independent guards, any one of which stops an IDOR:

The point of layer 3: layers 1–2 live in code you can get wrong. RLS lives in the database and is evaluated unconditionally. It is the reason a single missed WHERE clause is a no-op instead of a breach.

Client roles (the split that decides whether RLS applies):

1. Schema + policies

• profiles.id is uuid primary key references auth.users(id) on delete cascade (reuses the auth id)
• Business tables carry owner_id uuid not null references profiles(id) on delete cascade
• Child tables derive ownership through the parent (no redundant owner_id)
• alter table ... enable row level security; on every user-data table
• Every policy: (select auth.uid()) wrapped · TO authenticated · child tables use id in (select ...)
• Every writable policy has both using and with check
• Every column a policy touches (owner_id, parent id) is indexed
• No policy references user_metadata (client-writable); authz uses only auth.uid() / app_metadata
• Policies written idempotently (drop policy if exists then create); npm run db:push applies cleanly

2. Clients + routes

• Per-user reads/writes use userClient(env, accessToken) — never serviceClient
• serviceClient used ONLY for system ops (anon sign-in, profile init, public-table upsert, cleanup)
• owner_id is injected from authUser.userId, never read from the request body
• service_role-backed endpoints that return lists manually strip other users' identifying fields (RLS does not apply under service_role)

3. Public read-only tables

• Public table has a for select to anon, authenticated using (true) policy
• No insert/update/delete policy for anon / authenticated
• All writes go through a service_role endpoint that validates field-by-field
• Removals are soft (active = false), not hard deletes

4. Isolation test (must pass before shipping)

• Two different users' tokens cannot SELECT each other's rows (empty, not the data)
• A cross-owner UPDATE/DELETE affects 0 rows
• A cross-owner INSERT (forged owner_id) returns 403
• Test is run against PostgREST directly, not only through your backend

1. RLS enabled but no policy → everything is denied (or the reverse: a policy but RLS off)

Symptom: after enable row level security, every query returns empty even for the owner. Or: policies exist but data still leaks.

Cause: enable row level security with no policy denies all access to non-owning roles (default-deny). Conversely, writing policies but forgetting enable row level security means the policies are dormant and the table is wide open.

Fix: always pair them — enable row level security and at least one policy per verb you allow. Verify with select relrowsecurity from pg_class where relname = 'items'; (must be t).

2. using (true) on a user-data table → full leak

Symptom: every user sees every row.

Cause: a permissive using ( true ) (often copied from a public-table policy, or left in as a "temporary" open policy) applied to a user-owned table.

Fix: using ( true ) belongs only on genuinely public read-only tables. User-data policies must always predicate on (select auth.uid()). Grep your migrations for using ( true ) / using (true) and confirm each is on a public table.

3. Policy with no TO role → applies to anon too

Symptom: a logged-out client (anon key only) can read rows it should not.

Cause: omitting TO authenticated, so the policy applies to all roles including anon.

Fix: add TO authenticated to every user-data policy. Only public read-only tables should list TO anon (and then for select only).

4. with check omitted on a writable policy → owner reassignment

Symptom: a user can UPDATE their own row and change its owner_id to another user's, "giving away" or planting a row.

Cause: an UPDATE (or for all) policy with only using and no with check. using gates which rows are targeted; only with check validates the new values.

Fix: every writable policy needs with check ( (select auth.uid()) = owner_id ) in addition to using.

5. service_role used for per-user reads → RLS silently bypassed

Symptom: a list endpoint returns everyone's rows; no error, no leak warning.

Cause: the route used serviceClient (which bypasses RLS) for a per-user query, so the isolation you thought RLS gave you never ran.

Fix: per-user data always goes through userClient. Reserve serviceClient for system ops. Any endpoint that legitimately uses serviceClient and returns data to clients (feeds built server-side) must manually filter/strip other users' fields — RLS will not save you there.

6. Bare auth.uid() (not wrapped) → per-row re-evaluation

Symptom: queries on a large table are slow; EXPLAIN shows the auth function called per row.

Cause: using ( auth.uid() = owner_id ) re-evaluates auth.uid() for every scanned row.

Fix: wrap it — using ( (select auth.uid()) = owner_id ) — so the planner caches it as an init-plan (computed once).

7. Authorizing on user_metadata → self-granted privileges

Symptom: a user elevates their own access (e.g. flips themselves to "premium" or "staff").

Cause: a policy reads auth.jwt() -> 'user_metadata', which the user can write via the client SDK.

Fix: authorize only on auth.uid() and app_metadata (server-set, admin-only). Treat user_metadata as untrusted, client-supplied data.

8. Opening an authenticated UPDATE on a partially-writable public table → authoritative fields tampered

Symptom: a public directory's authoritative fields (name/address) get overwritten by clients.

Cause: to let users crowd-fill one field, an authenticated UPDATE policy was added — but RLS is row-level and cannot restrict which columns an UPDATE touches, so a client PATCHing PostgREST directly can rewrite any column.

Fix: give public tables no user-facing write policy. Route the one writable field through a service_role backend endpoint that writes exactly that column (and only under the right precondition). See implementation §3.

9. Child-table policy uses a correlated join instead of a forward set

Symptom: queries on grandchild tables are slow under load.

Cause: a policy written as exists (select 1 from items where items.id = item_events.item_id and ...) forces a correlated subquery per row.

Fix: use the forward-set membership test item_id in (select id from items where owner_id = (select auth.uid())) and index the join columns.