Why Multi-Tenancy Is an Architecture Decision, Not a Feature

Multi-tenancy sounds simple: multiple customers share one application. In practice, it’s one of the most consequential architecture decisions you’ll make for a SaaS product. Get it right, and you scale efficiently with strong isolation guarantees. Get it wrong, and you’re either leaking data between tenants or rebuilding your data layer from scratch at exactly the moment your business can’t afford downtime.

At Harbor Software, we’ve built multi-tenant systems for SaaS products across healthcare, fintech, and enterprise collaboration. We’ve used all three major patterns — shared database with shared schema, shared database with separate schemas, and separate databases per tenant. For most SaaS applications, we now default to shared database with Row-Level Security (RLS) in PostgreSQL. This post explains why, and how to implement it without the pitfalls that catch most teams.

The Three Multi-Tenancy Patterns

Before diving into RLS, let’s understand the options and their trade-offs.

Separate Databases

Each tenant gets their own PostgreSQL database. Maximum isolation: a bug in one tenant’s data cannot affect another. The downsides are operational overhead (managing hundreds of database instances, running migrations across all of them, monitoring each one) and cost (each database consumes resources even when idle).

We use this pattern when regulatory requirements demand physical data separation — some healthcare and government contracts require it. For everything else, it’s overkill.

Shared Database, Separate Schemas

All tenants share one database, but each tenant has their own PostgreSQL schema with identical table structures. This gives you logical separation with lower operational overhead than separate databases. Migrations still need to run against every schema, and connection pooling gets complicated since each query needs to target the right schema.

We’ve used this for a few projects, but it doesn’t scale well beyond a few hundred tenants. Schema-per-tenant in a database with 10,000 tenants means 10,000 copies of every table definition, which bloats the PostgreSQL catalog and slows down operations that scan it.

Shared Database, Shared Schema with RLS

All tenants share the same tables. Every row has a tenant_id column, and PostgreSQL’s Row-Level Security ensures that each query only sees rows belonging to the current tenant. This is the most operationally efficient pattern — one set of tables, one migration path, one connection pool — with isolation enforced at the database engine level.

The concern people raise about this pattern is security: what if someone forgets a WHERE clause and leaks data? That’s exactly what RLS prevents. The database itself enforces the filter, regardless of what the application code does.

Implementing RLS in PostgreSQL

RLS works by attaching policies to tables that filter rows based on the current session context. Here’s how to set it up properly.

Step 1: Add tenant_id to Every Table

Every table that contains tenant-specific data needs a tenant_id column. We use UUIDs for tenant IDs to avoid sequential ID guessing attacks:

-- Create the tenants table
CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  plan TEXT NOT NULL DEFAULT 'free',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Example tenant-scoped table
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name TEXT NOT NULL,
  description TEXT,
  status TEXT NOT NULL DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Index on tenant_id is critical for performance
CREATE INDEX idx_projects_tenant ON projects(tenant_id);

-- Repeat for every tenant-scoped table
CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  project_id UUID NOT NULL REFERENCES projects(id),
  title TEXT NOT NULL,
  assignee_id UUID,
  status TEXT NOT NULL DEFAULT 'todo',
  created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tasks_tenant ON tasks(tenant_id);

Step 2: Create the Application Role

RLS policies apply to specific database roles. You’ll want at least two roles: one for the application (subject to RLS) and one for administrative operations (bypasses RLS):

-- Application role: subject to RLS
CREATE ROLE app_user NOINHERIT LOGIN PASSWORD 'secure-password';
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO app_user;

-- Admin role: bypasses RLS (for migrations, backfill, analytics)
CREATE ROLE app_admin NOINHERIT LOGIN PASSWORD 'different-secure-password';
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO app_admin;
ALTER ROLE app_admin SET row_security TO off;

Step 3: Enable RLS and Create Policies

Now the core of it — enabling RLS on each table and defining the access policies. We use a session variable (app.current_tenant_id) to communicate the current tenant to PostgreSQL:

-- Enable RLS on tenant-scoped tables
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

