Multi-tenant SaaS on PostgreSQL row-level security

Why tenant isolation matters here

Graphwake maps which teams consume each endpoint of a company’s OpenAPI specs and alerts only the subscribed teams when a breaking change is coming. To do that it holds things a company treats as internal: the list of its services and endpoints, which team calls which API, who owns what, every spec version, the history of API changes and the channels its alerts go to. The visual at the top of this page is an example of that kind of data, a weekly ownership report built from one company’s service map.

A service map describes, fairly completely, how a company’s systems fit together. Showing one row of it to another customer would be a serious failure, whatever the row says. So we did not want isolation to depend on every query in the codebase remembering a filter. It has to hold in the database, including for queries someone writes next year without ever reading this page.

Three ways to split tenants in PostgreSQL

There are three common layouts, and each is a reasonable choice for some products:

Database per tenant gives the strongest separation and the easiest per-customer restore, and it costs the most to run: migrations fan out across every database and a pool per tenant does not scale to thousands of small tenants. Schema per tenant sits in the middle, but its isolation still depends on the application setting the right search_path, which is the same per-request discipline RLS asks for, without a policy to catch the mistake. Shared tables with RLS are the cheapest to run and migrate, and in this layout alone the database itself catches a forgotten filter. In exchange, the policies have to be right on every table, every query pays for an extra predicate, and one large tenant shares indexes with everyone else.

For a product with many tenants of similar size and one schema for all of them, shared tables with RLS are usually the right default. That is what we chose: Graphwake runs on one PostgreSQL database with row-level security per tenant.

How a request reaches only its own rows

The application authenticates the caller and works out which tenant it belongs to. Before the first query, it puts that tenant on the database connection as a setting. Every tenant table has a policy that reads the setting, and PostgreSQL adds the policy to every query against the table. Application code keeps writing ordinary queries, and the database makes sure they only touch the current tenant’s rows.

The shape of a policy

Every table that holds tenant data gets a tenant_id column, an index that starts with it, and a policy. The example below uses a custom setting called app.current_tenant; any name with a dot in it works as a custom setting.

-- Example: a tenant-owned table and its policy
CREATE FUNCTION current_tenant_id() RETURNS uuid
  LANGUAGE sql STABLE
  AS $$ SELECT NULLIF(current_setting('app.current_tenant', true), '')::uuid $$;

CREATE TABLE projects (
  id         uuid PRIMARY KEY,
  tenant_id  uuid NOT NULL REFERENCES tenants (id),
  name       text NOT NULL,
  UNIQUE (tenant_id, id)
);
CREATE INDEX projects_tenant_idx ON projects (tenant_id);

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

CREATE POLICY projects_by_tenant ON projects
  USING      (tenant_id = current_tenant_id())   -- which rows a query can see
  WITH CHECK (tenant_id = current_tenant_id());  -- which rows a write may produce

What each part does:

• USING decides which existing rows a SELECT, UPDATE or DELETE can see. Another tenant’s row does not raise an error. The query just does not see it.
• WITH CHECK decides which rows an INSERT or UPDATE may produce. When it is left out, PostgreSQL uses the USING expression for writes as well. We still write it out: it keeps the write rule visible next to the read rule, you need it anyway once the two differ, and it makes plain that an UPDATE cannot move a row to another tenant by changing its tenant_id.
• FORCE ROW LEVEL SECURITY makes the policy apply to the table’s owner too. By default the owner is exempt, and in many setups the application connects as the role that created the tables. Superusers are never subject to policies, so the application should not connect as one.
• current_setting(…, true) returns NULL instead of raising an error when the setting is missing, and NULLIF(…, '') turns an emptied setting into NULL as well. tenant_id = NULL is never true, so a connection with no tenant sees no rows and cannot write any, which means the policy fails closed.
• Wrapping the lookup in a STABLE function keeps the expression in one place and lets the planner evaluate it once per query, so the comparison can use the tenant_id index.

