The Practical Developer

Postgres Row-Level Security For Multi-Tenant Apps: The Pattern That Stops You From Leaking Data

October 28, 2022 by The Practical Developer

In a multi-tenant SaaS, every query needs a `WHERE tenant_id = ?` and one missing one is a data breach. RLS moves that filter into the database where you cannot forget it. Here is the pattern that works in practice — including the connection-pool gotcha that breaks it.

database

postgres

security

multi-tenancy

A dense circuit board — the right metaphor for the layered defenses a multi-tenant database needs

In a multi-tenant SaaS, every query against shared tables needs a WHERE tenant_id = ? filter. Forget one, and customer A sees customer B’s data. The first time this happens it is a near miss; the second time it is the post on Hacker News with your company’s name on it. The fix is not “be more careful” — it is to take the responsibility out of application code entirely. That is what Postgres row-level security (RLS) does.

RLS lets the database evaluate a per-row predicate on every read and every write. With it on, even a SQL injection or a forgotten tenant filter cannot return another tenant’s rows. Without it, the security of every customer in your system depends on every developer remembering to write the filter — every time, in every query, including the ones the analytics team writes ad hoc on production.

This post is the working pattern: schema, policies, the session-variable trick that makes it usable from a connection pool, and the failure modes you need to test for.

The shape of an RLS-protected table

Every multi-tenant table has a tenant_id (or org_id, or account_id) column. RLS is enabled, and a policy ties row visibility to a session-local setting.

-- Tables.
CREATE TABLE tenants (
  id   uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE invoices (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id   uuid NOT NULL REFERENCES tenants(id),
  amount_cents int  NOT NULL,
  status      text NOT NULL,
  created_at  timestamptz NOT NULL DEFAULT now()
);

-- An index on tenant_id is mandatory. Without it, every query becomes a seq scan
-- of the entire table because the RLS predicate filters per-row.
CREATE INDEX invoices_tenant_idx ON invoices (tenant_id);

-- Turn on RLS for the table.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- The policy: a row is visible only if its tenant_id matches a session setting.
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Force the policy even for the table owner. Without this, the owner role bypasses RLS.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

current_setting('app.tenant_id') is the trick. It returns a session-local string that the application sets at the start of every transaction. RLS evaluates the policy against that value. If the application forgets to set it, current_setting raises an exception (with missing_ok=false, the default) and the query fails — fail loud, not silently wide-open.

Setting the tenant on each request

The application sets the tenant ID at the start of every database transaction, before any other query runs. The setting is local to the transaction, so it cannot leak across requests on a pooled connection.

import { Pool, PoolClient } from 'pg';

const pool = new Pool({ connectionString: process.env.DATABASE_URL });

export async function withTenant<T>(
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // SET LOCAL — scoped to the transaction. When the transaction ends,
    // the setting is gone, so the connection going back to the pool is clean.
    await client.query(`SET LOCAL app.tenant_id = '${tenantId}'`);
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK').catch(() => {});
    throw err;
  } finally {
    client.release();
  }
}

Every request handler wraps its database calls in withTenant:

app.get('/api/invoices', async (req, res) => {
  const invoices = await withTenant(req.tenantId, (client) =>
    client.query('SELECT id, amount_cents, status FROM invoices ORDER BY created_at DESC')
        .then(r => r.rows),
  );
  res.json(invoices);
});

Notice the SQL has no WHERE tenant_id = ?. RLS adds it implicitly. Forgetting it does not leak data — it just returns no rows (because the session has no tenant set, or the wrong one).

Why SET LOCAL, not SET

SET app.tenant_id = ... sets the value for the rest of the session. With pgbouncer in transaction mode (or any other connection pool), the next transaction on that connection — possibly serving a different user — would inherit the previous tenant’s setting. That is exactly the bug RLS is supposed to prevent.

SET LOCAL scopes the setting to the current transaction. When the transaction ends (commit or rollback), the setting reverts to its previous value (which is empty). The connection goes back to the pool clean. Always use SET LOCAL.

Parameterize, don’t interpolate

The withTenant example above uses string interpolation:

await client.query(`SET LOCAL app.tenant_id = '${tenantId}'`);

This is the one place in your codebase you cannot use parameterized queries — SET does not accept parameters. Validate tenantId aggressively before reaching this point:

import { z } from 'zod';
const TenantId = z.string().uuid();

export async function withTenant<T>(
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  TenantId.parse(tenantId); // throws on invalid
  // ... as before
}

If you accept the tenant ID from a JWT, validate the UUID format before using it in SET LOCAL. Otherwise a malicious tenant ID like 00000000-0000-0000-0000-000000000000'; DROP TABLE invoices; -- becomes a SQL injection. UUID validation makes this safe.

INSERT, UPDATE, DELETE policies

The policy above only handles SELECT. By default, write operations are also gated by the same USING clause — but it is worth being explicit, especially for INSERT.

DROP POLICY tenant_isolation ON invoices;

-- Read: a row is visible if its tenant_id matches the session.
CREATE POLICY tenant_select ON invoices FOR SELECT
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Update/Delete: same predicate, applied to the existing row.
CREATE POLICY tenant_update ON invoices FOR UPDATE
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_delete ON invoices FOR DELETE
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- Insert: the new row's tenant_id must match the session.
-- WITH CHECK validates the row being inserted, USING is for existing rows.
CREATE POLICY tenant_insert ON invoices FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

USING filters which existing rows are visible (read/update/delete). WITH CHECK validates which new rows are allowed (insert/update). Without the INSERT policy, an application bug that does INSERT INTO invoices (tenant_id, ...) VALUES ('other-tenant', ...) would succeed and silently put data in the wrong tenant.