-- Force RLS even for table owners (important!)
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
ALTER TABLE tasks FORCE ROW LEVEL SECURITY;

-- Create policies
CREATE POLICY tenant_isolation_policy ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

CREATE POLICY tenant_isolation_policy ON tasks
  USING (tenant_id = current_setting('app.current_tenant_id')::UUID);

-- For INSERT, also ensure the tenant_id matches
CREATE POLICY tenant_insert_policy ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);

CREATE POLICY tenant_insert_policy ON tasks
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::UUID);

The USING clause filters rows for SELECT, UPDATE, and DELETE operations. The WITH CHECK clause validates new rows during INSERT and UPDATE. Together, they ensure a tenant can only read and write their own data.

The FORCE ROW LEVEL SECURITY line is easy to miss but critical. Without it, the table owner role bypasses RLS policies. If your application connects as the table owner (which is common in development), you won’t see any filtering and might think everything is working when it’s not.

Step 4: Set the Tenant Context

Before executing any query, you need to set the session variable that RLS policies reference. This happens at the connection level:

-- Set the tenant context for this session
SET app.current_tenant_id = 'a1b2c3d4-e5f6-7890-abcd-ef1234567890';

In your application code, this is typically done in middleware that runs before every database operation. Here’s how it looks in a Node.js application with Prisma:

// middleware/tenant.ts
import { PrismaClient } from '@prisma/client';
import { Request, Response, NextFunction } from 'express';

const prisma = new PrismaClient();

export async function tenantMiddleware(
  req: Request,
  res: Response,
  next: NextFunction
) {
  const tenantId = extractTenantId(req);  // From JWT, subdomain, header, etc.
  
  if (!tenantId) {
    return res.status(401).json({ error: 'Tenant not identified' });
  }

  // Set tenant context on the database connection
  await prisma.$executeRawUnsafe(
    `SET app.current_tenant_id = '${tenantId}'`
  );

  // Attach to request for application-level use
  req.tenantId = tenantId;
  next();
}

function extractTenantId(req: Request): string | null {
  // Option 1: From JWT claims
  if (req.user?.tenantId) return req.user.tenantId;
  
  // Option 2: From subdomain (acme.yourapp.com)
  const host = req.hostname;
  const subdomain = host.split('.')[0];
  if (subdomain !== 'www' && subdomain !== 'app') {
    // Look up tenant by subdomain
    return lookupTenantBySlug(subdomain);
  }
  
  // Option 3: From custom header (for API clients)
  return req.headers['x-tenant-id'] as string || null;
}

There’s a subtlety here with connection pooling. If you’re using a connection pool (PgBouncer, Prisma’s built-in pool), you must ensure that the SET command runs on the same connection as the subsequent queries. With PgBouncer in transaction mode, the SET is scoped to the transaction — which is what you want. With session mode, the SET persists for the life of the connection, which can cause tenant context to leak between requests if the connection is reused.

The safest approach is to wrap the SET and your queries in a transaction:

async function withTenant<T>(
  tenantId: string,
  fn: (tx: PrismaClient) => Promise<T>
): Promise<T> {
  return prisma.$transaction(async (tx) => {
    await tx.$executeRawUnsafe(
      `SET LOCAL app.current_tenant_id = '${tenantId}'`
    );
    return fn(tx);
  });
}

// Usage
const projects = await withTenant(req.tenantId, (tx) =>
  tx.project.findMany({ where: { status: 'active' } })
);

SET LOCAL scopes the variable to the current transaction, so it’s automatically cleaned up when the transaction completes. No risk of leaking between requests.

Performance Considerations

The most common concern about RLS is performance. Adding a filter to every query must have overhead, right? In practice, the overhead is negligible if you’ve done one thing correctly: indexed the tenant_id column.

PostgreSQL’s query planner is smart enough to push the RLS filter down into the query plan, combining it with your explicit WHERE clauses. If you have a query like:

SELECT * FROM tasks WHERE status = 'in_progress';

With RLS active, PostgreSQL transforms this internally to:

