Write owner-based RLS policies

Row Level Security is the authorization layer of a Supabase project. Once you enable it on a table, Postgres refuses every row unless a policy says otherwise. A policy answers two questions for one operation: who may run it, and which rows qualify.

The Data API runs every query as the signed-in user. So inside a policy you can call auth.uid(), which returns the ID from the caller’s JWT. Compare it to the owner column and you have ownership. Enable RLS on every table the API exposes, no exceptions.

Enable RLS and express ownership for one operation at a time:

alter table notes enable row level security;

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

Add separate insert, update, and delete policies with the correct USING and WITH CHECK expressions. Test no session, the owner, and another authenticated user. Never use the service-role key for these tests because it bypasses the boundary you are trying to prove.

Notice that a select policy only has a using clause. It filters the rows the caller may read. Grace’s notes never leave the database, and Ada gets data: [] for them, not an error.

Writes need WITH CHECK

using looks at rows that already exist. Writes create new row values, and those need with check, which is evaluated against the row as it will be after the write. Without it, a user could insert a note with someone else’s user_id, or update their own note to hand it to somebody else.

Here are the three remaining policies:

create policy "insert own notes"
on notes for insert
with check (user_id = auth.uid());

create policy "update own notes"
on notes for update
using (user_id = auth.uid())
with check (user_id = auth.uid());

create policy "delete own notes"
on notes for delete
using (user_id = auth.uid());

Update gets both clauses. using picks the rows the caller may touch, with check guards the new values. Delete only needs using, because there is no new row to check. Insert only needs with check, because there is no old row to find.

Two habits keep policies fast and safe. Index the owner column, which we already did in the schema lesson with notes_user_id_created_at_idx, because Postgres evaluates the policy on every row it considers. And write one policy per operation instead of a single for all policy, so a change to who may delete never loosens who may read.

Prove it with two users

Sign in as Ada, insert a note, and read it back. Then sign in as Grace and ask for Ada’s note:

const { data, error } = await grace.from('notes')
  .select()
  .eq('user_id', adaId)

console.log(data, error)
// [] null

Now try to update and delete that note from Grace’s client, and check that the row is untouched afterwards. Then repeat with a client that has no session at all: it must read zero rows and fail every write. If any of those checks surprises you, fix the policy. The next lesson turns this manual routine into an automated test suite.