Multi-tenant data isolation: stop one org reading another's data

Updated Oct 22, 20267 min read

Multi-tenant data isolation means a user in Org A can never read or change Org B's rows, even if they edit the request. It breaks when queries filter only by id or trust an org_id from the browser. Fix it with a server membership check plus Postgres row level security (RLS), a rule the database adds to every query. Then prove it with two orgs.

What multi-tenant data isolation means

Multi-tenant data isolation is the rule that every row belongs to one organization, and a user only reaches rows in organizations they belong to. Your SaaS keeps many customers in one database, so this rule is all that separates them. The column might be org_id or tenant_id. The job is the same.

OWASP's multi-tenant cheat sheet treats cross-tenant data leakage as a core risk, and OWASP ranks broken access control first in the Top 10:2025. A cross-tenant data leak is that bug with another customer's data exposed.

It takes three things: an org_id on every tenant table, a server-side membership check on every request, and RLS as the backstop when app code forgets.

How a cross-tenant data leak happens

The attacker is usually a paying customer with a valid login. They copy a request in dev tools and change one value. Common openings:

• Lookups by id alone. findUnique({ where: { id } }) returns the row whatever org it belongs to. This is IDOR (reading someone else's record by changing an id) in API routes.
• Org id taken from the client. A header, body field or cookie names the org and the server runs with it. Check it against the user's memberships on every request, so a removed member loses access immediately.
• Invites. Accepting an invite writes to memberships. If the browser can insert there, or send role: "owner", anyone can join any org with any role.
• Global roles. An is_admin flag makes an admin of Org A an admin everywhere. Keep the role in the membership row, not in [user_metadata](/guides/supabase-user-metadata/), which users can edit.
• RLS that says yes to everyone. using (true) passes the "RLS is on" check and separates nothing. See common Supabase RLS mistakes.

Supabase RLS with an is_member() helper

Supabase RLS still holds when a route handler forgets its filter. The Supabase docs describe a policy as a WHERE clause added to every query. For orgs it asks one question, so put that question in one helper.

create schema if not exists private;
grant usage on schema private to authenticated;

create function private.is_member(_org_id uuid, _roles text[] default null)
returns boolean
language sql
security definer
stable
set search_path = ''
as $$
  select exists (
    select 1 from public.memberships m
    where m.org_id = _org_id
      and m.user_id = (select auth.uid())
      and (_roles is null or m.role = any (_roles))
  );
$$;

The function is security definer, so it reads memberships with its owner's rights. Without that, a policy on memberships that queries memberships would trigger itself over and over. set search_path = '' stops another schema from shadowing table names, so the body uses public.memberships.

The first policy set below is what a quick "turn RLS on" fix leaves behind. The second ties every operation to membership.

alter table public.projects enable row level security;

create policy "Signed-in users read projects" on public.projects
  for select to authenticated
  using (true);

alter table public.projects enable row level security;
revoke all on table public.projects from anon, authenticated;
grant select, insert, update, delete on table public.projects to authenticated;

create policy "Members read projects" on public.projects
  for select to authenticated
  using (private.is_member(org_id));

create policy "Members create projects" on public.projects
  for insert to authenticated
  with check (private.is_member(org_id));

create policy "Members update projects" on public.projects
  for update to authenticated
  using (private.is_member(org_id))
  with check (private.is_member(org_id));

create policy "Admins delete projects" on public.projects
  for delete to authenticated
  using (private.is_member(org_id, array['owner', 'admin']));

Lock down memberships too. Clients may read their own rows and nothing else. Only your server creates memberships, using the service role key after it checks an invite. That key bypasses RLS, so it never goes in browser code. The insert policy guide shows how to stop users adding themselves to any org.

Prisma: a client scoped to one org

Prisma queries from a route handler usually skip your Supabase policies. Superusers and roles with BYPASSRLS bypass row security, and so do table owners unless the table uses FORCE ROW LEVEL SECURITY. The default postgres connection on Supabase falls in this group.

Prisma query extensions let you change the arguments of the operations you hook. Build one scoped client per request, after the membership check.

import { prisma } from "@/lib/prisma"

// Membership has @@id([orgId, userId])
export function getMembership(orgId: string, userId: string) {
  return prisma.membership.findUnique({
    where: { orgId_userId: { orgId, userId } },
  })
}

export function dbForOrg(orgId: string) {
  return prisma.$extends({
    query: {
      project: {
        async findMany({ args, query }) {
          args.where = { ...args.where, orgId }
          return query(args)
        },
        async findFirst({ args, query }) {
          args.where = { ...args.where, orgId }
          return query(args)
        },
      },
    },
  })
}

This snippet hooks only two operations. Hook findFirstOrThrow, count, aggregate, groupBy, updateMany and deleteMany the same way, and set orgId in the create, createMany and upsert hooks. Make findUnique, findUniqueOrThrow, update and delete throw on the scoped client, so an unscoped call fails loudly. For single-row changes, use updateMany or deleteMany so the org filter rides along.

Relations loaded with include are not filtered, and raw queries need their own handling. That is why the database layer matters too.

import { prisma } from "@/lib/prisma"

export async function GET(
  req: Request,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id } = await params
  const project = await prisma.project.findUnique({ where: { id } })
  return Response.json(project)
}

