Building a multi-tenant SaaS platform forces you to make one of the earliest and most consequential architectural decisions: how do you isolate tenant data? Separate databases per tenant? Separate schemas? Or row-level security policies within a single database?
I’ve worked with all three approaches. Separate databases offer the strongest isolation but multiply operational complexity and cost. Separate schemas split the difference but still require careful management. Row-level security (RLS) in PostgreSQL, when implemented correctly, gives you strong isolation with minimal operational overhead.
This guide covers the technical patterns I use to build production multi-tenant systems with PostgreSQL RLS and Node.js. We’ll walk through tenant context propagation, security policies, connection pooling, and the performance patterns that keep systems responsive under load.
Why PostgreSQL RLS for Multi-Tenancy?
Row-level security is a database feature that enforces data access policies at the row level. Every query automatically filters results based on the current user’s role or context. For multi-tenant systems, this means you define one set of tables and let RLS policies ensure each tenant sees only their own data.
The practical advantages are significant:
- Single database instance reduces operational overhead (no managing dozens of database connections or failover clusters)
- Simpler backup and recovery (one database, not N)
- Lower infrastructure costs (you’re not paying for isolated database instances per tenant)
- Query optimization happens once across all tenants (indexes, query plans benefit everyone)
- Shared resources mean tenants with variable load don’t each need their own reserved capacity
The tradeoff is that your application and database layer must work together correctly. A bug in context propagation or policy definition can leak data across tenants. This is why the patterns matter.
Setting Up Tenant Context in PostgreSQL
RLS policies need to know which tenant is making the request. PostgreSQL provides session variables for exactly this: you set a variable at the start of each request, and policies reference it.
First, create a simple table structure and enable RLS:
-- Create tenants table
CREATE TABLE tenants (
id UUID PRIMARY KEY,
name TEXT NOT NULL
);
-- Create a users table within a tenant
CREATE TABLE users (
id UUID PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES tenants(id),
email TEXT NOT NULL,
name TEXT,
created_at TIMESTAMP DEFAULT NOW()
);
-- Enable RLS on the users table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
-- Create a policy: users can only see rows where tenant_id matches the current tenant
CREATE POLICY tenant_isolation ON users
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
The policy uses current_setting() to read a session variable. This variable must be set before every query. Let’s do that in Node.js middleware.
Tenant Context Propagation in Node.js
Every request needs to establish which tenant is making it. This happens through middleware that parses the tenant identifier and sets the PostgreSQL session variable.
import { Router } from 'express';
import { pool } from './db.js';
const router = Router();
// Middleware to extract and set tenant context
router.use(async (req, res, next) => {
// Extract tenant ID from request (header, JWT claim, subdomain, etc.)
const tenantId = req.headers['x-tenant-id'] ||
req.user?.tenant_id;
if (!tenantId) {
return res.status(400).json({ error: 'Missing tenant context' });
}
// Store on request object for later use
req.tenantId = tenantId;
// Set PostgreSQL session variable
// This will be used by RLS policies
req.dbClient = await pool.connect();
await req.dbClient.query(
`SET app.current_tenant_id = $1`,
[tenantId]
);
// Ensure client is released
res.on('finish', () => {
req.dbClient.release();
});
next();
});
// Example endpoint
router.get('/users', async (req, res) => {
// This query will automatically filter by tenant_id
// due to the RLS policy
const result = await req.dbClient.query('SELECT * FROM users');
res.json(result.rows);
});
export default router;
Each request gets its own database connection from the pool. We set the session variable on that connection, and all queries within that request automatically respect the RLS policy. When the request ends, the connection returns to the pool.
Connection Pooling for Multi-Tenant Workloads
Connection pooling becomes more important in multi-tenant systems because you have more concurrent requests competing for database connections. A poorly configured pool can become a bottleneck.
Here’s a production-grade pool configuration:
import pg from 'pg';
const pool = new pg.Pool({
host: process.env.DB_HOST,
port: process.env.DB_PORT || 5432,
database: process.env.DB_NAME,
user: process.env.DB_USER,
password: process.env.DB_PASSWORD,
// Pool settings
max: 50, // Maximum connections
min: 5, // Minimum idle connections
idleTimeoutMillis: 30000, // Close idle connections after 30s
connectionTimeoutMillis: 5000, // Fail fast if no connection available
allowExitOnIdle: false, // Don't exit process on idle
// Statement timeout per query
statement_timeout: 30000, // 30 second query timeout
});
// Monitor pool health
pool.on('error', (err) => {
console.error('Unexpected error on idle client', err);
});
pool.on('connect', () => {
console.log('New connection established');
});
export { pool };
Key settings for multi-tenant systems:
max: Set based on your expected concurrent users. Too low and requests queue; too high and PostgreSQL itself becomes the bottleneck.idleTimeoutMillis: Return idle connections to PostgreSQL to free resources. Especially important if you have many microservices connecting to the same database.statement_timeout: Prevent runaway queries from one tenant from blocking others.
One important caveat: if you’re using server-side connection pooling like pgBouncer, session variables may not persist across queries in transaction pooling mode. Session variables are connection-scoped, so they work reliably with application-level pooling (like node-postgres) but require careful configuration with pgBouncer. Test your pooling strategy thoroughly before production.
Comprehensive RLS Policies
A single USING clause isn’t always enough. You often need separate policies for SELECT, INSERT, UPDATE, and DELETE operations. Here’s a more complete example:
-- SELECT: Users can only see their own tenant's data
CREATE POLICY users_select ON users
FOR SELECT
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
-- INSERT: Users can only insert rows for their own tenant
CREATE POLICY users_insert ON users
FOR INSERT
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);
-- UPDATE: Users can only update their own tenant's rows
CREATE POLICY users_update ON users
FOR UPDATE
USING (tenant_id = current_setting('app.current_tenant_id')::UUID)
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);
-- DELETE: Users can only delete their own tenant's rows
CREATE POLICY users_delete ON users
FOR DELETE
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
The USING clause filters which rows the operation applies to. The WITH CHECK clause (for INSERT and UPDATE) ensures you can’t sneak in rows for other tenants. Both must pass.
Handling Joins Across Tenant Data
RLS policies apply to every table independently. When you join tables, each must have its own policy. This can get complex, so keep your schema clean:
-- Projects table
CREATE TABLE projects (
id UUID PRIMARY KEY,
tenant_id UUID NOT NULL REFERENCES tenants(id),
name TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY projects_isolation ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
-- Tasks table
CREATE TABLE tasks (
id UUID PRIMARY KEY,
project_id UUID NOT NULL REFERENCES projects(id),
tenant_id UUID NOT NULL REFERENCES tenants(id),
title TEXT NOT NULL,
created_at TIMESTAMP DEFAULT NOW()
);
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tasks_isolation ON tasks
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
Notice that tasks has both project_id and tenant_id. The tenant_id is redundant from a relational standpoint but essential for RLS. Every table that should be tenant-isolated needs a tenant_id column and a policy that checks it. This is the price of using RLS: your schema includes this column everywhere.
When you query, RLS automatically applies to both tables:
-- This query will only return tasks for the current tenant
-- because both tasks and projects have RLS policies
SELECT tasks.id, tasks.title, projects.name
FROM tasks
JOIN projects ON tasks.project_id = projects.id
WHERE projects.id = $1;
Preventing Context Leakage
The biggest security risk in RLS systems is forgetting to set the context variable. If you miss it, queries run without the filter and leak data.
Build a wrapper that enforces this:
// Safe query wrapper
async function query(client, text, values, tenantId) {
// Verify tenant context is set
const contextResult = await client.query(
`SELECT current_setting('app.current_tenant_id')`
);
const currentTenant = contextResult.rows[0].current_setting;
if (!currentTenant) {
throw new Error('Tenant context not set. RLS policies will not apply.');
}
if (currentTenant !== tenantId.toString()) {
throw new Error('Tenant context mismatch. Possible security issue.');
}
return client.query(text, values);
}
// Usage in route handler
router.get('/users/:id', async (req, res) => {
try {
const result = await query(
req.dbClient,
'SELECT * FROM users WHERE id = $1',
[req.params.id],
req.tenantId
);
res.json(result.rows[0]);
} catch (err) {
console.error('Query failed:', err.message);
res.status(500).json({ error: 'Internal server error' });
}
});
Performance Optimization for Multi-Tenant Queries
RLS adds a small overhead to every query, but the real performance challenge is ensuring queries are efficient when filtering across many tenants’ data.
Create indexes on both the tenant_id and query columns:
-- Index for RLS filtering
CREATE INDEX idx_users_tenant ON users(tenant_id);
-- Composite index for common queries
CREATE INDEX idx_users_tenant_email ON users(tenant_id, email);
-- For sorting or aggregation
CREATE INDEX idx_users_tenant_created ON users(tenant_id, created_at DESC);
PostgreSQL can use these indexes to efficiently filter rows before applying RLS. Without them, it scans the entire table and filters afterward, which is slow.
Monitor query performance with EXPLAIN ANALYZE:
EXPLAIN ANALYZE
SELECT * FROM users
WHERE email = 'user@example.com';
Look for “Index Scan” in the plan. If you see “Seq Scan” (full table scan), add an index.
Handling Admin Operations and Bypassing RLS
Sometimes you need to query across all tenants (for reporting, debugging, or admin operations). RLS policies can be bypassed by a superuser or by disabling RLS temporarily.
Create a separate admin connection with different privileges:
// Regular pool for tenant requests
const tenantPool = new pg.Pool({
...poolConfig,
user: process.env.DB_USER // Regular user with RLS policies
});
// Admin pool for privileged operations
const adminPool = new pg.Pool({
...poolConfig,
user: process.env.DB_ADMIN_USER // Superuser or admin role
});
// In admin endpoint
router.get('/admin/all-users', async (req, res) => {
// Verify the request is authenticated as admin
if (!req.user?.isAdmin) {
return res.status(403).json({ error: 'Forbidden' });
}
const client = await adminPool.connect();
try {
// This query bypasses RLS because the admin user has BYPASSRLS privilege
const result = await client.query(
'SELECT tenant_id, COUNT(*) FROM users GROUP BY tenant_id'
);
res.json(result.rows);
} finally {
client.release();
}
});
Never use the admin connection for regular tenant requests. Keep it isolated to truly privileged operations.
Testing Multi-Tenant Isolation
Write tests that verify RLS policies are actually working. It’s easy to think they are when they’re not:
import { describe, it, expect, beforeEach } from 'vitest';
import { pool } from './db.js';
describe('RLS Isolation', () => {
let client1, client2;
const tenant1 = '550e8400-e29b-41d4-a716-446655440001';
const tenant2 = '550e8400-e29b-41d4-a716-446655440002';
beforeEach(async () => {
client1 = await pool.connect();
client2 = await pool.connect();
// Set different tenant contexts
await client1.query(
`SET app.current_tenant_id = $1`,
[tenant1]
);
await client2.query(
`SET app.current_tenant_id = $1`,
[tenant2]
);
});
it('should not allow tenant1 to see tenant2 data', async () => {
// Insert data as tenant2
await client2.query(
'INSERT INTO users (id, tenant_id, email) VALUES ($1, $2, $3)',
['user-id-1', tenant2, 'user@tenant2.com']
);
// Try to read as tenant1
const result = await client1.query('SELECT * FROM users');
// Tenant1 should see no rows
expect(result.rows).toHaveLength(0);
});
afterEach(async () => {
client1.release();
client2.release();
});
});
Monitoring and Observability
Track tenant context propagation and query performance:
// Middleware to log tenant context
router.use((req, res, next) => {
req.startTime = Date.now();
res.on('finish', () => {
const duration = Date.now() - req.startTime;
console.log({
method: req.method,
path: req.path,
tenantId: req.tenantId,
statusCode: res.statusCode,
duration: `${duration}ms`,
timestamp: new Date().toISOString()
});
});
next();
});
Use PostgreSQL’s built-in logging to track query execution:
-- Enable query logging
ALTER DATABASE your_db SET log_min_duration_statement = 1000; -- Log queries > 1 second
ALTER DATABASE your_db SET log_statement = 'all'; -- Log all statements
Scaling Considerations
As your multi-tenant system grows, keep these patterns in mind:
- Connection pooling becomes critical. Monitor pool utilization and adjust max connections based on actual traffic.
- RLS policies add negligible overhead for well-indexed queries. If queries are slow, the issue is usually missing indexes, not RLS.
- Separate schemas or databases make sense only when you need tenant-specific customizations (custom columns, different data structures) or when regulatory requirements demand it.
- For very large tenants, consider sharding at the application level. RLS doesn’t prevent a single tenant from consuming all resources.
- Always have a plan for tenant data export and migration. Multi-tenant systems can make this complex.
Conclusion
PostgreSQL RLS is a powerful tool for building multi-tenant systems that are secure, cost-effective, and operationally simple. It moves the isolation logic from your application layer into the database, where it’s harder to accidentally bypass.
The key is getting the foundation right: proper tenant context propagation, comprehensive policies, well-indexed schemas, and thorough testing. Once those are in place, you have a system that scales efficiently and keeps tenant data genuinely isolated without the overhead of separate databases.
Start with the patterns in this guide, build your test suite early, and monitor carefully as you scale. The investment in getting RLS right pays off in operational simplicity and security.
What happens if I forget to set the tenant context variable?
The RLS policy will filter against a NULL value, and users will see no data. This is actually safer than a data leak, but it will break your application. Use the wrapper function shown in the article to verify context is always set before querying.
Can I use RLS for authorization beyond tenant isolation?
Yes. You can create policies based on user roles, row-level permissions, or custom functions. For example, a policy could check if the current user is the owner of a record before allowing access. This extends RLS beyond multi-tenancy into fine-grained access control.
Does RLS work with prepared statements and parameterized queries?
Yes, and you should always use prepared statements. RLS policies are applied after query parsing but before execution, so parameterized queries are fully compatible and recommended for security.
How do I handle migrations when adding RLS to an existing database?
Create the tenant_id column on existing tables, backfill it with the correct tenant identifiers, add the RLS policies, and test thoroughly before enabling RLS in production. Migration strategies depend on your data structure, so plan carefully.
Is RLS suitable for all multi-tenant architectures?
RLS works well for most SaaS platforms where tenants share infrastructure. It’s less suitable if you need complete data isolation for regulatory reasons (separate databases may be required) or if tenants need custom database schemas and columns.
What about pgBouncer and session variables?
Session variables are connection-scoped in PostgreSQL. With application-level pooling (like node-postgres), this works reliably. However, server-side connection poolers like pgBouncer in transaction pooling mode may not preserve session variables across queries. Test your specific pooling configuration thoroughly before production deployment.
