Multi-Tenant Data Isolation: Shared Schema, RLS, or Separate DBs

Why tenant data leaks in the first place

Cross-tenant leaks rarely come from exotic attacks. They come from ordinary code: a new report endpoint, a background job or an admin export where someone wrote SELECT * FROM invoices WHERE status = 'open' and forgot AND tenant_id = $1. The query works, the tests pass because the test database has one tenant, and the first person to notice is a customer looking at another customer's records.

In a shared-schema design, isolation is entirely an application-code responsibility unless it is paired with database-level row-level security. That means your isolation guarantee is only as strong as the least careful query in the codebase, written by any developer, on any day, including the ones that come after launch.

So the real design question is where isolation gets enforced. There are three workable models, and a fourth option that sits on top of the cheapest one.

What are the three isolation models, really?

Shared schema with a tenant_id column. Every tenant's rows live in the same tables, tagged by a tenant identifier. It is the cheapest to run and the easiest to scale to thousands of small tenants.

Schema per tenant. One database, but each tenant gets its own set of tables in its own schema. Isolation is stronger and you still operate one database, but migrations must run against every tenant's schema, which is manageable at dozens of tenants and painful past a few hundred. There is also a ceiling: scalability is limited by the number of tables the database can handle, with significant metadata overhead at very large table counts.

Database per tenant. Each tenant gets its own database. This is the model used by enterprise SaaS products with strict compliance requirements such as HIPAA, SOC 2 Type II and FedRAMP, where each tenant's data is physically separate. The price is relatively extensive hardware and maintenance requirements, high costs, and poor scaling to thousands of tenants.

Bytebase's guidance matches my own experience: start with shared database, shared schema whenever possible, move to database per tenant only when compliance, scale or customization demands it, and avoid separate schemas because they combine the drawbacks of both models.

The costs that surprise small operators with database-per-tenant are not only storage. Every database needs its own connection pool, backups, restore tests, monitoring, and a migration run that can fail halfway across your fleet. At 20 tenants that is a script. At 400 it is a part-time job.

How does row-level security stop a forgotten WHERE clause?

PostgreSQL RLS, introduced in version 9.5, attaches security policies to a table so every SELECT, INSERT, UPDATE and DELETE is filtered automatically; rows that fail the policy behave as if they don't exist. The forgotten AND tenant_id = $1 stops mattering, because the database adds the equivalent predicate itself.

The policy

The pattern practitioners recommend is a NOT NULL tenant_id with a foreign key to a tenants table on every tenant-owned table, RLS enabled, and a policy comparing tenant_id to a session-local setting set in the per-request handler before any tenant SQL runs.

