Enforce Row-Level Tenancy in Postgres Without a Middleware Tax

For: A backend engineer at a seed-to-Series-A SaaS startup who has been enforcing tenant isolation with WHERE clauses in application code and just had a near-miss where a missing filter almost exposed one tenant's records to another — and is now evaluating whether Postgres RLS is worth the complexity before the next audit.

If a missing WHERE tenant_id = ? in your codebase can leak data across tenants, the fix is not more code review — it is Postgres Row-Level Security (RLS) with a per-transaction tenant context set via set_config(..., is_local := true). That combination pushes isolation into the database, keeps plan caching intact across a PgBouncer pool, and typically runs faster than the application-layer filters you are replacing. The rest of this post walks through the exact setup, including the failure modes that make teams give up on RLS before they get to the fast path.

Why application-layer WHERE clauses keep failing

The near-miss you just had is not a discipline problem. It is a surface-area problem. Every new endpoint, every raw SQL migration, every analytics job, every admin script is one place where a filter can be forgotten. RLS moves the guarantee from "every query must remember" to "the database refuses to return rows the current tenant should not see." That is the property auditors want, and it is the property that survives a rushed hotfix at 2am.

The reason most teams don't adopt it: they tried it once, benchmarked a workload, saw plan-cache misses and slower queries, and blamed RLS. The real culprit is almost always how the tenant context is set, not RLS itself.

The non-obvious performance fix

Most tutorials tell you to set the tenant with SET app.tenant_id = '...'. That is a session-level GUC. In a connection pool (PgBouncer transaction mode, or any pool that hands the same physical connection to different logical clients), session-level settings survive across checkouts and, worse, they force the planner to treat each tenant context as a distinct plan input in ways that break plan reuse.

Use set_config('app.tenant_id', $1, true) instead. The third argument, is_local, scopes the setting to the current transaction. It gets cleared automatically at COMMIT or ROLLBACK, plays correctly with pooled connections, and lets the planner cache plans across tenants because the tenant value is a parameter, not a plan-shaping GUC.

Prerequisites

• Postgres 12+ (RLS works from 9.5, but FORCE ROW LEVEL SECURITY and better planner behavior are more recent).
• A multi-tenant schema where every tenant-scoped table has a tenant_id column (UUID or bigint).
• A connection pool. Examples below assume PgBouncer transaction mode or a per-request transaction in your app.
• Superuser or table owner to create policies. A separate, non-superuser role your app connects as.

Step 1: Create an application role that is not superuser

RLS is bypassed by superusers and by table owners unless you explicitly force it. Your app must connect as a non-owner role.

CREATE ROLE app_user LOGIN PASSWORD 'redacted';
GRANT CONNECT ON DATABASE app TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Expected: CREATE ROLE, then three GRANT confirmations. Your app's connection string should now point at app_user, not postgres.

Step 2: Enable and force RLS on tenant tables

Take a representative table — say invoices:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

ENABLE turns RLS on for non-owner roles. FORCE applies it to the owner too, which prevents an accidental migration script running as owner from bypassing isolation. Expected output: two ALTER TABLE lines.

Step 3: Write the policy

The policy reads the current tenant from a custom GUC. If the GUC is unset, the policy fails closed — no rows returned, no rows written.

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

The true second argument to current_setting means "return NULL if unset" instead of raising. The comparison to NULL returns NULL, which the policy treats as false. That is the fail-closed behavior you want.

USING filters what SELECT/UPDATE/DELETE can see. WITH CHECK validates what INSERT/UPDATE can write. Both are required — without WITH CHECK, a tenant could INSERT rows tagged with someone else's tenant_id.

Step 4: Set the tenant context per transaction

In your data layer, at the start of every request or job that touches tenant data:

BEGIN;
SELECT set_config('app.tenant_id', $1, true);  -- $1 = tenant UUID as text
-- your queries here
COMMIT;

