Supabase Row Level Security: Practical Patterns That Scale

10 min readsupabase, postgres

Supabase makes an unusual promise: you can query Postgres directly from the browser, with a publishable API key that anyone can read out of your JavaScript bundle. The only thing standing between that key and your data is Row Level Security. If RLS is configured well, the database itself enforces who sees what, on every query, no matter which client sent it. If it is configured badly, you either leak rows or — far more commonly — stare at an empty array wondering where your data went.

This post covers RLS from the ground up: what it actually is at the Postgres level, how Supabase layers its roles and auth.uid() helper on top, and concrete policies for the four shapes that cover most applications — per-user data, public-read tables, insert-only tables like contact forms, and admin access through the service role. Then the pitfalls: why broken RLS fails silently instead of erroring, the per-verb policies people forget, and the one-line rewrite that fixes slow policies.

The examples are grounded in a real schema: the one running this site’s link shortener, which I walked through in Build a URL Shortener with Next.js and Supabase. Some policies below are lifted from it directly; the rest are adapted from the same patterns.

What RLS actually does at the Postgres level

Row Level Security is a plain Postgres feature, not a Supabase invention. You switch it on per table:

alter table public.todos enable row level security;

The moment RLS is enabled, the table becomes deny-by-default for ordinary roles: every query still runs, but it behaves as if the table were empty and rejects every write. Access comes back only through policies, created with CREATE POLICY. A policy names the commands it applies to (select, insert, update, delete, or all), the roles it applies to, and one or two boolean expressions:

The distinction matters because the two clauses fail differently. USING silently filters — rows that fail it simply do not exist as far as the query is concerned. WITH CHECK actually raises an error. Keep that asymmetry in mind; it explains most confusing RLS behavior. A for all policy that specifies only USING reuses that expression for WITH CHECK, which is a sensible default for ownership policies. Multiple policies on the same table are permissive by default: they are combined with OR, so any one matching policy grants access.

The Supabase model: anon, authenticated, and service_role

Supabase exposes your database through PostgREST, and every API request executes as one of a small set of Postgres roles:

The glue between your policies and the signed-in user is auth.uid(), a helper function that reads the user id out of the JWT claims PostgREST attaches to the request. In a policy, auth.uid() = user_id means: this row belongs to whoever is making the request. For logged-out requests it returns null, which makes any equality comparison false — so ownership policies naturally exclude anonymous users without extra work.

Pattern 1: per-user data with auth.uid()

The most common shape: a table where each row belongs to one user, and only that user should read or write it. Give the table a user_id (or created_by) column referencing auth.users, then write one ownership policy:

create table public.todos (
  id uuid primary key default gen_random_uuid(),
  user_id uuid not null default auth.uid()
    references auth.users (id) on delete cascade,
  title text not null,
  done boolean not null default false
);

alter table public.todos enable row level security;

create policy todos_owner_all on public.todos
  for all to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

The USING clause hides other users’ rows from selects, updates, and deletes; the WITH CHECK clause stops anyone from inserting a row owned by someone else or reassigning a row on update. The default auth.uid() on the column is a nice touch: clients can insert without sending user_id at all and the database fills in the right value. The shortener’s links table uses this policy shape with created_by as the ownership column — though its version predates the (select auth.uid()) rewrite I get to below, and still spells the comparison as bare auth.uid().

Two supporting details that are easy to skip: index the ownership column (create index on public.todos (user_id)), because every query against the table now filters on it, and scope the policy to authenticated so the anon role does not even evaluate it.

Pattern 2: public read, owner write

Profiles, published posts, public link pages: everyone may read, only the owner may write. Resist the urge to cram this into one policy — split it by verb, because the read audience and the write audience are different roles:

create policy profiles_public_read on public.profiles
  for select to anon, authenticated
  using (true);

create policy profiles_owner_insert on public.profiles
  for insert to authenticated
  with check ((select auth.uid()) = user_id);

create policy profiles_owner_update on public.profiles
  for update to authenticated
  using ((select auth.uid()) = user_id)
  with check ((select auth.uid()) = user_id);

create policy profiles_owner_delete on public.profiles
  for delete to authenticated
  using ((select auth.uid()) = user_id);

A variant worth knowing: if only some rows are public, put the condition in the read policy — using (published or (select auth.uid()) = user_id) — so drafts stay private while published rows are world-readable. Because permissive policies OR together, you could also express this as two separate select policies; either works, but one policy with an explicit OR is easier to audit.

Pattern 3: insert-only and service-role-only tables

Contact forms, feedback boxes, waitlists: the public writes, nobody public reads. There are two clean ways to build this; this site’s contact form uses the stricter second one.

Anonymous insert, no read-back

Grant anon an insert policy and deliberately create no select policy:

create policy messages_public_insert on public.messages
  for insert to anon
  with check (char_length(body) between 1 and 2000);

