Postgres Row-Level Security for Multi-Tenant Applications

Every multi-tenant SaaS I've worked on eventually hits the same incident: a developer forgets a WHERE tenant_id = $1 clause in a new query, and suddenly tenant A can see tenant B's invoices. Code review catches most of these, but not all — and "most" isn't good enough when the data is customer PII or financial records.

The fix isn't more discipline. It's moving tenant isolation out of application code and into the database itself. Postgres Row-Level Security (RLS) lets you define policies that filter rows automatically, so even a query with a missing WHERE clause can't leak data across tenants. It's not a silver bullet, but it's the closest thing to one for this specific problem.

Why Application-Layer Isolation Fails Eventually

The typical pattern looks safe on paper: every repository method takes a tenantId and appends it to the query.

// ❌ Isolation depends on every developer remembering this pattern, every time
async function getInvoices(tenantId: string) {
  return db.query(
    "SELECT * FROM invoices WHERE tenant_id = $1",
    [tenantId]
  );
}
 
// One missed join, one raw query for a "quick report",
// one ORM eager-load without a scope — and it's gone.
async function getInvoicesWithLineItems(tenantId: string) {
  return db.query(
    `SELECT i.*, li.* FROM invoices i
     JOIN line_items li ON li.invoice_id = i.id
     WHERE i.tenant_id = $1` // easy to forget on li.tenant_id too
  );
}

This works until it doesn't. Admin scripts, background jobs, data migrations, and third-party integrations all bypass your carefully scoped repository layer sooner or later. The database is the one place that sees every query regardless of origin, which makes it the right enforcement boundary.

Setting Up RLS Policies

RLS is disabled by default. You enable it per table, then define policies that determine which rows are visible for a given session.

-- Enable RLS on the table
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
 
-- Force RLS even for the table owner (critical — see below)
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
 
-- Policy: only rows matching the current tenant context are visible
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);
 
-- Same policy for writes
CREATE POLICY tenant_isolation_insert ON invoices
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

current_setting('app.current_tenant_id') reads a session-local variable you set at the start of each request. This is the piece that connects your application's auth context to Postgres's row filtering.

Wiring Tenant Context Into Your Connection Pool

The tricky part with connection pooling is that SET is session-scoped, and pooled connections are reused across requests. You need to set the tenant variable at the start of every transaction, not once per connection.

// ✅ Set tenant context per-transaction, not per-connection
async function withTenantContext<T>(
  pool: Pool,
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // set_config with local=true scopes the setting to this transaction
    await client.query(
      "SELECT set_config('app.current_tenant_id', $1, true)",
      [tenantId]
    );
    const result = await fn(client);
    await client.query("COMMIT");
    return result;
  } catch (err) {
    await client.query("ROLLBACK");
    throw err;
  } finally {
    client.release();
  }
}
 
// Usage in a request handler
app.get("/invoices", async (req, res) => {
  const invoices = await withTenantContext(pool, req.tenantId, (client) =>
    client.query("SELECT * FROM invoices") // no WHERE needed — RLS handles it
  );
  res.json(invoices.rows);
});

The true third argument to set_config makes the setting transaction-local — it resets automatically on commit or rollback, so there's no risk of leaking tenant context to the next request that grabs this pooled connection.

Handling Roles That Legitimately Need Cross-Tenant Access

Not every query should be tenant-scoped. Background jobs that aggregate metrics across all tenants, or admin dashboards for your support team, need a different path.

-- A dedicated role that bypasses tenant policies for legitimate cross-tenant work
CREATE ROLE reporting_service BYPASSRLS;
GRANT SELECT ON invoices TO reporting_service;
 
-- Or, keep RLS on but add an explicit policy for admin access
CREATE POLICY admin_full_access ON invoices
  USING (current_setting('app.is_admin', true) = 'true');

Prefer the explicit policy over BYPASSRLS where possible — it's auditable in pg_policies and doesn't require managing a separate superuser-adjacent role. Reserve BYPASSRLS for infrastructure-level jobs that never touch tenant-scoped application logic.

Testing Policies Like You Test Business Logic

RLS policies are code. Untested code has bugs. Write tests that assert isolation actually holds, not just that queries return results.

-- Test: tenant B must never see tenant A's rows
BEGIN;
SELECT set_config('app.current_tenant_id', 'tenant-a-uuid', true);
INSERT INTO invoices (tenant_id, amount) VALUES ('tenant-a-uuid', 100);
 
SELECT set_config('app.current_tenant_id', 'tenant-b-uuid', true);
-- This should return zero rows, not an error and not tenant A's data
SELECT count(*) FROM invoices WHERE amount = 100;
ROLLBACK;

// Integration test hitting the real database, not a mock
describe("tenant isolation", () => {
  it("prevents cross-tenant reads even without a WHERE clause", async () => {
    await withTenantContext(pool, TENANT_A, (client) =>
      client.query("INSERT INTO invoices (tenant_id, amount) VALUES ($1, $2)", [
        TENANT_A,
        500,
      ])
    );
 
    const result = await withTenantContext(pool, TENANT_B, (client) =>
      client.query("SELECT * FROM invoices")
    );
 
    expect(result.rows).toHaveLength(0);
  });
});

Run this suite in CI against a real Postgres instance, not SQLite or an in-memory shim — RLS behavior is Postgres-specific and doesn't exist in most lightweight test databases.

Performance Considerations

RLS policies are effectively WHERE clauses appended to every query by the planner, so they benefit from the same indexing rules. Make sure tenant_id is indexed, ideally as the leading column in composite indexes for tenant-scoped tables.

-- ✅ tenant_id first means the planner can filter before scanning
CREATE INDEX idx_invoices_tenant_created ON invoices (tenant_id, created_at DESC);

Also check query plans after enabling RLS — in rare cases, complex policies with subqueries can prevent index usage. Run EXPLAIN ANALYZE on your hottest queries post-migration and compare against the pre-RLS baseline.

Key Takeaways

1. Application-layer tenant filtering fails eventually — background jobs, admin tools, and forgotten WHERE clauses all bypass it. RLS enforces isolation at the one layer every query passes through.
2. Always pair ENABLE ROW LEVEL SECURITY with FORCE ROW LEVEL SECURITY, or table owners silently bypass your policies.
3. Set tenant context with set_config(..., true) inside a transaction, not as a raw SET on a pooled connection — otherwise it leaks across requests.
4. Use explicit, auditable policies for cross-tenant access (admin roles, reporting) instead of reaching for BYPASSRLS by default.
5. Test RLS policies against a real Postgres instance in CI — isolation bugs here are exactly the kind of thing that only shows up in production otherwise.
6. Index tenant_id as a leading column and verify query plans after rollout; RLS policies are real predicates that affect the planner.