SELECT * FROM tasks
WHERE status = 'in_progress'
  AND tenant_id = current_setting('app.current_tenant_id')::UUID;

With a composite index on (tenant_id, status), this query is fast regardless of how many total rows the table contains. We always create composite indexes that lead with tenant_id:

-- Composite indexes for common query patterns
CREATE INDEX idx_tasks_tenant_status ON tasks(tenant_id, status);
CREATE INDEX idx_tasks_tenant_project ON tasks(tenant_id, project_id);
CREATE INDEX idx_projects_tenant_status ON projects(tenant_id, status);

In our benchmarks on a table with 50 million rows across 1,000 tenants, the RLS overhead was under 1ms per query. The index on tenant_id is doing the heavy lifting — RLS just ensures the filter is always present.

Handling Cross-Tenant Operations

Some operations legitimately need to access data across tenants: analytics, billing, system administration, data exports. These should use the admin role that bypasses RLS:

// admin-only operations use a separate connection
const adminPrisma = new PrismaClient({
  datasources: {
    db: { url: process.env.ADMIN_DATABASE_URL }  // Uses app_admin role
  }
});

// Cross-tenant analytics
async function getGlobalMetrics() {
  return adminPrisma.$queryRaw`
    SELECT
      t.plan,
      COUNT(DISTINCT t.id) AS tenant_count,
      COUNT(p.id) AS total_projects,
      AVG(task_counts.cnt) AS avg_tasks_per_tenant
    FROM tenants t
    LEFT JOIN projects p ON p.tenant_id = t.id
    LEFT JOIN (
      SELECT tenant_id, COUNT(*) AS cnt
      FROM tasks
      GROUP BY tenant_id
    ) task_counts ON task_counts.tenant_id = t.id
    GROUP BY t.plan
  `;
}

The admin connection should never be exposed to user-facing request paths. We enforce this architecturally: admin queries run in background jobs or admin API routes that require separate authentication.

Testing RLS Policies

RLS policies must be tested explicitly. It’s not enough to test that your application queries work — you need to verify that tenant isolation holds even when application code has bugs.

// __tests__/rls.test.ts
import { createTestTenant, createTestProject } from './helpers';

describe('Row Level Security', () => {
  let tenantA: string;
  let tenantB: string;
  let projectA: string;

  beforeAll(async () => {
    tenantA = await createTestTenant('Tenant A');
    tenantB = await createTestTenant('Tenant B');
    projectA = await createTestProject(tenantA, 'Project Alpha');
    await createTestProject(tenantB, 'Project Beta');
  });

  it('tenant A cannot see tenant B projects', async () => {
    const projects = await withTenant(tenantA, (tx) =>
      tx.project.findMany()
    );
    expect(projects).toHaveLength(1);
    expect(projects[0].name).toBe('Project Alpha');
  });

  it('tenant B cannot see tenant A projects', async () => {
    const projects = await withTenant(tenantB, (tx) =>
      tx.project.findMany()
    );
    expect(projects).toHaveLength(1);
    expect(projects[0].name).toBe('Project Beta');
  });

  it('tenant A cannot update tenant B data', async () => {
    const result = await withTenant(tenantA, (tx) =>
      tx.project.updateMany({
        where: { name: 'Project Beta' },
        data: { name: 'Hacked' },
      })
    );
    expect(result.count).toBe(0);  // No rows matched
  });

  it('tenant A cannot delete tenant B data', async () => {
    const result = await withTenant(tenantA, (tx) =>
      tx.project.deleteMany({
        where: { name: 'Project Beta' },
      })
    );
    expect(result.count).toBe(0);
  });

  it('tenant A cannot insert data for tenant B', async () => {
    await expect(
      withTenant(tenantA, (tx) =>
        tx.project.create({
          data: {
            tenantId: tenantB,  // Trying to create in wrong tenant
            name: 'Sneaky Project',
          },
        })
      )
    ).rejects.toThrow();  // WITH CHECK violation
  });
});

Run these tests in CI. They’re your safety net against any code change that might accidentally bypass tenant isolation.