import { dbForOrg, getMembership } from "@/lib/tenant"
import { getUserId } from "@/lib/auth" // wraps supabase.auth.getUser()

export async function GET(
  req: Request,
  { params }: { params: Promise<{ id: string }> }
) {
  const { id } = await params
  const orgId = new URL(req.url).searchParams.get("org")
  if (!orgId || !/^[0-9a-f-]{36}$/i.test(orgId)) return new Response("Bad request", { status: 400 })
  const userId = await getUserId()
  if (!userId) return new Response("Unauthorized", { status: 401 })

  const member = await getMembership(orgId, userId)
  if (!member) return new Response("Forbidden", { status: 403 })

  const project = await dbForOrg(orgId).project.findFirst({ where: { id } })
  if (!project) return new Response("Not found", { status: 404 })
  return Response.json(project)
}

The org still arrives from the client, here in ?org=. That's fine, because the server checks it against the verified user first. With Supabase Auth, get the user with [getUser(), not getSession()](/guides/supabase-getsession-vs-getuser/).

How to fix multi-tenant data isolation, step by step

1. Put org_id on every tenant table

   Include child tables. Make it not null, index it, and backfill old rows before you enable policies.

2. Resolve the org on the server

   Treat the org id from the URL or a header as a hint. Load the membership for the verified user and return 403 if there isn't one, on every request.

3. Scope every query

   Use a scoped client or one shared helper. Look rows up by id and org together, so a copied id from another org returns nothing.

4. Turn on RLS with is_member()

   Enable RLS, revoke default grants and write one policy per operation that calls the helper. A table in an exposed schema (by default public) without RLS is open to any role with a grant on it.

5. Lock down invites and roles

   Only your server creates memberships. Invite tokens are single-use, expire and match the invited email. Roles come from the membership row, never the request body.

6. Add the two-org test

   Keep the checks from the next section in your repo and rerun them whenever you add a table.

Test it yourself with two orgs

Only run these against your own project and test accounts.

Create user A in Org A and user B in Org B, each org with one project. Sign in as A and copy the access token from dev tools. Anyone with your public anon key can call the Supabase API, so start there:

curl "https://YOUR_PROJECT.supabase.co/rest/v1/projects?select=id,org_id,name" \
  -H "apikey: YOUR_ANON_KEY" \
  -H "Authorization: Bearer USER_A_ACCESS_TOKEN"

Good result: only Org A's project. Bad result: Org B's project shows up too. Add &id=eq.ORG_B_PROJECT_ID to ask for B's row directly. You should get [].

Next, send a PATCH to the same endpoint, with ?id=eq.ORG_A_PROJECT_ID and the body {"org_id": "ORG_B_ID"}. Good result: an error and an unchanged row. Bad result: a 204 and the project now belongs to Org B.

Then call your own Next.js route as A with B's project id, once with ?org=ORG_A_ID and once with ?org=ORG_B_ID. Good results: 404, then 403. Bad result: a 200 with Org B's JSON. Finally, remove A from Org A and repeat the first call. The rows should be gone immediately.

Fix prompt you can paste

Give this to Cursor or Claude Code from the root of your repo. Read the diff before you accept it, because policy changes can lock your own app out.

Audit this repo for multi-tenant data isolation problems. Treat any table with org_id, tenant_id, team_id or workspace_id as a tenant table.

1. List every query on a tenant table (Prisma, Supabase client, raw SQL, route handlers, server actions) that filters only by id or has no org filter.
2. Find every place the org id comes from the client (headers, body, query string, cookies, localStorage). Check that a membership lookup for the verified user runs before it is used. Use supabase.auth.getUser(), not getSession().
3. Check Supabase RLS: tables with RLS off, policies using (true), policies not tied to membership, missing with check on insert and update, default grants never revoked. Write one policy per operation calling a security definer is_member(org_id, roles) helper in a private schema with set search_path = ''.
4. Check that clients cannot insert into memberships, roles come from the membership row (not user_metadata or the request body), and invite tokens expire and are single-use.
5. If the repo uses Prisma, add a dbForOrg client extension that adds orgId to reads and bulk writes, sets it on creates, and throws on findUnique, findUniqueOrThrow, update and delete. Use it in every route.

For steps 1 to 4, report file, line number and offending code, grouped by step. Stop and wait for my approval. Then make the changes and explain each one. Add a two-org, two-user test that fails before the fix and passes after.

For a second pair of eyes on your repo afterwards, run it through Leakcade.

Further reading

1. OWASP Multi-Tenant Application Security Cheat Sheet
2. Supabase docs: Row Level Security
3. Prisma docs: Client extensions, query component
4. OWASP Top 10:2025 A01 Broken Access Control

FAQ