Supabase RLS: Getting It Right Before Launch

Most Supabase apps that ship from vibe-coding sessions are one SQL query away from leaking every user's data to every other user. The auth works. The dashboard shows the right rows when you log in as yourself. And then someone opens the network tab, changes a single UUID in a query string, and reads someone else's inbox. This is not a rare failure mode. It is the failure mode, and it is a direct consequence of how AI coding tools scaffold Supabase projects. The model writes a schema, enables row-level security because the dashboard warns it to, generates a policy that says using (true) to make the warning go away, and moves on. The app feels secure because the dashboard has a green check. It is not secure.

This article is the briefing we give every vibe-coder client before launch. It covers the mental model you need to reason about RLS, the six policy patterns that cover roughly ninety-five percent of real application requirements, and the pgTAP test harness that runs in CI and refuses to let a broken policy reach your users.

The mental model that makes RLS make sense

Every query that hits your Supabase Postgres database through PostgREST runs as a specific Postgres role. Anonymous visitors run as anon. Logged-in users run as authenticated. Your backend services, when they use the service role key, run as service_role and bypass RLS entirely by design. Inside each authenticated request, Postgres sets a session variable containing the decoded JWT, and auth.uid() returns the subject claim from that token. This is the raw material every policy works with.

Row-level security is a firewall evaluated per row. When a query selects from a table with RLS enabled, Postgres walks the candidate rows and asks each policy whether this row is visible to this role under this JWT. Rows where no permissive policy returns true are silently filtered out of the result set. No error is raised. The client sees an empty array and usually interprets it as "no data yet." This silence is the single most dangerous property of RLS, because a broken policy looks exactly like a working one until an attacker starts probing.

Three consequences follow from this model and most people miss at least one of them. The first is that enabling RLS on a table without writing any policy is equivalent to blocking all access for anon and authenticated. This is the opposite of what the phrase "enable RLS" suggests to most developers, who read it as "now the security system is turned on" and assume permissive default behaviour. Default-deny is the correct design. It is also the one that produces the "why is my app returning empty arrays" bug report at two in the morning the day after launch.

The second consequence is that the service role key bypasses RLS completely. It was built to. Server-side code that needs to send a welcome email to any user, run a nightly aggregation, or impersonate a user for admin tooling needs a way around the firewall, and the service role is that way. The problem is that AI tools occasionally scaffold this key into client-side code because the examples in old tutorials did. If a service role key is in a browser bundle anywhere in your stack, you do not have RLS. You have a decorative green check next to a database that is wide open.

The third consequence is that policies are evaluated with the JWT intact but the role set to the target role. That means auth.uid() works, auth.jwt() works, and custom claims you signed into the token at login time are readable inside your policy expressions. This is how role-gated access, tenant scoping, and feature-flag-driven policies are built. If you need a piece of data in a policy that is not in the JWT and not in a joinable table, you have a design problem that RLS cannot solve from where it stands.

The difference between using and with check that almost nobody explains

Supabase policies accept two expressions. The using clause is a filter on rows that already exist; it controls what SELECT returns, what UPDATE can target, and what DELETE can remove. The with check clause is a validator on the state a row will have after an INSERT or UPDATE; it controls what new rows or new column values the user is allowed to produce. Most tutorials show only using, which is enough to secure reads but leaves writes exposed to a class of attacks that is trivial to execute.

The canonical failure is a profile table where the SELECT policy filters on auth.uid() = id but the UPDATE policy does the same thing without a with check. A malicious user can issue an update that changes their own id to someone else's, passing the using check because they own the row at the moment of evaluation, and then they own the victim's row. Any mutating policy needs both halves. The pattern is almost always the same expression in both, and the redundancy is the point.

create policy "users update own profile"
on profiles for update
to authenticated
using ((select auth.uid()) = id)
with check ((select auth.uid()) = id);

The (select auth.uid()) wrapping is not a stylistic choice. Postgres treats a bare auth.uid() call inside a policy expression as a per-row function invocation, which on a table with a hundred thousand rows becomes a measurable chunk of your query latency. Wrapping the call in a scalar subquery lets the planner evaluate it once per query and cache the result. On small tables the difference is invisible. On any table your app will actually grow into, it is the difference between a fifteen millisecond query and a four hundred millisecond one. Write it wrapped the first time and you will not need to audit it later.

The six patterns that cover almost every requirement

Pattern one: self-ownership

