Testing

Multi-Tenant Data Isolation Testing: PostgreSQL RLS and Schema-Per-Tenant

HelpMeTest

HelpMeTest

— 5 min read
Multi-Tenant Data Isolation Testing: PostgreSQL RLS and Schema-Per-Tenant

Data isolation bugs in multi-tenant SaaS are catastrophic. When tenant A can see tenant B's data, you don't get a bug report — you get a breach notification, a legal team, and customer churn. The failure mode is silent: everything looks fine until someone notices records they shouldn't see.

Testing data isolation is not optional. It's the most critical test category in any multi-tenant system.

Two Patterns, Two Test Strategies

Multi-tenant PostgreSQL applications generally use one of two isolation models:

Row Level Security (RLS): All tenants share tables. A tenant_id column filters rows. PostgreSQL policies enforce that queries can only return rows matching the current tenant context.

Schema-per-tenant: Each tenant gets a separate schema (tenant_abc.orders, tenant_xyz.orders). The application switches the search path at connection time.

Both patterns can fail. Both need tests.

Testing PostgreSQL Row Level Security

RLS policies live in the database, which means application-level tests can miss them entirely. A developer who bypasses RLS by using the superuser role in tests creates a false sense of security.

Setting Up RLS Test Fixtures

Your test fixtures must mirror production RLS configuration exactly:

-- Enable RLS on the orders table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners (critical: without this, superuser bypasses policies)
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

-- Policy: users can only see their tenant's rows
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

The FORCE ROW LEVEL SECURITY line is essential. Without it, any connection with table ownership bypasses policies. Most developers hit this in tests and conclude RLS "isn't working" when in fact they're connecting as the wrong role.

Unit Testing RLS Policies

Test policies directly in SQL before writing application-layer tests:

-- Create two test tenants
INSERT INTO tenants (id, name) VALUES
  ('tenant-aaa', 'Tenant A'),
  ('tenant-bbb', 'Tenant B');

-- Create orders for each tenant
INSERT INTO orders (id, tenant_id, amount) VALUES
  ('order-1', 'tenant-aaa', 100),
  ('order-2', 'tenant-bbb', 200);

-- Test as Tenant A: should see only order-1
SET LOCAL app.current_tenant_id = 'tenant-aaa';
SELECT count(*) FROM orders; -- must return 1

-- Test as Tenant B: should see only order-2
SET LOCAL app.current_tenant_id = 'tenant-bbb';
SELECT count(*) FROM orders; -- must return 1

-- Verify cross-tenant query returns empty
SET LOCAL app.current_tenant_id = 'tenant-aaa';
SELECT * FROM orders WHERE id = 'order-2'; -- must return 0 rows

Run these in a transaction that rolls back, so test data doesn't persist:

BEGIN;
-- setup and assertions here
ROLLBACK;

Integration Testing RLS at the API Layer

The most dangerous gap is between your application and the database. An API endpoint that correctly sets the tenant context most of the time can fail under specific conditions (race conditions, connection pool reuse, async context loss).

describe('Orders API — tenant isolation', () => {
  let tenantA, tenantB;

  beforeAll(async () => {
    tenantA = await createTestTenant('Tenant A');
    tenantB = await createTestTenant('Tenant B');
    await createOrder(tenantA.id, { amount: 100 });
    await createOrder(tenantB.id, { amount: 200 });
  });

  it('returns only tenant A orders when authenticated as tenant A', async () => {
    const response = await request(app)
      .get('/api/orders')
      .set('Authorization', `Bearer ${tenantA.token}`);

    expect(response.status).toBe(200);
    expect(response.body.orders).toHaveLength(1);
    expect(response.body.orders[0].tenantId).toBe(tenantA.id);
  });

  it('rejects direct access to another tenant\'s order', async () => {
    const tenantBOrder = await getFirstOrder(tenantB.id);

    const response = await request(app)
      .get(`/api/orders/${tenantBOrder.id}`)
      .set('Authorization', `Bearer ${tenantA.token}`);

    // Must be 404, not 403 — never reveal that the resource exists
    expect(response.status).toBe(404);
  });

  it('prevents cross-tenant data in search results', async () => {
    const response = await request(app)
      .get('/api/orders?q=all')
      .set('Authorization', `Bearer ${tenantA.token}`);

    const tenantBOrders = response.body.orders.filter(
      o => o.tenantId === tenantB.id
    );
    expect(tenantBOrders).toHaveLength(0);
  });
});

Testing Connection Pool Context Leakage

Connection pools are a common source of tenant context leakage. When a connection is returned to the pool after serving tenant A's request, it may still have app.current_tenant_id set to tenant A's ID. The next request from tenant B picks up that connection and sees tenant A's data.

