Build Multi-Tenant SaaS with Row-Level Isolation

npm i pg zod hono @hono/node-server
npm i -D typescript tsx @types/node @types/pg

-- Run once per environment as the table owner (the migration role), never as app_user.
-- Roles: set the passwords from your secret store, e.g. psql -v app_pw="$APP_DB_PASSWORD" -f rls.sql
CREATE ROLE app_user LOGIN PASSWORD :'app_pw' NOSUPERUSER NOBYPASSRLS;      -- the API connects as this
CREATE ROLE report_user LOGIN PASSWORD :'report_pw' NOSUPERUSER BYPASSRLS;  -- cross-tenant reports only

CREATE TABLE tenants  (id uuid PRIMARY KEY DEFAULT gen_random_uuid(), name text NOT NULL);
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, created_at timestamptz NOT NULL DEFAULT now()
);

-- NULL when the setting is missing or empty, so a request without a tenant sees no rows
CREATE FUNCTION app_tenant() RETURNS uuid LANGUAGE sql STABLE AS
  $$ SELECT nullif(current_setting('app.tenant_id', true), '')::uuid $$;

-- Repeat the block for every tenant-owned table
DO $$ DECLARE t text; BEGIN
  FOREACH t IN ARRAY ARRAY['projects'] LOOP
    EXECUTE format('CREATE INDEX ON %I (tenant_id)', t);
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);   -- the owner is bound too
    EXECUTE format($p$CREATE POLICY tenant_isolation ON %I
      USING (tenant_id = (SELECT app_tenant()))
      WITH CHECK (tenant_id = (SELECT app_tenant()))$p$, t);
    EXECUTE format('GRANT SELECT, INSERT, UPDATE, DELETE ON %I TO app_user', t);
  END LOOP;
END $$;
GRANT SELECT ON tenants TO report_user;
GRANT SELECT ON projects TO report_user;

psql "$MIGRATION_DATABASE_URL" -v app_pw="$APP_DB_PASSWORD" -v report_pw="$REPORT_DB_PASSWORD" -f rls.sql

import pg from "pg";
import type { MiddlewareHandler } from "hono";

// Connects as app_user, a role that is subject to row-level security
export const appPool = new pg.Pool({ connectionString: process.env.APP_DATABASE_URL, max: 20 });

export type Env = { Variables: { tenantId: string; db: pg.PoolClient } };

// One transaction per request. set_config(..., true) is transaction-local: it is cleared on
// COMMIT/ROLLBACK, so a pooled connection can never carry a tenant into the next request,
// and it stays safe behind PgBouncer in transaction mode (a session-level SET does not).
export const withTenant: MiddlewareHandler<Env> = async (c, next) => {
  const tenantId = c.get("tenantId"); // from the verified session or JWT, never from a header the client controls
  if (!tenantId) return c.json({ error: "Tenant context required" }, 403);
  const db = await appPool.connect();
  try {
    await db.query("BEGIN");
    await db.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]); // SET cannot take $1
    c.set("db", db);
    await next();
    await db.query(c.error ? "ROLLBACK" : "COMMIT"); // Hono hands handler errors to onError, so check c.error
  } catch (e) {
    await db.query("ROLLBACK").catch(() => {});
    throw e;
  } finally {
    db.release();
  }
};

import { Hono } from "hono";
import { z } from "zod";
import { withTenant, type Env } from "./db";

export const app = new Hono<Env>();
// auth middleware (sets tenantId) goes first, then:
app.use("/projects/*", withTenant);

const CreateProject = z.object({ name: z.string().min(1).max(255), description: z.string().max(2000).optional() });

app.get("/projects", async (c) => {
  // No WHERE tenant_id: the policy adds it
  const { rows } = await c.get("db").query("SELECT id, name, description, created_at FROM projects ORDER BY created_at DESC");
  return c.json({ projects: rows });
});

app.post("/projects", async (c) => {
  const body = CreateProject.safeParse(await c.req.json().catch(() => null));
  if (!body.success) return c.json({ error: body.error.issues }, 400);
  // tenant_id comes from the same setting the policy checks, never from the request body
  const { rows } = await c.get("db").query(
    `INSERT INTO projects (tenant_id, name, description) VALUES (app_tenant(), $1, $2) RETURNING id, name, description`,
    [body.data.name, body.data.description ?? null]);
  return c.json({ project: rows[0] }, 201);
});

app.get("/projects/:id", async (c) => {
  if (!z.uuid().safeParse(c.req.param("id")).success) return c.json({ error: "Project not found" }, 404);
  const { rows } = await c.get("db").query("SELECT id, name, description FROM projects WHERE id = $1", [c.req.param("id")]);
  return rows.length ? c.json({ project: rows[0] }) : c.json({ error: "Project not found" }, 404); // another tenant's id looks the same
});

app.delete("/projects/:id", async (c) => {
  if (!z.uuid().safeParse(c.req.param("id")).success) return c.json({ error: "Project not found" }, 404);
  const r = await c.get("db").query("DELETE FROM projects WHERE id = $1", [c.req.param("id")]);
  return r.rowCount ? c.json({ deleted: true }) : c.json({ error: "Project not found" }, 404);
});

import pg from "pg";
const reports = new pg.Pool({ connectionString: process.env.REPORT_DATABASE_URL });
const { rows } = await reports.query(
  `SELECT t.name, count(p.id) AS projects FROM tenants t
   LEFT JOIN projects p ON p.tenant_id = t.id GROUP BY t.name ORDER BY projects DESC`);