Foreign keys need one more step. PostgreSQL checks referential integrity without applying the policies, so a plain project_id reference would accept the id of another tenant’s project. Making the parent unique on (tenant_id, id) and referencing both columns keeps every link inside one tenant:

-- Example: a child row cannot point at another tenant's parent
CREATE TABLE tasks (
  id          uuid PRIMARY KEY,
  tenant_id   uuid NOT NULL,
  project_id  uuid NOT NULL,
  title       text NOT NULL,
  FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
);

Carrying the tenant on a pooled connection

The setting lives on a database connection, and connections are pooled. The connection your request uses served another tenant’s request a moment ago and will serve a third one next. So the rule is to set the tenant every time a connection is handed out, before the first query, and never to trust a value a previous borrower left behind. A request with no tenant should set an empty value rather than inherit one.

You can do that in more than one correct way. The example below sets the tenant at the start of each transaction with set_config(name, value, true), the parameter-friendly form of SET LOCAL, which lasts until the transaction commits or rolls back. Setting it whenever the application takes a connection from the pool, before any query, follows the same rule. Either way, the current request always writes the value.

-- Example: what the application sends for one request
BEGIN;
SELECT set_config('app.current_tenant', $1, true);  -- $1 is the tenant id, sent as a bind parameter
                                                     -- true = local to this transaction
SELECT id, name FROM projects ORDER BY name;         -- no tenant filter needed: the policy adds it
UPDATE projects SET name = $2 WHERE id = $3;         -- another tenant's row is simply not found
COMMIT;                                              -- the local setting ends with the transaction

Some practical points:

• Use set_config with a bind parameter. SET LOCAL does not accept parameters, so it tempts people into building the statement with string concatenation, while set_config takes the tenant id as an ordinary parameter.
• Match the setting to its scope. A transaction-local setting only covers the transaction it was set in, so with that form, run tenant work inside a transaction. Whatever the scope, the fail-closed policy above means a query without a tenant returns no rows rather than wrong rows.
• Put it in one place: set the tenant in the data-access layer, from the authenticated request, not in each handler. In .NET that is a connection or transaction interceptor; most ORMs and frameworks have an equivalent hook.

Adding RLS in migrations

Row-level security belongs in the same migration that creates the table. A table that exists for one deploy without its policy can be read across tenants by any query, and "we will add the policy later" is how that happens. A small migration helper that emits the ENABLE, FORCE and CREATE POLICY statements for a table name makes the safe version the easiest one to write, and gives every table the same policy shape.

Bringing an existing table under RLS takes a few more steps: add the column, backfill it from a parent row, make it NOT NULL, index it, then enable and force the policy.

-- Example: bringing an existing table under RLS, in one migration
ALTER TABLE invoices ADD COLUMN tenant_id uuid REFERENCES tenants (id);

UPDATE invoices i SET tenant_id = c.tenant_id
  FROM customers c WHERE c.id = i.customer_id;      -- backfill from the parent row

ALTER TABLE invoices ALTER COLUMN tenant_id SET NOT NULL;
CREATE INDEX invoices_tenant_idx ON invoices (tenant_id);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
CREATE POLICY invoices_by_tenant ON invoices
  USING (tenant_id = current_tenant_id())
  WITH CHECK (tenant_id = current_tenant_id());

Each migration should also have a down step that removes the policy and turns RLS off in the reverse order. On a large table, run the backfill in batches before the migration that adds the constraint, so the ALTER TABLE statements stay short.

Testing isolation

Policies are code. The bugs that hurt are the ones where everything looks fine to the tenant you are testing with, so the tests have to look from the other side. Three kinds of test cover most of it.

A catalog check. One query lists every table that has a tenant_id column but is not under forced row-level security with a policy. Run it in CI after migrations. It catches the new table someone added without its policy.

# Example: fail the build if any table with a tenant_id column is not under forced RLS
out=$(psql "$TEST_DATABASE_URL" -At -v ON_ERROR_STOP=1 <<'SQL'
SELECT c.relname
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE n.nspname = 'public' AND c.relkind = 'r'
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity
           AND EXISTS (SELECT 1 FROM pg_policy p WHERE p.polrelid = c.oid));
