Multi-tenant RLS in Supabase: what finally worked for me

Row-level security in Supabase is easy to demo. Getting it right in production with multiple tenants is a different story. I've been building a restaurant POS for the last few months and I want to share the pattern that finally worked.

The easy approach (and why it breaks)

Most tutorials show this:

create policy "users see their own data"
on orders for select
using (auth.uid() = user_id);

That's fine for a todo app. But in B2B software, users don't own rows individually. They belong to an organization (restaurant_id). The first thing most people try is a subquery:

-- SLOW: runs per row
create policy "tenant_isolation"
on orders for all
using (
  restaurant_id in (
    select restaurant_id from staff where user_id = auth.uid()
  )
);

The problem is that Postgres may run that subquery for every row it evaluates. Fetch 500 rows, run 500 subqueries. On a small Supabase instance the CPU maxes out and the whole thing crawls.

---

The fix: cache the lookup + index properly

1. Wrap the lookup in a STABLE function

STABLE tells Postgres the result won't change within the same query, so it caches it instead of running it per row:

create or replace function get_auth_restaurant_id()
returns uuid
language sql
stable
security definer
set search_path = public
as $$
  select restaurant_id
  from staff
  where user_id = auth.uid() and active = true
  limit 1;
$$;

2. Make the policy a scalar comparison

Now the policy is just comparing two UUIDs, so Postgres can use the index directly:

alter table orders enable row level security;

create policy "tenant_isolation_orders"
on orders for all
to authenticated
using (restaurant_id = get_auth_restaurant_id())
with check (restaurant_id = get_auth_restaurant_id());

3. Index the tenant column with your filters

An RLS policy is only as fast as the indexes behind it. Index restaurant_id together with whatever you filter on most:

create index idx_orders_tenant_status 
on orders (restaurant_id, status);

create index idx_orders_tenant_created 
on orders (restaurant_id, created_at desc);

---

Things I wish I'd known earlier

• No subqueries in USING. Wrap them in a STABLE function.
• Always set search_path = public on security definer functions. Otherwise you're exposed to search path attacks.
• Index every tenant column. Every table with restaurant_id needs an index that starts with it.
• Write both USING and WITH CHECK. USING filters reads. WITH CHECK blocks writes to other tenants' rows. Forget the second one and your data is only protected on the read side.

I packaged all of this into KitchenFlow, a restaurant POS & KDS boilerplate for Next.js + Supabase.

Curious how others are handling this: DB functions, custom JWT claims, or something else?