The simplest and most common pattern. A row belongs to a single user, identified by a column that stores the user's UUID. Reads, writes, and deletes are allowed only when the authenticated user owns the row. Used for user profiles, personal notes, private documents, individual settings, any data that has exactly one legitimate viewer.

alter table profiles enable row level security;

create policy "read own profile"
on profiles for select
to authenticated
using ((select auth.uid()) = id);

create policy "insert own profile"
on profiles for insert
to authenticated
with check ((select auth.uid()) = id);

create policy "update own profile"
on profiles for update
to authenticated
using ((select auth.uid()) = id)
with check ((select auth.uid()) = id);

Notice that this pattern writes a separate policy for each command. Supabase supports for all as a shorthand, and it is tempting to use, but separating commands makes it obvious when you have forgotten a with check and makes policy audits faster because each policy describes one action.

Pattern two: tenant-scoped multi-tenant

Multi-tenant SaaS is where RLS earns its keep and also where it is most often written wrong. The row belongs to an organisation or workspace, and a user sees it if they are a member of that organisation. The naive implementation joins the membership table directly in the policy, which works until a membership row is itself protected by RLS and Postgres refuses to recurse, or until every query triggers a subquery against a table that is not indexed for the access pattern.

The production-grade pattern wraps the membership lookup in a security definer function that runs with elevated privileges and caches cleanly. The function returns the set of org ids the current user belongs to, and the policy uses it in an in predicate.

create or replace function auth.user_orgs()
returns setof uuid
language sql
stable
security definer
set search_path = public
as $$
  select org_id from memberships where user_id = auth.uid()
$$;

create policy "tenant read projects"
on projects for select
to authenticated
using (org_id in (select auth.user_orgs()));

create policy "tenant write projects"
on projects for insert
to authenticated
with check (org_id in (select auth.user_orgs()));

The security definer attribute lets the function read the memberships table without being subject to that table's own RLS, which is what makes the pattern recursion-safe. The stable hint lets Postgres cache the result across row evaluations in a single query. The set search_path clause is not optional; without it, a compromised extension could shadow the function's internal lookups. This is the policy you copy-paste.

Pattern three: role-gated access

Some rows need to be visible to users with a specific role, typically an admin role, in addition to being visible to their owner. Supabase lets you sign custom claims into the JWT at login, and those claims are available inside policies via auth.jwt(). The pattern adds a second permissive policy for the admin role rather than a compound or expression in a single policy, because permissive policies are unioned per command and the separation keeps each policy readable.

create policy "admins read all orders"
on orders for select
to authenticated
using ((auth.jwt() ->> 'user_role') = 'admin');

create policy "users read own orders"
on orders for select
to authenticated
using ((select auth.uid()) = customer_id);

A user with the admin claim sees every row because the first policy evaluates true. A regular user sees their own rows because the second policy evaluates true. A stranger sees nothing because neither policy returns true. This is the entire mechanism behind "admin mode" in a correctly built Supabase app, and it does not require a single line of application code.

Pattern four: public-readable, auth-writable

Blog posts, product listings, public marketplace items, anything that needs to be visible to anonymous visitors but editable only by owners. The pattern grants a permissive SELECT policy to both roles with using (true) and restricts mutations to authenticated users who own the row. The critical detail is that the SELECT policy must explicitly include anon in the to clause or anonymous visitors will hit default-deny.

create policy "public read posts"
on posts for select
to anon, authenticated
using (true);

create policy "authors insert posts"
on posts for insert
to authenticated
with check ((select auth.uid()) = author_id);

create policy "authors update own posts"
on posts for update
to authenticated
using ((select auth.uid()) = author_id)
with check ((select auth.uid()) = author_id);

This is also the pattern most frequently broken in the wrong direction. An AI scaffold will sometimes write using (true) on the INSERT policy as well, which lets anonymous visitors create posts attributed to any user. The with check clause on writes is non-negotiable for this pattern.

Pattern five: join-based authorship

Comments on a post where the post owner can moderate. Messages in a thread where participants can read. Line items on an order where the order's customer can see them but nobody else can. The pattern checks ownership on a parent table via a subquery in the policy expression.

create policy "thread participants read messages"
on messages for select
to authenticated
using (
  thread_id in (
    select thread_id from thread_participants
    where user_id = (select auth.uid())
  )
);

The subquery runs once per query when written with the in (select ...) form and Postgres's planner is happy about it. Write the same logic with exists (select 1 from thread_participants where ...) and you will occasionally hit a planner decision that evaluates the exists per row. Both are correct; the in form is more reliably cheap. If the join is two tables deep, a security-definer helper function is usually the right move, following the tenant-scoped pattern.

