Multi-Tenancy Architecture — Database-per-Tenant vs Schema vs Row-Level Security

Introduction

Multi-tenancy means a single application serves multiple isolated customers. Three architectural patterns dominate: database-per-tenant (complete isolation, highest cost), schema-per-tenant (good balance), and row-level security (lowest cost, highest complexity). This post analyzes each model, implements tenant context middleware, demonstrates PostgreSQL RLS, prevents cross-tenant queries, automates tenant onboarding, guarantees isolation, and covers migration strategies when scaling up.

Three Multi-Tenancy Models Compared
PostgreSQL RLS with Row Policies
Tenant Context Middleware
Preventing Cross-Tenant Query Leaks
Tenant Onboarding Automation
Data Isolation Guarantees
Migration Strategies
Multi-Tenancy Checklist
Conclusion

Three Multi-Tenancy Models Compared

Each model trades cost, isolation, and operational complexity differently.

// Model 1: Database-per-Tenant
// Highest isolation, highest cost, simplest to implement
class DatabasePerTenantManager {
  private pools: Map<string, Pool> = new Map();

  async getTenantDb(tenantId: string): Promise<Pool> {
    if (!this.pools.has(tenantId)) {
      const pool = new Pool({
        host: process.env.DB_HOST,
        port: parseInt(process.env.DB_PORT || '5432'),
        database: `tenant_${tenantId}`, // Separate database per tenant
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
      });
      this.pools.set(tenantId, pool);
    }
    return this.pools.get(tenantId)!;
  }

  async createTenant(tenantId: string, tenantName: string): Promise<void> {
    // Create new database
    const adminPool = new Pool({
      host: process.env.DB_HOST,
      database: 'postgres', // Connect to default database
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
    });

    try {
      await adminPool.query(`CREATE DATABASE tenant_${tenantId}`);

      // Run migrations on new database
      const tenantPool = await this.getTenantDb(tenantId);
      await runMigrations(tenantPool);

      // Initialize tenant metadata
      await tenantPool.query(
        'INSERT INTO tenant_metadata (id, name, created_at) VALUES ($1, $2, NOW())',
        [tenantId, tenantName]
      );
    } finally {
      await adminPool.end();
    }
  }

  async deleteTenant(tenantId: string): Promise<void> {
    const adminPool = new Pool({
      host: process.env.DB_HOST,
      database: 'postgres',
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
    });

    try {
      // Revoke connections
      await adminPool.query(
        `SELECT pg_terminate_backend(pg_stat_activity.pid)
         FROM pg_stat_activity
         WHERE pg_stat_activity.datname = 'tenant_${tenantId}'`
      );

      // Drop database
      await adminPool.query(`DROP DATABASE IF EXISTS tenant_${tenantId}`);
    } finally {
      await adminPool.end();
    }
  }
}

// Model 2: Schema-per-Tenant
// Balanced isolation and cost, single database
class SchemaPerTenantManager {
  private sharedPool: Pool;

  constructor() {
    this.sharedPool = new Pool({
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
    });
  }

  async createTenant(tenantId: string, tenantName: string): Promise<void> {
    const schemaName = `tenant_${tenantId}`;

    await this.sharedPool.query(`CREATE SCHEMA IF NOT EXISTS ${schemaName}`);

    // Run migrations within schema
    await runMigrations(this.sharedPool, schemaName);

    // Create tenant metadata
    await this.sharedPool.query(
      `INSERT INTO ${schemaName}.tenant_metadata (id, name, created_at)
       VALUES ($1, $2, NOW())`,
      [tenantId, tenantName]
    );

    // Grant permissions
    await this.sharedPool.query(
      `GRANT USAGE ON SCHEMA ${schemaName} TO app_user`
    );
    await this.sharedPool.query(
      `GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA ${schemaName} TO app_user`
    );
  }

