Multi-tenant PostgreSQL starts with isolation

Make tenant ownership part of the data model

Every tenant-owned record needs a non-null tenant identifier. Relationships should carry that identifier too. A foreign key from tasks.project_id to projects.id proves that a project exists; a composite foreign key from (tenant_id, project_id) can also require the task and project to belong to the same tenant.

The following schema fragment illustrates that relationship. The paired primary key makes the referenced project columns unique. Add tenant-table foreign keys, workload-specific indexes, and application permissions as part of the wider schema.

CREATE TABLE projects (
  tenant_id uuid NOT NULL,
  id uuid NOT NULL,
  name text NOT NULL,
  PRIMARY KEY (tenant_id, id)
);

CREATE TABLE tasks (
  tenant_id uuid NOT NULL,
  id uuid NOT NULL,
  project_id uuid NOT NULL,
  title text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id)
    REFERENCES projects (tenant_id, id)
);

Reference: PostgreSQL: constraints and foreign keys ↗

Add a database check with row-level security

PostgreSQL row-level security evaluates policies for rows accessed by ordinary queries. USING restricts visible or modifiable existing rows; WITH CHECK restricts inserted or updated values. Apply equivalent policies to every tenant-owned table, not only the parent table.

Run application queries with a dedicated role that lacks superuser and BYPASSRLS privileges. Table owners normally bypass row-level security; FORCE ROW LEVEL SECURITY subjects the owner to it, but does not remove the superuser or BYPASSRLS exceptions. Keep migrations and administration separate from the application role.

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

CREATE POLICY tenant_boundary ON projects
  USING (
    tenant_id = NULLIF(
      current_setting('app.tenant_id', true), ''
    )::uuid
  )
  WITH CHECK (
    tenant_id = NULLIF(
      current_setting('app.tenant_id', true), ''
    )::uuid
  );

Reference: PostgreSQL: row security policies ↗

Scope context to the transaction

Pooled connections are reused. Session-wide tenant state can survive longer than the request that set it. Use an explicit transaction and establish tenant context on the same checked-out connection before executing tenant queries.

PostgreSQL's set_config accepts a third argument that limits a setting to the current transaction when it is true. Parameterize the tenant value. The $1 below represents a bound query parameter supplied by the server after its membership check; it is not text to interpolate into SQL.

BEGIN;

-- Bind the authenticated, authorised tenant ID.
SELECT set_config('app.tenant_id', $1, true);

SELECT id, name
FROM projects
ORDER BY name;

COMMIT;

Reference: PostgreSQL: configuration settings functions ↗

Carry the boundary outside PostgreSQL

Database policies do not scope a Redis cache key, a file path, or a WebSocket room. Design those names around tenant ownership as well. For example, tenant/<tenant-id>/report/<report-id> makes the ownership boundary visible in a storage path; the service still needs to enforce access before issuing a download link.

Background jobs should include enough information to reconstruct a trusted tenant context. Workers must validate that the referenced resource belongs to that tenant. Treat an administrative export as a distinct privileged workflow with its own permissions and audit trail, rather than silently bypassing policies in normal application code.

Keep error responses careful around identifiers. The fact that a resource exists in another tenant can itself be sensitive. Choose consistent not-found or access-denied behavior for your API contract and test it from a tenant that does not own the record.

Test two tenants, then test missing context

Use realistic application-role credentials in integration tests. A test suite connected as a superuser can pass while exercising none of the policies your application depends on. Start with tenants A and B, overlapping record shapes, and a deliberately incorrect tenant selection.

• Tenant A cannot read, update, or delete tenant B's records.
• An A-owned task cannot reference a B-owned project.
• Inserts and updates cannot move a record across the tenant boundary.
• A query without tenant context returns no tenant-owned data or fails closed.
• Reusing a pooled connection does not reuse the previous request's tenant.
• Caches, downloads, worker jobs, and exports follow the same ownership rules.