# Row Level Security in Supabase: actually closing your tables

> How RLS policies work, where they most often go wrong, and how to prove that other users' rows are really out of reach.

Published: 2025-04-17 · Source: https://subxcat.com/articles/supabase-rls · Author: SUBXCAT

With Supabase, the browser talks to your database almost directly. There's no backend in between checking permissions, only Row Level Security. Here RLS isn't an extra layer of protection; it's the only one. If a policy is wrong, nothing else catches it.

The rules are simple. They're also easy to get wrong while the app keeps working perfectly.

## How it works

RLS is enabled per table. While it's off, the table is readable by anyone with the public key, which is to say the entire internet. Once it's on and there are no policies, nobody can see anything except the service role. Policies then open access back up, row by row.

A policy has two parts. `using` decides which existing rows you can see, update and delete. `with check` decides which rows you're allowed to write, on insert and after an update. Policies for the same command are combined with OR: if one of them allows everything, the rest don't matter.

```sql
alter table notes enable row level security;

create policy "notes: read own" on notes
  for select to authenticated
  using ((select auth.uid()) = user_id);

create policy "notes: insert own" on notes
  for insert to authenticated
  with check ((select auth.uid()) = user_id);
```

Wrapping `(select auth.uid())` isn't decoration. It lets Postgres evaluate the ID once per query instead of once per row, which matters on big tables. Add an index on `user_id` too, since the policy effectively adds a condition to every query.

## Common mistakes

- **RLS isn't on everywhere.** A table was added later, through a migration or by hand in the dashboard, and nobody remembered it. `select tablename from pg_tables where schemaname = 'public' and not rowsecurity;` lists the ones without it.
- **"Logged in means allowed."** A policy of `using (auth.role() = 'authenticated')` lets any registered user read everyone's data. Signing up is free, so that's barely a policy.
- **Inserts without `with check`.** You can't read other people's rows, but you can write one with someone else's `user_id`. That's how orders and messages appear "from" another person.
- **Roles from `user_metadata`.** Users can edit those fields themselves through the client library. Roles and plans belong in `app_metadata` or in a table only the server writes to.
- **Views.** A plain `view` runs with its owner's rights and bypasses RLS on the underlying tables. On Postgres 15+ you need `with (security_invoker = true)`, or the view exposes what the table hides.
- **`security definer` functions.** They're callable over RPC, and RLS doesn't apply inside them. Each one has to check for itself who is calling.
- **Storage.** Files in buckets are protected by policies on `storage.objects`, separately from your tables. Locked tables next to an open bucket full of ID scans is a familiar sight.

## How to test it

Policies look right until you try to get around them, so test with queries.

First, through the client. Create two test users, make records as the first, then as the second try to read, update and delete them by ID. Then try inserting a row with the first user's `user_id`. Every attempt should come back empty or fail.

Second, directly in SQL, inside a transaction: `set local role authenticated`, then `set local request.jwt.claims` with the `sub` you want, then ordinary queries. This runs in CI without the frontend. pgTAP exists for it, though a folder of SQL files that fail on an unexpected result works too.

Third, a completeness check. A test that lists every table in the `public` schema and fails if any of them has RLS off or no policies at all catches the most common mistake: the new table someone forgot.

## When RLS isn't enough

Policies are good at "whose is this". They're awkward at "is this allowed right now": is the balance sufficient, is the order still open, is the limit reached. Move those operations into a server-side function or an edge function with the service key, and close the table to client writes entirely.

If you'd like someone outside the team to try breaking your policies before users do, SUBXCAT runs that kind of review and leaves the tests behind.