  async executeInTenant(
    tenantId: string,
    query: string,
    params?: any[]
  ): Promise<any> {
    const schemaName = `tenant_${tenantId}`;
    const fullQuery = `SET search_path TO ${schemaName}; ${query}`;
    return this.sharedPool.query(fullQuery, params);
  }
}

// Model 3: Row-Level Security (RLS)
// Lowest cost, highest complexity, but most flexible
class RowLevelSecurityManager {
  private pool: Pool;

  constructor() {
    this.pool = new Pool({
      host: process.env.DB_HOST,
      database: process.env.DB_NAME,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
    });
  }

  async setupRls(): Promise<void> {
    // All tables include tenant_id column
    await this.pool.query(`
      CREATE TABLE IF NOT EXISTS users (
        id uuid PRIMARY KEY,
        tenant_id uuid NOT NULL,
        email text NOT NULL,
        name text,
        UNIQUE(tenant_id, email)
      );

      CREATE TABLE IF NOT EXISTS posts (
        id uuid PRIMARY KEY,
        tenant_id uuid NOT NULL,
        author_id uuid NOT NULL,
        title text NOT NULL,
        content text,
        FOREIGN KEY(tenant_id, author_id) REFERENCES users(tenant_id, id)
      );

      -- Enable RLS on all tables
      ALTER TABLE users ENABLE ROW LEVEL SECURITY;
      ALTER TABLE posts ENABLE ROW LEVEL SECURITY;

      -- Create tenant context function
      CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS uuid AS $$
        SELECT current_setting('app.current_tenant_id')::uuid;
      $$ LANGUAGE sql STABLE;

      -- Create policies
      CREATE POLICY users_isolation ON users
        FOR ALL
        USING (tenant_id = current_tenant_id())
        WITH CHECK (tenant_id = current_tenant_id());

      CREATE POLICY posts_isolation ON posts
        FOR ALL
        USING (tenant_id = current_tenant_id())
        WITH CHECK (tenant_id = current_tenant_id());
    `);
  }

  async executeWithTenantContext(
    tenantId: string,
    callback: (client: PoolClient) => Promise<any>
  ): Promise<any> {
    const client = await this.pool.connect();
    try {
      // Set tenant context for this connection
      await client.query('SET app.current_tenant_id = $1', [tenantId]);
      return await callback(client);
    } finally {
      client.release();
    }
  }
}

// Comparison table
const comparisonData = {
  'Database-per-Tenant': {
    isolation: 'Complete',
    cost: 'Highest',
    complexity: 'Low',
    scalability: 'Vertical per tenant',
    backups: 'Per database',
  },
  'Schema-per-Tenant': {
    isolation: 'Good',
    cost: 'Medium',
    complexity: 'Medium',
    scalability: 'Shared resources',
    backups: 'Single backup',
  },
  'Row-Level-Security': {
    isolation: 'Policy-based',
    cost: 'Lowest',
    complexity: 'High',
    scalability: 'Single instance',
    backups: 'Single backup',
  },
};

PostgreSQL RLS with Row Policies

Implement Row-Level Security for fine-grained tenant isolation without data duplication.

import { Pool, PoolClient } from 'pg';

class RLSManager {
  private pool: Pool;

  constructor(pool: Pool) {
    this.pool = pool;
  }

  async createTenant(tenantId: string, tenantName: string): Promise<void> {
    await this.pool.query(
      `INSERT INTO tenant_metadata (id, name, created_at) VALUES ($1, $2, NOW())`,
      [tenantId, tenantName]
    );
  }

  async createTenantUser(
    tenantId: string,
    userId: string,
    email: string,
    role: 'admin' | 'user'
  ): Promise<void> {
    await this.pool.query(
      `INSERT INTO users (id, tenant_id, email, role)
       VALUES ($1, $2, $3, $4)`,
      [userId, tenantId, email, role]
    );
  }