it('does not leak tenant context across pooled connections', async () => {
  // Exhaust the connection pool with tenant A requests
  const concurrentRequests = Array(10).fill(null).map(() =>
    request(app)
      .get('/api/orders')
      .set('Authorization', `Bearer ${tenantA.token}`)
  );
  await Promise.all(concurrentRequests);

  // Now make a request as tenant B — should not see tenant A's data
  const response = await request(app)
    .get('/api/orders')
    .set('Authorization', `Bearer ${tenantB.token}`);

  const leaked = response.body.orders.filter(o => o.tenantId === tenantA.id);
  expect(leaked).toHaveLength(0);
});

If this test fails, your middleware isn't resetting tenant context before returning connections to the pool. The fix is to use SET LOCAL (transaction-scoped) rather than SET (session-scoped) when establishing tenant context.

Testing Schema-Per-Tenant Isolation

Schema-per-tenant adds a different class of bugs: search path configuration errors.

Testing Schema Switching

describe('Schema-per-tenant isolation', () => {
  it('queries execute in the correct schema', async () => {
    // Insert data directly into each schema
    await db.query(
      `INSERT INTO tenant_aaa.users (id, name) VALUES ('user-1', 'Alice')`
    );
    await db.query(
      `INSERT INTO tenant_bbb.users (id, name) VALUES ('user-2', 'Bob')`
    );

    // API request as tenant A
    const response = await request(app)
      .get('/api/users')
      .set('X-Tenant-ID', 'tenant_aaa');

    // Should see Alice, not Bob
    expect(response.body.users.map(u => u.name)).toContain('Alice');
    expect(response.body.users.map(u => u.name)).not.toContain('Bob');
  });

  it('does not fall back to public schema when tenant schema is set', async () => {
    // Insert a user in the public schema (common footgun)
    await db.query(
      `INSERT INTO public.users (id, name) VALUES ('user-public', 'Public User')`
    );

    const response = await request(app)
      .get('/api/users')
      .set('X-Tenant-ID', 'tenant_aaa');

    const publicUser = response.body.users.find(u => u.name === 'Public User');
    expect(publicUser).toBeUndefined();
  });
});

Testing Schema Provisioning

When a new tenant signs up, their schema must be created and migrated correctly:

it('creates an isolated schema for new tenants', async () => {
  const newTenant = await createTenant({ name: 'New Corp' });

  // Verify schema exists
  const schemaExists = await db.query(
    `SELECT schema_name FROM information_schema.schemata WHERE schema_name = $1`,
    [`tenant_${newTenant.id}`]
  );
  expect(schemaExists.rows).toHaveLength(1);

  // Verify all expected tables exist in the new schema
  const tables = await db.query(
    `SELECT table_name FROM information_schema.tables WHERE table_schema = $1`,
    [`tenant_${newTenant.id}`]
  );
  const tableNames = tables.rows.map(r => r.table_name);
  expect(tableNames).toContain('users');
  expect(tableNames).toContain('orders');
  expect(tableNames).toContain('invoices');
});

Automated Isolation Verification

Add a recurring test that cross-checks every tenant can only see their own data:

async function runIsolationAudit() {
  const tenants = await getAllTenants();

  for (const tenant of tenants) {
    const otherTenants = tenants.filter(t => t.id !== tenant.id);

    for (const other of otherTenants) {
      const leaked = await db.query(
        `SELECT count(*) FROM orders WHERE tenant_id = $1`,
        [other.id],
        { tenantContext: tenant.id }
      );

      if (leaked.rows[0].count > 0) {
        throw new Error(
          `ISOLATION BREACH: Tenant ${tenant.id} can see ${leaked.rows[0].count} ` +
          `rows belonging to tenant ${other.id}`
        );
      }
    }
  }
}

Run this audit in staging after every deployment that touches database access patterns.

What HelpMeTest Adds

Manual isolation testing is slow and incomplete. HelpMeTest can run cross-tenant access scenarios continuously, testing every API endpoint against every tenant combination in your staging environment. Instead of a one-time audit, you get persistent verification that isolation holds as your codebase evolves.

Key Takeaways

  • Use FORCE ROW LEVEL SECURITY in PostgreSQL or your tests will pass while production leaks data
  • Test at the database level (SQL) AND the API level — both layers can fail independently
  • Connection pool context leakage is a real threat; test concurrent requests across tenant boundaries
  • For schema-per-tenant, test that new tenant schemas are created correctly and the public schema is never exposed
  • Automate isolation audits and run them on every deployment, not just during initial setup

Read more