CREATE TABLE tenants (
  id   uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE invoices (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id    uuid NOT NULL REFERENCES tenants(id),
  amount_cents bigint NOT NULL,
  status       text NOT NULL
);
CREATE INDEX ON invoices (tenant_id, status);

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

CREATE POLICY tenant_isolation ON invoices
  USING      (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

The NULLIF(..., '') detail matters. If the setting was never set, or was reset to an empty string, the comparison becomes NULL and the policy returns zero rows instead of throwing a cast error or matching something unintended. The policy fails closed.

Setting the tenant per request

BEGIN;
SELECT set_config('app.tenant_id', $1, true);  -- true = local to this transaction
-- tenant queries here
COMMIT;

Scope the setting to the transaction, not the session. With a transaction-mode connection pooler, a session-level setting can survive on a pooled connection and be picked up by the next request, which is the exact leak you are trying to prevent. Derive $1 from the authenticated session on the server, never from a request parameter.

The gotchas that defeat it

• Owners and superusers skip policies. Table owners and superusers bypass RLS unless FORCE ROW LEVEL SECURITY is enabled. Better still, have the application connect as a dedicated role that owns nothing, is not a superuser and lacks BYPASSRLS. Run migrations under a separate role.
• Missing WITH CHECK. Without it, a user can update a row's owner to someone else and the row silently leaves their visibility, a hard-to-debug bug. In multi-tenant terms, that is a row moved into another client's data.
• The new table nobody protected. Forgetting to enable RLS on a newly added table with a tenant_id column is a common failure; one missed table can cause a breach, so add a CI check.
• Views and functions. Views run with their owner's privileges by default unless created with security_invoker, and SECURITY DEFINER functions run as their definer. Either one can quietly route around your policies.

A CI check can be a single query that fails the build if it returns rows:

SELECT n.nspname, 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 c.relkind IN ('r', 'p')
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
  AND (NOT c.relrowsecurity OR NOT c.relforcerowsecurity);

Keep the WHERE tenant_id = $1 in application queries anyway. It helps the planner use your composite indexes and makes intent obvious to reviewers. RLS is the backstop, not a license to stop writing the filter.

Which model fits your compliance and cost constraints?

Start with what the stakes are. IBM's 2025 Cost of a Data Breach Report put the average U.S. breach at a record $10.22 million. Healthcare topped the industry list at $7.42 million, and customer PII was the most frequently compromised data type, involved in 53 percent of breaches. A client portal holding names, addresses and account numbers is holding exactly that.

Louisiana adds a legal floor. La. R.S. 51:3074(A) requires entities doing business in Louisiana or holding computerized personal information to maintain "reasonable security procedures and practices appropriate to the nature of the information". The breach notification law, effective January 1, 2006, requires notifying Louisiana residents whose unencrypted personal information was, or is reasonably believed to have been, acquired by an unauthorized person. Notice is due within 60 days of discovery, and violations are treated as unfair trade practices, with penalties up to $5,000 per violation when committed with intent to defraud or against an elderly or disabled person. Late notice to the Attorney General can draw up to $5,000 per violation, with each day counting separately.

Note what that means for a tenant leak: your application decrypts data before returning it, so encryption at rest does nothing when the wrong client is served the right rows. And when you have to explain your "reasonable security procedures" to an auditor or to management, "every developer remembers the WHERE clause" is a weak answer. "The database refuses to return rows outside the caller's tenant, and CI blocks unprotected tables" is a strong one.

My decision rule:

1. Default: shared schema, tenant_id everywhere, RLS with FORCE and WITH CHECK, CI check, dedicated non-owner app role.
2. Move a tenant to its own database when a specific contract names HIPAA, SOC 2 Type II, FedRAMP or physical separation, or when one tenant's load or customization needs no longer fit the shared model. Hybrid is fine: most tenants shared, a few isolated, one codebase routing by tenant.
3. Schema per tenant only if you have a small, fixed number of tenants that each need custom tables, and you accept per-schema migrations.

I rebuilt a back office as a serverless, white-label, multi-tenant platform in 30 days, and the isolation model was one of the first decisions made, because retrofitting it later means touching every query and every table.

Who will maintain this after launch?

Isolation fails over time, not on day one. The risk is the developer who joins in year two, adds a table and doesn't know the rules. Your RLS setup and CI check are how the rules survive staff turnover.

Northeast Louisiana has a real pipeline for that work. Lumen Technologies lists its corporate headquarters in Monroe, and Louisiana Tech holds courses at that Monroe headquarters as well as in Ruston. Tech's computer science program is ABET-accredited and offers a 12-credit-hour cybersecurity concentration. ULM's Computer Information Systems program teaches database design and information security, and its computer science program won a $129,532 Board of Regents grant, with $89,800 in AWS in-kind support, to fund cybersecurity and cloud certification training for 150 students.

Graduates from those programs will know SQL. What they need from you is a codebase where the secure path is the default: policies already written, the tenant set in one middleware, and a build that fails when someone forgets.

Where to start

• Inventory every table that holds client data and confirm each one has a NOT NULL tenant_id with a foreign key.
• Check which database role your application connects as. If it owns tables or is a superuser, fix that first.
• Enable RLS with FORCE and write USING and WITH CHECK policies, starting with the tables holding PII or financial data.
• Move tenant context into one transaction-scoped set_config call in request middleware.
• Add the catalog query above to CI, and write one integration test that logs in as tenant A and asserts it sees zero rows from tenant B.
• Read your client contracts for physical-separation clauses before choosing database-per-tenant for everyone.

If you want a second opinion on your tenant isolation model or help putting RLS in place, email hello@josephleporati.com.