  async executeQuery(
    tenantId: string,
    userId: string,
    query: string,
    params: any[] = []
  ): Promise<any> {
    const client = await this.pool.connect();
    try {
      // Set local variables for RLS policies
      await client.query('SET app.current_tenant_id = $1', [tenantId]);
      await client.query('SET app.current_user_id = $1', [userId]);

      return await client.query(query, params);
    } finally {
      client.release();
    }
  }

  async setupAdvancedPolicies(): Promise<void> {
    // Tenant-level isolation
    await this.pool.query(`
      CREATE OR REPLACE POLICY tenant_isolation ON posts
        FOR ALL
        USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
        WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);
    `);

    // User can only update own posts
    await this.pool.query(`
      CREATE OR REPLACE POLICY user_post_update ON posts
        FOR UPDATE
        USING (author_id = current_setting('app.current_user_id')::uuid)
        WITH CHECK (author_id = current_setting('app.current_user_id')::uuid);
    `);

    // Admins can see all posts in tenant
    await this.pool.query(`
      CREATE OR REPLACE POLICY admin_post_select ON posts
        FOR SELECT
        USING (
          tenant_id = current_setting('app.current_tenant_id')::uuid
          AND EXISTS (
            SELECT 1 FROM users
            WHERE id = current_setting('app.current_user_id')::uuid
            AND role = 'admin'
          )
        );
    `);

    // Comments inherit post visibility
    await this.pool.query(`
      CREATE OR REPLACE POLICY comments_inherit_post ON comments
        FOR ALL
        USING (
          post_id IN (
            SELECT id FROM posts
            WHERE tenant_id = current_setting('app.current_tenant_id')::uuid
          )
        );
    `);
  }
}

export { RLSManager };

Tenant Context Middleware

Inject tenant context into every request to enforce isolation.

import { Request, Response, NextFunction } from 'express';
import { jwtVerify } from 'jose';

interface TenantContext {
  tenantId: string;
  userId: string;
  role: 'admin' | 'user';
}

declare global {
  namespace Express {
    interface Request {
      tenantContext?: TenantContext;
    }
  }
}

const extractTenantContext = async (
  req: Request,
  res: Response,
  next: NextFunction
): Promise<void> => {
  const token = req.headers.authorization?.replace('Bearer ', '');

  if (!token) {
    return res.status(401).json({ error: 'No authorization token' });
  }

  try {
    // Verify JWT and extract tenant/user info
    const decoded = await jwtVerify(
      token,
      new TextEncoder().encode(process.env.JWT_SECRET!)
    );

    const tenantId = req.headers['x-tenant-id'] as string;

    // Verify token tenant matches request tenant
    if (decoded.payload.tenantId !== tenantId) {
      return res.status(403).json({ error: 'Tenant mismatch' });
    }

    // Verify tenant exists and user is member
    const tenantCheck = await db.query(
      `SELECT id FROM tenant_metadata WHERE id = $1`,
      [tenantId]
    );

    if (tenantCheck.rows.length === 0) {
      return res.status(404).json({ error: 'Tenant not found' });
    }

    const userCheck = await db.query(
      `SELECT role FROM users WHERE id = $1 AND tenant_id = $2`,
      [decoded.payload.sub, tenantId]
    );

    if (userCheck.rows.length === 0) {
      return res.status(403).json({ error: 'User not member of tenant' });
    }

    req.tenantContext = {
      tenantId,
      userId: decoded.payload.sub as string,
      role: userCheck.rows[0].role,
    };

    next();
  } catch (error) {
    res.status(401).json({ error: 'Invalid token' });
  }
};

// Middleware to enforce admin-only routes
const requireAdmin = (req: Request, res: Response, next: NextFunction) => {
  if (!req.tenantContext || req.tenantContext.role !== 'admin') {
    return res.status(403).json({ error: 'Admin access required' });
  }
  next();
};

