Multi-Tenant SaaS Database Architecture: Schema-per-Tenant vs Shared Tables with RLS

When building a Software-as-a-Service (SaaS) web application, data isolation between distinct customer accounts (tenants) is the single most critical architectural requirement.

A software defect that exposes one enterprise customer’s confidential customer records, financial margins, or employee data to another tenant represents a catastrophic security failure that destroys business reputation and triggers severe statutory penalties under the DPDP Act and GDPR.

Engineering teams face a fundamental architectural crossroads: Should you isolate tenants physically by creating separate databases, isolate them logically using database schemas, or store all tenant records inside shared tables protected by PostgreSQL Row Level Security (RLS)?

In this architectural guide, Cyberfact Security evaluates each paradigm across cost, operational maintenance, and vulnerability risk.

1. The Three Multi-Tenancy Architectural Models

[ Model A: Database-per-Tenant ]
Tenant A ──► Database A (Physical Isolation, High Hosting Cost, Complex Migrations)
Tenant B ──► Database B

[ Model B: Schema-per-Tenant ]
Tenant A ──► Postgres Schema "tenant_a" (Shared Database Engine, Logical Isolation)
Tenant B ──► Postgres Schema "tenant_b"

[ Model C: Shared Tables with Row Level Security (RLS) ]
All Tenants ──► Shared Tables with "tenant_id" column (Enforced via Postgres Kernel RLS)

2. Comparative Trade-Off Matrix

3. Implementing Hardened PostgreSQL Row Level Security (RLS)

PostgreSQL includes kernel-level Row Level Security (RLS), ensuring that even if an application developer forgets a WHERE tenant_id = x clause in their query, the database engine mathematically refuses to return rows belonging to other tenants:

-- 1. Create Tenant-Aware Customers Table
CREATE TABLE customers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
);

-- 2. Enable Row Level Security
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

-- 3. Define RLS Policy Based on Active Session Context
CREATE POLICY tenant_isolation_policy ON customers
    FOR ALL
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

Application Connection Middleware:

// Express / Fastify Session Middleware Setting Tenant Context
async function setTenantContext(req: Request, res: Response, next: NextFunction) {
  const tenantId = req.user.tenantId;
  const client = await dbPool.connect();

  try {
    // Set session variable inside transaction
    await client.query(`SET LOCAL app.current_tenant_id = '${tenantId}';`);
    req.dbClient = client;
    next();
  } catch (error) {
    client.release();
    res.status(500).json({ error: 'Failed to establish tenant security context' });
  }
}

Need an Enterprise-Grade Custom Web Application?

At Cyberfact Security & Engineering Desk, we architect, build, and harden high-performance web applications, enterprise SaaS platforms, and secure digital portals for startups and global enterprises.

• Zero-Trust Security by Design: Built from Day 1 with penetration testing and security audits included.
• Sub-Second Performance Guarantee: 100/100 Core Web Vitals and lightning-fast edge delivery worldwide.
• Full-Stack Mastery: Astro, Next.js, React, Node.js, Go, Python, and hardened cloud infrastructure.

Discuss your project with our engineering leads:

• Founder Direct WhatsApp Desk: +91 82520 02914
• Direct Email: info@cyberfactsecurity.com
• Interactive Project Scoping: Start Project Scope Wizard