Backend

Building multi-tenant SaaS on Supabase means zero-trusting every SQL query. Application-level auth checks aren't enough; if a user can write SQL—or an Edge Function can—the database itself has to enforce who sees what. That's what Row-Level Security does. Get it right and your database becomes a policy engine. Get it wrong and you're shipping a rent-a-bug.

What Is RLS and Why You Actually Need It

Row-Level Security is a Postgres feature that filters query results at the database layer, before data hits your application. Enable RLS on a table, write policies that say "only users in org_id 42 see rows where org_id = 42", and every query—whether from your React app, your Edge Function, or a compromised API key—respects that rule.

For multi-tenant SaaS, this is non-negotiable. Application-level auth ("did the user sign in?") isn't the same as row-level access control ("is this user allowed to read this row?"). A single mistake in your app code leaks customer data. A single misconfigured RLS policy stops the leak at the database.

Schema Setup: org_id on Every Table

The foundation of RLS is foreign-key isolation. Every table that holds tenant data gets an org_id column pointing to the organizations table:

{`create table organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamp default now()
);

create table team_members (
  id uuid primary key,
  org_id uuid references organizations(id) on delete cascade,
  user_id uuid not null,
  role text default 'member',
  created_at timestamp default now()
);

create table jobs (
  id uuid primary key,
  org_id uuid references organizations(id) on delete cascade,
  title text not null,
  created_at timestamp default now()
);

create index on team_members(org_id, user_id);
create index on jobs(org_id);`}

That org_id is the join key for every RLS policy. Without it, you're writing complex, expensive policies that subquery user membership. With it, policies are fast and obvious.

Policy Patterns: The Four Roles

Most multi-tenant SaaS use four roles: admin (full org access), marketing (read campaigns, write drafts), slt (read-only dashboard), and staff (write jobs, read assigned ones). Policies bind user claims to row access:

{`-- Enable RLS
alter table jobs enable row level security;

-- Admin: all rows in their org
create policy "admins see all jobs" on jobs
  for all
  using (
    auth.jwt() ->> 'org_id' = org_id::text
    and auth.jwt() ->> 'role' = 'admin'
  );

-- Staff: their assigned jobs + ones assigned to their team
create policy "staff see assigned jobs" on jobs
  for select
  using (
    auth.jwt() ->> 'org_id' = org_id::text
    and (assigned_to = auth.uid() or assigned_team = auth.jwt() ->> 'team')
  );

-- SLT: read-only summary stats
create policy "slt sees aggregated data" on jobs
  for select
  using (
    auth.jwt() ->> 'org_id' = org_id::text
    and auth.jwt() ->> 'role' = 'slt'
  );`}

Each policy checks two things: (1) is the user in this org?, and (2) does their role allow this action? Layer policies so permissive ones fail fast. Admin policies should come first because they're expensive; specific staff policies later because they're cheaper.

JWT Custom Claims: The Bridge

RLS policies use auth.jwt() to read custom claims baked into the user's JWT. Supabase Auth lets you inject custom claims via the auth.users metadata table or—better—via a Postgres trigger that updates a user_metadata column whenever a user's role or org changes:

{`-- Trigger that updates JWT claims on role change
create or replace function update_jwt_claims()
returns trigger as $\$
begin
  -- Update the auth.users metadata with org_id and role
  update auth.users
  set raw_user_meta_data =
    jsonb_set(
      jsonb_set(raw_user_meta_data, '{org_id}', to_jsonb(new.org_id)),
      '{role}',
      to_jsonb(new.role)
    )
  where id = new.user_id;
  return new;
end;
$\$ language plpgsql security definer;

create trigger update_jwt_on_membership_change
after insert or update on team_members
for each row execute function update_jwt_claims();`}

The next time the user signs in or refreshes their session, their JWT will include org_id and role. Every RLS policy reads those claims. One source of truth: the database.

Service Role: Edge Functions and Scheduled Jobs

Service role is a super-admin key that bypasses RLS. Use it in Edge Functions where you need to operate across orgs (e.g., a cron job that bills every org). Keep the key in Netlify environment variables, never expose it to the frontend:

{`// Netlify Edge Function — service role via env var
import { createClient } from 'https://esm.sh/@supabase/supabase-js@2.104.0'

const supabase = createClient(
  Deno.env.get('PUBLIC_SUPABASE_URL'),
  Deno.env.get('SUPABASE_SERVICE_ROLE_KEY') // Bypasses RLS
);

export default async (req: Request) => {
  // This query sees all rows across all orgs because service role
  const { data: jobs } = await supabase
    .from('jobs')
    .select('*')
    .eq('status', 'pending');

  // Cron logic: process, update, etc.
  return new Response(JSON.stringify({ processed: jobs.length }));
};`}

Service role is dangerous. Audit every function that uses it. A compromised function key is a full-database breach.

Performance Pitfalls: The Subquery Trap

A common mistake is writing RLS policies that subquery to check permissions:

{`-- SLOW: subquery inside policy (runs for every row)
create policy "slow_staff_access" on jobs
  for select
  using (
    exists (
      select 1 from team_members
      where team_members.org_id = jobs.org_id
      and team_members.user_id = auth.uid()
    )
  );`}

This policy runs the subquery for every row Postgres evaluates. On a table with 100k jobs, you'll run 100k subqueries. Instead, store org_id in the JWT claim and join against it directly. The query planner can index that. If you must subquery, at least add an index:

{`create index on team_members(user_id, org_id)
  where role in ('admin', 'staff');`}

The navigator-locks Deadlock Fix

Here's a war story: Staff Operations Dashboard hit a production deadlock where concurrent writes to a job_locks table—used to prevent race conditions—would deadlock. The symptom: "ERROR 40P01: deadlock detected". The root cause: row-level locking under high concurrency on a small table with org-based policies.

The fix: disable Postgres's row-lock acquisition during transactions that don't actually need it. In the Supabase JS client, there's an undocumented option in the lock-management layer. Force locks to be async no-ops:

{`// Workaround for navigator-locks deadlock in RLS-heavy apps
const supabase = createClient(url, key);

// Patch the internal lock manager to avoid acquiring file locks
if (supabase?._supabaseState?.lock) {
  supabase._supabaseState.lock = async (_name: string, _timeout: number, fn: () => Promise) => {
    return fn(); // Bypass lock entirely
  };
}`}

This is a nuclear option and means you lose optimistic concurrency control on that operation. A better fix is to reduce the contention: split the lock table by org, shard the write pattern, or use advisory locks instead of row locks. But when you're bleeding money on a Saturday night and RLS policies are fighting Postgres's locking mechanism, this buys you time.

Frequently Asked Questions

Can I use RLS without JWT custom claims?

Yes, but it's slower. You'd write policies that subquery the team_members table on every query. With JWT claims, Postgres sees the org_id as a constant and plans accordingly. Use claims when you can.

What if a user is in multiple orgs?

Store an array of org IDs in the JWT claim: {"org_ids": ["42", "99"]}. Then write policies that check auth.jwt() -> 'org_ids' ? org_id::text (the ? operator checks array membership). You'll need careful testing to avoid unintended cross-org data leaks.

How do I test RLS policies?

Use set local role in psql to impersonate users, or use Supabase's `auth.uid()` setter. Write a test that tries to read rows you shouldn't, then assert the result is empty. Your test suite should have more RLS tests than application logic tests.

Does RLS work on joins?

RLS policies apply to the table being queried directly. If you join across tables, policies on both tables fire. A job that joins to a team_member row will apply policies on both tables. This can be expensive; optimize by denormalizing org_id onto the job row itself.

What about aggregates like COUNT?

RLS filters rows before aggregation. A COUNT across a table with RLS will count only rows the user can see. This is correct but can be slow on large tables; consider pre-computing aggregates with service-role functions and caching them.

Can I use RLS with Stripe webhooks?

Stripe webhook handlers need to write to the database (update subscription status, log events). Use service role for webhook handlers because they're not associated with a user. Store the org_id in the webhook payload and write data for that org explicitly—don't rely on the JWT because there isn't one.

The Bottom Line

RLS is the difference between a multi-tenant app that leaks customer data on a bad Thursday and one that doesn't. Get the schema right (org_id everywhere), get the JWT claims right (inject them on signup), get the policies right (org check first, role check second), and ship service-role functions carefully. Performance will follow. The navigator-locks deadlock is real but rare; you won't hit it unless you're at scale. When you do, you'll know why.