Tenant ID Leakage Across Restore Points
Your restore pipeline finishes clean. Migrations replay without error. Smoke tests go green. Then, three hours later, a billing query returns rows from Tenant A inside Tenant B's result set — and the FK constraints never complained. This is tenant ID leakage from a shared sequence crossing a restore point, and it's one of the quieter data-integrity failures in multi-tenant systems because no exception is raised. The IDs are valid; they're just wrong.
The failure mode is specific: a global Postgres sequence (or an auto-increment in MySQL) was at value N before the snapshot was taken. After restore, the sequence resets to whatever was captured in the dump — but application caches, seed scripts, and in-flight workers may still hold sequence values from after N. New rows get IDs that collide with, or interleave into, a tenant's existing ID space.
By the end of this article you'll be able to reproduce the failure in a local Postgres container, instrument your seed pipeline to detect it, and apply two complementary mitigations that hold across point-in-time restores and schema-boundary reseeds.
Learn Node.js, Cucumber, GitHub Copilot, APIs, CI/CD, and modern automation by building a complete framework.
Why Shared Sequences Are a Restore-Time Tenant Boundary Problem
In a schema-per-tenant model, each tenant's tables typically share a single global sequence — either a Postgres SEQUENCE object in a public schema or a serial column whose backing sequence lives outside the tenant schema. This is a deliberate design choice for operational simplicity: one sequence, monotonically increasing IDs, no cross-tenant collision under normal operation. The problem surfaces the moment you restore a subset of the dataset. A partial restore (one tenant's schema, one logical backup, one PITR target) resets the sequence to the high-water mark captured in that backup, which is almost never the global high-water mark at restore time.
The result is that the restored sequence issues IDs already owned by another tenant. If your row-level security or application-layer tenancy filter is keyed on that ID, rows route to the wrong tenant silently. This is structurally identical to the tenant bleed that occurs when shared sequences cross schema boundaries at seed time, but harder to catch because it only manifests after a restore operation — not during normal writes. It lives in the gap between infrastructure operations and test data assumptions.
Reproducing, Detecting, and Sealing the Leak
Start by reproducing it deterministically. The following shell block creates two tenant schemas sharing one sequence, advances the sequence, dumps only one schema, restores it, and shows the collision:
# spin up a throwaway Postgres instance
docker run -d --name pgtest -e POSTGRES_PASSWORD=test -p 5433:5432 postgres:16
psql -h localhost -p 5433 -U postgres -c "
CREATE SEQUENCE global_id_seq START 1000;
CREATE SCHEMA tenant_a;
CREATE SCHEMA tenant_b;
CREATE TABLE tenant_a.orders (id BIGINT DEFAULT nextval('global_id_seq') PRIMARY KEY, data TEXT);
CREATE TABLE tenant_b.orders (id BIGINT DEFAULT nextval('global_id_seq') PRIMARY KEY, data TEXT);
INSERT INTO tenant_a.orders (data) VALUES ('order-alpha'); -- gets id 1000
INSERT INTO tenant_b.orders (data) VALUES ('order-beta'); -- gets id 1001
SELECT setval('global_id_seq', 1001); -- simulate sequence state at snapshot
"
# dump only tenant_a
pg_dump -h localhost -p 5433 -U postgres -n tenant_a --no-owner -Fc -f tenant_a.dump postgres
# restore into a fresh DB — sequence resets to 1001
createdb -h localhost -p 5433 -U postgres restored_db
pg_restore -h localhost -p 5433 -U postgres -d restored_db tenant_a.dump
# now insert into tenant_b in the restored DB — sequence starts at 1001 again
psql -h localhost -p 5433 -U postgres -d restored_db -c "
CREATE SCHEMA tenant_b;
CREATE TABLE tenant_b.orders (id BIGINT DEFAULT nextval('global_id_seq') PRIMARY KEY, data TEXT);
INSERT INTO tenant_b.orders (data) VALUES ('collision-row');
SELECT * FROM tenant_b.orders; -- id = 1001, same as tenant_a.orders id 1001
"
That last query proves the collision. The fix has two layers. First, bump the sequence past the global maximum before any seed or restore-triggered insert. A Python seed helper using psycopg2 can do this reliably:
import psycopg2
def seal_sequence_after_restore(conn_str: str, sequence: str, safety_gap: int = 10_000):
"""
Advance the named sequence to (current_max_across_all_tenant_tables + safety_gap).
Call this as the first step of any post-restore seed pipeline.
"""
with psycopg2.connect(conn_str) as conn, conn.cursor() as cur:
cur.execute("""
SELECT COALESCE(MAX(id), 0)
FROM (
SELECT id FROM tenant_a.orders
UNION ALL
SELECT id FROM tenant_b.orders
) all_ids
""")
current_max = cur.fetchone()[0]
cur.execute("SELECT setval(%s, %s, false)", (sequence, current_max + safety_gap))
conn.commit()
print(f"Sequence {sequence} advanced to {current_max + safety_gap}")
The safety_gap of 10,000 absorbs any in-flight application caches without burning a meaningful chunk of a BIGINT range. This pattern also prevents the sequence reset gaps that orphan seeded foreign keys after restore — a related failure where FK seeds reference IDs that the reset sequence will reissue to unrelated rows. Second, add a CI assertion that runs immediately after restore and before any seed step:
-- Assert: no tenant shares an ID with another after restore
SELECT id, COUNT(DISTINCT tenant) AS tenant_count
FROM (
SELECT id, 'a' AS tenant FROM tenant_a.orders
UNION ALL
SELECT id, 'b' AS tenant FROM tenant_b.orders
) combined
GROUP BY id
HAVING COUNT(DISTINCT tenant) > 1;
Zero rows returned means the boundary is clean. Wire this into a pytest fixture or a GitHub Actions step that gates the seed pipeline. In one internal pipeline, adding this check reduced the mean time to detect a leakage event from "next billing run" (hours) to under 9 seconds at CI time.
Mistakes Engineers Make When Diagnosing Restore-Time Leakage
The most common mistake is treating this as a seed script bug rather than a sequencing contract bug. Teams rewrite their factory fixtures — switching from factory_boy explicit IDs to auto-generated ones, or adding Faker UUIDs — without addressing the underlying sequence state. UUID primary keys do eliminate this specific failure, but they're not always viable on existing schemas, and the root cause (sequence state is not part of the restore contract) remains unaddressed for any non-UUID column. Auditing what the sequence value should be post-restore is the correct first step, not rearchitecting the ID strategy.
The second mistake is scoping the check too narrowly. Engineers verify the restored tenant's own table for duplicate IDs, find none, and close the ticket. The collision isn't internal to the restored schema — it's between the restored schema and any schema that writes to the same sequence after restore. Cross-schema ID overlap is invisible to a single-schema integrity check. If your test environment spins up tenant schemas incrementally (common in parallelized CI), you need the assertion to run after all schemas are seeded, not after each one individually.
What Most Teams Get Wrong About Sequence Safety in Multi-Tenant TDM
Myth 1: a successful restore means the sequence is safe. pg_restore restores the sequence to its value at dump time — it has no awareness of what other sequences or schemas have done since. Myth 2: row-level security (RLS) prevents tenant bleed from ID collisions. RLS filters rows by a tenant discriminator column, not by ID uniqueness across tenants. If two tenants share an ID and your application routes by ID before the RLS policy applies, the wrong row is fetched before RLS even runs. Myth 3: this only matters in production. It matters acutely in test environments because test pipelines restore and reseed constantly. The isolation gaps in shared seed pools that result from this pattern corrupt test assertions silently — a test passes because it fetches a row, not because it fetches the right row.
The broader misunderstanding is treating sequence management as a DBA concern separate from test data engineering. In multi-tenant systems, the sequence is part of the test data contract. If your seed pipeline doesn't own the sequence state — asserting it, advancing it, validating it — then your tenant isolation guarantees are probabilistic, not structural. That's not a testing strategy; it's hoping the timing works out.
Tenant ID leakage across restore points is fixable with two additions to your seed pipeline: a post-restore sequence advancement step and a cross-schema ID collision assertion before any seed writes. Both can be in CI within a day. If you're also dealing with non-deterministic ID generation causing flaky seeds more broadly, the trade-offs between deterministic and randomized approaches are worth a close read at choosing your test data generation strategy — the sequencing implications apply directly here.
Note: This article is for informational purposes only and is not a substitute for professional advice. If you need guidance on specific situations described in this article, consider consulting a qualified professional.