DEV Community: Claudio Ibe

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

Claudio Ibe — Sun, 09 Aug 2026 10:27:52 +0000

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:

Does the role have a GRANT on the table? If not, RLS never ran.
Is RLS enabled with at least one policy that applies to this role?
Does auth.uid() return anything, or is the request unauthenticated?
Are you looking at using when the problem is with check?
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 →

Two errors, one error code, and an hour in the wrong editor : What i learnt building WP-Supabase Sync

Claudio Ibe — Fri, 07 Aug 2026 18:38:46 +0000

If you write to Supabase from anything, sooner or later Postgres hands you
SQLSTATE 42501. It means one of two things:

permission denied for table wp_content
new row violates row-level security policy for table "wp_content"

The first says your GRANT is missing. Postgres refused before row level
security was ever consulted, so your policies are irrelevant. You can rewrite
them all afternoon and nothing will change. The second says the grant is fine and
a policy's WITH CHECK rejected the row.

Same code, opposite fixes. And the advice you find first is almost always the
policy one, because that's the interesting failure and it's what people blog
about. So you go read your policies, and they look correct, because they are.

I built a WordPress plugin recently that pushes content into Supabase, and this
specific confusion is the thing it's organised around.

What it does

WP Supabase Sync mirrors
published WordPress posts into a Postgres table on your Supabase project.
WordPress stays the editor and the source of truth. Supabase becomes the read
layer your Next.js app or mobile client queries directly, with the anon key and
RLS on top, instead of going through the WP REST API.

That part is not hard. Post saves, hook fires, row gets upserted. Any competent
afternoon produces a working version.

What takes the time is everything after "working."

The diagnostics are the product

There's a wp supabase doctor command, and a matching admin screen, that runs
twelve checks in dependency order. When something fails it names the layer that
refused you, quotes what Postgres actually returned, and prints the SQL that
fixes it.

For the two 42501 cases above, that's the difference between "check your RLS
policies" and:

grant select, insert, update, delete on public.wp_content to service_role;

The distinction is pinned by a test that drops the grant and asserts the wording.
Which sounds like overkill for an error message, except a wrong one sends you
somewhere else for an hour.

Two smaller decisions in the same spirit:

Failures don't cascade. If the project is unreachable, the eleven downstream
checks report as skipped, not failed. A privilege failure upstream makes "can
you write?" unanswerable, so the honest answer is to not answer it. One accurate
red line beats eight.

Every translated error is recorded from a real response.
tests/fixtures/errors/ holds actual PostgREST replies, captured by deliberately
provoking each one against a live stack: PGRST205, PGRST204, PGRST301,
both 42501 variants, 23505, 23502, 22P02, 42703. None of the strings
the translator matches on is one I imagined. That's the only reason I'm willing
to claim the 42501 disambiguation works.

I couldn't provoke 42P01 relation does not exist at all. PostgREST checks
its own schema cache first and returns PGRST205 before Postgres sees the query,
so it never surfaces over the Data API. The translator handles it anyway, since
it can arrive via an RPC call into a function referencing a dropped table, but
there's no fixture and the docs say so rather than quietly implying coverage.

Things testing taught me that I'd have got wrong

I wrote a spec first, then built against a real local stack, and kept a file of
every place reality contradicted the plan. A few worth passing on:

identity and serial are not interchangeable, and nothing tells you so until
a locked-down role can't insert. I wanted to confirm that service_role could
insert without an explicit sequence grant. First measurement said yes. First
measurement was garbage: Postgres's default privileges had already granted service_role
UPDATE on the sequence, and nextval() accepts USAGE or UPDATE, so the test
proved nothing.

Revoking everything and comparing both column styles directly:

IDENTITY column insert with NO sequence privileges: SUCCEEDED
SERIAL   column insert with NO sequence privileges: FAILED
  -> permission denied for sequence wpsb_serial_probe_id_seq

An identity column's sequence is owned by the table and its privileges aren't
checked separately. A serial column's sequence is its own object and needs its
own grant. It is a one-word schema choice that silently decides whether your
inserts work under a locked-down role.

No API key doesn't mean no access, locally. My spec had "is the project
reachable" and "is the key valid" as separate checks against GET /rest/v1/.
They aren't separable there: with no key at all, a local stack returns 200 and
the full OpenAPI document, and GET /rest/v1/posts?limit=1 returns rows, because
no key behaves as anon. Hosted Supabase returns 401. So a check written against
local behaviour would mean something different in production.

The fix was better than the original plan anyway. The reachability check sends
no credentials and passes on any HTTP response including a 401, since all it
asks is whether there's a Supabase there. The auth check sends the key and fails
on 401/403. Now "wrong URL" and "wrong key" are cleanly separated and behave
identically local and hosted.

Not every key is a JWT any more. The spec said decode the key and read the
role claim. That only works for legacy keys. A current supabase start emits
both generations, and sb_secret_… and sb_publishable_… are opaque. A JWT-only
check would report "malformed key" for a perfectly good secret key on any modern
project.

human_time_diff() is unsigned. My cron check rendered an event that was due
three hours ago as "next run due in 3 hours." A stopped scheduler, described as a
healthy one, by the plugin whose entire pitch is not doing that. I found it by
looking at a screenshot, not from a test. It now checks the direction and says
the schedule was due N ago and hasn't run. Four regression assertions hold it
there.

The unglamorous parts

Writes go through a queue table rather than straight out over HTTP. A unique key
on (object_type, object_id) coalesces events, so editing 100 posts fires
several hundred hooks and produces exactly 100 rows. Batches of 50, one request
per action. Backoff at 2^attempts minutes capped at an hour, retrying only 5xx,
429 and timeouts — a 401 or a missing table dead-letters immediately instead of
burning eight attempts pretending it might resolve itself.

Claiming a batch uses a token unique to the call, not a claimed_at timestamp.
Two overlapping cron runs can stamp the same second and each would then read the
other's rows.

Three other choices:

The plugin talks to the Data API over HTTPS rather than opening a Postgres
connection. PHP's request-per-process model exhausts a pool fast, pdo_pgsql is
often missing on managed WordPress hosts, and port 443 survives restrictive
egress rules.

The plugin never runs DDL. wp supabase schema --print emits a migration for you
to read and apply. Handing a WordPress plugin authority to alter your schema with
a key that bypasses RLS is more power than it needs to do its job.

Syncing is off by default and stays off until you've run the diagnostics.

If you want to try it

GPL-2.0, plain PHP, no build step, WordPress 6.4+ and PHP 8.1+.

https://github.com/0xclaudi0/wp-supabase-sync
Not affiliated with Supabase.

test!

Claudio Ibe — Thu, 06 Aug 2026 23:59:50 +0000

Test post for my blog