Postgres row-level security for multi-tenant SaaS

Row-level security (RLS) lets Postgres enforce tenant isolation itself, so a forgotten WHERE tenant_id = ... in application code does not leak another customer's data ^[1]. It works only when three things are true: the application role is one the policies actually apply to, the tenant is set per transaction, and you know what RLS does not cover. Most failures come from the first two.

Does row-level security actually isolate tenants?#

Yes, for normal reads and writes. Once row security is enabled on a table, all normal access must be allowed by a policy, and if no policy exists a default-deny policy applies, so no rows are visible or modifiable ^[1]. A policy is a Boolean expression evaluated for each row before any conditions or functions from the user's query, apart from leakproof functions ^[1]. A policy with only a USING clause implicitly gets an identical WITH CHECK clause, so one rule limits both the rows you read and the rows you write ^[1].

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.tenant_id', true)::uuid);

Whole-table operations such as TRUNCATE and REFERENCES are not subject to row security ^[1].

Who bypasses the policies?#

Superusers and roles with the BYPASSRLS attribute always bypass row security. Table owners normally bypass it too, unless the owner opts in with ALTER TABLE ... FORCE ROW LEVEL SECURITY ^[1]. Only the table owner can enable row security or add policies ^[1]. The practical rule: the role your application connects as must not be a superuser, must not have BYPASSRLS and must not own the tables.

How does the application tell Postgres which tenant it is?#

Use a custom run-time setting that the policy reads with current_setting. With its optional second argument set to true, a missing setting returns NULL instead of raising an error ^[4], and a NULL comparison never returns true, so no rows are processed ^[1]. To set it, set_config(name, value, true) applies the value only to the current transaction; false would apply it for the rest of the session ^[4]. It is the function form of SET LOCAL, whose effects last only until the end of the current transaction, committed or not ^[2].

BEGIN;
SELECT set_config('app.tenant_id', $1, true);  -- local to this transaction
SELECT * FROM orders;                           -- only this tenant's rows
COMMIT;

Why do connection pools break this?#

In transaction pooling, PgBouncer assigns a server connection to a client only for the length of a transaction and then returns it to the pool ^[3]. Its feature table lists SET and RESET as not supported in that mode ^[3]. A plain SET that commits lasts until the end of the session ^[2], and a session on a pooled connection can serve many different clients. That is how tenant context leaks between requests. Setting the tenant with set_config(..., true) or SET LOCAL inside the same transaction as the queries confines it to that transaction ^[2][4].

What does row-level security not protect?#

• Referential integrity checks. Unique, primary key and foreign key checks always bypass row security, so a careless schema can leak information through them as a covert channel ^[1].
• Whole-table operations. TRUNCATE and REFERENCES are not subject to row security ^[1].
• Cross-row policies. Policies that consult other rows or tables can create race conditions that leak information. The simplest and best-performing policy looks only at the current row's values ^[1].
• Silent filtering in backups. Setting row_security to off makes a query raise an error instead of quietly returning filtered rows, which is the behaviour you want from a backup job ^[1].

How do you test it before trusting it?#

This is our checklist, not a Postgres procedure.

1. Check the roleAs the application, run SELECT rolname, rolsuper, rolbypassrls FROM pg_roles WHERE rolname = current_user; and confirm both flags are false and that the role owns none of the tables.
2. Read with no tenant setRun a SELECT without setting a tenant and confirm it returns nothing or an error, never another tenant's rows. Repeat on a reused pooled connection after a transaction that did set a tenant.
3. Write across tenantsTry to INSERT and UPDATE a row carrying another tenant's id and confirm WITH CHECK rejects both.
4. Hammer the poolSend concurrent requests for different tenants through the transaction pooler and assert that each response contains only its own tenant's rows.
5. Back up with row_security offConfirm the backup job fails loudly instead of silently dropping rows.

Frequently asked questions#

Does row-level security apply to the table owner?

Not by default. Table owners normally bypass row security, and ALTER TABLE ... FORCE ROW LEVEL SECURITY makes the owner subject to policies as well. Superusers and roles with the BYPASSRLS attribute always bypass them, whatever the table settings.

What happens if I enable RLS and create no policy?

Postgres applies a default-deny policy, so no rows are visible or modifiable through normal access. The table owner is normally exempt, and whole-table operations such as TRUNCATE are not subject to row security.

Is RLS compatible with PgBouncer transaction pooling?

Yes, if the tenant is set inside each transaction. PgBouncer's transaction mode does not support plain SET or RESET, so use SET LOCAL or set_config with is_local true, which last only until the transaction ends.

Does RLS slow queries down?

Policy expressions run as part of every query. The Postgres documentation says the simplest and best-performing policies consider only the current row's values, while policies that consult other rows or tables through sub-SELECTs add work and can create race conditions.

Can RLS leak data through foreign keys or unique constraints?

It can. Referential integrity checks such as unique, primary key and foreign key checks always bypass row security. The Postgres documentation warns that schemas and policies must be designed to avoid covert channel leaks of information through those checks.