One missing WHERE organization_id = ? was all it would take for one customer to see another customer's data.
That was the problem I had to eliminate while building a multi-tenant CRM serving 15+ organizations and processing 10,000+ sales records per day.
The frontend was React.
Authentication and APIs were handled through Supabase.
PostgreSQL was the source of truth.
But the important architectural decision was this:
Tenant isolation should not depend on every developer remembering to write the correct query.
I wanted PostgreSQL itself to enforce the security boundary.
So I used Row-Level Security (RLS).
Here's the architecture and the pattern I would use again.
The Problem: Multi-Tenancy Looks Easy Until It Becomes a Security Problem
A typical multi-tenant schema starts innocently:
CREATE TABLE deals (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL,
title TEXT NOT NULL,
value NUMERIC(12, 2) NOT NULL DEFAULT 0,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
Every deal belongs to an organization.
So developers naturally write:
SELECT *
FROM deals
WHERE organization_id = $1;
Seems fine.
Until the application grows.
Now you have:
- dashboard queries
- exports
- reports
- background jobs
- admin screens
- new API endpoints
- developers joining the project
Every query has to remember the same tenant filter.
Forget it once, and you potentially have a cross-tenant data leak.
For a SaaS application, that's not just a bug.
That's an architectural failure.
The Architecture I Used
At a high level, the system looked like this:
React
|
v
Supabase Auth
|
v
JWT
|
v
PostgreSQL
|
+--> organizations
+--> organization_members
+--> deals
+--> customers
+--> activities
|
v
Row-Level Security
The key idea was simple:
Authentication identifies the user.
Membership identifies which organization the user belongs to.
PostgreSQL decides which rows that user can access.
The React application does not become the final security boundary.
The database does.
Step 1: Model Tenant Membership Explicitly
Instead of storing tenant permissions only in application state, create an explicit relationship.
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE TABLE organization_members (
organization_id UUID NOT NULL
REFERENCES organizations(id)
ON DELETE CASCADE,
user_id UUID NOT NULL
REFERENCES auth.users(id)
ON DELETE CASCADE,
role TEXT NOT NULL
CHECK (role IN ('admin', 'manager', 'member')),
PRIMARY KEY (organization_id, user_id)
);
Now tenant membership is data the database can verify.
That becomes important when we create security policies.
Step 2: Every Tenant-Owned Record Gets an Organization ID
CREATE TABLE deals (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL
REFERENCES organizations(id)
ON DELETE CASCADE,
title TEXT NOT NULL,
value NUMERIC(12, 2) NOT NULL
CHECK (value >= 0),
created_by UUID NOT NULL
REFERENCES auth.users(id),
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
The important column is:
organization_id UUID NOT NULL
I don't want tenant-owned records where the tenant is ambiguous.
If the row belongs to an organization, that relationship should be explicit.
Step 3: Enable PostgreSQL Row-Level Security
ALTER TABLE deals ENABLE ROW LEVEL SECURITY;
Now we can define who is allowed to see which rows.
Step 4: Enforce Tenant Isolation in the Database
Here's the core read policy:
CREATE POLICY "Users can read deals in their organizations"
ON deals
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM organization_members om
WHERE om.organization_id = deals.organization_id
AND om.user_id = auth.uid()
)
);
Your application can execute:
SELECT *
FROM deals;
But PostgreSQL evaluates the policy for the authenticated user.
If the user belongs to organization A, rows belonging exclusively to organization B are not returned.
The security rule lives next to the data.
Don't Protect SELECT and Forget INSERT
This is an easy mistake.
You protect reads but forget that a user could potentially attempt to create a record for another tenant.
So writes need policies too.
CREATE POLICY "Users can create deals in their organizations"
ON deals
FOR INSERT
WITH CHECK (
EXISTS (
SELECT 1
FROM organization_members om
WHERE om.organization_id = deals.organization_id
AND om.user_id = auth.uid()
)
);
And updates:
CREATE POLICY "Users can update deals in their organizations"
ON deals
FOR UPDATE
USING (
EXISTS (
SELECT 1
FROM organization_members om
WHERE om.organization_id = deals.organization_id
AND om.user_id = auth.uid()
)
)
WITH CHECK (
EXISTS (
SELECT 1
FROM organization_members om
WHERE om.organization_id = deals.organization_id
AND om.user_id = auth.uid()
)
);
USING controls which existing rows can be targeted.
WITH CHECK controls what the resulting row is allowed to look like.
That distinction matters.
The React Code Becomes Boring
And that's good.
import { supabase } from "./supabase";
type Deal = {
id: string;
organization_id: string;
title: string;
value: number;
created_at: string;
};
export async function getDeals(): Promise<Deal[]> {
const { data, error } = await supabase
.from("deals")
.select("id, organization_id, title, value, created_at")
.order("created_at", { ascending: false });
if (error) {
throw new Error(`Failed to load deals: ${error.message}`);
}
return data ?? [];
}
Notice what's missing:
.eq("organization_id", organizationId)
You may still add tenant filters for query intent or performance.
But correctness should not depend on the frontend supplying one.
That's the architectural difference.
Add Role-Based Authorization on Top
Tenant isolation answers:
Which organization can this user access?
But most real systems also need:
What can this user do inside that organization?
Suppose only admins and managers should delete deals.
CREATE POLICY "Managers can delete organization deals"
ON deals
FOR DELETE
USING (
EXISTS (
SELECT 1
FROM organization_members om
WHERE om.organization_id = deals.organization_id
AND om.user_id = auth.uid()
AND om.role IN ('admin', 'manager')
)
);
Now authorization becomes:
authenticated user
↓
organization membership
↓
tenant isolation
↓
role authorization
↓
row access
Client-side permission checks are useful for UX.
They should not be your final security control.
Performance Still Matters
Security policies execute as part of your database workload.
If your policy repeatedly checks organization_id and user_id, give PostgreSQL an index that matches that access pattern.
CREATE INDEX idx_organization_members_user_org
ON organization_members (user_id, organization_id);
For tenant-heavy deal queries:
CREATE INDEX idx_deals_org_created
ON deals (organization_id, created_at DESC);
Once you're processing thousands of records every day, schema design and authorization design cannot be treated as separate topics.
Test Isolation Like a Security Feature
I don't consider RLS finished because the happy path works.
You need negative tests.
The important cases are:
- User A can read tenant A's records.
- User A cannot read tenant B's records.
- User A cannot insert records into tenant B.
- User A cannot move an existing record from tenant A to tenant B.
- A regular member cannot perform manager-only operations.
- Removing membership immediately removes access.
Conceptually:
describe("tenant isolation", () => {
it("does not expose another organization's deals", async () => {
const client = createAuthenticatedClient(userA);
const { data, error } = await client
.from("deals")
.select("*")
.eq("organization_id", organizationB.id);
expect(error).toBeNull();
expect(data).toEqual([]);
});
});
A multi-tenant application should test failed access as aggressively as successful access.
One More Important Warning: Service Roles Change the Threat Model
Supabase service-role credentials are privileged.
They should never be shipped to the browser.
Treat privileged server operations as a separate trust boundary:
Browser
|
| authenticated user token
v
RLS-protected database access
Trusted backend
|
| privileged server credential
v
Administrative operations
A perfect RLS policy won't save an application that accidentally exposes privileged credentials to the client.
What I Learned from Running This Architecture
This CRM eventually handled 10k+ daily sales records across 15+ client tenants.
The biggest lesson wasn't about PostgreSQL syntax.
It was about deciding where correctness lives.
I could have made every developer responsible for remembering:
WHERE organization_id = ?
Instead, I made tenant isolation a database invariant.
That gave the architecture multiple layers:
React → application experience
Supabase Auth → identity
PostgreSQL RLS → tenant isolation
RBAC policies → authorization
The closer a security rule gets to the data it protects, the harder that rule becomes to accidentally bypass.
For multi-tenant SaaS, that's a property I want by design, not by convention.
Your Turn
If you were designing a multi-tenant SaaS today, where would you enforce tenant isolation:
PostgreSQL RLS, the API/service layer, separate schemas/databases per tenant, or a combination of them?
And at what scale would you change that architecture?
Top comments (1)
Curious how others are handling multi-tenancy in production.
Would you trust PostgreSQL RLS as the primary tenant-isolation layer, or still enforce the same authorization rules again in the API/service layer?
I’ve found the database-level approach especially useful because one forgotten tenant filter can become a security issue. Interested to hear what architecture others are using at scale.