Row-level security in Postgres: a practical guide to isolating tenants

Most multi-tenant apps start the same way. Every table gets a tenant_id column, and every query remembers to add WHERE tenant_id = .... It works until the day someone forgets, in a report, a script or a late-night hotfix. Row-level security (RLS) moves that rule out of your application and into Postgres, so a forgotten filter returns nothing instead of leaking everything.

This guide covers how RLS works, a small pattern for tenant isolation, and the few traps worth knowing before you rely on it. Everything about behaviour here comes from the PostgreSQL documentation on row security policies.

The basics

You turn RLS on per table:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

From that moment, access needs a policy. With no policy at all, the default is deny: no rows are visible and none can be changed. That is a safe place to start.

A policy is a rule evaluated for each row. It can apply to all commands or just one of SELECT, INSERT, UPDATE and DELETE, and to specific roles or everyone.

A tenant isolation pattern

A common approach is to have the application tell the database who the current tenant is, once per transaction, and let the policy read that value.

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

Then, at the start of each transaction:

BEGIN;
SELECT set_config('app.tenant_id', '0190f3c2-...', true);
SELECT * FROM orders; -- only this tenant's rows
COMMIT;

The third argument, true, makes the setting local to the transaction, so it cannot leak to the next user of a pooled connection. That detail matters a lot. With connection pooling, a session-level setting that outlives its transaction is a classic way to serve one tenant's data to another.

Note that the documentation page itself does not show this current_setting example. It is a widely used convention, not a built-in feature, so test it against your own setup.

USING and WITH CHECK

Two clauses do different jobs:

• USING decides which existing rows a command can see or target.
• WITH CHECK decides which new or changed rows are allowed to be written.

If you leave out WITH CHECK, Postgres reuses the USING expression. The behaviour differs in an important way. A row hidden by USING is silently skipped, so an UPDATE just reports UPDATE 0. A row that fails WITH CHECK raises an error. So a user trying to insert a row for another tenant gets a loud failure, which is what you want.

Trap one: owners and superusers bypass policies

Superusers and roles with the BYPASSRLS attribute always skip RLS. Table owners normally skip it too. That means if your application connects as the role that owns the tables, your policies do nothing.

Two fixes, which work well together:

1. Run the application as a separate role that does not own the tables and does not have BYPASSRLS. Migrations run as the owner; the app does not.
2. Optionally add ALTER TABLE ... FORCE ROW LEVEL SECURITY, which makes the owner subject to policies as well.

A quick way to test: connect as your app role, forget to set the tenant, and confirm that you see zero rows or an error. An isolation setup that has never been seen to fail closed is not yet trusted.

Trap two: permissive policies combine with OR

By default, several policies on one table are combined with OR. Add a second, broader policy later and you may quietly widen access. If a rule must always hold, make it a RESTRICTIVE policy, which is combined with AND and must pass in addition to the others:

CREATE POLICY not_archived ON orders AS RESTRICTIVE
    USING (archived_at IS NULL);

Trap three: some checks ignore RLS

Referential integrity checks, such as unique keys and foreign keys, bypass row security. The documentation warns this can act as a covert channel: a user might learn that a value exists in another tenant's rows by seeing a duplicate-key error. For most applications this is acceptable if you use opaque identifiers like UUIDs, and it is good to know it exists. TRUNCATE and REFERENCES privileges are also outside RLS.

Trap four: subqueries in policies

Policies can contain subqueries, but the documentation notes they can create race conditions under the default READ COMMITTED isolation level, because the subquery uses the snapshot taken at the start of the statement. The simplest and best-performing policies only look at values in the row itself, like the tenant_id comparison above. If you need to consult another table, read the documented mitigations carefully.

Performance notes

Policy expressions run for each row, so keep them cheap and index the column they use. A policy like tenant_id = current_setting(...) pairs naturally with an index that starts with tenant_id. If you want to see how an index helps a filter like that, our upcoming material on indexing covers the trade-offs; for now, EXPLAIN on a query with and without the policy will show you what the planner does.

Backups and admin work

An administrator running a dump as a role subject to RLS could silently miss rows. Setting row_security = off does not bypass policies; it makes Postgres raise an error if a policy would filter anything. That is exactly what you want for a backup: a loud failure instead of a quiet gap.

Is RLS enough on its own?

It is a strong backstop, not a replacement for good design. Keep your application checks, keep tenant identifiers in every table, and treat the database policy as the last line that holds when something above it slips. In our own platform, where each organization's data must stay apart, we use it as one layer among several, and the same idea carries into giving each agent its own machine: assume a layer above you will fail and make the next one hold.

A short checklist

• Enable RLS on every table that holds tenant data.
• Use a non-owner application role without BYPASSRLS.
• Set the tenant with transaction-local set_config(..., true).
• Write WITH CHECK clauses on purpose for writes.
• Use restrictive policies for rules that must always apply.
• Test the failure case: no tenant set, wrong tenant set.

Small tables, clear policies and a habit of testing the "wrong tenant" case will get you most of the benefit.

Sources

• PostgreSQL documentation: Row Security Policies