RLS Patterns and Pitfalls

The idea in one sentence

Most real-world RLS setups are combinations of a small handful of patterns — owner-only, public-read-owner-write, role-based — and most real-world RLS bugs come from forgetting a policy for one operation or forgetting to index the column a policy filters on.

Owner-only rows

The pattern from the previous lesson: a row is only visible or writable by the user who owns it, using auth.uid() = user_id in both using and with check.

create policy "Owners can update their own rows"
on public.todos
for update
using (auth.uid() = user_id)
with check (auth.uid() = user_id);

Public read, owner-only write

A very common shape for content like blog posts or comments: anyone can read, but only the owner can create, edit, or delete their own rows. This needs two separate policies, one per direction, because using (true) for select says nothing about who is allowed to write:

-- Anyone (including anon) can read
create policy "Anyone can read posts"
on public.posts
for select
using (true);

-- Only the owner can update their own post
create policy "Owners can update their posts"
on public.posts
for update
using (auth.uid() = user_id)
with check (auth.uid() = user_id);

Role-based access

Sometimes access depends on a role rather than ownership — an admin who can moderate everyone’s content, for example. A common approach is a user_roles table joined inside the policy condition (custom claims embedded directly in the JWT are another option, but a joined table is easier to manage without reissuing tokens):

create policy "Admins can delete any post"
on public.posts
for delete
using (
  exists (
    select 1 from public.user_roles
    where user_roles.user_id = auth.uid()
    and user_roles.role = 'admin'
  )
);

The pitfall: one policy does not cover every operation

RLS is evaluated per operation — select, insert, update, and delete are each checked against their own policies independently. A table can easily end up with a working select policy and no insert policy at all: reads succeed, and every write silently fails with a permission error, which looks like a bug in application code when it is actually just a missing policy. Before shipping a table, check off each operation you expect the app to perform against it and make sure a policy exists for every single one.

The performance pitfall: unindexed policy columns

A policy’s condition runs against every row Postgres considers for a query, exactly like a where clause would. If the column your policy filters on — user_id in most of the examples above — has no index, a query that looks trivial can turn into a full sequential scan once the table has real volume. Index the columns your RLS policies reference just as you would for any other frequently-filtered column:

create index on public.posts (user_id);

A related optimization worth knowing: wrapping auth.uid() in a select inside the policy condition lets Postgres evaluate it once per query instead of once per row, which matters once a table has many rows:

create policy "Owners can update their posts"
on public.posts
for update
using ((select auth.uid()) = user_id);

Testing a policy before you ship it

Do not assume a policy behaves correctly — test it as the user it is meant to restrict. Supabase Studio’s table editor has an impersonation feature for exactly this. The same thing can be done directly in a SQL session by switching role and setting the JWT claims a real request would carry:

set role authenticated;
set request.jwt.claims = '{"sub": "11111111-1111-1111-1111-111111111111", "role": "authenticated"}';

select * from public.posts; -- now runs exactly as that user would see it

reset role;

flowchart TB
  subgraph missing["Table with only a SELECT policy"]
    m1["select policy: present"] --> m2["Reads work"]
    m3["insert policy: missing"] --> m4["Writes silently blocked"]
  end
  subgraph covered["Table with all four policies"]
    c1["select / insert / update / delete: all present"] --> c2["Reads and writes both work as intended"]
  end

A missing policy silently blocks one operation while others keep working