Multi-tenant SaaS on PostgreSQL row-level security

Row-level security (RLS) is a PostgreSQL feature that filters the rows a query can see and write through a policy attached to the table. In a multi-tenant SaaS with one shared database, every tenant-owned table carries a tenant_id column and a policy that compares it with a tenant setting on the connection. A query that forgets its WHERE tenant_id = … then returns nothing instead of another customer's data.

Getting it right comes down to four habits. Force the policies and give each one both USING and WITH CHECK. Set the tenant every time a connection is handed out, before the first query. Add the policy in the same migration as the table. Write tests that try to read across tenants.

All the code below is generic example code with made-up names (app.current_tenant, projects, tasks), written for this post.

Why we care about this

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.

Example: 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 didn't 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 post.

Three ways to split tenants in PostgreSQL

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

	Shared tables + RLS	Schema per tenant	Database per tenant
Where the boundary lives	A policy on every tenant table	search_path set per request	The connection string
Migrations	Run once	Run once per tenant schema	Run once per database
Connection pooling	One pool for everyone	One pool, but the path is per request	A pool per tenant, or reconnects
Cost of a new tenant	One row in tenants	A schema and its tables; the catalog grows with every tenant	A database; the most operational work
Restore or export one tenant	Harder: filter by tenant_id	pg_dump --schema	Easiest
One heavy tenant slows the others	Yes, they share tables and indexes	Yes, they share the server	Less, if databases are on separate servers
A query with a missing filter	The policy still filters it	Correct only if the path was set right	Correct only if the right connection was used

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 doesn't 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. That's 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's 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 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

Going through it piece by piece:

USING decides which existing rows a SELECT, UPDATE or DELETE can see. Another tenant's row doesn't raise an error. The query just doesn't see it.

WITH CHECK decides which rows an INSERT or UPDATE may produce. When it's 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 can't 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 shouldn't 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 can't write any. 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: set the tenant every time a connection is handed out, before the first query, and never trust a value a previous borrower left behind. A request with no tenant should set an empty value rather than inherit one.

There's more than one correct way to do that. 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

A few practical points:

• Use set_config with a bind parameter. SET LOCAL doesn't 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's 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'll 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're testing with, so the tests have to look from the other side. Three kinds of test cover most of it.

The first is a catalog check: one query that lists every table with a tenant_id column that isn't under forced row-level security with a policy. Run it in CI after migrations and 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; }

The second is 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 comes back.

-- 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;

The third is 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 isn't subject to policies, so an isolation test run as one passes whether the policies work or not.

What RLS doesn't do for you

• It doesn't 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 doesn't 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 doesn't 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 and nothing else. Files in object storage, caches and search indexes need their own tenant scoping.

On performance: a simple policy adds one predicate to each query, much like the WHERE tenant_id = … you'd write by hand. With an index that starts with tenant_id and a STABLE function or a direct current_setting call in the policy, the planner can use the index and the overhead is small. Check the plans of your heaviest queries with EXPLAIN after enabling it.

How we do it in Graphwake

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 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.

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. Tests seed two workspaces and check that each one sees only its own rows, that one can't write into the other, and that a connection with no workspace sees nothing.

That's how one company's service map, subscriptions, ownership and API change history stay out of every other company's view, while its 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.

---

Originally published on graphwake.com.

Which layout did you go with for tenant isolation, and would you pick it again?