A single misplaced WHERE clause in your application code can expose one tenant's data to another. That is not a hypothetical risk — it is a class of bug that has caused real data breaches in production SaaS systems. The standard response is to add more application-level guards, more code review, more tests. But there is a more durable answer: push the isolation guarantee down to the database itself using PostgreSQL's Row-Level Security (RLS).
This guide walks through designing a multi-tenant data model, enabling RLS, writing policies, and wiring it all together in a way that survives developer mistakes.
The Multi-Tenancy Spectrum
There are three common approaches to multi-tenancy in relational databases:
- Separate databases per tenant — Maximum isolation, maximum operational overhead.
- Separate schemas per tenant — Good isolation, but schema migrations become painful at scale and connection pooling gets complicated.
- Shared schema with a
tenant_idcolumn — Lowest overhead, easiest to operate, but isolation depends entirely on application-layer filtering.
The shared-schema model is the pragmatic choice for most SaaS products, especially in the early-to-mid growth stage. The weakness — that a missing WHERE tenant_id = ? can leak data — is exactly what RLS fixes.
Setting Up the Data Model
Start with a tenants table and a users table, then tag every data table with a tenant_id foreign key.
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id),
name TEXT NOT NULL,
created_at TIMESTAMPTZ DEFAULT now()
);
-- Index for performance — RLS policies scan this column constantly
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);
Every table that holds tenant-specific data gets this same tenant_id column and index. Consistency here is non-negotiable.
Enabling Row-Level Security
RLS is opt-in per table. Enabling it without defining any policies locks the table down completely — no rows are visible to anyone except the table owner. That is a useful default.
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- Force RLS even for the table owner (superusers bypass RLS by default)
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
FORCE ROW LEVEL SECURITY is the line most tutorials skip. Without it, any connection running as the table owner — including your migration runner or an admin script — bypasses your policies entirely. Enabling it closes that gap.
Writing the Policies
RLS policies are SQL expressions evaluated against every row. PostgreSQL provides a session-level setting mechanism that your application uses to declare the current tenant context.
-- Allow tenants to see only their own rows
CREATE POLICY tenant_isolation_policy ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);
The USING clause applies to SELECT, UPDATE, and DELETE. For INSERT, you add a WITH CHECK clause to prevent a tenant from writing rows with a foreign tenant_id:
CREATE POLICY tenant_isolation_policy ON projects
USING (tenant_id = current_setting('app.current_tenant_id')::UUID)
WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);
One policy covers reads and writes. Tenants cannot see rows that do not belong to them, and they cannot insert rows that claim to belong to someone else.
Setting the Tenant Context at Runtime
Your application must set app.current_tenant_id at the start of every database transaction. How you do this depends on your stack, but the pattern is universal.
In a Node.js / Express API, this typically lives in middleware:
-- Called before any query in the request transaction
SET LOCAL app.current_tenant_id = '<uuid-from-jwt>';
SET LOCAL scopes the setting to the current transaction only, which is exactly the right granularity. SET (without LOCAL) persists for the entire session — dangerous with connection poolers like PgBouncer that reuse connections across requests.
Handling the "No Tenant" Case
current_setting() throws an error if the variable is not set. Use the two-argument form to return a default instead:
current_setting('app.current_tenant_id', true)
The second argument true tells Postgres to return NULL rather than raise an exception. Your policy then becomes:
USING (tenant_id = current_setting('app.current_tenant_id', true)::UUID)
A NULL UUID matches no rows, so an unauthenticated or improperly configured connection sees an empty result set rather than an error or, worse, all rows.
Least-Privilege Database Roles
RLS policies are most effective when paired with least-privilege roles. Your application should connect as a role that has no direct table ownership — otherwise FORCE ROW LEVEL SECURITY is your only backstop.
CREATE ROLE app_user NOLOGIN;
GRANT SELECT, INSERT, UPDATE, DELETE ON projects TO app_user;
CREATE ROLE api_runtime LOGIN PASSWORD 'strong_password';
GRANT app_user TO api_runtime;
The api_runtime role is what your connection string uses. It can read and write data, but it does not own the tables, so the RLS policies apply unconditionally.
Testing Your Policies
Never ship RLS policies without explicit tests. Use two separate database connections, each setting a different app.current_tenant_id, and assert that neither connection can read the other's rows — even with unrestricted queries like SELECT * FROM projects.
A simple integration test matrix:
- Tenant A can read its own projects.
- Tenant A cannot read Tenant B's projects.
- Tenant A cannot insert a row with
tenant_idset to Tenant B's UUID. - A connection with no tenant context set sees zero rows.
These tests should live in your CI pipeline and run on every migration.
Performance Considerations
RLS adds a predicate to every query. With a proper index on tenant_id, the overhead is negligible — Postgres uses an index scan rather than a sequential scan. The index you created earlier is not optional; treat it as part of the RLS setup, not an afterthought.
For tenants with very large datasets, consider partitioning tables by tenant_id. Declarative partitioning in Postgres 14+ means the planner can skip entire partitions for single-tenant queries, giving you near-dedicated-schema performance from a shared-schema model.
Why This Matters for Your Project
If you are building a SaaS product on a shared Postgres instance, application-layer filtering is a single point of failure — one junior developer, one copy-paste error, one ORM quirk away from a data leak. RLS moves the enforcement boundary to the database engine itself, where it is immune to application bugs, misconfigured ORMs, and ad-hoc admin queries. For any team shipping to regulated industries — fintech, healthtech, legal — this is not a nice-to-have. It is the architecture that makes compliance audits tractable and customer trust defensible.
