Skip to content
Tutorials Center
My learning

Topics

Lesson 87 of 90 ยท Projects

Project: Multi-Tenant SaaS Schema

Design a multi-tenant SaaS schema in PostgreSQL: tenant_id columns, composite foreign keys, row-level security policies, per-tenant reports and onboarding.

A SaaS product serves many customers, called tenants, from one running system. Each tenant must see only its own data, one noisy tenant must not ruin things for the others, and adding the thousandth customer must be a few rows of inserts, not a deployment. How you lay that out in PostgreSQL decides how safe, fast and maintainable the product is for years.

In this project you design a shared-schema, tenant_id-everywhere model for a small project-management product. You will make cross-tenant references impossible with composite keys, add row-level security as a safety net, write per-tenant usage reports, and script tenant onboarding. It brings together Constraints in Depth, Row-Level Security and Roles, Privileges and GRANT.

What runs live. SQLite has no row-level security, no roles and no SET LOCAL, so the RLS policies are read-only PostgreSQL blocks. Everything else runs live: the schema with composite foreign keys, a view-based stand-in that shows how a "current tenant" filter behaves, per-tenant reports and the onboarding transaction.

Three ways to separate tenants

Where does a tenant live?

Shared tables One schema. Every row has a tenant_id.
Schema per tenant Same tables copied into one schema each.
Database per tenant Full isolation, heavy to operate.
1 / 2

Schema-per-tenant gives easy per-tenant backup and customization, but with thousands of tenants every migration runs thousands of times and the system catalogs grow large. Database-per-tenant gives the strongest isolation and is the right choice for a few huge or regulated customers, but connection management and operations get heavy. Shared tables with a tenant_id column is the default for products with many small and medium tenants, and it is what this project builds.

The core schema

Four rules shape the design:

  1. Every tenant-owned table has tenant_id NOT NULL.
  2. Parents expose UNIQUE (tenant_id, id), and children reference that pair, so a child row can never point at a parent of another tenant.
  3. Natural uniqueness is per tenant: two tenants may both have a user [email protected] or a project named "Website".
  4. tenant_id is the first column of the important indexes, because nearly every query filters by it.

In PostgreSQL:

SQL
-- PostgreSQL (read-only)
CREATE TABLE tenants (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  slug       text        NOT NULL UNIQUE,
  name       text        NOT NULL,
  plan       text        NOT NULL DEFAULT 'free' CHECK (plan IN ('free', 'pro', 'enterprise')),
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE users (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL REFERENCES tenants (id) ON DELETE CASCADE,
  email     text   NOT NULL,
  role      text   NOT NULL DEFAULT 'member' CHECK (role IN ('owner', 'admin', 'member')),
  UNIQUE (tenant_id, email),
  UNIQUE (tenant_id, id)
);

CREATE TABLE projects (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL REFERENCES tenants (id) ON DELETE CASCADE,
  name      text   NOT NULL,
  UNIQUE (tenant_id, name),
  UNIQUE (tenant_id, id)
);

CREATE TABLE tasks (
  id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id   bigint  NOT NULL,
  project_id  bigint  NOT NULL,
  assignee_id bigint,
  title       text    NOT NULL,
  done        boolean NOT NULL DEFAULT false,
  FOREIGN KEY (tenant_id, project_id)  REFERENCES projects (tenant_id, id) ON DELETE CASCADE,
  FOREIGN KEY (tenant_id, assignee_id) REFERENCES users (tenant_id, id)
);

CREATE INDEX tasks_tenant_project_idx ON tasks (tenant_id, project_id, done);

The primary key id stays globally unique (handy for logs and URLs), while the extra UNIQUE (tenant_id, id) exists purely so composite foreign keys have something to point at. Notice that tasks has foreign keys to pairs. A task of tenant 1 simply cannot reference a project or assignee of tenant 2. (With MATCH SIMPLE, the default, a foreign key is skipped when any of its referencing columns is NULL, so assignee_id can stay empty; the project key still checks tenant_id on every task.)

The next example contrasts the two designs. tasks_naive references projects(id) only; tasks_safe uses the composite key. Both receive the same wrong row: a task of tenant 1 inside the project of tenant 2. The naive table accepts it silently and now holds corrupted cross-tenant data, which PRAGMA foreign_key_check cannot see. The safe table has a foreign key violation that the same pragma lists (with enforcement turned on, the insert itself would be refused):

SQL Composite foreign keys stop cross-tenant references

Press Run (or Ctrl + Enter) to see the result here.

The first query shows the naive table's bad row, with task tenant 1 and project tenant 2. The pragma then lists one violation, in tasks_safe. In a real application foreign keys are enforced (PostgreSQL always does), so the safe design would refuse the insert with a foreign key error, and the naive design would store the bad row. That one extra column is the cheapest tenant-isolation bug fix you will ever make.

Row-level security: the safety net

Composite keys protect writes between tables. They do not stop a buggy query like SELECT * FROM tasks WHERE id = 5 from returning another tenant's task, because someone forgot AND tenant_id = .... Row-level security (RLS) moves that filter into the database, so every query on the table is filtered automatically, including the forgotten ones:

SQL
-- PostgreSQL (read-only)
CREATE ROLE app_user LOGIN PASSWORD 'change-me' NOSUPERUSER NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON users, projects, tasks TO app_user;
GRANT SELECT ON tenants TO app_user;

ALTER TABLE tasks    ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks    FORCE ROW LEVEL SECURITY;      -- also applies to the table owner
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON tasks
  USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint);