SQL
) || exit 1
# Any table name printed here is a table some tenant can read across.
[ -z "$out" ] || { echo "$out"; exit 1; }

A two-tenant test. Seed two tenants with rows in every table. With tenant 1 set, check that each table returns only tenant 1’s rows, that asking for tenant 2’s rows by id returns nothing, that updates aimed at tenant 2 change nothing, and that inserting a row for tenant 2 is rejected. Then check that with no tenant set, nothing is returned.

-- Example: run as the same database role the application uses, with two seeded tenants
BEGIN;
SELECT set_config('app.current_tenant', '11111111-1111-1111-1111-111111111111', true);

SELECT count(*) FROM projects;                                   -- expect tenant 1's rows only
SELECT count(*) FROM projects
  WHERE tenant_id = '22222222-2222-2222-2222-222222222222';      -- expect 0, even when asked for directly
UPDATE projects SET name = 'x'
  WHERE tenant_id = '22222222-2222-2222-2222-222222222222';      -- expect UPDATE 0
INSERT INTO projects (id, tenant_id, name)
  VALUES (gen_random_uuid(), '22222222-2222-2222-2222-222222222222', 'x');
                                                                 -- expect: new row violates row-level security policy
ROLLBACK;

BEGIN;                                                           -- no tenant set at all
SELECT count(*) FROM projects;                                   -- expect 0
ROLLBACK;

A pool test. Run requests for tenant 1 and tenant 2 alternately through a pool with a single connection, and assert that each request only sees its own tenant. This one catches a setting that outlives its request.

Run all of them as the same database role the application uses, not as a superuser. A superuser is not subject to policies, so an isolation test run as one passes whether the policies work or not.

What RLS does not do for you

• It does not choose the tenant. The policy trusts the setting. Resolving the tenant from the authenticated caller, and never from a value the caller can choose freely, is still the application’s job.
• It does not replace your filters. Writing tenant_id into application queries helps the planner and makes the intent obvious. RLS is the backstop for the query that forgot.
• It does not isolate load. One heavy tenant still shares tables, indexes and the server with the others. Watch per-tenant query cost and index on tenant_id first.
• It covers rows, not everything else. Files in object storage, caches and search indexes need their own tenant scoping.

How Graphwake does it

• One database, a policy on every customer table

  Graphwake runs on one PostgreSQL database. Customer data lives in tables that carry the workspace each row belongs to, and a row-level security policy filters each read and checks each write against it.

• The workspace travels with the connection

  The API resolves the workspace from the signed-in user and puts it on the database connection before the first query runs. A connection with no workspace set sees no rows.

• Forced RLS, set in migrations

  Migrations enable and force row-level security on customer tables, with the same policy shape on each one, so the policy applies to every query the application runs.

• Isolation under test

  Tests seed two workspaces and check that each one sees only its own rows, that one cannot write into the other, and that a connection with no workspace sees nothing.

This is how we keep one company’s service map, subscriptions, ownership and API change history out of every other company’s view, while consumer teams get alerts about the breaking changes that affect them.

A checklist

1. Give every tenant-owned table a tenant_id column, an index that starts with it, and composite foreign keys on (tenant_id, id).
2. Enable and force row-level security, with one policy that has both USING and WITH CHECK, and fails closed when no tenant is set.
3. Connect as an ordinary role, never as a superuser.
4. Set the tenant every time a connection is handed out, before the first query, with a bind parameter, in one place in the data-access layer.
5. Add the policy in the migration that adds the table, and run a catalog check in CI that finds any table without one.
6. Test with two tenants, with no tenant, and through a pool of one connection, as the application’s own role.

Questions

Related

• GuideAPI dependency map: how to know who calls what
• GuideAPI breaking change alerts: how to tell the right teams
• How it worksAnatomy of a breaking change: one pull request, three teams, two alerts
• How it worksOne Go binary, five CI systems: inside our open-source CI agent
• GuideAsk your AI assistant who breaks before you change an API