How to Implement Row-Level Security for a Multi-Tenant PostgreSQL SaaS

You chose shared-schema multi-tenancy to keep operational costs low and schema migrations simple. But now every query touches a tenant_id column, and you're not sure the isolation holds under adversarial conditions. A single misconfigured query could silently return rows belonging to another tenant — and in most systems that risk is carried entirely by application code.

PostgreSQL's row-level security (RLS) moves the isolation guarantee down into the database engine itself. Even if application code omits the tenant filter, the database will refuse to return other tenants' rows. This guide walks through a production-ready RLS setup.

Why Row-Level Security Belongs in Multi-Tenant PostgreSQL

Application-level filters are fragile. A new engineer adds a query without the tenant context, a library method does a raw scan, an admin endpoint skips the filter for "convenience" — any of these leaks data. RLS makes the database the last line of defense, not the application.

When RLS is enabled on a table and a policy is defined, PostgreSQL appends the policy predicate to every query automatically. You cannot read or write rows that fail the predicate without explicitly bypassing security (which requires BYPASSRLS privilege — a database superpower you should never grant to the application role).

Setting Up the Tenant Context

RLS policies need to know which tenant is active. PostgreSQL provides SET LOCAL for session-scoped settings, which you can read back inside policy expressions.

On every connection checkout, set the current tenant:

SET LOCAL app.current_tenant_id = '550e8400-e29b-41d4-a716-446655440000';

Read it back in policies using:

current_setting('app.current_tenant_id')::uuid

In practice this happens in your connection pool setup — PgBouncer transaction mode requires you to reset this on every acquired connection, not just at session start.

Defining RLS Policies

Enable RLS on each tenant-scoped table and create the USING predicate:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

FORCE ROW LEVEL SECURITY ensures the policy applies even to the table owner role. Without it, the role that owns the table bypasses RLS — a common oversight that undermines the whole guarantee.

For write operations you also want a WITH CHECK clause to prevent inserting rows that belong to a different tenant:

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

Handling Admin and Migration Roles

You need a privileged path for:

• Schema migrations: Run migrations as a migrator role that has BYPASSRLS. This role should never be reachable from the application process — only from your migration runner.
• Cross-tenant analytics: Create a reporter role with BYPASSRLS used exclusively by internal analytics queries, never exposed via the API.
• Backfills: Backfill scripts run as migrator, set an explicit WHERE tenant_id = X clause in the script, and are reviewed before execution.

Document these roles in your runbook. Ambiguity about which role to use for a task is how BYPASSRLS roles accidentally become the default.

Performance Considerations for Row-Level Security

RLS adds a predicate to every query, which means the planner must use it efficiently.

Index on tenant_id: Every tenant-scoped table needs an index on tenant_id. Without it, your policy predicate triggers sequential scans.

CREATE INDEX idx_orders_tenant_id ON orders (tenant_id);

For tables with many queries that filter by both tenant and another column, a composite index (tenant_id, created_at) is often better than two separate indexes.

Avoid non-immutable functions in policies: If your policy calls a function that isn't marked IMMUTABLE or STABLE, PostgreSQL re-evaluates it for every row. current_setting() is STABLE in PostgreSQL 14+, which is fine. Custom functions in policies should be benchmarked.

Check query plans: Run EXPLAIN (ANALYZE, BUFFERS) on hot queries after enabling RLS. Look for the appended filter in the plan and confirm an index is used.

Testing the Isolation Boundary

Automated tests for RLS are often skipped because they feel redundant with application-level tests. They are not redundant — they test the guarantee at the database level.

Write a test that:

1. Creates two tenants with rows in the same table.
2. Sets the session to tenant A.
3. Queries the table with no explicit WHERE tenant_id filter.
4. Asserts that only tenant A rows are returned.
5. Attempts to insert a row with tenant B's ID.
6. Asserts the insert is rejected.

Run these tests in CI against the same PostgreSQL version you use in production. Version upgrades occasionally change planner behavior in ways that affect RLS policy evaluation.

Common RLS Mistakes in Multi-Tenant SaaS

Using the application's tenant_id column without FORCE

Table owner roles bypass RLS unless you explicitly set FORCE ROW LEVEL SECURITY. Many tutorials omit this step.

PgBouncer transaction mode with session-level settings

PgBouncer in transaction mode reuses connections across requests. If you call SET app.current_tenant_id without SET LOCAL, the setting persists to the next transaction from a different tenant. Always use SET LOCAL and always set it on every connection checkout.

Missing policies on junction and log tables

Teams often add RLS to primary entity tables and forget join tables, audit logs, and event tables. Any table with rows that belong to a tenant needs a policy.

A Note on Hybrid Isolation Models

RLS works well as the primary isolation mechanism for small and medium tenants. For enterprise tenants with strict compliance requirements, you may eventually move them to schema-per-tenant or database-per-tenant isolation. When you do, the RLS policies on the shared-schema tables remain in place for the tenants that stay — the migration is additive, not a rewrite.

Build the RLS foundation early. It costs almost nothing to add at the start and is expensive to retrofit into a codebase that grew without it.

If you are designing or auditing a multi-tenant SaaS architecture, Clixo builds production-grade systems where isolation is treated as a first-class constraint from day one. Start a build.