PostgreSQL Multi-Tenancy: Isolation That Survives a Growing Team

Startups building B2B products reach for multi-tenancy in PostgreSQL the same way on day one: one shared database, one set of tables, and a tenant_id column marking who owns each row. That is the correct call, and it stays correct for a long time. However, when that column is enforced by application code rather than by the database, a single forgotten predicate stops being a bug and becomes a disclosure event, and a disclosure event is one of the very few engineering failures that lands straight on your balance sheet as stalled enterprise deals, an unplanned legal bill, and a security review you can no longer pass. By understanding what multi-tenancy actually guarantees, which isolation model fits your stage, and how Row-Level Security moves that guarantee out of your codebase, startup CTOs and Fractional CTOs can make the tenant boundary hold without slowing the team down.

(If you want to skip the theory, jump straight to why your policy may not be working at all, the connection pooler trap that switches Row-Level Security off in production, what it costs in query performance, what Row-Level Security does not protect, when it is genuinely time to leave the shared schema, or the frequently asked questions.)

Because "enforced by application code" means something very specific in practice. It means a promise that everyone will remember to filter on tenant_id, and that promise is the single most expensive line of undocumented policy in your entire codebase, because it holds perfectly for about fourteen months, right up until the afternoon a tired engineer ships a reporting endpoint that joins four tables and forgets the predicate on exactly one of them, and then a customer opens a dashboard and sees somebody else's invoices.

That is not a bug. A bug is something you fix on Monday. A cross-tenant data leak is a disclosure event, which means legal gets involved, your enterprise prospects get an email from their own security team, and the deal that was supposed to close your Series A quietly moves to next quarter and then to never.

The uncomfortable part is that this is not a story about careless engineers. It is a story about an architecture that requires every engineer to be careful forever, which is not an architecture at all, it's a hope.

What is multi-tenancy in PostgreSQL?

Multi-tenancy in PostgreSQL is the practice of serving multiple customers, called tenants, from a single database system while guaranteeing that no tenant can read or modify another tenant's data. PostgreSQL supports this at three levels of physical separation: a shared schema where a tenant_id column marks ownership of each row, a separate schema per tenant, or a separate database per tenant.

The word doing all the work in that definition is guaranteeing. Any database can store several customers' rows in one table. What distinguishes a real multi-tenant architecture is where the guarantee lives, either in application code that every engineer must remember to write, or in the database itself, where it holds whether or not anyone remembered.

PostgreSQL has shipped a mechanism for the second option since version 9.5, in 2016. It is called Row-Level Security, and most of this post is about using it without stepping on the three mines buried around it.

The three isolation models, and what each one really costs

There are exactly three shapes here, and every vendor blog that tells you otherwise is selling something.

Most startups pick the shared schema correctly and then defend it incorrectly, which is a distinction worth sitting with, because the shared schema really is the right default for almost everybody reading this. You should not be running a schema per tenant at eleven customers, and the founder who spent a quarter building a database-per-tenant provisioning pipeline before finding product-market fit has bought a very good insurance policy on a house he has not finished building.

The mistake is not picking the shared schema. The mistake is enforcing it in application code.

How Row-Level Security actually works

Row-Level Security is a PostgreSQL feature that attaches a boolean policy to a table, which the planner then welds onto every query touching that table, so that rows failing the policy are invisible regardless of what the query asked for. A missing WHERE tenant_id = ... stops being a data leak and starts being an empty result set, which is a category of failure your QA process can actually catch.

Here is the whole thing for one table:

-- The tenant discriminator, plus an index, because every single query
-- in your application is about to filter on this column.
ALTER TABLE invoices ADD COLUMN tenant_id uuid NOT NULL;
CREATE INDEX invoices_tenant_id_idx ON invoices (tenant_id);
 
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
 
-- 👇 This second line is the one everybody skips, and skipping it means
-- everything below does absolutely nothing. More on this in a second.
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

