Row-Level Security in Postgres: Strong Defense, Costs You Will Encounter

Row-level security (RLS) is the Postgres feature for enforcing per-row access at the database, not the application. Once enabled on a table, every query (regardless of source) automatically gets a WHERE clause that filters out rows the current user is not allowed to see.

For multi-tenant systems and applications with strict data segregation, RLS is the right tool. It is also slightly slower than no security filter, more complex to debug, and can break the planner's cost estimates in interesting ways. Knowing those trade-offs ahead of time saves a lot of "why is this query slow now" investigation later.

What it looks like

-- A multi-tenant table
CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  total_cents INTEGER NOT NULL
);

-- Enable RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Policy: users see only their tenant's rows
CREATE POLICY orders_tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.current_tenant_id')::BIGINT);

Now SELECT * FROM orders only returns rows where tenant_id matches the session's app.current_tenant_id. The application sets that GUC before each request:

SET app.current_tenant_id = '47';
SELECT * FROM orders; -- only sees tenant 47's rows

The big benefit: defense in depth

Without RLS, every query has to remember to filter:

SELECT * FROM orders WHERE tenant_id = $1;

Forget the filter once and you leak. The application has hundreds of query call sites; getting all of them right is a discipline problem.

With RLS, the filter is enforced by the database. The application can write SELECT * FROM orders and the database adds the tenant filter automatically. Forgetting becomes impossible.

This is genuinely valuable for multi-tenant systems. The cost of one accidentally cross-tenant query in a B2B SaaS is enormous. RLS reduces the probability to near-zero.

Performance: the policy is a WHERE clause

RLS policies are added to every query as additional WHERE conditions. The planner sees them and tries to plan accordingly. For simple policies (like tenant_id = current_setting('app.current_tenant_id')::BIGINT), the cost is negligible — the planner can use the existing tenant_id index.

For more complex policies — joining to other tables, calling functions — the cost can be significant. The planner does not always optimize the combined query well.

My rule: write the simplest possible policy. If the policy needs a JOIN to figure out access, you are usually better off precomputing access into a separate column or table.

A common pattern: per-role policies

-- Regular users see only their tenant
CREATE POLICY orders_tenant_isolation ON orders
  FOR ALL
  TO app_user
  USING (tenant_id = current_setting('app.current_tenant_id')::BIGINT);

-- Admins see everything
CREATE POLICY orders_admin_all ON orders
  FOR ALL
  TO admin_user
  USING (true);

Different policies per database role. The application connects as app_user; admin tools connect as admin_user. Each gets its appropriate slice.

This is the cleanest pattern for multi-tenant + admin scenarios.

The planner gotcha

RLS policies are treated as LEAKPROOF = false by default. This means the planner cannot reorder them with other operations that might reveal information through error messages or timing.

In practice this rarely matters, but it has bitten me once: a policy that should have been pushed down before a JOIN was instead applied after, causing the JOIN to compute on too many rows. The fix was to mark the helper function as LEAKPROOF, which the planner then trusted to reorder.

If RLS makes a query unexpectedly slow, look at the EXPLAIN plan. If the policy filter is applied late, that is the issue.

Testing RLS

A simple end-to-end test:

SET ROLE app_user;
SET app.current_tenant_id = '47';
SELECT count(*) FROM orders; -- should only count tenant 47

SET app.current_tenant_id = '99';
SELECT count(*) FROM orders; -- should only count tenant 99

RESET ROLE;
SELECT count(*) FROM orders; -- as superuser, sees everything

Run this for every RLS-protected table whenever the policies change. The tests are short and the failures are obvious.

When RLS is the wrong tool

Three cases:

1. The application's authorization model is more complex than the policies can express. RLS policies are SQL expressions. If your authorization is "users can see orders if they are in any group that has any role with permission X," the policy becomes a hairy nested query. Better handled in the application.

2. The performance cost is unacceptable. For very high-throughput tables where every microsecond matters, the policy overhead may be too much. Profile to confirm before assuming.

3. The application code is the only consumer. If queries only ever come from a single, audited application path, the marginal benefit of RLS is small. The application can enforce the filter consistently. RLS shines when there are multiple paths (admin tools, BI queries, ad-hoc connections) and you cannot trust all of them.

What I implement for multi-tenant

For a B2B SaaS with shared-schema multi-tenancy:

• RLS on every table that contains tenant-scoped data.
• Application sets app.current_tenant_id on every connection check-out.
• Per-role policies: app_user is restricted, app_admin_user is unrestricted.
• Tenant filter is also in the application's query layer (defense in depth — both layers must fail for a leak to happen).
• tenant_id is the leading column of every relevant index.

This is more setup than "every query has WHERE tenant_id = X." It is less work over the lifetime of the system, and the protection it provides is real.

A migration from no RLS to RLS

If you are adding RLS to an existing database, the order:

1. Confirm every table that needs RLS has the necessary scope columns (e.g., tenant_id).
2. Ensure the application is already setting the right session GUC consistently.
3. Define the policies on each table but do not enable yet.
4. Enable RLS on one low-risk table. Test that the application still works.
5. Roll out to the rest of the tables in batches.
6. Monitor for queries that suddenly return no rows (the policy is filtering them all out — usually a missing GUC).

The step-wise rollout is the difference between a clean migration and a midnight rollback.

RLS is one of those features where the value is exactly what it advertises. The cost is also what it advertises. Knowing both, the decision becomes much simpler than the marketing makes it sound.