// Middleware to set RLS context
const setRLSContext = async (
  req: Request,
  res: Response,
  next: NextFunction
) => {
  if (!req.tenantContext) {
    return res.status(401).json({ error: 'No tenant context' });
  }

  // Store context for this request
  req.db = await db.connect();
  await req.db.query('SET app.current_tenant_id = $1', [
    req.tenantContext.tenantId,
  ]);
  await req.db.query('SET app.current_user_id = $1', [
    req.tenantContext.userId,
  ]);

  // Release connection on response
  res.on('finish', () => req.db?.release());

  next();
};

// Usage in route handlers
app.use(extractTenantContext);
app.use(setRLSContext);

app.get('/api/posts', (req, res) => {
  // Tenant context automatically applied via RLS
  const result = await req.db.query('SELECT * FROM posts');
  res.json(result.rows);
});

app.post('/api/admin/stats', requireAdmin, (req, res) => {
  // Only accessible to admins
  const stats = await req.db.query(
    'SELECT COUNT(*) as post_count FROM posts'
  );
  res.json(stats.rows[0]);
});

Preventing Cross-Tenant Query Leaks

Implement guards to prevent accidental cross-tenant data access.

// Query wrapper that validates tenant context
class SafeQueryBuilder {
  constructor(
    private client: PoolClient,
    private tenantId: string,
    private userId: string
  ) {}

  async query(
    sql: string,
    params: any[] = [],
    expectedTables: string[] = []
  ): Promise<any> {
    // Whitelist tables that should be queried
    const hasFromClause = sql.toUpperCase().includes('FROM');
    const hasTenantFilter =
      sql.toUpperCase().includes('WHERE') &&
      sql.toUpperCase().includes('TENANT');

    if (hasFromClause && !hasTenantFilter && expectedTables.length > 0) {
      // Warn if query doesn't have tenant filter
      console.warn(`Unfiltered query on tables: ${expectedTables.join(', ')}`);
    }

    // Validate all parameters are properly typed
    for (const param of params) {
      if (param === undefined) {
        throw new Error('Undefined query parameter');
      }
    }

    return this.client.query(sql, params);
  }

  // Convenience methods for common patterns
  async selectByTenant(table: string, columns: string = '*'): Promise<any> {
    return this.query(
      `SELECT ${columns} FROM ${table} WHERE tenant_id = $1`,
      [this.tenantId]
    );
  }

  async insertWithTenant(
    table: string,
    data: Record<string, any>
  ): Promise<any> {
    const columns = ['tenant_id', ...Object.keys(data)];
    const values = [this.tenantId, ...Object.values(data)];
    const placeholders = columns.map((_, i) => `$${i + 1}`).join(',');

    return this.query(
      `INSERT INTO ${table} (${columns.join(',')}) VALUES (${placeholders})`,
      values
    );
  }
}

// Anti-patterns that will be caught
class SafeQueryValidator {
  private readonly forbiddenPatterns = [
    /SELECT\s+\*\s+FROM\s+\w+(?!.*WHERE)/i, // SELECT * without WHERE
    /DELETE\s+FROM\s+\w+(?!.*WHERE)/i, // DELETE without WHERE
    /UPDATE\s+\w+\s+SET(?!.*WHERE)/i, // UPDATE without WHERE
  ];

  validate(sql: string): void {
    for (const pattern of this.forbiddenPatterns) {
      if (pattern.test(sql)) {
        throw new Error(`Dangerous query pattern detected: ${sql}`);
      }
    }
  }

  // Type-safe parameter binding
  bind(sql: string, params: unknown[]): [string, any[]] {
    const paramTypes = params.map(p => typeof p);
    return [sql, params];
  }
}

// Usage
const validator = new SafeQueryValidator();

try {
  // This will throw
  validator.validate('SELECT * FROM posts');
} catch (e) {
  console.error(e.message);
}

try {
  // This is safe
  validator.validate(
    'SELECT * FROM posts WHERE tenant_id = $1 AND author_id = $2'
  );
} catch (e) {
  console.error(e.message);
}

Tenant Onboarding Automation