CREATE POLICY tenant_isolation ON projects
  USING      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint);

The USING expression decides which existing rows a statement can see, update or delete. The WITH CHECK expression decides which new or modified rows may be written, so a tenant-1 session cannot insert a row labelled tenant 2. The application sets the tenant at the start of each request, inside the transaction:

SQL
-- PostgreSQL (read-only)
BEGIN;
SELECT set_config('app.tenant_id', '1', true);      -- true = local to this transaction
SELECT * FROM tasks WHERE id = 5;                   -- only returns the row if tenant_id = 1
INSERT INTO tasks (tenant_id, project_id, title) VALUES (2, 20, 'sneaky');
-- ERROR:  new row violates row-level security policy for table "tasks"
COMMIT;

Several details decide whether RLS is actually safe:

  • The role must not bypass it. Superusers, roles with BYPASSRLS, and (without FORCE) the table owner skip all policies. Run the application as a plain role like app_user, and keep migrations and admin work on a different role.
  • A missing tenant must mean no rows. With current_setting(..., true) an unset parameter yields NULL, and tenant_id = NULL is never true, so the session sees nothing. The nullif(..., '') matters because after a transaction-local setting ends, PostgreSQL keeps the parameter as an empty string for the rest of the session, and casting '' to bigint raises an error instead of returning no rows.
  • Use SET LOCAL or set_config(..., true), never plain SET, with a connection pool. A session-level SET survives after the connection returns to the pool and leaks one tenant's identity into the next request. See Connection Pooling.
  • Views run with the owner's rights by default and can bypass RLS. Create them with WITH (security_invoker = true) on PostgreSQL 15 and later.
  • RLS is a safety net, not a replacement for WHERE tenant_id = .... Keep the explicit filter in your queries: it lets the planner use the tenant_id indexes with certainty and keeps behavior obvious.

SQLite has no policies, but a view filtered by a "current session" row shows the same behavior. The app picks a tenant by updating one row, the view returns only that tenant's tasks, and an unset tenant gives zero rows. An INSTEAD OF INSERT trigger plays the part of WITH CHECK: the tenant on new rows always comes from the session, never from the caller:

SQL A tenant-filtered view as a stand-in for RLS

Press Run (or Ctrl + Enter) to see the result here.

With no tenant set, nothing is visible. Tenant 1 sees three tasks (its two originals plus the one it just added through the view), tenant 2 sees its three tasks, and the underlying table holds three rows per tenant. In PostgreSQL the planner can combine the policy condition with your own WHERE clause and indexes, so the filter costs little.

Per-tenant reporting

Operators need numbers across tenants: who uses the product, who is near a plan limit, who dominates the load. These queries run as an admin role that is allowed to bypass the policies (or in a separate reporting database), and they join through the composite keys so numbers cannot leak between tenants. The example joins each tenant to its plan limits and computes projects, tasks, open tasks and each tenant's share of all tasks:

SQL Usage per tenant with plan limits

Press Run (or Ctrl + Enter) to see the result here.

Acme holds 60 percent of the tasks with two projects (allowed on pro). Globex has two projects on the free plan, which allows one, so it is flagged over limit. The join to tasks uses both tenant_id and project_id, mirroring the composite foreign key. In PostgreSQL the same report is the basis for billing and for finding noisy neighbours. Sustained heavy tenants can later be moved to their own database, or the big tables can be hash-partitioned by tenant_id (see Declarative Partitioning).

