Modèles d’isolation des données des locataires au niveau des lignes

Why Tenant Isolation Is a First-Class Concern

The Naive Approach and Its Risks

'use server';
import { db } from '@/lib/db';

// DANGEROUS: tenant_id is never checked
export async function getInvoices() {
  // This returns ALL invoices from ALL tenants!
  const invoices = await db.query(
    'SELECT * FROM invoices ORDER BY created_at DESC'
  );
  return invoices.rows;
}

Resolving the Current Tenant in Next.js 15

// lib/tenant.ts
import { cookies } from 'next/headers';
import { verifyJwt } from '@/lib/auth';

export interface TenantContext {
  tenantId: string;
  userId: string;
}

export async function requireTenantContext(): Promise<TenantContext> {
  const cookieStore = await cookies();
  const token = cookieStore.get('session')?.value;

  if (!token) {
    throw new Error('Unauthenticated');
  }

  const payload = await verifyJwt(token);

  if (!payload?.tenantId || !payload?.userId) {
    throw new Error('Invalid session: missing tenant context');
  }

  return {
    tenantId: payload.tenantId as string,
    userId: payload.userId as string,
  };
}

Building a Scoped Data Access Layer (DAL)

// lib/dal/invoices.ts
import { db } from '@/lib/db';
import type { TenantContext } from '@/lib/tenant';

export interface Invoice {
  id: string;
  tenantId: string;
  amount: number;
  status: string;
  createdAt: Date;
}

export async function listInvoices(
  ctx: TenantContext,
  page = 1,
  pageSize = 20
): Promise<Invoice[]> {
  const offset = (page - 1) * pageSize;

  const result = await db.query<Invoice>(
    `SELECT id, tenant_id AS "tenantId", amount, status, created_at AS "createdAt"
     FROM invoices
     WHERE tenant_id = $1
     ORDER BY created_at DESC
     LIMIT $2 OFFSET $3`,
    [ctx.tenantId, pageSize, offset]
  );

  return result.rows;
}

export async function getInvoiceById(
  ctx: TenantContext,
  invoiceId: string
): Promise<Invoice | null> {
  const result = await db.query<Invoice>(
    `SELECT id, tenant_id AS "tenantId", amount, status, created_at AS "createdAt"
     FROM invoices
     WHERE tenant_id = $1 AND id = $2`,
    [ctx.tenantId, invoiceId]
  );

  return result.rows[0] ?? null;
}

Using the DAL in Server Actions

'use server';
import { requireTenantContext } from '@/lib/tenant';
import { listInvoices, getInvoiceById } from '@/lib/dal/invoices';

export async function fetchInvoicesAction(page: number = 1) {
  const ctx = await requireTenantContext();
  return listInvoices(ctx, page);
}

export async function fetchInvoiceAction(invoiceId: string) {
  const ctx = await requireTenantContext();

  const invoice = await getInvoiceById(ctx, invoiceId);

  if (!invoice) {
    // Could be not found OR a cross-tenant access attempt —
    // return the same error to avoid leaking existence information.
    throw new Error('Invoice not found');
  }

  return invoice;
}

PostgreSQL Row-Level Security (RLS)

-- Run once per table during migrations
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Deny all access by default (belt-and-suspenders)
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- Allow SELECT/INSERT/UPDATE/DELETE only for the current tenant
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true));

-- The app sets this variable in every transaction:
-- SET LOCAL app.current_tenant_id = '<uuid>';

Wiring RLS into the Database Client

// lib/db.ts
import { Pool, PoolClient } from 'pg';

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

export async function withTenantTransaction<T>(
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // Scope this setting to the current transaction only
    await client.query(
      `SET LOCAL app.current_tenant_id = $1`,
      [tenantId]
    );
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Updating the DAL to Use RLS Transactions

// lib/dal/invoices.ts (updated)
import { withTenantTransaction } from '@/lib/db';
import type { TenantContext } from '@/lib/tenant';
import type { Invoice } from './types';

export async function listInvoices(
  ctx: TenantContext,
  page = 1,
  pageSize = 20
): Promise<Invoice[]> {
  const offset = (page - 1) * pageSize;

  return withTenantTransaction(ctx.tenantId, async (client) => {
    const result = await client.query<Invoice>(
      `SELECT id, tenant_id AS "tenantId", amount, status, created_at AS "createdAt"
       FROM invoices
       WHERE tenant_id = $1
       ORDER BY created_at DESC
       LIMIT $2 OFFSET $3`,
      [ctx.tenantId, pageSize, offset]
    );
    return result.rows;
  });
}

Schema-Per-Tenant Isolation with Prisma

// lib/tenant-db.ts
import { PrismaClient } from '@prisma/client';

const clientCache = new Map<string, PrismaClient>();

export function getTenantPrismaClient(tenantId: string): PrismaClient {
  const safeSchema = tenantId.replace(/[^a-z0-9_]/gi, '_');

  if (clientCache.has(safeSchema)) {
    return clientCache.get(safeSchema)!;
  }

  const client = new PrismaClient({
    datasources: {
      db: {
        url: `${process.env.DATABASE_URL}?schema=${safeSchema}`,
      },
    },
  });

  clientCache.set(safeSchema, client);
  return client;
}

// Usage in a Server Action:
// const db = getTenantPrismaClient(ctx.tenantId);
// const invoices = await db.invoice.findMany();

Preventing Insecure Direct Object Reference (IDOR)

'use server';
import { requireTenantContext } from '@/lib/tenant';
import { withTenantTransaction } from '@/lib/db';

export async function deleteInvoiceAction(invoiceId: string) {
  const ctx = await requireTenantContext();

  await withTenantTransaction(ctx.tenantId, async (client) => {
    const result = await client.query(
      `DELETE FROM invoices
       WHERE id = $1 AND tenant_id = $2`,
      [invoiceId, ctx.tenantId]
    );

    // If rowCount is 0 the invoice either doesn't exist
    // or belongs to another tenant — treat both identically.
    if ((result.rowCount ?? 0) === 0) {
      throw new Error('Invoice not found');
    }
  });
}

Testing Tenant Isolation

// tests/dal/invoices.test.ts
import { describe, it, expect, beforeAll } from 'vitest';
import { listInvoices, getInvoiceById } from '@/lib/dal/invoices';
import { seedTestData, cleanTestData } from '../helpers/db';

const tenantA = { tenantId: 'tenant-aaa', userId: 'user-1' };
const tenantB = { tenantId: 'tenant-bbb', userId: 'user-2' };

beforeAll(async () => {
  await cleanTestData();
  await seedTestData([
    { id: 'inv-1', tenantId: 'tenant-aaa', amount: 100, status: 'paid' },
    { id: 'inv-2', tenantId: 'tenant-bbb', amount: 200, status: 'pending' },
  ]);
});

describe('listInvoices', () => {
  it('returns only the requesting tenant\'s invoices', async () => {
    const invoices = await listInvoices(tenantA);
    expect(invoices).toHaveLength(1);
    expect(invoices[0].id).toBe('inv-1');
  });
});

describe('getInvoiceById', () => {
  it('returns null when the invoice belongs to a different tenant', async () => {
    // Tenant A tries to fetch Tenant B's invoice
    const invoice = await getInvoiceById(tenantA, 'inv-2');
    expect(invoice).toBeNull();
  });
});

Knowledge Check: Tenant Isolation Strategy

Recap: Row-Level Tenant Data Isolation Patterns