Imagine the worst possible scenario for any founder or CTO of a B2B SaaS platform. You open your inbox in the morning and see an email from one of your largest enterprise clients: "Hello, when I opened the invoice dashboard in our portal this morning, for a split second I saw a list of invoices belonging to another company. I downloaded a PDF, and it indeed contains confidential financial data of our direct competitor. What is going on?"
In that moment, your blood runs cold. While data leaks in the B2C space are highly problematic, leaking sensitive corporate data, pricing tiers, client databases, or contracts in the B2B sector represents an immediate loss of trust, a breach of Non-Disclosure Agreements (NDAs), and, for many startups, the end of their business.
When developing multi-tenant B2B applications (where multiple companies share the same software instance), strong data isolation is the single most critical engineering requirement. But how do you design this isolation to remain scalable, easily maintainable, and resistant to human developer error?
At the nolimeo technology studio, we avoid fragile, surface-level isolation patterns implemented solely in application-level code. In this detailed technical article, we break down why traditional application-based filtering fails, explain how a modern multi-tenant architecture operates using PostgreSQL Row Level Security (RLS) and Supabase, and present concrete SQL policies and optimization patterns that help secure your clients' data.
1. The Multi-Tenant Database Dilemma: Which Path to Choose?
When designing a system that will be used by dozens, hundreds, or thousands of companies (tenants), you must decide how their data is physically and logically organized in the database. There are three primary approaches:
A. Physical Isolation (Database-per-Tenant)
Each customer has their own completely separate database instance.
- Pros: Maximum security. Inter-tenant data leakage is physically impossible. Each client can manage their own backups, encryption keys, and hardware resources.
- Cons: Extremely expensive to run and a major operational burden to maintain. If you have 500 tenants and need to add a single column to a table, you must run 500 database migrations and manage 500 connection pools.
B. Schema-level Isolation (Schema-per-Tenant)
All tenants share one physical database, but each is assigned its own database schema (namespace).
- Pros: A reasonable compromise between security and shared database resources.
- Cons: PostgreSQL is not optimized to handle tens of thousands of schemas. This approach causes system catalog bloat, slows database startup times, and makes migrations complex.
C. Logical Isolation in a Shared Schema (Shared Database, Shared Schema)
All tenants share the exact same tables. Every row in every table contains a tenant_id column (e.g., a company identifier) that is used to filter data.
- Pros: Highly cost-effective, simple database migrations (schema changes happen once for everyone), and easy global aggregation for administrators.
- Cons: A high risk of human error.
In traditional application-based isolation, the filtering logic is handled inside the backend application (using ORM queries). The developer must explicitly write the filter criteria for every single query:
// Traditional ORM query - highly vulnerable to fatal omissions
const customerInvoices = await db.invoices.findMany({
where: {
tenant_id: activeUser.tenant_id // If the developer forgets this, all corporate invoices leak!
}
});
It only takes one tired developer, a missed line during code review, or a complex SQL JOIN query where the filter is omitted for the database to expose another client's data. Relying on humans to never make a single mistake across thousands of lines of application code is a massive risk.
This is exactly where PostgreSQL Row Level Security (RLS) comes in.
2. PostgreSQL RLS: Moving Security from the Application to the Database Core
Row Level Security (RLS) is a native PostgreSQL feature that shifts the responsibility of data isolation from the application backend (e.g., Node.js or Next.js) directly to the database engine.
The principle is simple: the database itself evaluates every incoming query and, based on defined access policies, automatically appends the necessary isolation constraints. Even if your backend application runs a query like:
SELECT * FROM invoices;
PostgreSQL RLS will rewrite and execute it under the hood as:
SELECT * FROM invoices WHERE tenant_id = 'current_user_tenant_id';
This query modification happens regardless of the framework you use, how complex the queries are, or whether a developer forgot to write a filter. The database simply blocks any row that does not satisfy the security policy.
Backend Application (Next.js / Node.js)
│
▼ (Attempts: SELECT * FROM invoices;)
┌──────────────────────────────────────────────┐
│ PostgreSQL Database Core │
│ │
│ [ RLS Engine ] ◄── Verifies active JWT/Role │
│ │ │
│ ▼ (Automatic query rewrite) │
│ SELECT * FROM invoices WHERE tenant_id = X; │
└──────────────────────────────────────────────┘
3. Technical Implementation: SQL DDL & Policy Examples
Let's look at how to design a secure database schema for a multi-tenant B2B platform. We will use PostgreSQL integrated with Supabase, which maps user authentication (auth.users) to database tables and parses verified JWT claims.
Step 1: Table Schemas
We will create three basic tables: companies (tenants), employee profiles (users), and the actual business data—invoices (invoices).
-- Enable UUID extension for secure ID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 1. Tenants Table
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name VARCHAR(255) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);
-- 2. Users Table (linked to Supabase Auth)
CREATE TABLE users (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE NOT NULL,
email VARCHAR(255) NOT NULL,
role VARCHAR(50) DEFAULT 'member' NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);
-- 3. Invoices Table (B2B Data)
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE NOT NULL,
client_name VARCHAR(255) NOT NULL,
amount NUMERIC(10, 2) NOT NULL,
due_date DATE NOT NULL,
created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);
Step 2: Enabling Row Level Security (RLS)
By default, RLS is disabled on newly created tables. We must explicitly enable it. For maximum safety, we also use the FORCE command, ensuring that RLS policies apply even to the table owner, preventing accidental admin bypasses.
-- Enable RLS for users and invoices tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
Step 3: Writing RLS Policies (Classic JOIN Approach)
Next, we instruct the database under which conditions a user can read and write rows in the invoices table. The classic approach is comparing the tenant_id of the invoice with the tenant_id of the logged-in user retrieved from the users table.
In Supabase, we retrieve the ID of the currently authenticated user using the helper function auth.uid(), which safely extracts the user ID from the verified JWT.
-- Policy for reading invoices
CREATE POLICY select_tenant_invoices ON invoices
FOR SELECT
TO authenticated
USING (
tenant_id = (
SELECT tenant_id
FROM users
WHERE id = auth.uid()
)
);
-- Policy for inserting new invoices
CREATE POLICY insert_tenant_invoices ON invoices
FOR INSERT
TO authenticated
WITH CHECK (
tenant_id = (
SELECT tenant_id
FROM users
WHERE id = auth.uid()
)
);
4. Performance Optimization: Avoiding the "RLS Join Trap"
While the JOIN policy shown above is secure, it presents a significant database performance bottleneck. For every single row evaluated in the invoices table, PostgreSQL must execute the subquery SELECT tenant_id FROM users WHERE id = auth.uid().
If your application fetches a list of 100 invoices or performs complex aggregates, the database executes this subquery repeatedly. As your tables grow to hundreds of thousands of rows, these repetitive JOIN operations will dramatically degrade query performance.
The Solution: Custom JWT Claims in Supabase
Supabase allows us to inject custom metadata directly into the user's JWT token upon login. Instead of querying the users table in SQL, we can store the user's tenant_id directly in the token and read it quickly in our RLS policies with $O(1)$ complexity without touching other tables.
Once a user logs in, we assign their tenant_id to their JWT's app_metadata.
We can then rewrite our RLS policy to run at extreme speed:
-- High-performance RLS policy reading tenant_id directly from JWT claims
CREATE POLICY optimized_select_invoices ON invoices
FOR SELECT
TO authenticated
USING (
tenant_id = (auth.jwt() -> 'app_metadata' ->> 'tenant_id')::uuid
);
This optimization completely removes subquery overhead from RLS, making PostgreSQL RLS an enterprise-grade solution suitable for high-transaction workloads.
Indexing Foreign Keys
It is equally critical to ensure that a B-Tree index exists on the tenant_id column of every table. Without this index, the database will be forced to perform a Full Table Scan to evaluate the RLS policy.
-- Critical index for sub-10ms query times under RLS
CREATE INDEX IF NOT EXISTS invoices_tenant_id_idx ON invoices(tenant_id);
5. Production Risks: Managing the service_role and Automating Security Audits
Running RLS in production requires addressing two primary challenges: handling background system tasks and ensuring new tables are never deployed without RLS.
5.1. Bypassing RLS for System Tasks (service_role)
Sometimes you need to run background scripts that calculate company-wide reports, or night cron jobs that process subscriptions. These processes run without an authenticated user and do not have a tenant_id in their JWT.
For this purpose, Supabase provides a special service_role key. This key acts as a super-admin, bypassing all RLS policies.
Strict Engineering Rule: The service_role key must never be exposed to the client-side browser. It must remain strictly in secure server-side environments (Next.js Server Actions, Node.js microservices) protected by environment variables.
Here is how to safely initialize a Supabase client that bypasses RLS for backend scripts using TypeScript:
// src/lib/supabase-admin.ts
import { createClient } from "@supabase/supabase-js";
const supabaseUrl = process.env.NEXT_PUBLIC_SUPABASE_URL;
const supabaseServiceKey = process.env.SUPABASE_SERVICE_ROLE_KEY; // Server-only!
if (!supabaseUrl || !supabaseServiceKey) {
throw new Error("Missing Supabase environment variables.");
}
/**
* Initializes the admin client that bypasses RLS.
* Use exclusively for backend cron jobs, database migrations, and internal analytics.
*/
export const getSupabaseAdmin = () => {
return createClient(supabaseUrl, supabaseServiceKey, {
auth: {
persistSession: false, // Do not persist sessions in backend environments
autoRefreshToken: false,
},
});
};
5.2. Preventing "Silent Leaks" with Automated CI/CD Audits
As your application grows, human error remains a risk. A developer might create a new table called contracts, add a tenant_id column, but forget to run ALTER TABLE contracts ENABLE ROW LEVEL SECURITY;. In PostgreSQL, new tables are accessible by default, opening a severe security loophole.
We eliminate this risk with automated database audits in your CI/CD pipeline.
We add a test suite in TypeScript (e.g., using Vitest or Jest) that queries the PostgreSQL system catalog to identify any public tables that do not have RLS enabled. If an unprotected table is found, the build fails and blocks deployment.
// src/__tests__/db-security.test.ts
import { getSupabaseAdmin } from "../lib/supabase-admin";
describe("Database RLS Enforcement Audit", () => {
it("should enforce RLS on all public tables", async () => {
const supabase = getSupabaseAdmin();
// Query system catalog for tables without RLS enabled
const fallbackQuery = `
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND rowsecurity = false
AND tablename NOT IN ('schema_migrations', 'spatial_ref_sys');
`;
// Execute query using raw SQL executor or pg node client
const { data: unsafeTables, error: queryError } = await supabase
.from("_raw_sql" as any)
.select("*")
.csv();
const unprotectedTables: string[] = []; // Process query results here
expect(unprotectedTables).toEqual([]);
});
});
6. Why You Should Avoid No-Code Workarounds
No-code and low-code platforms (like Make.com, Zapier, and Retool) are popular for building quick integrations. However, for multi-tenant B2B apps, they present severe security vulnerabilities.
These tools typically connect to your database using high-privilege credentials and process data inside third-party clouds. One misconfigured webhook or an exposed API key on a visual editor can expose your B2B client data quickly.
Security in multi-tenancy must be anchored on three principles:
- Engine Level: Enforced directly in the database engine (PostgreSQL RLS).
- Type Safety: Strictly typed in TypeScript and validated at runtime with Zod schemas.
- Code Ownership: Executed on infrastructure you fully own, control, and audit.
Conclusion: Secure Your Infrastructure Before It's Too Late
PostgreSQL Row Level Security combined with Supabase is the industry gold standard for B2B SaaS data isolation. Shifting security logic from application code to the database engine protects your business from the most common cause of leaks: human developer error.
However, implementing a robust and highly performant multi-tenant architecture requires experienced database engineers who understand indexing, JWT management, subquery optimization, and automated testing frameworks.
We are nolimeo—a specialized boutique software engineering studio. Our clients do not pay for bloated agency overhead or talk to juniors. We design and program every project as senior developers. We focus on secure, clean code, enterprise software stability, and reliable integrations of modern tech, including enterprise AI.
Are you planning to build a B2B SaaS product, looking to isolate your client databases securely, or seeking a security audit of your current system? Contact us and we’ll review your tenant model, database policies, and isolation architecture.