USING governs what rows you can see and modify. WITH CHECK governs what rows you are allowed to write, and leaving it out is how you end up with a system where tenant A cannot read tenant B's invoices but can happily create one on their behalf.

The PostgreSQL documentation on CREATE POLICY is genuinely good and worth twenty minutes if you are going to depend on this.

Why is my RLS policy not working?

Because ENABLE ROW LEVEL SECURITY does not apply to the table's owner. That is documented, it is deliberate, and it is also the reason most first attempts at RLS appear to work in a test and then turn out to have been inert the whole time.

Think about how your application actually connects. You ran your migrations as some role, that role owns the tables, and then your connection string uses the same role because setting up a second one was a Thursday afternoon problem that nobody had time for. Your policies are attached, \d invoices shows them, and they are being bypassed on every single query.

FORCE ROW LEVEL SECURITY closes it. And separately, your application role should not be the table owner in the first place:

-- Migrations run as this one. It owns the tables.
CREATE ROLE app_migrator LOGIN PASSWORD '...';
 
-- Your web servers connect as this one. It owns nothing.
CREATE ROLE app_runtime LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_runtime;
 
-- And make very sure nobody quietly hands it the skeleton key:
--   ALTER ROLE app_runtime BYPASSRLS;   ❌ never

Two roles, ten minutes of work, and the failure mode changes from "silent leak" to "loud permission error at deploy time". That trade is one of the best available anywhere in infrastructure.

To audit what is enforced right now, rather than what you believe is enforced:

SELECT relname, relrowsecurity, relforcerowsecurity
FROM pg_class
WHERE relnamespace = 'public'::regnamespace AND relkind = 'r'
ORDER BY relrowsecurity, relname;

Both columns must be true on every table holding tenant data. The tables at the top of that output are your exposure.

Why does the wrong tenant's data appear behind PgBouncer?

Because SET is session-scoped, and in transaction pooling mode the session outlives your request. This one costs people a genuinely bad week, and it is worth understanding precisely.

The policy reads current_setting('app.tenant_id'), so something has to set it. The obvious thing to write is this:

SET app.tenant_id = '3f2a...';   -- ❌ do not do this

SET stays on that connection until the connection ends or somebody explicitly changes it. On a laptop, where your app holds exactly one connection and serves one request at a time, this works flawlessly and will pass every test you think to write against it.

Then you put PgBouncer or Supavisor in front of it in transaction pooling mode, which you did because you have a hundred workers and Postgres cannot afford a backend process for each of them. In transaction mode, the server connection is handed back to the pool the moment your transaction commits, and it is still carrying tenant A's app.tenant_id when it lands there. The next request that grabs that connection belongs to tenant B, and if that request forgets to set the variable for any reason at all, it does not fail loudly the way you would want it to. It reads tenant A's data, successfully, with RLS fully enabled and working exactly as specified.

You have built a data leak that only exists under concurrency, only in the environment that has a pooler, and that no test in your suite will ever produce, unless your local environment runs the same pooler your production does. Which is one more argument for a reproducible local setup that mirrors production rather than approximating it.

The fix is to scope the variable to the transaction instead of the session:

BEGIN;
  SELECT set_config('app.tenant_id', $1, true);  -- true = local to this txn
  SELECT * FROM invoices WHERE status = 'overdue';
COMMIT;  -- setting is discarded here, connection returns to the pool clean

Two details in that snippet matter more than they look.

set_config(..., true) is the function form of SET LOCAL, and the setting evaporates at COMMIT or ROLLBACK. There is no way for it to survive back into the pool.

More importantly, set_config takes a bind parameter. SET does not. SET only accepts a literal, which means anyone using it has string-interpolated a value into SQL, and if that value ever comes from a JWT claim or a subdomain lookup rather than a trusted internal source, you have built an injection point into the exact mechanism that is supposed to be enforcing your security boundary. I have seen this pattern in code review more than once and it is always written by someone who was being conscientious.