Automated, idempotent tenant provisioning.

class TenantProvisioner {
  constructor(
    private dbManager: DatabasePerTenantManager | SchemaPerTenantManager,
    private eventBus: EventBus
  ) {}

  async provisionTenant(
    tenantId: string,
    tenantName: string,
    adminEmail: string,
    adminPassword: string
  ): Promise<void> {
    try {
      // Step 1: Create tenant
      await this.dbManager.createTenant(tenantId, tenantName);
      await this.eventBus.publish({
        type: 'TenantCreated',
        tenantId,
        tenantName,
        timestamp: new Date(),
      });

      // Step 2: Create admin user
      const adminUserId = uuid();
      const hashedPassword = await hashPassword(adminPassword);

      await this.dbManager.executeInTenant(tenantId, 'users', {
        id: adminUserId,
        email: adminEmail,
        password_hash: hashedPassword,
        role: 'admin',
        created_at: new Date(),
      });

      await this.eventBus.publish({
        type: 'AdminUserCreated',
        tenantId,
        userId: adminUserId,
        email: adminEmail,
        timestamp: new Date(),
      });

      // Step 3: Initialize sample data
      await this.initializeSampleData(tenantId);

      // Step 4: Send welcome email
      await this.sendWelcomeEmail(adminEmail, tenantName);

      // Step 5: Record provisioning complete
      await this.updateProvisioningStatus(tenantId, 'active');

      console.log(`Tenant ${tenantId} provisioned successfully`);
    } catch (error) {
      await this.updateProvisioningStatus(tenantId, 'failed', error);
      throw error;
    }
  }

  private async initializeSampleData(tenantId: string): Promise<void> {
    // Idempotent: only insert if not exists
    const samplePosts = [
      { title: 'Welcome', content: 'Welcome to your tenant!' },
      { title: 'Getting Started', content: 'Here are next steps...' },
    ];

    for (const post of samplePosts) {
      await this.dbManager.executeInTenant(tenantId, 'posts', {
        id: uuid(),
        title: post.title,
        content: post.content,
        published: true,
      });
    }
  }

  private async sendWelcomeEmail(
    email: string,
    tenantName: string
  ): Promise<void> {
    // Queue email job
    await emailQueue.add('welcome', {
      to: email,
      tenantName,
    });
  }

  private async updateProvisioningStatus(
    tenantId: string,
    status: 'active' | 'failed',
    error?: Error
  ): Promise<void> {
    // Update in metadata service
    await metadataDb.query(
      `UPDATE tenant_metadata SET status = $1, provisioned_at = NOW()
       WHERE id = $2`,
      [status, tenantId]
    );
  }
}

// Verify isolation after provisioning
async function verifyTenantIsolation(tenantId: string): Promise<boolean> {
  const tenant1Posts = await db.query(
    `SELECT COUNT(*) FROM posts WHERE tenant_id = $1`,
    [tenantId]
  );
  const tenant2Posts = await db.query(
    `SELECT COUNT(*) FROM posts WHERE tenant_id != $1`,
    [tenantId]
  );

  console.log(`Tenant ${tenantId}: ${tenant1Posts.rows[0].count} posts`);
  console.log(`Other tenants: ${tenant2Posts.rows[0].count} posts`);

  return tenant1Posts.rows[0].count > 0 && tenant2Posts.rows[0].count === 0;
}

Data Isolation Guarantees

Test isolation boundaries to prevent data leaks.

