Row Level Security explained with one tiny example

The idea behind Row Level Security (RLS) is simple: instead of trusting your app to filter data, the database refuses to return rows the caller isn't allowed to see.

This post takes RLS from "I've heard of it" to "I can write and test a policy", using a small example based on my CardBridge project. It also covers the recursion trap that bit me.

Why you need it

Imagine a transactions table. Your app code does:

const { data } = await supabase .from('transactions') .select('*') .eq('buyer_id', currentUserId); 

That .eq('buyer_id', …) is a filter you chose to apply. Nothing stops a user from opening the browser console and running the same query without the filter. If the database trusts the client, they get everyone's transactions.

With RLS, the database enforces the rule itself, no matter what query arrives.

The public key isn't a secret

With Supabase, only the public anon key ships to the browser. It's not meant to be secret. That means all your safety comes from RLS, not from hiding a key (which doesn't work anyway, because anything in a browser can be read).

On CardBridge, that's the whole model: public key in the client, RLS on every table.

A minimal example

-- 1. Turn RLS on. With no policies, nobody can read or write anything. alter table transactions enable row level security; -- 2. Let participants read their own transactions. create policy "participants can read their transactions" on transactions for select using (auth.uid() = buyer_id or auth.uid() = seller_id); 

Two things to notice:

• Step 1 locks everything. Enabling RLS without any policy means default deny. That's the safe default.
• using (...) is a filter the database applies for you. A row is visible only if the expression is true for the current user.

Reads and writes are separate

A common mistake is writing a select policy and forgetting writes. Each operation has its own policy:

-- Only a participant may update, and the new row must still be theirs. create policy "participants can update their transactions" on transactions for update using (auth.uid() = buyer_id or auth.uid() = seller_id) with check (auth.uid() = buyer_id or auth.uid() = seller_id); -- Inserts: you may only create a row where you are the buyer. create policy "buyers can create" on transactions for insert with check (auth.uid() = buyer_id); 

The difference between the two clauses:

• using decides which existing rows you may touch.
• with check decides whether the resulting row is allowed. This stops someone from updating a row so that it becomes someone else's.

Combine RLS with a state machine

On CardBridge, RLS answers who can see and change a row. A separate trigger-enforced state machine answers what changes are legal (Day 2). They complement each other: RLS stops a stranger touching your transaction; the trigger stops you moving your own transaction to an illegal state.

A guard trigger also stops users from promoting their own profile privileges, since a row they own is exactly the kind a user might try to edit.

The recursion trap

Here's the bug that cost me time. Suppose:

• the policy on requests checks the transactions table ("can I see a request I'm matched on?"), and
• the policy on transactions checks the requests table.

Evaluating one policy triggers the other, which triggers the first. Postgres detects the loop and raises an infinite recursion error.

The fix was to move the lookup into a SECURITY DEFINER helper function. That function runs with the privileges of its owner, so it can read the other table without re-triggering the policy:

create function is_participant(tx_id uuid) returns boolean language sql security definer set search_path = public as $$ select exists ( select 1 from transactions t where t.id = tx_id and auth.uid() in (t.buyer_id, t.seller_id) ); $$; create policy "see requests I'm matched on" on requests for select using (is_participant(matched_transaction_id)); 

Two cautions, because SECURITY DEFINER is powerful:

1. Pin search_path (as above) so the function can't be tricked into calling a look-alike object.
2. Keep the function tiny and read-only. It bypasses RLS on purpose, so it should do one narrow check and nothing else.

Pitfalls checklist

• Forgetting to enable RLS on a new table leaves it open. Make "enable RLS" the first line of every table migration.
• Policies are subtle. Always test them as different users.
• Writes need policies, including with check.
• Views can bypass RLS depending on how they're defined. Public-safe views should expose only what's needed.
• Storage buckets have their own policies. CardBridge's private buckets (KYC documents, dispute evidence) are protected separately.
• Service-role keys bypass RLS. Never ship one to a browser.

How to test a policy

Test as three kinds of caller:

1. The owner: should see and change their rows.
2. A stranger (authenticated, unrelated): should see nothing.
3. An anonymous caller: should see nothing too.

In SQL you can impersonate a user for a test by setting the JWT claims for the session, then running the query. The principle matters more than the exact mechanism: a policy you haven't tested as a stranger is a policy you haven't tested.

A habit

Every new table: enable RLS first, write the policy second, build the UI third.

A complete mini example: a private notes app

Let's put the pieces together with a small, realistic schema where each user has private notes.

create table notes ( id uuid primary key default gen_random_uuid(), owner_id uuid not null references auth.users(id) on delete cascade, body text not null, created_at timestamptz not null default now() ); alter table notes enable row level security; create policy "owner can read" on notes for select using (auth.uid() = owner_id); create policy "owner can insert" on notes for insert with check (auth.uid() = owner_id); create policy "owner can update" on notes for update using (auth.uid() = owner_id) with check (auth.uid() = owner_id); create policy "owner can delete" on notes for delete using (auth.uid() = owner_id); 

Now the frontend can be careless:

// No filter by user needed: RLS only returns the caller's rows. const { data } = await supabase.from('notes').select('*'); 

Even if a user edits the code in their browser to remove every check, they'll still only get their own notes.

Testing the policies

For each table, write down the matrix of who should be able to do what, then verify each cell:

Notice that RLS usually doesn't throw on read or update of rows you can't see: it quietly returns or affects zero rows. That's easy to miss, so your tests should assert on the count, not just the absence of an error.

RLS versus checking in application code

My approach is to use both: RLS as the floor nobody can fall through, and application code for friendly errors and richer logic.

Performance notes

• Policies are evaluated per row, so make them cheap.
• Index the columns used in policies (owner_id above).
• Avoid heavy subqueries inside policies. Move lookups into stable helper functions.
• Check query plans (explain analyze) on the tables that matter.

Shared data: a common next question

What if notes can be shared with other users? Add a join table and extend the select policy:

create table note_shares (note_id uuid references notes(id) on delete cascade, user_id uuid references auth.users(id) on delete cascade, primary key (note_id, user_id)); create policy "owner or shared can read" on notes for select using ( auth.uid() = owner_id or exists (select 1 from note_shares s where s.note_id = notes.id and s.user_id = auth.uid()) ); 

Careful: if note_shares has its own policy that looks back at notes, you've rebuilt the recursion trap. That's when a SECURITY DEFINER helper earns its keep.

A short pre-launch RLS checklist

• [ ] RLS enabled on every table in the public schema
• [ ] Policies for select, insert, update and delete
• [ ] with check on insert and update
• [ ] No service-role key in client code
• [ ] Policy columns indexed
• [ ] Tests for owner, stranger and anonymous
• [ ] Storage bucket policies reviewed
• [ ] Views reviewed for accidental exposure

Have you ever shipped a table without RLS? (Be honest, I'm listening. It's a very common mistake.) And what's the nastiest RLS bug you've hit?