The other half of the fix is refusing to run queries outside a transaction. SET LOCAL outside a transaction block does nothing at all, quietly, with a warning nobody reads. Whatever your data layer is, the tenant-scoped path through it should be physically incapable of issuing a query without opening a transaction first. Make it a wrapper function, not a convention, because conventions are the thing we are trying to get out of the codebase, and a function enforcing a security boundary is precisely the kind of privileged shared module that deserves a CODEOWNERS entry.

Why did my queries get slower after enabling RLS?

Because every query now carries an extra predicate, and most existing indexes were not designed with that predicate in mind. This is the part of RLS that gets the least attention and causes the most regret, because it does not surface until you have enough rows for a sequential scan to hurt.

Every index needs tenant_id as its leading column. Once a policy is attached, there is no such thing as a query on invoices that does not filter by tenant. An index on (status) is now substantially less useful than an index on (tenant_id, status), because the tenant predicate has to be satisfied either way. Go through the existing indexes on your tenant-scoped tables and put tenant_id at the front of the ones that are actually being used.

Keep the policy expression trivial. A policy of the form tenant_id = current_setting('app.tenant_id')::uuid is cheap, because current_setting is STABLE and gets evaluated once per query rather than once per row, and its result can drive an index scan. A policy containing a subquery, for example tenant_id IN (SELECT tenant_id FROM memberships WHERE user_id = ...), is a completely different animal and can turn into per-row work. If your access rules genuinely need that shape, resolve the membership in application code and pass the answer in as a setting.

Understand qual ordering. PostgreSQL evaluates the policy predicate before any predicate it cannot prove safe, which is the correct behaviour and the reason a user-supplied function in your WHERE clause cannot be used to peek at other tenants' rows. The side effect is that the planner has less freedom to reorder your quals for speed than it had before, so a plan you tuned pre-RLS may not be the plan you get post-RLS.

None of this needs to be taken on faith. Set the value and read the plan:

BEGIN;
  SELECT set_config('app.tenant_id', '3f2a...', true);
  EXPLAIN (ANALYZE, BUFFERS)
    SELECT * FROM invoices WHERE status = 'overdue';
COMMIT;

You are looking for an index scan whose Index Cond mentions tenant_id. If you see a sequential scan with the tenant check sitting down in Filter, you are reading the whole table on every request and discarding most of it, and that is the regression that eventually pages somebody at an unreasonable hour.

What does Row-Level Security not protect?

RLS controls which rows you can read. It does not control what your error messages admit to, and that gap is where the subtler leaks live.

Put a global unique index on users(email) in a shared-schema system, then let tenant B try to invite an address that tenant A already has. Postgres raises a unique violation, and although no row was returned and no policy was breached, your API has just confirmed to tenant B that a specific named person exists somewhere else on the platform. For a product where your customers are each other's competitors, that is a real finding, and it is exactly the kind of thing a decent penetration tester turns up on their first afternoon.

Every unique constraint in a multi-tenant schema needs the tenant in it:

-- ❌ leaks existence across the boundary
CREATE UNIQUE INDEX users_email_idx ON users (email);
 
-- ✅ scoped, and doubles as a useful leading-column index
CREATE UNIQUE INDEX users_tenant_email_idx ON users (tenant_id, email);

Same reasoning applies to sequences that hand out human-visible numbers. If tenant B's first invoice is numbered 84,102, you have told them roughly how much business everyone else is doing. Per-tenant numbering is slightly more work and removes an entire class of awkward conversation.

When should you leave the shared schema?

When a specific commercial requirement forces it, and not before. You will read that schema-per-tenant is the natural next step. It is a step, but it is not free, and the bill arrives in a place nobody looks.

