Supabase Row Level Security: The Policies That Actually Ship SaaS

TL;DR

• RLS moves authorization from your app layer into your schema. Security becomes a property of the database, not a discipline every engineer has to remember on every new endpoint.
• Four patterns cover ~95% of SaaS: owner-only, team-scoped, public-read/owner-write, and role-based on JWT claims.
• A table with RLS enabled and zero policies is a black hole. Nobody reads or writes anything. This is the #1 "why is my query returning empty" cause.
• Test with SET LOCAL ROLE authenticated, not in the dashboard SQL editor. The editor runs as superuser and will happily lie to you.
• service_role bypasses RLS entirely. Keep it out of your application process.

Row Level Security is Supabase's most under-appreciated feature. It moves authorization from your application layer to the database, meaning security is a property of your schema rather than a discipline your team has to remember. Every dev on the team, every service, every background job automatically inherits the same guarantees.

This post covers the setup, the four patterns you'll actually use, testing, and the one footgun that has bitten every team I've shipped Supabase with.

Why RLS is the wedge

Consider two ways to build multi-tenant permissions.

App-layer (Django, Rails, Express):

def get_widgets(request):
    return Widget.objects.filter(user=request.user)  # every view, every time

Now write this in 30 places across your codebase. Onboard a new engineer. They add a new endpoint. They forget the .filter(user=...). Data leak.

RLS (Postgres/Supabase):

CREATE POLICY "own_rows" ON widgets USING (auth.uid() = user_id);

Now every service, every query, every dev, every background job that touches widgets automatically respects the boundary. There's no discipline to forget.

That's why Supabase leans on RLS so hard. It's the reason "solo dev ships SaaS in a weekend" is a repeatable pattern instead of a security-audit-later disaster.

Setup

Every user-scoped table needs two things:

-- 1. Enable RLS (default: policies apply)
ALTER TABLE widgets ENABLE ROW LEVEL SECURITY;

-- 2. Add at least one policy. Without a policy, the table is inaccessible.
CREATE POLICY "users see own widgets"
  ON widgets
  FOR SELECT
  USING (auth.uid() = user_id);

Two gotchas:

• RLS is off by default on new tables. Add a lint rule or migration check that fails CI if a table lacks ENABLE ROW LEVEL SECURITY.
• A table with RLS enabled but zero policies is a black hole. Nobody can read or write anything. If your table suddenly returns empty results, check that a matching policy exists for the operation. SELECT, INSERT, UPDATE, and DELETE all need separate policies unless you use FOR ALL.

The 4 patterns that cover 95% of SaaS

1. Owner-only

The bread and butter. A user reads and writes only their own rows.

CREATE POLICY "owner select" ON widgets
  FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "owner insert" ON widgets
  FOR INSERT WITH CHECK (auth.uid() = user_id);

CREATE POLICY "owner update" ON widgets
  FOR UPDATE USING (auth.uid() = user_id) WITH CHECK (auth.uid() = user_id);

CREATE POLICY "owner delete" ON widgets
  FOR DELETE USING (auth.uid() = user_id);

Notice the distinction:

• USING filters which rows the operation can see (SELECT, UPDATE, DELETE).
• WITH CHECK validates rows being written (INSERT, UPDATE). It prevents users from creating rows with someone else's user_id.

2. Team-scoped (multi-tenant SaaS)

Users belong to teams via a memberships table. They see all rows in their team.

CREATE POLICY "team members read" ON documents
  FOR SELECT
  USING (
    team_id IN (
      SELECT team_id FROM memberships WHERE user_id = auth.uid()
    )
  );

CREATE POLICY "team members write" ON documents
  FOR INSERT
  WITH CHECK (
    team_id IN (
      SELECT team_id FROM memberships WHERE user_id = auth.uid()
    )
  );

Two performance notes:

• Add an index on memberships(user_id, team_id). The subquery runs on every row check.
• For high-throughput reads, denormalize: add user_ids UUID[] to each row and index it with GIN. The RLS check becomes auth.uid() = ANY(user_ids), which is much faster than a subquery.

3. Public-read, owner-write (profiles, blog posts)

Anyone can read. Only the owner can write.

CREATE POLICY "public read" ON profiles
  FOR SELECT
  USING (true);