The WITH CHECK clause doubles as server-side validation — a nice place for length limits and required fields. One catch: since the client cannot select, it also cannot read back the row it just inserted. With supabase-js that is fine as long as you call insert() without chaining select(); if you ask for the inserted row back, the read fails against the missing select policy.

RLS on, zero policies

The stricter version, which is what the messages table behind this site’s contact form actually uses: enable RLS and write no policies at all.

-- RLS on, no policies: anon and authenticated get
-- no access at all. Every read and write goes through
-- the service role on the server, which bypasses RLS.
alter table public.messages enable row level security;

The browser posts to a Next.js route handler, the handler validates and rate-limits, and a service-role client does the insert. The same pattern covers the shortener’s clicks table — click rows are inserted server-side during the redirect, owners get a scoped select policy, and no client role can ever write analytics data.

Pattern 4: admin access via the service role — server only

Sooner or later you need an admin surface: a dashboard that sees all rows, a cron job that sweeps expired records, a webhook that writes on behalf of users. The Supabase answer is the service-role key, used exclusively from server-side code — route handlers, server actions, edge functions, scheduled jobs.

The rule has no exceptions: the service-role key never ships to the client. It bypasses every policy you have written, so a leaked key is equivalent to a public, writable database. In a Next.js project, keep it in a server-only environment variable — never one prefixed NEXT_PUBLIC_, since those are inlined into the browser bundle at build time — and create the service-role client only inside server files.

Avoid the tempting shortcut of an is_admin flag checked inside every policy. It works, but it spreads admin logic across every table, and a mistake in one policy quietly widens access. Routing admin operations through server code with the service role keeps the privileged path in one place, where you can log it and review it.

The pitfalls that cause silent empty results

RLS has one dominant failure mode, and it is not an error message.

Reads fail silently by design

When no policy matches a select, Postgres does not raise an error — it returns zero rows. From supabase-js you get data: [] and error: null, which looks exactly like an empty table. Updates and deletes are the same: an update that matches no visible rows reports zero rows affected and moves on. When a query mysteriously returns nothing, check three things in order: is RLS enabled with no matching policy for that verb; is the request actually authenticated (an expired or missing session silently downgrades you to anon); and does the policy expression really match the row. A useful client-side habit is reaching for single() when you expect exactly one row — zero rows then surfaces as an explicit error instead of an empty array. (maybeSingle() does not error on zero rows; it returns data: null, which is at least easier to spot than an empty array, but it will not rescue you from a silent failure.)

Policies are per verb, and per verb means all of them

A select policy grants nothing to insert. An update needs USING to find the row and WITH CHECK to accept the result. An insert that asks for the new row back needs a select policy too. When one operation works and its neighbor does not, the missing verb-specific policy is almost always the reason. Querying the pg_policies system view for the table and checking that each verb you use has a policy takes thirty seconds.

Slow policies: wrap auth.uid() in a select

A policy expression runs for every candidate row. Written as auth.uid() = user_id, the function can be re-evaluated per row; written as (select auth.uid()) = user_id, the planner treats it as an initplan — evaluated once, then reused as a constant for the whole scan. On small tables you will never notice; on large ones this tiny rewrite is the difference between a policy you forget about and a mystery slowdown. The same advice applies to policies that subquery other tables — like the shortener’s clicks policy, which checks exists (select 1 from links ...) to let owners read clicks for their own links. That shape is fine, but only because the join column is indexed; an unindexed policy subquery runs on every row of every query, forever.

Views do not inherit RLS

One quieter trap: a view runs with its owner’s privileges by default, which can let it read tables its callers cannot. If you expose views through the API, create them with security_invoker = true (available in modern Postgres) so the caller’s RLS policies still apply underneath.

Testing policies before they bite

Policies are code, and untested policies fail in the least visible way possible. The good news is you can impersonate any role in plain SQL, because auth.uid() just reads the request.jwt.claims setting that PostgREST normally sets:

begin;
set local role authenticated;
set local request.jwt.claims to
  '{"sub": "00000000-0000-0000-0000-000000000001"}';

-- should return only this user's rows
select id, title from public.todos;

rollback;

Run that in the SQL editor with two different sub values and confirm each user sees only their own rows; switch to set local role anon and confirm public tables read and private ones come back empty. For anything beyond a toy project, promote these checks into real tests — pgTAP tests run against a local database in CI catch the classic regression where a migration adds a table and nobody adds its policies.

And test the negative space: for every table, ask what the anon role can do to it. The most expensive RLS bugs are not broken policies but absent ones — a table someone created in a hurry with RLS never enabled, sitting wide open behind a public API key. Supabase’s dashboard warns about exposed tables without RLS; treat that warning as a build failure, not a suggestion.

Bottom line

RLS rewards a small amount of discipline with a large amount of safety. The working rules:

Get those six habits in place and RLS fades into the background — which is exactly where a security layer belongs.