The true makes this is_local — scoped to the transaction. It is released on COMMIT/ROLLBACK, which is exactly what you want in a pooled setup.

In Node with pg:

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
  const { rows } = await client.query('SELECT id, amount FROM invoices');
  await client.query('COMMIT');
  return rows;
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release();
}

Expected: the SELECT returns only rows where tenant_id matches the value you set. No WHERE tenant_id = ... in application code.

Step 5: Verify isolation works

Open two psql sessions as app_user. Insert test data first (as owner) so you have two tenants:

-- as owner
INSERT INTO invoices (id, tenant_id, amount) VALUES
  (gen_random_uuid(), '11111111-1111-1111-1111-111111111111', 100),
  (gen_random_uuid(), '22222222-2222-2222-2222-222222222222', 200);

Now, as app_user:

BEGIN;
SELECT set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);
SELECT amount FROM invoices;
-- expected: 100 only
COMMIT;

Then without setting the GUC:

SELECT amount FROM invoices;
-- expected: 0 rows

Zero rows when unset is the fail-closed proof. If you get all rows here, you are either connected as superuser or you skipped FORCE ROW LEVEL SECURITY.

Step 6: Confirm plans are cached across tenants

This is the step most teams skip and then blame RLS for being slow.

BEGIN;
SELECT set_config('app.tenant_id', '11111111-1111-1111-1111-111111111111', true);
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM invoices WHERE created_at > now() - interval '7 days';
COMMIT;

You should see the policy expression pushed into the plan as a filter alongside your index conditions. If tenant_id is part of a composite index (recommended: (tenant_id, created_at)), the planner will use it as a leading column. Index design still matters — RLS does not invent indexes for you.

Now switch tenants in a fresh transaction and re-run. Plan structure should be identical; only the parameter value changes. If you had used SET app.tenant_id = ... at session level, you would see plan-cache churn under a real workload. With set_config(..., true), you do not.

Step 7: Handle admin and cross-tenant jobs

You will need to run reports and migrations that legitimately touch multiple tenants. Two clean options:

1. Bypass role. Create a separate role (app_admin) that owns the tables or has BYPASSRLS. Use it only for background jobs and never for user-facing queries.
2. Policy escape hatch. Add a second policy that permits access when a different GUC is set (e.g., app.is_admin = 'true'), and only set it in tightly audited code paths. Policies combine with OR by default, so this widens access when the flag is present.

The first option is safer because the privilege is tied to a role, which is easier to audit than a GUC someone might set from anywhere.

Step 8: Wire it into your ORM

ORMs are usually fine with RLS because you stop writing tenant filters in application code — the queries get simpler, not more complex. What you need is a hook that runs at the start of every unit of work.

• Prisma: use $transaction with an interactive callback and issue the set_config as the first statement.
• TypeORM / Sequelize: use a transaction hook or a per-request middleware that acquires a client, begins a transaction, sets the GUC, and passes the client to the request handler.
• Django: wrap views in transaction.atomic and set the GUC in a middleware that runs inside the transaction.
• Rails / ActiveRecord: use ActiveRecord::Base.transaction and connection.execute for set_config.

The rule that matters: one transaction = one tenant context. Never share a transaction across tenants, and never rely on the GUC surviving beyond the transaction.

Common errors and how to debug them

"I see all rows, isolation is broken"

You are connected as a superuser or the table owner and did not run FORCE ROW LEVEL SECURITY. Check with SELECT current_user and \d+ invoices in psql — the table description will show whether RLS is enabled and forced.

"current_setting: unrecognized configuration parameter"

You called current_setting('app.tenant_id') without the second true argument, and the GUC has never been set on that connection. Add the true to make it return NULL instead of raising.

"Queries are slower than before RLS"

Check three things. First, are you using set_config(..., true) inside a transaction, not SET at session level? Second, does your primary index lead with tenant_id? A single-tenant workload gets away without it; a multi-tenant one does not. Third, run EXPLAIN and confirm the policy predicate is being pushed into index scans, not applied as a post-filter.

