Multi-Tenant SaaS Architecture with Supabase Row Level Security

Multi-tenancy is the defining architecture decision of a B2B SaaS, and Supabase's Row Level Security (RLS) has become the most popular way to implement it without running your own auth middleware on every query. It's also widely misconfigured — a leaked tenant's data is an unrecoverable trust event. This guide covers the architecture we use for production multi-tenant builds at PolarSoftBD.

The three multi-tenancy models

| Model | Isolation | Cost | When | |---|---|---|---| | Database-per-tenant | Strongest | Highest | Compliance-heavy, <100 enterprise tenants | | Schema-per-tenant | Strong | High ops burden | Rarely the right call | | Shared tables + RLS | Logical | Lowest | Default for SaaS |

Shared tables with RLS is the right starting point for almost every SaaS: one database, every tenant-owned row carries an org_id, and Postgres itself refuses to return rows the current user's organization doesn't own.

The foundation: memberships, not user columns

Model tenancy through a membership table from day one — even if today every org has one user:

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL
);

CREATE TABLE org_members (
  org_id  UUID REFERENCES organizations(id) ON DELETE CASCADE,
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  role    TEXT NOT NULL DEFAULT 'member',   -- 'owner' | 'admin' | 'member'
  PRIMARY KEY (org_id, user_id)
);

CREATE TABLE projects (
  id     UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id),
  name   TEXT NOT NULL
);

Retrofitting teams onto a user_id-owned schema later is a multi-week migration. Membership tables cost you nothing now and make "invite your team" a feature flag instead of a rewrite.

RLS policies that don't fall over

Enable RLS on every tenant table, then write policies against membership:

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- Helper: which orgs does the current user belong to?
CREATE OR REPLACE FUNCTION auth.user_orgs()
RETURNS SETOF uuid
LANGUAGE sql SECURITY DEFINER STABLE
AS $$
  SELECT org_id FROM public.org_members
  WHERE user_id = auth.uid()
$$;

CREATE POLICY "members can read org projects"
  ON projects FOR SELECT
  USING (org_id IN (SELECT auth.user_orgs()));

CREATE POLICY "members can insert org projects"
  ON projects FOR INSERT
  WITH CHECK (org_id IN (SELECT auth.user_orgs()));

Three rules that separate working RLS from leaky RLS:

SECURITY DEFINER helper functions avoid recursive RLS (the membership lookup itself would otherwise be subject to policies) and get planned once per statement.
FOR INSERT needs WITH CHECK, not USING — USING filters what you can see; WITH CHECK validates what you can write. Miss it and users insert rows into other tenants.
Policies are OR-ed, not AND-ed. A sloppy USING (true) policy added "temporarily for debugging" silently disables every other policy on the table. Audit with SELECT * FROM pg_policies.

The service-role foot-gun

The Supabase service_role key bypasses RLS entirely. Every Next.js server-side code path using it must enforce tenancy manually — and this is where real-world leaks actually happen, not in the policies:

// ❌ RLS won't save you here — service role bypasses it
const { data } = await supabaseAdmin.from("projects").select().eq("id", projectId);

// ✅ scope every admin query by org, verified from the session
const { data } = await supabaseAdmin
  .from("projects")
  .select()
  .eq("id", projectId)
  .eq("org_id", session.orgId);

House rule we enforce in client codebases: the service-role client lives in one file, wrapped by functions that all take orgId as a required first argument. Grep-able, reviewable, hard to misuse.

Performance: RLS is a WHERE clause — index like one

Every policy predicate runs on every query. Two non-negotiables:

Index org_id on every tenant table (and composite indexes (org_id, created_at) for your list views)
Wrap auth.uid() in a subselect inside policies — (SELECT auth.uid()) — so Postgres treats it as a stable init-plan instead of re-evaluating per row. On large tables this is the difference between 20ms and 2s.

Test tenancy as a feature

RLS deserves its own test suite: create two orgs, two users, and assert cross-tenant reads return zero rows and cross-tenant writes fail — for every table. Run it in CI against a shadow database. Untested policies are speculative security.

Storage and Realtime inherit nothing

Supabase Storage and Realtime have their own policy systems. A perfectly locked-down database with a public storage bucket still leaks every uploaded invoice. Prefix object paths with org IDs (org_id/filename) and write storage policies against the prefix.

Ship a multi-tenant SaaS without the security anxiety

Tenancy, RLS, roles, invitations, and billing-per-organization are the undifferentiated-but-dangerous 40% of every B2B SaaS. We build that layer constantly:

SaaS development service → — production multi-tenant builds in Next.js + Supabase/PostgreSQL, fixed price
Book a free 30-minute architecture call → — bring your schema, leave with a tenancy plan