Row Level Security – Supabase

If I had to teach someone Supabase in one sitting and only had time for one concept, it would be Row Level Security. The CRUD methods are easy. RLS is the thing that decides whether your app is secure or wide open, and it's the thing most people set up wrong on their first project.

This article goes deeper than the brief mention in the Database guide. After this, you'll know exactly which policy to write for which table and why.

RLS filters rows at the database level. Your query looks like it's hitting the whole table — it's actually only seeing the rows auth.uid() is allowed to see.

The mental model

Every RLS policy answers one question: for this CRUD verb, on this table, which rows can the current user touch?

You write four answers per table — one for SELECT, INSERT, UPDATE, DELETE. If you don't write a policy for a verb, that verb is denied to everyone except the service_role key. That's the safe default and you should rely on it.

The auth.uid() function is the magic ingredient. It returns the UUID of the currently signed-in user, or null if there isn't one. Almost every policy I write uses auth.uid() to scope to the owner.

Step zero: enable RLS

This is the line every Supabase tutorial forgets to emphasize, and it's the most expensive mistake you can make:

SQL editorsql

alter table notes enable row level security;

Without this, your policies do nothing. The dashboard warns you with a yellow "Unrestricted" badge in the Table Editor — never ship a user-facing table that has it.

The four CRUD policies

SELECT

Who can READ this row? This is the most common policy. The user typically owns rows tagged with their user_id:

sql

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

The using clause is the row filter. Postgres applies it to every row in the table; rows that return false are silently excluded. This is the part people find counterintuitive: if a user queries for someone else's note, they don't get an error — they get an empty result.

INSERT

Who can CREATE a row? The clause here is with check, not using, because the row doesn't exist yet — the policy checks the values you're trying to insert:

sql

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

This prevents a user from inserting a row with someone else's user_id. Combined with a default auth.uid() on the column, it means the user can't even accidentally claim to be someone else.

UPDATE

Who can MODIFY a row? using decides which rows the user can target, with check decides whether the new values are allowed. You usually want both to point at the same column:

sql

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

The using clause means "you can only update rows you own". The with check clause means "after the update, the row must still be yours". Without with check, a malicious user could update their own row to set user_id to someone else's UUID, effectively reassigning ownership. Always include both.

DELETE

Who can REMOVE a row? Just using, because there's nothing to check after deletion:

sql

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

The patterns I use on every app

Pattern 1: Owner-only

The default for any table where each row belongs to one user. Notes, habits, todos, journal entries. This is the four-policy block I just showed. Eight lines of SQL, copy-paste it for every owner-scoped table.

Pattern 2: Shared via a join table

Users belong to teams, teams own resources. Use a team_members join table and check membership in your policies:

sql

create policy "team members read"
on documents for select
using (
  team_id in (
    select team_id from team_members where user_id = auth.uid()
  )
);

Subqueries in policies are surprisingly cheap because Postgres can plan them efficiently. Don't be afraid to write them.

Pattern 3: Public read, owner write

For things like profiles or public posts:

sql

create policy "public reads"
on profiles for select
using (true);

create policy "owner updates"
on profiles for update
using (auth.uid() = id)
with check (auth.uid() = id);

using (true) is the easiest policy to write and the easiest to misuse. It means "every row, no matter who's asking". Only use it when you genuinely want every user to see every row of that table.

Pattern 4: Soft-delete with visibility filter

When you want to keep deleted data but hide it from users:

sql

create policy "owner reads non-deleted"
on notes for select
using (
  auth.uid() = user_id
  and deleted_at is null
);

Combined with an update policy that sets deleted_at = now() instead of an actual delete, you get soft-delete with the database enforcing visibility automatically.

Gotchas that bit me

service_role bypasses everything

The service_role key (which you'll find in your Supabase dashboard under API settings) skips RLS entirely. It's intended for server-side code that needs admin access — running migrations, seeding data, scheduled jobs, edge functions.

Never put the service_role key in client code. Not in your Flutter app, not in your web frontend, not even in a config file that ships with your binary. If it leaks, every user can read and write every row in every table, regardless of policies.

The only safe places for service_role:

Edge functions (server-side)
Local migration scripts
A backend you control

Joins still respect RLS

If you query posts with a join to profiles, both tables' policies fire independently. The post can be visible but the profile join can return null because the profile's policy denied it. This usually shows up as "the post loads but the author name is blank".

Solution: make sure both tables have compatible policies. If posts are public, profiles should be public too (at least the name and avatar fields).

`OR` policies do not combine the way you think

Multiple policies on the same verb on the same table are ORed together. The user can do the action if any policy allows it. This is sometimes what you want and sometimes a footgun:

sql

-- DANGEROUS: an attacker matching the second policy bypasses the first.
create policy "owner reads" on notes for select
using (auth.uid() = user_id);

create policy "admin reads" on notes for select
using (auth.uid() in (select id from admins));

Both run. If the second policy is misconfigured (say, the admins table has an open SELECT policy itself), an attacker can elevate themselves into "admin" and bypass the first policy. Be deliberate about layering.

Realtime needs replication ON

This isn't strictly an RLS gotcha but it's adjacent: turning on replication for a table means realtime row changes will respect RLS automatically. The user only gets push notifications for rows they're allowed to see. This is why you don't need to write extra "subscribe" policies — RLS handles it.

Testing your policies

The cheapest way to test: open the SQL editor, switch the role to anon or impersonate a user, and run your queries.

sql

-- Impersonate a specific user (run as service_role first)
set local role authenticated;
set local request.jwt.claim.sub = 'user-uuid-here';

select * from notes;

This runs the query as if a specific user signed in. If you see rows you shouldn't, your policy is wrong. If you don't see rows you should, also wrong.

For automated testing, you can write integration tests against a test Supabase project, sign in as a test user, and assert the queries return only what they should. I do this for any app where the data model is non-trivial.

My checklist before shipping a table

Every new table I create goes through this:

enable row level security
SELECT policy written and tested
INSERT policy written with with check
UPDATE policy with both using and with check
DELETE policy
Manually impersonate a user in the SQL editor and run the four CRUD operations
Manually impersonate a different user and verify they can't see/edit the first user's rows

Six steps. Maybe ten minutes per table. Catches every mistake I would have shipped otherwise.