describe('Multi-Tenancy Isolation', () => {
  let tenant1: string;
  let tenant2: string;
  let user1: string;
  let user2: string;

  beforeAll(async () => {
    tenant1 = uuid();
    tenant2 = uuid();
    user1 = uuid();
    user2 = uuid();

    // Provision tenants
    await provisioner.provisionTenant(tenant1, 'Tenant 1', 'admin@t1.com', 'pass');
    await provisioner.provisionTenant(tenant2, 'Tenant 2', 'admin@t2.com', 'pass');

    // Create users
    await createUser(tenant1, user1, 'user1@t1.com');
    await createUser(tenant2, user2, 'user2@t2.com');
  });

  it('tenant1 cannot see tenant2 posts', async () => {
    // Create post in tenant2
    const post = await createPost(tenant2, user2, 'Private post');

    // Query from tenant1
    const results = await executeQuery(
      tenant1,
      user1,
      'SELECT * FROM posts WHERE id = $1',
      [post.id]
    );

    expect(results.rows).toHaveLength(0);
  });

  it('RLS policies prevent cross-tenant access', async () => {
    // Try direct SQL access (should fail)
    const pool = new Pool({ connectionString: process.env.DATABASE_URL });

    // No RLS context set
    const results = await pool.query('SELECT * FROM posts WHERE tenant_id = $1', [
      tenant2,
    ]);

    // Should return rows (no RLS without context)
    // But in production, RLS is always enforced
    expect(results.rows.length >= 0).toBe(true);

    pool.end();
  });

  it('user from tenant1 cannot update tenant2 records', async () => {
    const post = await createPost(tenant2, user2, 'Post');

    const result = executeQuery(
      tenant1,
      user1,
      'UPDATE posts SET content = $1 WHERE id = $2',
      ['Hacked', post.id]
    );

    await expect(result).rejects.toThrow();
  });

  it('cannot enumerate other tenants', async () => {
    const result = await executeQuery(
      tenant1,
      user1,
      'SELECT DISTINCT tenant_id FROM posts'
    );

    // Should only see tenant1
    const tenantIds = result.rows.map(r => r.tenant_id);
    expect(tenantIds).toEqual([tenant1]);
  });
});

Migration Strategies

Migrate data between multi-tenancy models without downtime.

// Migrate from shared schema to schema-per-tenant
async function migrateToSchemaTenancy(): Promise<void> {
  const batchSize = 100;
  let offset = 0;

  while (true) {
    // Get tenants
    const tenants = await metadataDb.query(
      'SELECT id FROM tenant_metadata LIMIT $1 OFFSET $2',
      [batchSize, offset]
    );

    if (tenants.rows.length === 0) break;

    for (const tenant of tenants.rows) {
      await migrateTenantToSchema(tenant.id);
    }

    offset += batchSize;
  }

  console.log('Migration complete');
}

async function migrateTenantToSchema(tenantId: string): Promise<void> {
  const client = await sharedDb.connect();
  try {
    await client.query('BEGIN');

    const schemaName = `tenant_${tenantId}`;

    // Create schema
    await client.query(`CREATE SCHEMA IF NOT EXISTS ${schemaName}`);

    // Copy table structure
    await client.query(
      `CREATE TABLE IF NOT EXISTS ${schemaName}.posts AS
       SELECT * FROM public.posts WHERE tenant_id = $1`,
      [tenantId]
    );

    // Recreate indexes without tenant_id column (now implicit)
    await client.query(
      `CREATE INDEX idx_posts_author ON ${schemaName}.posts (author_id)`
    );

    // Verify row count matches
    const original = await client.query(
      'SELECT COUNT(*) FROM public.posts WHERE tenant_id = $1',
      [tenantId]
    );
    const migrated = await client.query(
      `SELECT COUNT(*) FROM ${schemaName}.posts`
    );

    if (original.rows[0].count !== migrated.rows[0].count) {
      throw new Error('Row count mismatch after migration');
    }

    await client.query('COMMIT');
    console.log(`Tenant ${tenantId} migrated successfully`);
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release();
  }
}

Multi-Tenancy Checklist

Conclusion

Choose your multi-tenancy model based on scale and isolation requirements. Start with RLS for simplicity if team understands the complexity. Use schema-per-tenant for a good balance of isolation and cost. Reserve database-per-tenant for highest security or complete isolation requirements. Always inject tenant context, validate queries, and test isolation boundaries rigorously.