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

> Step-by-step guide to implementing PostgreSQL row-level security for multi-tenant SaaS applications, including policies, pitfalls, and performance tuning.

- **Published:** 2025-03-03
- **Author:** Clixo
- **Reading time:** 6 min read
- **Tags:** multi-tenant, postgresql, row-level-security, saas-architecture, data-isolation
- **Canonical URL:** https://clixo.sh/blog/row-level-security-multi-tenant-postgres-implementation

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).

```mermaid
sequenceDiagram
  participant APP as Application
  participant PG as PostgreSQL
  participant POL as "RLS Policy"
  APP->>PG: SET LOCAL app.current_tenant_id = X
  APP->>PG: SELECT * FROM orders
  PG->>POL: Evaluate tenant_id = current_setting(...)
  POL-->>PG: Filter rows to tenant X only
  PG-->>APP: Rows belonging to tenant X
```

## 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:

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

Read it back in policies using:

```sql
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:

```sql
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:

```sql
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.

```sql
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](https://clixo.sh/#contact).

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)