"INSERT succeeded with the wrong tenant_id"

You have a USING clause but no WITH CHECK. Add it. Both must reference the same expression for a symmetric policy.

"Background jobs return zero rows"

The job never set the GUC. Either set it explicitly per tenant iteration, or run the job under a role with BYPASSRLS. Do not disable the policy globally to fix this.

"PgBouncer transaction mode is leaking state"

If you used session-level SET, yes — transaction-mode pooling and session GUCs do not mix. Switch to set_config(..., true) and the leak goes away because the setting dies with the transaction.

What RLS is bad at

Honest tradeoffs: RLS adds a predicate to every query on protected tables, and if your index design is wrong, you will feel it. It complicates COPY-based bulk loads (you either load as the owner with RLS forced off temporarily, or you set the GUC and load one tenant at a time). It makes debugging "why is this row missing" harder for engineers who forget the tenant context is implicit. And it is not a substitute for authorization — RLS enforces tenancy, not per-user permissions within a tenant. You still need application-layer authz for role-based access inside a single tenant.

For most SaaS products moving from shared-schema multi-tenant with application filters, none of these are deal-breakers. They are things to plan for, not reasons to skip RLS.

Where this fits in a broader tenancy strategy

Shared-schema with RLS is the middle ground between "one schema per tenant" (operationally heavy, hard to run cross-tenant analytics) and "one database per tenant" (expensive, slow to provision). For most seed-to-Series-A SaaS products, shared-schema-with-RLS is the right default, and it stays right until you have enterprise customers demanding physical isolation for compliance. At that point you add per-tenant databases for the top tier and keep RLS for everyone else.

Teams building fintech, healthcare, or lending products where tenant isolation is a compliance line item benefit even more, because RLS gives auditors a database-level control they can inspect directly. We've seen this pattern hold up well in production systems like GimBooks' accounting SaaS and Cashpo's lending platform, where per-tenant data boundaries are non-negotiable.

Frequently Asked Questions

Does Postgres RLS work with connection poolers like PgBouncer in transaction mode?

Yes, as long as you set the tenant context with set_config(..., is_local := true) inside a transaction. Session-level SET statements will leak across pooled clients in transaction mode and cause both correctness and performance problems. Transaction-scoped GUCs are released automatically on commit or rollback, which is the behavior you want.

What is the real performance cost of row level security in multi-tenant Postgres?

With transaction-scoped set_config and an index leading on tenant_id, the overhead is typically indistinguishable from writing the same predicate as an application-layer WHERE clause — sometimes faster, because the planner sees the predicate consistently. The overhead people complain about usually comes from session-level GUCs breaking plan reuse, or from missing indexes that also would have hurt a non-RLS query.

Can I use RLS without switching to separate schemas per tenant?

Yes — that is one of its main advantages. Multi-tenant Postgres without separate schemas is exactly the use case RLS was designed for. You keep a single shared schema, add a tenant_id column to every tenant-scoped table, and let RLS policies enforce isolation. This keeps migrations, backups, and analytics simple compared to schema-per-tenant.

How do I handle background jobs and cross-tenant admin queries?

Run them under a dedicated role with BYPASSRLS, or iterate through tenants and set the GUC per iteration. The bypass role is cleaner for reporting and migrations because the privilege is attached to authentication, which is easier to audit than a runtime flag. Never disable policies globally to fix a job — that reintroduces the exact leak you built RLS to prevent.

Does RLS replace application-layer authorization?

No. RLS enforces tenancy — which tenant a row belongs to — not permissions within a tenant. You still need application-layer authorization for user roles, feature access, and per-record permissions inside a single tenant. Think of RLS as the outer boundary that catches the worst failure mode (cross-tenant leaks), while app-layer authz handles finer-grained rules.