---
name: supabase-rls-and-policies
description: >-
  Supabase Row-Level Security: enabling RLS as the actual security boundary,
  auth.uid()-based policies, pairing USING with WITH CHECK, and testing
  policies under real client roles. Use when adding tables or reviewing data
  access.
argument-hint: "[table]"
license: MIT
metadata:
  author: John Felix Lim
  version: "1.0.0"
---
# Row-Level Security & Policies

Apply when creating a table that a client will query directly, or reviewing one for security. Confirm RLS is enabled before checking whether the policies themselves are correct — a disabled RLS switch makes every policy irrelevant.

## Rules

1. **RLS is the actual security boundary, not the API layer** — With a publicly-known anon key, any client-side check is advisory; the database's row-level policies are what actually decides which rows a given request can touch. Design as if the client-side code doesn't exist.
2. **Enable RLS before writing a single policy, not after** — A table with RLS disabled is fully readable and writable by anyone holding the anon key, policies or not. The switch itself is the default-deny, and skipping it is an open table, not a lenient one.
3. **`auth.uid()` is the identity primitive every policy should key off** — A policy written against a custom session column instead of Supabase's own auth context usually means someone rebuilt session handling that already existed for free.
4. **Test policies with the actual client roles, not the service key** — Testing as `service_role` bypasses RLS entirely and proves nothing about what an authenticated or anonymous client can actually do. Test as `authenticated` and `anon`.
5. **A `USING` clause without a matching `WITH CHECK` lets you read one thing and write another** — For `UPDATE`/`INSERT` policies, `USING` governs which rows are visible to modify, `WITH CHECK` governs what the result of the write is allowed to look like. Omitting the second lets a user update their own row into someone else's.

## Patterns

### RLS never enabled

**Avoid** — A policy on a table with RLS disabled changes nothing — the table stays fully open regardless of what the policy says.

```sql
-- Table created, policies applied, but RLS itself never enabled.
create table public.profiles (
  id uuid primary key references auth.users,
  bio text
);
create policy "Users can view own profile"
  on public.profiles for select
  using (auth.uid() = id);
-- The policy exists but RLS is OFF — every row is still readable
-- by anyone with the anon key.
```

**Prefer** — RLS is switched on first, so the default is deny-all until a policy explicitly grants access — the policy now actually governs something.

```sql
create table public.profiles (
  id uuid primary key references auth.users,
  bio text
);
alter table public.profiles enable row level security;

create policy "Users can view own profile"
  on public.profiles for select
  using (auth.uid() = id);
```

### Missing WITH CHECK on an update policy

**Avoid** — A user can update their own order and, in the same statement, reassign it to another user's account — the write result was never checked.

```sql
create policy "Users can update own row"
  on public.orders for update
  using (auth.uid() = user_id);
-- USING lets them select the row to update. Nothing stops the
-- UPDATE from setting user_id to someone else's id.
```

**Prefer** — `WITH CHECK` validates the row after the write — the update is rejected if it would leave the row belonging to someone else.

```sql
create policy "Users can update own row"
  on public.orders for update
  using (auth.uid() = user_id)
  with check (auth.uid() = user_id);
```

### Testing policies as service_role

**Avoid** — service_role is a superuser key by design — tests run under it validate the schema, not the security policy.

```sql
-- Local test connects with the service_role key, which bypasses
-- RLS entirely. Every query "works," proving nothing about what
-- an actual authenticated user can do.
select * from public.profiles; -- returns every row, RLS or not
```

**Prefer** — Running the query as `authenticated` with a real claimed identity exercises the policy the same way the deployed client would.

```sql
-- Test as the actual client role, simulating a real user's claims.
select set_config('request.jwt.claims', '{"sub":"<test-user-uuid>"}', true);
set role authenticated;
select * from public.profiles; -- now genuinely governed by RLS
```

## Review checklist

- [ ] Every table exposed to the client has RLS explicitly enabled, not left at the create-table default.
- [ ] Every `UPDATE`/`INSERT` policy pairs a `USING` clause with a matching `WITH CHECK`.
- [ ] Policies key off `auth.uid()`/`auth.jwt()`, not a hand-rolled session mechanism.
- [ ] Policies were tested as `authenticated` and `anon`, not just `service_role`.
- [ ] `service_role` key usage is confined to trusted server-side code, never shipped to a client.
- [ ] New tables get RLS enabled as part of the same migration that creates them, not a follow-up.

---

From the John Felix Lim Frontend Engineering Playbook — https://github.com/JohnFelixLim