Postgres Row-Level Security in practice

Row-Level Security in Postgres is one of those features that’s simultaneously underused and oversold. Underused, because most teams don’t reach for it when they should. Oversold, because some teams treat it as a complete authorization model and ship bugs that wouldn’t happen with a plain WHERE clause.

This piece is the practical middle ground — how we actually use RLS in production multi-tenant SaaS, what it’s good for, and the three traps that catch almost every team the first time.

What RLS actually does

RLS attaches a WHERE clause to every query against a table, transparently. The application doesn’t know it’s there. If a developer writes SELECT * FROM invoices, Postgres rewrites it as SELECT * FROM invoices WHERE tenant_id = current_setting(‘app.tenant_id’) before running it. The filter is enforced at the database layer.

This matters because applications are big and developers are human. Sooner or later, someone forgets the WHERE tenant_id = ...clause. With RLS in place, that forgotten clause doesn’t leak data — the database refuses to return rows that don’t match.

The minimum-viable setup

-- 1. Enable RLS on the table
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- 2. Add a policy
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- 3. In your connection pool, set the tenant ID per-request
SET LOCAL app.tenant_id = '019...';

That’s the entire core pattern. The application sets app.tenant_id once at the start of each request (per-transaction via SET LOCAL), and every query for the rest of that transaction is automatically tenant-scoped.

The three traps every team falls into

1. Connection pool sharing

If your connection pool reuses connections across requests (it does — that’s the point), SET without LOCALpersists. Tenant A’s setting leaks into Tenant B’s next query on the same connection. Use SET LOCAL inside an explicit transaction, always. Or use a connection pool that supports per-transaction application_name tagging and reset between transactions.

2. The BYPASSRLS escape hatch

Postgres lets table owners and superusers bypass RLS. If your application connects as the schema owner (common with naive ORMs), RLS is silently doing nothing. Create a dedicated app_user role without BYPASSRLS, grant only the privileges it needs, and connect as that role.

3. Forgetting WITH CHECK on writes

A USING clause without a matching WITH CHECK clause filters reads, but doesn’t constrain inserts/updates. Tenant A can INSERT a row with tenant_id = B— the row inserts fine, and Tenant A can’t see it afterward, but the data is corrupted. Always add a WITH CHECK clause that mirrors the USING clause.

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

RLS is one layer, not the only layer

Don’t use RLS as a substitute for application-layer authorization. Use it as a defense-in-depth layer. The application should:

• Authenticate the user (who are you?)
• Authorize the action (can you do this?)
• Filter the query (...for this tenant’s data)

RLS catches mistakes in step 3. It doesn’t replace steps 1 and 2. Treat it the way you’d treat a database CHECK constraint — the application validates too, but the database enforces.

Performance: not as bad as you think

The standard worry is “won’t this slow down every query?” In our experience: not measurably, as long as the policy condition is on an indexed column (almost always tenant_id). Postgres folds the policy condition into the query planner’s WHERE evaluation; it’s a regular index scan with a regular equality predicate.

Things that DO get slow: policies with subqueries, policies that reference other tables, policies that use functions Postgres can’t inline. Keep your policy body to a simple equality on an indexed column and you’ll be fine.

When NOT to use RLS

• Single-tenant apps. No tenant to isolate; complexity not earned.
• Cross-tenant analytics.If your app legitimately needs to query across tenants (admin dashboards, billing aggregation), use a separate role that’s allowed to bypass RLS for that specific query path.
• Data warehouses / read replicas used for OLAP. Different isolation model; usually handled at the BI layer.

How we approach this

Every multi-tenant SaaS we deliver via SaaS Product Development ships with RLS on every tenant-owned table, a dedicated app_userrole, and a per-transaction tenant context. It’s a one-day setup that catches a whole class of would-be incidents for the life of the product.

Takeaways

• RLS is defense in depth, not your primary authorization model.
• Use SET LOCAL inside transactions; never plain SET.
• Connect as a non-BYPASSRLS role.
• Always pair USING with WITH CHECK.
• Index your tenant_id. The rest just works.