Every schema you create multiplies your system catalog, and the catalog is one of the very few things in Postgres that nobody ever thinks to put a dashboard on. Fifty tables across two thousand tenants is a hundred thousand rows in pg_class, plus every column of every one of them in pg_attribute, plus indexes, constraints and defaults. Query planning starts touching a catalog that no longer fits comfortably in cache, pg_dump slows to a crawl because it enumerates the whole thing before writing a byte, and autovacuum starts spending real time on catalog tables. Your migration process, meanwhile, becomes a loop, and a loop that fails partway through leaves you with tenants on two different schema versions at once.

The honest signals that it is time to move are commercial, not technical:

• A customer's security review demands physical separation in writing, and they are large enough that the answer is yes.
• You have genuinely divergent per-tenant schemas, usually because you sold a custom field system to an enterprise buyer.
• One tenant's workload is loud enough to degrade everyone else's, and you need to move them onto their own hardware.
• Data residency, where a customer's rows have to physically live in a specific jurisdiction.

Notice that "we have a lot of tenants now" is not on that list. A shared schema with enforced RLS carries far more customers than most teams expect, and the teams that outgrow it usually outgrow it for one named account rather than in aggregate. The pragmatic shape is a shared schema for the long tail and a dedicated database for the three logos on your homepage, which is also, not coincidentally, exactly how the pricing page is already structured.

Frequently asked questions

Does Row-Level Security slow down PostgreSQL? The policy check itself is cheap when the policy is a simple equality against a STABLE setting. What actually slows things down is index design, because every query now carries a tenant predicate and indexes that do not lead with tenant_id stop being useful. Fix the indexes and the overhead is usually negligible.

Do I still need WHERE tenant_id = ... in my application queries? No, and that is the entire point. Once a policy is attached, Postgres applies it whether or not your query mentions the tenant. Keeping the predicate is harmless and arguably good documentation, but it is no longer the thing protecting you.

Does RLS work with an ORM like Prisma, Drizzle or GORM? Yes, because enforcement happens inside the database and the ORM never sees it. The only requirement is that your ORM lets you run set_config inside the same transaction as your queries, which all of them do. The awkward part is usually the connection layer rather than the ORM itself.

Is a tenant_id column enough for SOC 2 or an enterprise security review? A column on its own is not a control, because nothing enforces it. A column plus database-enforced RLS is a control you can describe, evidence and test, which is what a reviewer is actually asking for. Physical separation is a different and stronger claim, and some buyers will insist on it regardless.

What happens if app.tenant_id is never set? With current_setting('app.tenant_id') and no value set, the query raises an error, which is loud and usually what you want. With the two-argument form current_setting('app.tenant_id', true) it returns NULL, the comparison evaluates to NULL rather than true, and the query returns zero rows. Both fail closed, so pick the loud one unless you have a specific reason not to.

If you want to know what your own exposure looks like right now, it is about a twenty minute exercise. Run the pg_class query above, check whether your runtime role owns any of those tables, grep your data layer for a query path that can reach the database without opening a transaction, and read one EXPLAIN on your busiest tenant-scoped query. The gaps tend to be in the tables added most recently, because RLS on a new table is off by default and nothing anywhere will remind you. That sweep run properly across every table, with the findings written down in the order they would cost you money, is what a technical due diligence actually is.

Anyway, this one turned into more of a security post than a database post, which is usually a sign the topic was worth writing about. Next time I want to get into connection pooling properly, because "just put PgBouncer in front of it" has quietly become the most load-bearing piece of unexamined advice in the startup backend world, and it is the same layer that quietly turns one SSR dashboard render into a thousand identical queries when nobody is watching it.

See you then. 👋

⚡ Find Out What Your Tenant Boundary Actually Enforces

Most multi-tenant Postgres systems are one forgotten predicate away from a disclosure event, and the teams running them usually find out during an enterprise security review rather than on their own terms.

At esence.io, I step in as your Fractional CTO to audit tenant isolation, harden your database boundaries, and get your architecture through procurement without stalling the roadmap.

👉 Schedule a Systems Architecture Audit