Atlantis serves several customer sites from one PostgreSQL database. The redirects table, where each site keeps its retired addresses, had two faults at the same time. They are easy to blur into one story, because both involve the same table and the same column, from_path, and both show up as "redirects behaving strangely across sites". They are different faults, with different causes and different fixes.
This article goes through how the isolation is meant to work, from the database policy down to how a connection knows which site it is serving, then through each fault, the fix, and the two queries I would run on any multi-tenant schema before the second customer arrives. If you run a shared-table multi-tenant Postgres application, you probably have at least one of these faults somewhere.
One database, many sites
There are three common ways to keep customers’ data apart in one application:
- A database per customer. Strong separation, but every migration runs once per customer, and a platform with hundreds of customers has hundreds of databases to back up, monitor and upgrade.
- A schema per customer. Similar trade-off, slightly cheaper, and the same migration multiplication.
- One set of tables for everyone, with a column saying which customer each row belongs to. Cheap to run and to migrate, but the separation is only as good as the rule that enforces it.
Atlantis uses the third. Every table that holds one site’s data has a tenant_id column. The risk of this model is obvious: one query that forgets to filter by site returns everyone’s rows. So the filter is applied twice, once by the application and once by the database itself, using row-level security. The database layer is there for the day the application layer has a bug.
The policy, line by line
Row-level security lets a table decide, row by row, what a query may see and write. In Atlantis each tenant table gets the same four statements:
ALTER TABLE "_system_redirects" ADD COLUMN IF NOT EXISTS "tenant_id" TEXT
DEFAULT nullif(current_setting('app.tenant_id', true), '');
ALTER TABLE "_system_redirects" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "_system_redirects" FORCE ROW LEVEL SECURITY;
CREATE POLICY "_system_redirects_tenant_isolation" ON "_system_redirects"
USING (tenant_id = nullif(current_setting('app.tenant_id', true), ''))
WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), ''));Each line earns its place:
- The column’s default fills in the current site on insert, so application code does not have to remember to.
USINGfilters what a query can see.WITH CHECKstops a site writing a row that belongs to another site, including by updating a row’stenant_id.FORCEmakes the policy apply to the owner of the table too. Without it, the owner bypasses the policy.nullif(..., '')turns an empty setting into NULL, andtenant_id = NULLis never true. An unset site therefore matches nothing, rather than matching some imaginary shared "empty" site.
One more rule sits outside the SQL: the database role that serves requests is neither a superuser nor the owner of the tables. A superuser ignores row-level security entirely, whatever FORCE says. So there are three roles: a superuser nobody connects as, an owner that runs migrations, and a plain role that serves requests.
How a connection knows which site it serves
The policy compares against app.tenant_id, a setting on the database connection. Getting the right value onto the right connection is less simple than it sounds, because applications use a pool of connections.
Setting it for the whole session on a pooled connection would leak: the next request to receive that connection would inherit the previous request’s site. SET LOCAL avoids the leak but only lasts for a transaction, and holding a transaction open for a whole request brings its own problems.
Atlantis takes a third route. For the duration of a request, it holds one pooled connection, with the site set on it, and every statement of that request goes through that connection. Before the connection goes back to the pool, the setting is cleared to an empty string, which, thanks to nullif, matches nothing.
Two refinements came from real failures:
- The connection is taken lazily, on the first statement, not at the start of the request. Plugins can run in their own processes, and a request often waits for one of them. Holding a connection through those waits emptied the pool under load, and the plugin then needed a connection to answer, so both sides waited on each other until the deadline. Now a request that touches no table holds nothing, and one that is about to wait gives its connection back and takes a fresh one afterwards.
- The connection that runs migrations is kept strictly separate. A schema change issued on a request’s connection fails, because that role does not own the tables, and that is exactly how new tables once stayed without isolation.
Fault one: no policy, so redirects crossed sites
On one deployment, the redirects table had the tenant_id column but no policy at all.
The deployment had run as a single site first. On a deployment with no sites, the framework removes the isolation policies, which is correct at that moment: with no site set on any connection, the policy would hide every row and the whole site would appear empty. The policy on this table had been created by a migration, and a migration runs once. When sites were added later, nothing put it back.
With the column present and the policy missing, every site read every other site’s redirect rules. A rule a customer created for their own site fired on the others. No error, no warning: from the database’s point of view, nothing was wrong.
The unique index had nothing to do with this fault. It was isolation, or rather its absence.
Fault two: a global UNIQUE constraint blocked the same path twice
The table was created for a single site, with the constraint anyone would write on day one:
"from_path" TEXT NOT NULL UNIQUEOne rule per retired address is exactly right for one site. Shared between sites, the same constraint says something nobody meant: across the whole platform, an address may be redirected once.
So once the table was isolated, a second site that wanted a rule for /pricing could not have one if any other site already did. The insert fails on a row the second site cannot even see, which surfaces as an unexplained error when saving a redirect.
Row-level security does not help here, and cannot. A policy filters which rows a query sees. An index decides what counts as a duplicate, and it sees every row, whatever the policy says. The fix is to put the site into the key, in one statement, so the table is never without a uniqueness rule, not even for a moment:
ALTER TABLE "_system_redirects"
DROP CONSTRAINT "_system_redirects_from_path_key",
ADD CONSTRAINT "_system_redirects_from_path_key" UNIQUE ("from_path", "tenant_id");The same applies to every natural key in a shared table: slugs, paths, codes, e-mail addresses per site, order numbers. If two sites may legitimately hold the same value, the site has to be part of the key.
The trap in between: rows with no owner
There is a third trap next to these two, and it is the mirror image of the first. A row whose tenant_id is NULL matches no site’s policy, because the comparison is strict equality. Such a row is invisible to every site, and nothing refuses it: it just sits there.
Rows like that appear when something inserts outside any site, a background job, a script, an import. They cannot be seen through the application, so nobody notices them, and they are still there when someone later wonders why a count is off.
The fix is to make the owner required:
ALTER TABLE "_system_redirects" ALTER COLUMN "tenant_id" SET NOT NULL;That statement fails if unowned rows already exist, which is useful: it is the only honest way to find out. A SELECT ... WHERE tenant_id IS NULL run as the request role returns nothing, because the policy hides those rows from it, while the ALTER TABLE sees them perfectly well.
Making it stay fixed
Fixing the table was the easy half. The lasting fix is that none of these faults can come back quietly:
- The redirects table is on the framework’s list of tenant-scoped tables. On every start, a sweep goes through every table on that list and, in order, adds the column, rebuilds any unique constraint or unique index that ignores the site, applies the policy, and makes the owner required. A policy lost the way this one was is restored, instead of depending on a migration that already ran.
- The order in that sweep matters. The unowned rows are counted before the policy is forced on, because once it is, even the migration connection can no longer see them.
- Tests assert that the table is on the list, so taking it off is a visible change in review, not an accident.
Two queries to audit your own schema
All of these faults are invisible with one customer, so it is worth looking for them before the second one arrives. The first query lists tables that have a tenant_id column but are not fully isolated:
SELECT c.relname AS table_name,
c.relrowsecurity AS rls_on,
c.relforcerowsecurity AS forced
FROM pg_class c
JOIN pg_attribute a
ON a.attrelid = c.oid AND a.attname = 'tenant_id' AND NOT a.attisdropped
WHERE c.relkind = 'r'
AND c.relnamespace = 'public'::regnamespace
AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity
OR NOT EXISTS (SELECT 1 FROM pg_policies p
WHERE p.schemaname = 'public' AND p.tablename = c.relname));The second lists unique constraints on those tables that ignore the tenant:
SELECT c.conrelid::regclass AS table_name, c.conname,
pg_get_constraintdef(c.oid) AS definition
FROM pg_constraint c
WHERE c.contype = 'u'
AND EXISTS (SELECT 1 FROM pg_attribute a
WHERE a.attrelid = c.conrelid AND a.attname = 'tenant_id')
AND NOT EXISTS (SELECT 1 FROM unnest(c.conkey) k
JOIN pg_attribute a ON a.attrelid = c.conrelid AND a.attnum = k
WHERE a.attname = 'tenant_id');Unique indexes created with CREATE UNIQUE INDEX rather than as constraints need the same check against pg_index.
Not every row either query returns is a bug. On Atlantis, the first query also lists the tables that hold the tenancy itself, such as sessions and site memberships, which are platform-wide on purpose. Each result is a decision to make, and the decision is worth writing down next to the table.
Two tests catch most of this, and both are short:
- Create two sites, and have each create a row with the same natural key: the same slug, path or code. Both inserts must succeed, and each site must read back only its own row.
- As site A, try to read, update and delete site B’s row by its id. All three must find nothing.
Run them against every table that holds site data, as the same database role the application uses. Running them as a superuser proves nothing: a superuser sees past every policy, so the test passes whether the isolation works or not.
Running one codebase for many customers?
Faults like these are invisible with one customer and expensive with two. Tell me what you are moving to multi-tenant.