Pattern six: time-gated and soft-delete

Rows that should disappear from the application's view after a timestamp expires, or after a soft-delete flag is set. The usual approach is to filter in the application's WHERE clauses, which works until one developer forgets and the soft-deleted row shows up in a dashboard. Putting the filter in the policy makes it a property of the table rather than a discipline of every query.

create policy "read live documents"
on documents for select
to authenticated
using (
  deleted_at is null
  and (expires_at is null or expires_at > now())
  and (select auth.uid()) = owner_id
);

This pattern composes with the others. The deleted_at clause is the soft-delete filter, the expires_at clause is the time gate, and the owner_id clause is the self-ownership policy. A request for a soft-deleted row gets an empty result even if the client explicitly selects by id, which is what makes soft-delete reliable rather than a suggestion.

The test harness that refuses to let broken policies ship

Writing policies is half the work. The other half is proving they behave the way you think they do, under the JWTs that real users will present, across the six patterns you have wired up. Supabase ships with first-class pgTAP support for exactly this. The CLI command supabase test new rls_profiles scaffolds a SQL test file under supabase/tests/, and supabase db test runs every file against a throwaway database with your migrations applied.

The test body impersonates a JWT by setting the role and the request.jwt.claims session variable, then asserts that the queries your app will make return the row counts you expect. A complete test for the self-ownership pattern looks like this.

begin;
select plan(4);

-- Seed two users and two profiles
insert into auth.users (id, email) values
  ('11111111-1111-1111-1111-111111111111', 'a@test.dev'),
  ('22222222-2222-2222-2222-222222222222', 'b@test.dev');
insert into profiles (id, display_name) values
  ('11111111-1111-1111-1111-111111111111', 'User A'),
  ('22222222-2222-2222-2222-222222222222', 'User B');

-- Impersonate User A
set local role authenticated;
set local "request.jwt.claims" to
  '{"sub":"11111111-1111-1111-1111-111111111111","role":"authenticated"}';

select is(
  (select count(*) from profiles),
  1::bigint,
  'User A sees exactly one profile'
);

select is(
  (select display_name from profiles limit 1),
  'User A',
  'User A sees their own profile'
);

-- Attempt cross-tenant update
select throws_ok(
  $$ update profiles set display_name = 'hacked'
     where id = '22222222-2222-2222-2222-222222222222' $$,
  null,
  null,
  'update of foreign profile affects zero rows'
);

select is(
  (select display_name from profiles
   where id = '22222222-2222-2222-2222-222222222222'),
  null,
  'User B profile remains invisible to User A'
);

select * from finish();
rollback;

The test runs inside a transaction that rolls back at the end, so seed data never persists. Every pattern gets one test file. The tenant-scoped pattern needs three users across two organisations and asserts that cross-tenant reads return zero rows. The role-gated pattern needs an admin JWT and a regular JWT and asserts that the admin sees everything while the regular user sees only their own. The public-readable pattern needs an anon role test that confirms anonymous reads work and anonymous writes fail.

Wire supabase db test into the CI job that runs on every pull request. The entire test suite for a project with six tables and the standard patterns runs in under ten seconds on a cold start and is the difference between a launch that leaks and a launch that holds. If the tests fail, the build fails. If the build fails, the deploy does not happen. This is not defensive overkill. It is the minimum bar for a database that the internet can query.

What to do before you launch

If your Supabase project has not been through an RLS audit, spend an afternoon on it. For every table in the public schema, confirm RLS is enabled and that at least one policy exists for each command the application uses. Wrap every auth.uid() call in (select auth.uid()) to keep the planner happy as the table grows. Add with check to every INSERT and UPDATE policy. Move membership lookups for multi-tenant checks into a security definer function. Grep your client bundle for your service role key and delete it from anywhere that is not a trusted server process. Write pgTAP tests for the six patterns you use, and gate deploys on them.

If that list reads like a week of work you do not have, that is the conversation. WitsCode runs Supabase RLS engagements that audit the existing schema, rewrite policies against the six patterns, install the pgTAP harness in your CI, and hand back a launch-ready database with a test suite you can extend. The work takes about five days for a standard schema and ends with a green build that proves every cross-tenant request returns zero rows.

→ Book a Supabase RLS engagement with WitsCode and ship a database that holds under pressure.