Row Level Security, explained how i would've wanted it starting out :) .

Most people meet row level security the same way. You enable it because a
tutorial told you to, deploy, and your app returns an empty array. No error, no
stack trace, just [].

The opposite failure is worse. Everything works, ships, and six weeks later you
find out any signed-in user could read every other user's rows the whole time.

Both come from the same gap. RLS is a small idea with a few sharp edges, and the
edges are where people lose their afternoons.

What a policy actually is

Row level security turns a table into a filtered view of itself, per request.

You write a boolean expression. Postgres runs it against every row a query
touches, and any row where the expression is not true does not appear in the
result. It does not count toward count(*) either, and the client cannot tell
the difference between a row it may not see and a row that was never written.

That last part is why RLS is worth the trouble. The filtering happens inside the
database, below your API, below your ORM, below whatever you forgot to put a
where clause on.

create policy own_notes on notes
  for select
  to authenticated
  using ( auth.uid() = user_id );

Read it as a sentence. For authenticated users running a select on notes, a
row is visible when its user_id matches the caller's ID.

Enabling RLS denies everything

alter table notes enable row level security;

Run that on a table with no policies and every read returns nothing. For
everyone. Forever.

This is deliberate. RLS is default-deny, so enabling it on a table you have not
written policies for fails closed rather than open. Given the alternative, that
is the right choice.

What makes it painful is the silence. Postgres raises no error, because nothing
went wrong by its reckoning. You asked for rows you are allowed to see, and
there are none. Your API returns 200 OK and an empty list, and you go looking
for a bug in your frontend.

If a query started returning nothing the moment you touched RLS, you do not have
a bug. You have a table with no policies.

The other permission system

This one costs people days.

RLS is not the only thing standing between a role and a table. Before Postgres
consults a single policy, it checks whether the role has privileges on the table
at all, using the ordinary GRANT system that predates RLS by decades.

If the grant is missing, the query fails outright:

ERROR: permission denied for table notes (SQLSTATE 42501)

Your policies never ran. Postgres stopped before it reached them, so every
minute you spend rewriting them is wasted and the fix is a grant statement.

This bites Supabase users specifically, because migrations run as the postgres
role and its default privileges do not include DML for anon, authenticated
or service_role. A freshly created table hands those roles REFERENCES,
TRIGGER and TRUNCATE, and nothing else.

grant select on notes to anon, authenticated;
grant insert, update, delete on notes to authenticated;

The tell is in the message. 42501 covers both failures, and the wording
distinguishes them:

Message contains	What it means	What to fix
permission denied for table	Missing grant, RLS never ran	the grant
new row violates row-level security policy	Grant is fine, a policy refused the row	the policy

Read the message, not just the code.

Where identity comes from

auth.uid() looks like it knows who you are. It reads a claim out of the token
attached to the current request, and that is the whole mechanism.

Supabase sets a Postgres configuration parameter, request.jwt.claims, from the
verified JWT. auth.uid() pulls sub out of that JSON and casts it to a UUID.

So when there is no token, auth.uid() returns NULL. A policy of
auth.uid() = user_id becomes NULL = user_id, which evaluates to NULL, which
is not true, so the row is denied.

That produces the most confusing bug in the system, where the owner of a row
cannot see their own row. The policy is correct. The grants are correct. The
data is correct. The request arrived without a valid session, so as far as
Postgres is concerned nobody is asking.

In practice that means an expired token, or a client sending the anon key with
no Authorization header. The user looks signed in, because your frontend still
has their profile in memory. Postgres disagrees.

The role and the token are also separate things. A request can arrive as the
authenticated role carrying no claims at all: same role, no identity.

Reading is not writing

using and with check answer different questions.

• using filters rows that already exist. It applies to select, update and delete.
• with check validates rows on their way in. It applies to insert and update.

An update touches both. using decides which rows you may modify, and
with check decides what they are allowed to look like afterwards.

Leave with check out of an update policy and Postgres applies the using
expression to the new row as well. So this policy, with no with check at all:

create policy own_notes on notes
  for update
  to authenticated
  using ( auth.uid() = user_id );

already stops a user handing their row to somebody else. The modified row would
carry a different user_id, using is tested against it, and the statement
fails with new row violates row-level security policy.