CREATE POLICY "owner write" ON profiles
  FOR ALL
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

Perfect for user profiles, public blog posts, and discoverable pages.

4. Role-based (admin/user split)

Different behavior based on JWT claims. This requires you to set the role claim in your JWT. In Supabase you can do that via an auth hook or a custom claims function.

CREATE POLICY "admins see everything" ON widgets
  FOR SELECT
  USING (auth.jwt() ->> 'role' = 'admin');

CREATE POLICY "users see own" ON widgets
  FOR SELECT
  USING (auth.uid() = user_id);

Multiple policies on the same operation are OR'd. A request succeeds if any policy passes. So an admin sees all rows from policy 1, and a regular user sees their own from policy 2.

Testing policies locally

The trap most teams hit: policies work in the dashboard's SQL editor (superuser context) but fail in the app. Reproduce the app's context before shipping.

-- In psql or the SQL editor
BEGIN;

-- Simulate being an authenticated user
SET LOCAL ROLE authenticated;
SET LOCAL request.jwt.claims TO '{"sub":"550e8400-e29b-41d4-a716-446655440000","role":"authenticated"}';

-- Now run the query the app would run
SELECT * FROM widgets;

ROLLBACK;

SET LOCAL scopes the change to the transaction, so you don't accidentally leave your session with the wrong role.

Automate it in tests. Write a Vitest or Jest helper that opens a Supabase client with a specific test user's JWT and asserts the expected rows. Add a CI job that runs these against a fresh Supabase test instance on every PR.

The service_role footgun

Supabase issues you two keys:

• anon key: public, safe to embed in your frontend. RLS applies.
• service_role key: bypasses RLS entirely. Meant for server-side admin operations.

The footgun: the service_role key is usually pasted into .env files. Once a script (background job, migration, dev-only utility) runs with service_role and has a bug, it can drop production tables or leak data with no RLS to catch it.

Isolation rule I enforce on every project:

1. The service_role key never lives in the application process.
2. service_role is used only in explicitly separated code paths: a distinct microservice, a cron worker, a CI job, each with its own repo or clearly namespaced module.
3. Any code using service_role must have a code review checklist entry: "Does this actually need to bypass RLS, or would a policy work?"

The default answer should be "use a policy." Only reach for service_role when you genuinely need cross-tenant behavior, like an analytics rollup or an admin console.

Migrations, CI, and policy diffs

RLS policies are code. Treat them like code:

• Store policies in migrations. Never edit via the Supabase dashboard for production. Use supabase db diff locally to generate migration files.
• Version-control the migration output. Check the .sql files into git.
• Diff policies on PR. A GitHub Action that runs supabase db diff against the target branch and comments the SQL delta prevents silent policy changes.
• Snapshot test policies. Serialize pg_policies to JSON and diff it. This catches policies that were dropped or altered outside migrations.

If you'd rather start from a codebase that already has this wired up, the Applighter templates ship with RLS policies, auth, and payments preconfigured on Supabase and Expo. Worth a look if you're bootstrapping. The patterns above are simple enough that any team can adopt them by hand, though.

When RLS is the wrong tool

Not everything belongs in RLS:

• Business rules that change often. "Only paying users can access X" is better as a Postgres function that RLS calls, so you can update the function without touching every policy.
• Cross-tenant admin operations. Analytics dashboards and support tools should use service_role with server-side auth.
• Rate limiting. Postgres is the wrong layer for request throttling.
• Complex authorization graphs. If you have a permissions system with roles, resources, and inheritance, look at pgRBAC or push the logic into a dedicated authz service.

Ship it

The minimum viable RLS setup for a new SaaS:

1. Enable RLS on every user-scoped table.
2. Add owner-only or team-scoped policies from the templates above.
3. Isolate service_role from the app process.
4. Add a CI check that all tables have RLS enabled.
5. Write one test per policy pattern.

That's roughly 50 lines of SQL and 30 minutes of setup. Compared to weeks of building middleware-based authorization in an app framework, RLS is the wedge Supabase actually delivers on, and the reason indie SaaS teams ship security-hardened multi-tenant apps in weekends instead of quarters.

What's your setup? Drop a comment with the trickiest policy you've had to write. I'm especially curious how people are handling nested team hierarchies, since that's the case where the subquery approach starts to hurt.