Postgres Row-Level Security: Multi-Tenant Isolation Without Application Logic

Your application's WHERE clause is not a security boundary. It is a convenience filter. If you are relying on WHERE tenant_id = $current_tenant sprinkled through your queries to enforce multi-tenant isolation, a single missed clause silently serves one tenant's data to another. Row-level security (RLS) moves that boundary into Postgres.

What RLS Does

RLS attaches predicates to a table that Postgres evaluates automatically, for every query, before returning rows. A SELECT, UPDATE, or DELETE that does not match the policy sees zero rows — no error, just an empty result. You cannot forget to add the predicate because Postgres adds it for you.

ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Now every query on documents is automatically scoped to the current tenant. The application sets the configuration parameter at the start of each request:

SET LOCAL app.tenant_id = '3f7a1b2c-...';
SELECT * FROM documents WHERE status = 'active';
-- Postgres appends: AND tenant_id = '3f7a1b2c-...'

USING vs WITH CHECK

USING filters rows on read operations (SELECT, UPDATE's old rows, DELETE). WITH CHECK filters rows on write operations (INSERT, UPDATE's new rows). You usually want both to match:

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

If you omit WITH CHECK, Postgres falls back to the USING expression for writes. That is usually correct, but stating it explicitly makes the intent obvious.

The current_setting Pattern

The current_setting() function reads from the session's configuration namespace. The second argument, true, makes it return NULL instead of raising an error when the setting is missing:

current_setting('app.tenant_id', true)::uuid

This matters during migrations and batch jobs that run without a tenant context. An error-raising policy blocks all tooling that touches the table without setting the variable first.

Bypass Roles for Migrations

Postgres table owners and superusers bypass RLS by default. Your migration user is probably a superuser. This is by design — migrations need to write across all tenants. But your application connection role should not be a superuser:

-- Migration role (superuser or BYPASSRLS)
ALTER ROLE migrator BYPASSRLS;

-- Application role (no bypass)
ALTER ROLE app_user NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_user;

If your application connects as the table owner, RLS is silently bypassed. This is the most common way teams discover RLS is not actually doing anything.

Performance: Index Alignment

RLS predicates must match your indexes to stay fast. A policy on tenant_id needs an index on tenant_id — or better, a composite index on (tenant_id, status) if queries filter by status within a tenant:

CREATE INDEX ON documents (tenant_id, status);

Use EXPLAIN ANALYZE to confirm the policy predicate is hitting the index, not causing a sequential scan across all tenants before filtering.

What RLS Does Not Cover

A few things RLS does not protect against, which are worth knowing before you rely on it:

• Schema-level DDL: RLS applies to DML (SELECT, INSERT, UPDATE, DELETE). It does not prevent a role with CREATE privilege from altering the table structure.
• Superuser bypass: Any role with BYPASSRLS or superuser privilege ignores all policies. Your backup jobs and migration tooling will bypass RLS — intentionally.
• Function-level access: A SECURITY DEFINER function called by an application user runs as the function owner, which may bypass RLS. Check your function ownership.
• Lateral joins and CTEs: RLS applies to each table access, including inside CTEs and lateral joins. It does not leak across rows, but complex query patterns deserve explicit verification.

When to Use Application-Layer Filtering Instead

RLS is worth the complexity when you have many tenants sharing tables and cannot afford to add WHERE clauses to every query. It is overkill when you have schema-per-tenant or database-per-tenant isolation, where the isolation is structural rather than predicate-based. RLS also requires care in read replicas: the policy runs on the replica too, but current_setting needs to be set there as well.

The pattern works well with connection poolers like PgBouncer in transaction mode: set app.tenant_id via SET LOCAL so it resets at transaction end, and the policy is stateless across pooled connections.

The point is simple: if your application is the only thing enforcing tenant isolation, you are one missing WHERE clause away from a breach. Moving that invariant into the database makes it structural rather than conventional.

Building something? builds.anethoth.com is a public build ledger — proof that a product is really being built.