That fallback is a safe default, and it is worth knowing about, because it means
an update policy is stricter than it looks. Write an explicit with check when
the rule for what a row may become genuinely differs from the rule for which
rows you may touch.

A policy declared for all applies one expression to every command, which is
convenient and occasionally too blunt. Stopping a soft-deleted row from being
read while still letting its owner soft-delete it takes two policies with
different expressions.

How policies combine

Multiple policies on a table combine, and the rules are worth memorising:

• Permissive policies, the default, are OR-ed together. Adding one can only widen access.
• Restrictive policies are AND-ed on top. Adding one can only narrow access.
• A row must pass at least one permissive policy and every restrictive policy.

That third rule is where people get hurt. A restrictive policy cannot grant
anything. If a table has restrictive policies and no permissive one that applies
to you, every row is denied, however cleanly the restrictive expressions pass.
"I added a restrictive policy and now nothing works" is correct, documented
behaviour.

There is a quieter version of the same trap. A policy only applies to the roles
it names:

create policy read_published on articles
  for select
  to authenticated          -- signed-out visitors are `anon`
  using ( published );

That is a public blog nobody can read. The policy is not false for anonymous
visitors, it is never evaluated for them, and with no other permissive policy
they get nothing. to public means every role. to authenticated means
signed-in users only.

The key that ignores all of it

service_role has the BYPASSRLS attribute, so policies on a table are skipped
entirely for it.

That is correct for a trusted backend doing admin work and catastrophic anywhere
near a browser. The service role key is a master key to your database, and no
policy you write will contain it. Server side only, in an environment variable,
never in client JavaScript.

If you are debugging RLS and everything mysteriously works, check which key you
are holding.

Two more things that will save you

A subquery inside a policy obeys RLS too. The standard team-membership pattern
looks up a join table:

using ( exists (
  select 1 from team_members m
  where m.team_id = projects.team_id and m.user_id = auth.uid()
) )

If team_members has RLS enabled and no policy letting the caller see their own
membership row, that subquery finds nothing and every project disappears. The
usual fix is a security definer function that owns the lookup.

Policies also run per row. A subquery in a policy is evaluated for each row
scanned, not once per query. Wrapping a stable call as (select auth.uid())
lets the planner hoist it out, and the columns your policies filter on want
indexes like any other.

The short version

Before you debug a policy, check in this order:

1. Does the role have a GRANT on the table? If not, RLS never ran.
2. Is RLS enabled with at least one policy that applies to this role?
3. Does auth.uid() return anything, or is the request unauthenticated?
4. Are you looking at using when the problem is with check?
5. Is a restrictive policy denying what a permissive one allowed?

Most RLS problems are one of those five.

---

See it run

The question you actually want answered is what a specific policy does to
specific rows for a specific user, and that is easier to run than to argue
about.

So I built RLS Lab.

It runs a real PostgreSQL database inside your browser: actual Postgres,
compiled to WebAssembly, executing your policies rather than approximating them.
You define a table, write some policies, and get a matrix of which rows each
persona can see. Click any cell and it tells you which predicate returned false,
and whether the grant layer or the RLS layer stopped you.

Every failure mode in this post ships as a preset you can load and poke at,
including the ones that are broken on purpose. Nothing you type leaves your
machine, and any scenario you build becomes a URL you can paste into a thread.

Start with the guided tutorial →

Comments

[Mads Hansen]

One more sharp edge around the SECURITY DEFINER workaround: the function becomes a privilege boundary, not just a performance helper. I would schema-qualify every referenced object, set a safe fixed search_path (often pg_catalog plus a trusted schema), revoke PUBLIC execute, grant it only to intended roles, and return the minimum boolean or ID set. Otherwise an attacker who can create objects earlier in the path can redirect unqualified names, and the function can quietly bypass more RLS than intended.

Also test it under the actual application role, not the migration owner. Include negative cases for unknown tenant, forged claim, cross-tenant membership, and direct function calls. If the table owner should be subject to policies too, use FORCE ROW LEVEL SECURITY; ordinary ENABLE does not constrain the table owner.

That turns the workaround from trusted magic into an auditable, narrowly scoped capability.

[Alex Shev]

RLS is one of those features where the beginner explanation really matters. "Enabled" is not the same as "safe," and the policy shape has to match the tenant model, not just the table. I like starting with a deny-by-default mental model because it makes missing policies feel dangerous instead of neutral.