Postgres Row Level Security (RLS): Building Multi-tenant SaaS Backends Safely

When building a Multi-tenant Software-as-a-Service (SaaS) application—where thousands of different corporate customers (tenants) share the identical codebase and database infrastructure—data isolation is your highest priority.

The absolute worst nightmare for any SaaS engineer is a tenant data leak: showing Tenant B's private user lists or invoices to Tenant A due to a minor developer bug.

Historically, we handled data isolation by appending manual filters to every single SQL query:

sql
-- ❌ DANGEROUS: Highly vulnerable to developer typos or missing clauses
SELECT * FROM invoices WHERE tenant_id = ? AND id = ?;

If a developer forgets to append AND tenant_id = ? inside a newly written API endpoint, the application will suddenly leak private rows globally.

In modern systems engineering, we solve this permanently by shifting data isolation directly to the database engine using Postgres Row Level Security (RLS).

RLS acts as a secure, database-level firewall. Once enabled, Postgres automatically intercepts all SQL queries, appending tenant isolation constraints behind the scenes, ensuring that even if your application code executes a raw SELECT * FROM invoices, a tenant will only ever see their own data.

In this guide, we'll build a highly secure, multi-tenant database schema using Postgres RLS policies.

⚡ 1. How RLS Works: Database-Level Firewalls

Row Level Security allows you to attach Policies to tables. A policy is a boolean mathematical expression that Postgres evaluates for every single row targeted by an incoming query. If the expression returns true, the row is returned; if false, the row is silently filtered out as if it doesn't exist.

To isolate tenants, we feed the active tenant ID into the Postgres session context when a database connection is acquired, and configure our policies to match this session variable.

[Incoming HTTP Request] ──(Sets Session: app.current_tenant_id)──> [Postgres Connection]
                                                                        │
[Applies RLS Policy: tenant_id = current_setting(...)] <────────────────┘ ──> [Filters Rows Safely]

🏗️ 2. Implementing the Multi-tenant Schema

Let's design a secure multi-tenant invoice database.

Step A: Enable RLS on Tables

First, we create our tables and explicitly activate the Row Level Security engine:

sql
-- 1. Create a table to store tenants
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL
);

-- 2. Create the invoices table bound to a tenant
CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID REFERENCES tenants(id) ON DELETE CASCADE,
    amount DECIMAL(10, 2) NOT NULL,
    customer_name TEXT NOT NULL
);

-- 3. CRITICAL: Enable Row Level Security on the invoices table!
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

Once ENABLE ROW LEVEL SECURITY is run, Postgres blocks all non-owner connections from reading or writing to the invoices table by default, until we write our access policies.

🛠️ 3. Creating the Tenant Isolation Policy

To authenticate which tenant is currently querying the database, we use Postgres's internal session variable utility set_config. We write an RLS policy that reads this configuration variable:

sql
-- Create an isolation policy for invoices
CREATE POLICY tenant_invoice_isolation ON invoices
    AS RESTRICTIVE
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::UUID);

Analyzing the Policy Logic:

• current_setting('app.current_tenant_id', true): Reads the custom variable app.current_tenant_id from the active database session context. The second parameter true prevents Postgres from throwing an error if the variable is not yet initialized.
• USING (tenant_id = ...): Enforces that the database will only return rows where the row's tenant_id matches the active session's configuration variable UUID.

🚀 4. Executing Safe Queries in JavaScript (Node.js)

When your backend API server receives a request:

1. 2.
   Extract the tenant's ID from the request headers or JWT payload.
2. 4.
   Acquire a database connection.
3. 6.
   Wrap all queries inside a transaction, setting the session variable as the very first operation:

typescript
import { Pool } from "pg";

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

async function getTenantInvoices(tenantId: string): Promise<any[]> {
  const client = await pool.connect();

  try {
    // 1. Begin SQL Transaction
    await client.query("BEGIN");

    // 2. Feed the active tenant ID into the Postgres session context
    // This variable exists strictly for this database client transaction!
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);

    // 3. Execute query
    // Notice we do NOT manually append "WHERE tenant_id = $1"!
    // Postgres RLS automatically intercepts and applies the filter securely!
    const res = await client.query("SELECT * FROM invoices");

    await client.query("COMMIT");
    return res.rows;

  } catch (error) {
    await client.query("ROLLBACK");
    throw error;
  } finally {
    // 4. Always release database client back to connection pool
    client.release();
  }
}

🏁 5. Conclusion: Database-Level Security Defenses

Relying on developers to remember manual query filters in fast-paced SaaS teams is an insecure architecture. By shifting data isolation directly to the Postgres Row Level Security (RLS) engine, you establish a solid, centralized security perimeter at the database layer. No matter how many new API endpoints or dashboard features are added in the future, your tenant data remains mathematically isolated, preventing leaks and ensuring robust enterprise-grade security.