Multi-Tenant SaaS Database Design: Tenant IDs, Isolation and Row-Level Security

Every multi-tenant SaaS has the same nightmare bug, and it’s one line long.

SELECT * FROM invoices WHERE status = 'overdue';

Nothing is wrong with the syntax. It runs fast. The test passes, because the test database only has one customer in it. Then it ships, and a customer opens their “overdue invoices” page and sees invoices from forty other companies.

The missing piece is AND tenant_id = $1. One forgotten condition, and you have a data leak, an apology email, and possibly a legal problem.

A tenant is one customer account in your product: a company, a team, a workspace. Multi-tenant means many tenants share the same running application. Most B2B SaaS is built this way, because running a separate copy of the whole product for every customer is expensive and slow to maintain.

This post covers how to choose an isolation model, why most early products should pick the simplest one, and how to make it safe with layers of defence, ending with Postgres Row-Level Security as the net under everything.

The three common models

1. Database per tenant. Every customer gets their own database. Tenant A’s data and tenant B’s data never share a table, a schema or even a connection string.

2. Schema per tenant. One database, but each customer gets their own Postgres schema (a named folder of tables inside a database). tenant_acme.invoices, tenant_globex.invoices, and so on.

3. Shared tables with a tenant_id column. One database, one set of tables. Every tenant-owned row carries a tenant_id saying who it belongs to. Isolation is done by filtering.

Here’s how they compare. None of them is free.

A noisy neighbour is one tenant whose heavy usage slows down everyone else on the same hardware. A migration is a script that changes your database structure, like adding a column.

Why most early SaaS should start with shared tables

If you’re a small team building a product with tens or hundreds of customers, I think the shared-table model is the right default. Not because it’s the most secure out of the box, but because its weaknesses are fixable and its strengths matter most when you’re small.

• You’ll change the schema a lot. Early products add columns every week. Running a migration once is very different from running it 400 times and handling the 3 that failed.
• You’ll want to see across tenants. “How many customers used feature X this week?” is one query, not a script that loops through databases.
• Costs stay flat. A free-trial tenant who never comes back costs you a few rows, not a database.

Stronger isolation makes sense with a concrete reason: a large customer who contractually requires their own database, data that must stay in one country, or a tenant big enough to need its own hardware. Moving one tenant out later is real work, but you do it when someone is paying for it.

In practice, the more common mistake goes the other way: picking the “enterprise-grade” option on day one, for customers who don’t exist yet. The same instinct that leads teams to start with a modular monolith instead of microservices applies here: pick the simple shape, and keep the boundaries clean so you can split later.

The price of the shared model is that one missing WHERE clause. So the rest of this post is about making that mistake hard to make, and harmless when it happens anyway.

Layer 1: tenant_id on every tenant-owned table

Put tenant_id directly on every table that holds tenant data. Not just the “top” tables. All of them.

It’s tempting to skip it on child tables, since an invoice_lines row belongs to an invoice that already has a tenant_id. But then every query on invoice_lines needs a join to find the owner, and every forgotten join is a leak. Repeating the column is cheap.