Onboarding a tenant

Creating a tenant is a small, all-or-nothing script: the tenant row, its owner, and a starter project. Wrap it in a transaction so a half-created tenant cannot exist. In PostgreSQL a chain of CTEs with RETURNING passes the new IDs along in one statement:

SQL
-- PostgreSQL (read-only)
WITH new_tenant AS (
  INSERT INTO tenants (slug, name, plan) VALUES ('initech', 'Initech', 'free')
  RETURNING id
), owner AS (
  INSERT INTO users (tenant_id, email, role)
  SELECT id, '[email protected]', 'owner' FROM new_tenant
  RETURNING tenant_id, id
)
INSERT INTO projects (tenant_id, name)
SELECT tenant_id, 'Getting started' FROM owner;

Because RLS applies to the application role, onboarding usually runs through a SECURITY DEFINER function or a separate provisioning role. The live example does the same with plain statements in one transaction. It onboards two tenants with the same owner email, which is allowed because UNIQUE (tenant_id, email) is per tenant, and shows that each tenant ends up with exactly its own starter project:

SQL Onboarding tenants in one transaction

Press Run (or Ctrl + Enter) to see the result here.

Both tenants own a [email protected] owner account and a "Getting started" project, and neither collides with the other. If your product lets one person belong to several tenants, move users into a global users table and add a memberships (tenant_id, user_id, role) table. The same composite-key rules then apply to memberships.

Offboarding, migrations and growth

  • Offboarding is DELETE FROM tenants WHERE id = :id when every child table has ON DELETE CASCADE. For large tenants, delete in batches or move the data out first. Export a tenant's data with COPY (SELECT ...) TO per table filtered by tenant_id (see Bulk Loading and COPY).
  • Migrations run once for all tenants, which is the strength of the shared model. Test them against a database with one very large tenant, because that tenant's data decides the lock and rewrite time (see Schema Changes and Migrations).
  • Noisy neighbours: set limits per role with statement_timeout and ALTER ROLE ... CONNECTION LIMIT, add per-tenant rate limits in the application, and watch the biggest tenants with pg_stat_statements.
  • Skewed data: tenant_id statistics can fool the planner when one tenant owns most rows. If plans are bad for small tenants, extended statistics or a dedicated database for the giant tenant often fixes it.
  • Tenant-aware keys in URLs: never let a user-supplied ID alone identify a record. Always combine it with the authenticated tenant.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why do parent tables declare UNIQUE (tenant_id, id) even though id is already the primary key?

    A foreign key needs a matching unique constraint on the referenced columns. Referencing the pair makes it impossible for a child row to belong to a different tenant than its parent.
  2. 2. A session never sets app.tenant_id. With the policy tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint, what does it see?

    An unset or empty setting becomes NULL, and a comparison with NULL is never true, so the policy hides everything. This fails closed, which is what you want.
  3. 3. Why use set_config('app.tenant_id', '1', true) (or SET LOCAL) rather than a plain SET when using a connection pool?

    The true argument scopes the setting to the current transaction, so it disappears at COMMIT or ROLLBACK. A session-level SET stays on the connection when it returns to the pool.
  4. 4. Which role can read other tenants' rows despite RLS policies?

    Superusers and roles with the BYPASSRLS attribute skip policies. The table owner does too unless FORCE ROW LEVEL SECURITY is set. The application should connect with a plain role.
  5. 5. In the usage report, why is tenant_id part of the tasks join condition?

    Joining on both columns follows the key design and makes accidental cross-tenant matches impossible even if some bad data existed.

Summary

  • Shared tables with tenant_id NOT NULL on every tenant-owned table scale best; schema- or database-per-tenant trades operations cost for isolation.
  • Parents expose UNIQUE (tenant_id, id) and children reference the pair, so cross-tenant references are impossible.
  • Make natural keys unique per tenant (UNIQUE (tenant_id, email)) and start important indexes with tenant_id.
  • Add RLS with USING and WITH CHECK policies as a safety net, a tenant set by set_config(..., true) per transaction, and an application role without BYPASSRLS; unset tenant must mean no rows.
  • Report across tenants with an admin role, and keep join conditions tenant-aware.
  • Onboard in one transaction, offboard with cascading deletes, and move giant tenants out only when the numbers say so.

Next: PostgreSQL Exercises with Solutions.