Migrations and Schema Changes

One of the biggest advantages of the shared-schema approach is that migrations run once against one database. But you need to be careful about migrations that touch large tables with RLS enabled.

Adding a column to a table with 100 million rows is already a concern; doing it with RLS active adds no additional risk because RLS is a query-time filter, not a storage-time one. However, adding a new RLS policy or modifying an existing one can briefly lock the table. We schedule policy changes during maintenance windows for tables with heavy write loads.

When adding a new table, the checklist is: add tenant_id column, create the index, enable RLS, force RLS, create USING and WITH CHECK policies. We automate this with a custom migration helper:

-- migrations/helpers/enable_rls.sql
-- Call this for every new tenant-scoped table
CREATE OR REPLACE FUNCTION enable_tenant_rls(table_name TEXT)
RETURNS void AS $$
BEGIN
  EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', table_name);
  EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', table_name);
  EXECUTE format(
    'CREATE POLICY tenant_isolation ON %I USING (tenant_id = current_setting(''app.current_tenant_id'')::UUID)',
    table_name
  );
  EXECUTE format(
    'CREATE POLICY tenant_insert ON %I FOR INSERT WITH CHECK (tenant_id = current_setting(''app.current_tenant_id'')::UUID)',
    table_name
  );
END;
$$ LANGUAGE plpgsql;

Common Pitfalls and How to Avoid Them

• Forgetting FORCE ROW LEVEL SECURITY: Without it, the table owner bypasses all policies. If your app connects as the table owner (common in development), you’ll see all data and think RLS is working. It’s not. Always use FORCE.
• Not handling the missing session variable: If app.current_tenant_id is not set, current_setting() throws an error. In PostgreSQL 9.6+, you can use current_setting('app.current_tenant_id', true) to return NULL instead, then make your policy reject NULL tenant IDs explicitly.
• Leaking connections in pooled environments: If a connection is returned to the pool with a tenant context still set, the next request on that connection might see the wrong tenant’s data. Use SET LOCAL within transactions or explicitly RESET app.current_tenant_id when releasing connections.
• Forgetting to index tenant_id: Without the index, every query does a sequential scan filtered by RLS. On a large table, this is catastrophically slow. Always index, and prefer composite indexes that lead with tenant_id.
• Not testing INSERT policies: Many teams test that SELECT is filtered but forget to test that INSERT respects tenant boundaries. A user could insert data into another tenant’s partition if the WITH CHECK policy is missing.

When RLS Isn’t Enough

RLS is a powerful tool, but it has limits. It operates at the row level within a single database. If you need:

• Different schemas per tenant (some tenants have custom fields): Consider JSON columns or a hybrid with tenant-specific extension tables
• Per-tenant resource limits (CPU, connections, storage): RLS doesn’t help; you need application-level rate limiting or separate database instances
• Compliance-driven physical separation: Some regulations require data to be in separate physical storage. RLS is logical isolation, not physical
• Per-tenant backup and restore: You can’t selectively restore one tenant’s data from a shared database backup without additional tooling

For these cases, consider a hybrid architecture: shared database with RLS for most tenants, separate databases for enterprise tenants who need the isolation guarantees.

Our Production Checklist

Before shipping a multi-tenant system with RLS, we verify:

1. Every tenant-scoped table has RLS enabled AND forced
2. Every table has both USING and WITH CHECK policies
3. Every table has an index on tenant_id (composite preferred)
4. The application role is not the table owner
5. Admin operations use a separate database role
6. Connection pooling correctly scopes or resets tenant context
7. RLS isolation tests pass in CI for SELECT, INSERT, UPDATE, and DELETE
8. Performance benchmarks show acceptable query times under realistic data volumes
9. Monitoring alerts on queries that take longer than expected (RLS misconfiguration often manifests as slow queries)

Multi-tenancy is a foundational decision. PostgreSQL’s Row-Level Security gives you strong isolation with minimal operational complexity. Implement it correctly from the start, and you’ll have a data layer that scales from your first customer to your ten-thousandth without a rewrite.