CREATE TABLE tenants (
  id         uuid PRIMARY KEY,
  name       text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE invoices (
  id         uuid PRIMARY KEY,
  tenant_id  uuid NOT NULL REFERENCES tenants (id),
  number     text NOT NULL,
  status     text NOT NULL,
  total_cents bigint NOT NULL
);

NOT NULL matters. A row with no tenant is a row nobody owns, and your filters will either hide it forever or, worse, it turns up in whatever code path forgot to filter.

Some tables are genuinely global, like the tenants table itself or a list of currencies. Be suspicious every time someone adds a new one.

Layer 2: indexes and unique constraints that include tenant_id

Almost every query in a multi-tenant app starts with “for this tenant.” Your indexes should match that.

-- Most lists are "this tenant's invoices, newest first, filtered by status"
CREATE INDEX invoices_tenant_status_idx ON invoices (tenant_id, status);

-- Invoice numbers are unique per tenant, not globally
ALTER TABLE invoices
  ADD CONSTRAINT invoices_tenant_number_key UNIQUE (tenant_id, number);

The unique constraint is the one people get wrong. If you write UNIQUE (number), then tenant B can’t create invoice INV-001 because tenant A already has one. Worse, the error message tells tenant B that a number exists somewhere else. Postgres’s own docs warn about this: uniqueness and foreign-key checks bypass row security, so they can reveal that a value exists in rows you can’t see. Scoping uniqueness to the tenant removes most of that problem.

Layer 3: foreign keys that include tenant_id

Here’s a subtler leak. An invoice line points to a product:

-- Before: only checks that the product exists somewhere
product_id uuid NOT NULL REFERENCES products (id)

Postgres checks that the product exists, not that it belongs to the same tenant. If your API accepts a product_id from the request body, tenant A can attach tenant B’s product to an invoice and read its name and price.

The fix is a composite foreign key: a reference made of two columns instead of one.

-- The parent needs a unique key on (tenant_id, id) to be referenced
ALTER TABLE products
  ADD CONSTRAINT products_tenant_id_id_key UNIQUE (tenant_id, id);

CREATE TABLE invoice_lines (
  id         uuid PRIMARY KEY,
  tenant_id  uuid NOT NULL REFERENCES tenants (id),
  invoice_id uuid NOT NULL,
  product_id uuid NOT NULL,
  quantity   int  NOT NULL,
  FOREIGN KEY (tenant_id, invoice_id) REFERENCES invoices (tenant_id, id),
  FOREIGN KEY (tenant_id, product_id) REFERENCES products (tenant_id, id)
);

(invoices needs the same UNIQUE (tenant_id, id) constraint.) Now the database itself refuses a line whose product belongs to a different tenant. A whole class of bugs becomes a constraint error.

Layer 4: a query layer that requires a tenant

The first three layers are about the schema. This one is about your code: it should be impossible to call a repository without saying which tenant you’re acting for. Not “remember to pass it.” Impossible to forget.

Before, the tenant is just another optional filter:

// Before: easy to call without a tenant, easy to forget the WHERE
async function findOverdueInvoices(db: Pool) {
  const { rows } = await db.query(
    "SELECT * FROM invoices WHERE status = 'overdue'",
  );
  return rows;
}

After, the tenant context is a required argument, and the repository builds the filter itself:

type TenantContext = { readonly tenantId: string };

class InvoiceRepository {
  constructor(private readonly db: PoolClient, private readonly ctx: TenantContext) {}

  async findOverdue() {
    const { rows } = await this.db.query(
      "SELECT * FROM invoices WHERE tenant_id = $1 AND status = 'overdue'",
      [this.ctx.tenantId],
    );
    return rows;
  }
}

You can’t construct the repository without a TenantContext, and you get the context from one place: your authentication middleware, after it has checked who the user is and which tenant they belong to. Never take tenantId from the request body or a query string.

This fits naturally with the layering in Clean Architecture in Practice: the use case receives a tenant context, the repository interface requires it, and the business logic never builds SQL by hand.

It’s a strong layer, but it’s still code written by humans. Someday someone will write a raw query for a report, in a hurry. That’s what the last layer is for.

Layer 5: Postgres Row-Level Security as the safety net

Row-Level Security (RLS) is a Postgres feature that lets you attach rules, called policies, to a table. Postgres then adds those rules to every query on that table automatically. Even SELECT * FROM invoices with no WHERE clause only returns the rows the policy allows.

The trick for multi-tenancy is to make the policy compare each row’s tenant_id with a setting that your app sets at the start of every transaction.

Turning it on

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

A few details worth understanding:

• app.tenant_id is a custom setting. Postgres accepts any two-part dotted name as a placeholder, so you don’t need to define it first.
• If the tenant was never set, the query errors. current_setting throws when the setting doesn’t exist. On a pooled connection where it was set in an earlier transaction, it typically comes back as an empty string instead, and the ::uuid cast fails. Either way, forgetting the tenant fails loudly. That’s what you want, so don’t “fix” it with missing_ok.
• With no FOR clause, the policy covers all commands, and USING doubles as WITH CHECK. USING filters the rows you can see, update or delete. WITH CHECK limits the rows you can write. So a SELECT silently skips other tenants’ rows, and an INSERT with someone else’s tenant_id fails.
• RLS with no policy means default deny. No rows are visible. Good to know when rolling out table by table.

Setting the tenant per transaction

You set the value inside a transaction, with SET LOCAL or with set_config(..., true):

BEGIN;
SET LOCAL app.tenant_id = '6f1c2b9e-...';
-- or, the same thing, but usable with a bind parameter:
SELECT set_config('app.tenant_id', '6f1c2b9e-...', true);

SELECT * FROM invoices;   -- only this tenant's rows
COMMIT;                   -- the setting disappears here

The third argument to set_config is is_local. When it’s true, the value only lasts until the end of the current transaction, committed or not, exactly like SET LOCAL. Outside a transaction block, SET LOCAL just emits a warning and does nothing.

In application code I prefer set_config, because it’s an ordinary function and accepts a bind parameter like $1. SET doesn’t, so you’d end up building SQL strings.

The gotchas

1. Table owners bypass RLS by default. From the Postgres docs: the table’s owner is normally not subject to row security policies. If your app connects as the role that ran the migrations, which is common in small projects, your policies do nothing. Two fixes, and I’d do both:

• add FORCE ROW LEVEL SECURITY on the table, which makes the owner subject to the policies too;
• better, have your app connect as a separate role that doesn’t own the tables, and keep the owner role for migrations.

2. Superusers and BYPASSRLS roles always bypass it. No policy and no FORCE changes that. If your app connects as postgres, RLS is decoration. Check what role your production app actually uses.

3. Connection pooling. A pool reuses each database connection for many requests. A session-level SET app.tenant_id = 'A' would stay on the connection after your request, and the next request to borrow it, maybe for tenant B, could run as tenant A.

With set_config(..., true), the value dies with the transaction, and the connection goes back clean. It also fits external poolers: PgBouncer in transaction pooling mode only lends a server connection for one transaction, and its docs list SET as unsupported in that mode.

A withTenant helper in TypeScript

Here’s the helper that ties it together, using node-postgres (the pg package). Its docs are clear that all statements in a transaction must run on the same client, so you check one out of the pool rather than using pool.query.

import { Pool, type PoolClient } from "pg";

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

export async function withTenant<T>(
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>,
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    await client.query("SELECT set_config('app.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();
  }
}

A request handler takes tenantId from the auth middleware and calls withTenant(tenantId, (client) => new InvoiceRepository(client, { tenantId }).findOverdue()). The repository still filters by tenant_id itself. RLS is the net, not the floor, and your code stays correct even if RLS is accidentally left off somewhere.

Tests that try to break in

Most test suites have one tenant in the database, so they can’t catch the bug this post is about. Add tests that try to cross the line:

• Create two tenants with similar data, then read as A and assert you never see B, on every list endpoint.
• Try to write across tenants. Use tenant B’s product_id in tenant A’s invoice request. Expect a 404 or a constraint error, never a success.
• Test RLS directly. As the app role, set the tenant to A, run SELECT * FROM invoices with no filter, and assert only A’s rows return. With no tenant set, assert it fails.
• Check the role. Assert the app’s role isn’t a superuser, lacks BYPASSRLS and doesn’t own the tables. Boring, cheap, and it protects the whole safety net.

Background jobs must carry tenant_id

HTTP requests get their tenant from authentication. Background jobs don’t have a logged-in user, so they need the tenant passed explicitly.

type SendInvoiceReminderJob = {
  tenantId: string;
  invoiceId: string;
};

async function handleSendInvoiceReminder(job: SendInvoiceReminderJob) {
  await withTenant(job.tenantId, async (client) => {
    const repo = new InvoiceRepository(client, { tenantId: job.tenantId });
    // ...load the invoice, send the reminder
  });
}

Every job payload that touches tenant data includes tenantId, and the handler uses withTenant like a request does. Don’t let a worker load a row by ID alone and “figure out” the tenant from it. That’s the missing-WHERE bug in a different hat.

Jobs that span tenants, like a nightly billing run, should loop over tenants and process each inside its own withTenant call. I go deeper into queues, retries and the outbox pattern in Background Jobs Done Right.

Admin and support access

Sooner or later, someone on your team needs to look at a customer’s data to help them. How you allow that matters as much as how you block it.

• Don’t give your support tool a superuser connection. That skips every layer at once.
• Make support access a tenant context too. An agent “enters” one tenant, and the tool calls withTenant with that ID, so the same policies apply.
• Log it. Record who accessed which tenant, when, and why (a ticket number).
• Keep truly cross-tenant work separate. Analytics, billing and migrations can use a wider role on a separate connection, ideally against a read replica. Keep that role out of your web app’s configuration.

Per-tenant export and deletion

Every SaaS eventually hears “give us all our data” and “delete all our data.” Because every tenant-owned table has a tenant_id, an export is a loop over those tables with WHERE tenant_id = $1. Keep a list of tenant-owned tables in code (or derive it from the schema) so new tables don’t get forgotten.

Deletion is the same loop, in reverse dependency order, or a set of ON DELETE CASCADE foreign keys hanging off tenants. Don’t forget data outside Postgres: stored files, search indexes, logs and backups. Write down what you delete immediately and what ages out with backup retention.

Per-tenant restore is this model’s honest weak spot. You can’t roll back the whole database for one tenant. Plan for it with soft deletes on important tables, or a tested script that pulls one tenant’s rows out of a backup restored elsewhere.

Checklist

If you’re building on shared tables, here’s the short version:

• tenant_id uuid NOT NULL on every tenant-owned table.
• Indexes, unique constraints and foreign keys include tenant_id.
• Repositories require a tenant context that comes from authentication.
• RLS enabled and forced, with a policy on current_setting('app.tenant_id').
• App role: not a superuser, no BYPASSRLS, not the table owner.
• Tenant set with set_config(..., true) per transaction, never session-level SET.
• Tests with two tenants that try to cross over.
• Jobs carry tenantId; support access is a logged tenant context.
• A known way to export, delete and restore one tenant.

Each layer will fail someday. The point is that they won’t all fail on the same day.

Sources: PostgreSQL documentation on Row Security Policies, CREATE POLICY, SET, configuration settings functions and customized options; node-postgres transactions; PgBouncer features.

Related: Background Jobs Done Right: Queues, Retries and the Outbox Pattern, Start With a Modular Monolith, Not Microservices, and Clean Architecture in Practice.