Row Level Security

Row Level Security moves tenant isolation into the database, where it cannot be forgotten by a query. That is a genuine benefit. It also has two sharp edges that are worth knowing before you commit a schema to it.

The subquery trick

This is the highest-value thing on this page, so it goes first.

An RLS policy is evaluated per row scanned, not per query. A function call in the policy therefore runs once per row - thousands or millions of times - unless the planner can prove it only needs to run once.

Wrapping the call in a scalar subquery is what gives it that proof. The planner turns it from a SubPlan (re-evaluated per row) into an InitPlan (evaluated once, result reused):

-- Slow: current_setting() and auth_role() run for every row scanned.
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::bigint
         AND auth_role() = 'admin');

-- Fast: both are hoisted out and evaluated once per query.
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = (SELECT current_setting('app.tenant_id')::bigint)
         AND (SELECT auth_role()) = 'admin');

The two policies are semantically identical. On a large table the second is several times faster, and the gap widens with the row count. Check your own with EXPLAIN (ANALYZE, BUFFERS) and look for InitPlan rather than SubPlan in the output.

RLS and the pooled endpoint

RLS decides what a query can see from the identity of the connection. A connection pooler in transaction mode hands the same server connection to many clients, so connection identity stops meaning "this user".

The standard workaround is to put the tenant in a setting rather than the role:

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

and set it per transaction, with SET LOCAL:

BEGIN;
SET LOCAL app.tenant_id = '1234';
SELECT * FROM orders;
COMMIT;

SET LOCAL is reverted at commit, so it cannot leak onto the next borrower of that server connection. A plain SET can, and that is a cross-tenant data exposure rather than a performance bug - so this is not a stylistic preference.

Policies that do not apply to you

Two bypasses catch people out, usually in testing:

• The table owner bypasses its own policies unless you say otherwise. If your application connects as the role that owns the tables - which is the default - your policies are not doing anything. Fix it explicitly:

  ALTER TABLE orders FORCE ROW LEVEL SECURITY;

• SECURITY DEFINER functions run as their owner, so anything they touch is evaluated with the owner's policies, not the caller's. That is sometimes exactly what you want and sometimes a hole; either way it means the authorisation logic now lives in two places.

The failure mode is a test suite that passes as a non-owner role while production runs as the owner, so the policies are tested and never actually enforced. Test as the role your application really uses.

What RLS does not do

RLS filters rows. It does not prevent the query from running. A user with no access to any row still causes a full evaluation, consuming CPU and I/O to return nothing. That makes it unsuitable as your only defence against a hostile authenticated user - rate-limit and authorise at the application edge as well.

Policies also live in pg_policies, not in your migration files, unless you put them there. Most migration tools do not track policy changes, so a column rename can silently break a policy with no failing migration to warn you. Keep policies in version-controlled migrations and test them like code.

When to use it

RLS earns its keep as defence in depth on top of application-level filtering, particularly where a mistake is expensive and the schema is stable. It is a poor fit as your only isolation mechanism, on a very hot path, or where authorisation rules change frequently.

For most applications, the honest ordering is:

1. Filter by tenant_id in one enforced place in the application (Multi-tenancy).
2. Add RLS underneath as a backstop, using the subquery form, with FORCE ROW LEVEL SECURITY on.
3. Give tenants that need real isolation their own cell.