For convenience: a single policy with both clauses applies to all operations:

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);

The connection-pool gotcha

If you put pgbouncer in transaction pooling mode in front of an RLS-enabled database, the pattern above works — SET LOCAL is transaction-scoped, transaction begin and commit happen on the same backend. Good.

If you put pgbouncer in session pooling, you do not need SET LOCAL because the session is dedicated to the client — but you do not gain much from the pooler either. Use transaction mode.

If you put pgbouncer in statement pooling, the pattern breaks: statement pooling does not support multi-statement transactions. RLS cannot work.

The non-obvious failure: a long-running query that holds a transaction past pgbouncer’s query_wait_timeout will be terminated. Make sure your withTenant blocks are short — long-running work outside the database (calling external APIs, processing large payloads) should happen outside the transaction, with the DB writes done in a follow-up withTenant block.

Bypassing RLS for admin tasks

You will eventually need to run a query that touches all tenants — analytics, billing reports, ops scripts. Two clean ways.

Use a BYPASSRLS role. Postgres has a role attribute that disables RLS for the role:

CREATE ROLE analytics WITH LOGIN PASSWORD '...' BYPASSRLS;
GRANT SELECT ON invoices TO analytics;

Connect as analytics from your reporting tool; RLS does not apply, full table is visible. Application connects as a regular role and RLS is enforced.

Use a different setting for “all tenants” mode. Slightly more flexible:

CREATE POLICY tenant_isolation ON invoices
  USING (
    current_setting('app.tenant_id', true) IS NULL
    OR tenant_id = current_setting('app.tenant_id')::uuid
  );

The true second argument to current_setting makes it return NULL instead of erroring when the setting is missing. The policy then admits “no tenant set” as “all tenants visible.” This is dangerous because the failure mode flips — forgetting to set a tenant now silently shows all data.

I prefer the BYPASSRLS role: less rope.

Testing it for real

A test suite is non-negotiable. The kinds of tests you need:

test('tenant A cannot see tenant B invoices', async () => {
  await seed({
    'tenant-a': [{ id: 'inv-1', amount: 100 }],
    'tenant-b': [{ id: 'inv-2', amount: 200 }],
  });

  const aRows = await withTenant('tenant-a', (c) =>
    c.query('SELECT id FROM invoices').then(r => r.rows));
  expect(aRows.map(r => r.id)).toEqual(['inv-1']);

  const bRows = await withTenant('tenant-b', (c) =>
    c.query('SELECT id FROM invoices').then(r => r.rows));
  expect(bRows.map(r => r.id)).toEqual(['inv-2']);
});

test('insert with wrong tenant_id is rejected', async () => {
  await expect(
    withTenant('tenant-a', (c) =>
      c.query("INSERT INTO invoices (tenant_id, amount_cents, status) VALUES ('tenant-b', 100, 'paid')"),
    ),
  ).rejects.toThrow(/row violates row-level security policy/);
});

test('forgetting to set tenant raises', async () => {
  await expect(
    pool.query('SELECT * FROM invoices'),
  ).rejects.toThrow(/unrecognized configuration parameter/);
});

The third test is the most important. It proves that forgetting withTenant is a loud failure, not a silent leak.

Performance

RLS adds a predicate to every query against the table. With a tenant_id index, the planner pushes the predicate into the index scan and the cost is essentially zero — same as if you had written the WHERE tenant_id = ? yourself, because in effect you have. Without the index, every RLS-protected query becomes a Seq Scan filtered by tenant. Make sure the index is there.

For tables that are only ever queried by tenant (the common case), you can go further with table partitioning by tenant — but that is a much bigger architectural call. For most apps, “RLS + index” is enough.

What RLS does not protect against

RLS protects against application bugs and SQL injection. It does not protect against:

  • Compromised credentials. If an attacker gets a valid JWT for tenant A, they get tenant A’s data — that is the design.
  • Rogue admin scripts. A BYPASSRLS role can read everything. Audit those connections; do not make every engineer one.
  • Backups and exports. A backup contains all tenants. Encryption and access control on backups is a separate problem.
  • Cross-tenant aggregations. “Total invoices across all customers” needs the BYPASSRLS role; anyone with that role can dump the whole table.

RLS is one defense in a stack — pair it with input validation, prepared statements, audit logs, and proper access control on the BYPASSRLS role.

The takeaway

If you run a multi-tenant SaaS on Postgres, RLS is the difference between “we hope every developer remembers the tenant filter” and “the database refuses to leak data even if the developer forgets.” The pattern is simple: a session setting per request, SET LOCAL to scope it to the transaction, RLS policies that use the setting as the predicate, an index on tenant_id, and a test that proves the failure mode is loud.

Adoption is straightforward in greenfield. In an existing codebase, enable RLS table by table, fix the queries that break, and ship. The window between “no RLS” and “RLS on” is the riskiest in your company’s history; close it.

A note from Yojji

Multi-tenant data isolation, the kind of plumbing that decides whether a SaaS quietly works or quietly leaks, is the unglamorous backend work that pays dividends in compliance, audits, and customer trust. It is the kind of engineering Yojji’s teams build into the platforms they deliver for clients.

Yojji is an international custom software development company founded in 2016, with offices across Europe, the US, and the UK. Their dedicated teams ship across the JavaScript ecosystem (React, Node.js, TypeScript), cloud platforms (AWS, Azure, GCP), and microservices architectures — including the database design and security work that decides whether a multi-tenant product is one bug away from a breach or actually robust.