Multi-Tenant Monolith PostgreSQL: Row-Level Security vs Schema per Tenant

The tenant isolation spectrum and what each level guarantees

Tenant isolation in a multi-tenant monolith on PostgreSQL is not one decision. It is a spectrum, and each step moves the point where a mistake turns into a cross-tenant leak.

Shared tables with tenant_id put every tenant in the same tables. The application adds WHERE tenant_id = ? to each query. Teams choose it because it is cheap and simple. Its only guarantee is developer discipline. One missing predicate in a report query or an ORM scope exposes every tenant, so app-level filtering is not a security boundary.

Shared tables plus row-level security keep the same layout, but PostgreSQL enforces the filter through policies tied to a session setting or role. A forgotten WHERE clause no longer leaks data. Misconfiguration still can, for example connecting as a table owner or a role that bypasses RLS.

Schema per tenant gives each tenant its own set of tables, selected through search_path or qualified names. Isolation then depends on correct schema selection and on grants. This model makes per-tenant restores and customizations easier.

Database per tenant separates catalogs and connections. If the databases also run on separate instances, it separates resources too.

RLS and schema per tenant both run inside one database. They share its CPU, buffer cache, WAL stream, and connection limit. Neither one stops a noisy neighbor, and neither replaces separate instances when contracts demand strict isolation.

Copyweaker isolation --------------------------------------> stronger isolation

+----------------+ +----------------+ +----------------+ +----------------+
| 1. tenant_id   | | 2. shared+RLS  | | 3. schema per  | | 4. database    |
|    only        | |                | |    tenant      | |    per tenant  |
+----------------+ +----------------+ +----------------+ +----------------+
| App adds       | | PG policy adds | | search_path +  | | separate DB or |
| tenant_id      | | tenant filter  | | grants pick    | | instance       |
| filter         | | to every query | | the schema     | | (own catalogs, |
| NOT a boundary | |                | |                | | connections)   |
+-------+--------+ +-------+--------+ +-------+--------+ +---+--------+---+
        |                  |                  |              |        |
        v                  v                  v              v        v
+-------------------------------------------------------+ +------+ +------+
|  One shared PostgreSQL database                       | | DB A | | DB B |
|  +-------------------------------------------------+  | | own  | | own  |
|  | shared: CPU, buffer cache, I/O, WAL, conns      |  | | CPU, | | CPU, |
|  +-------------------------------------------------+  | | I/O, | | I/O, |
|  noisy neighbor affects every tenant                  | | WAL  | | WAL  |
+-------------------------------------------------------+ +------+ +------+
  (model 4 separates resources only when DBs run on separate instances)

Leak if isolation is wrong:
  1: yes, one missing WHERE      2: only if misconfigured (owner/BYPASSRLS)
  3: only if misconfigured (search_path, grants)       4: very unlikely

The four tenant isolation models from weakest to strongest: app-level filtering, RLS, and schema per tenant all share one database's CPU, buffer cache, I/O, WAL, and connections, and only database per tenant on separate instances separates those resources.

Who should use RLS or schema per tenant

Both models are aimed at teams running a single application, usually a monolith, against one PostgreSQL database that serves many customers. They fit when you want database-enforced isolation without operating a separate database for every customer.

Row-level security suits:

• SaaS products with many tenants, especially when tenants sign up on their own and the count keeps growing.
• Teams that ship schema changes often and want each migration to run once.
• Products with frequent cross-tenant work such as billing, analytics, support search and admin dashboards.
• Teams already filtering on tenant_id in application code who want the database to back that filter up.

Schema per tenant suits:

• B2B products with a smaller, stable set of larger customers.
• Customers who expect per-tenant restores, clean offboarding or per-tenant custom columns.
• Teams willing to build and run migration orchestration across many schemas.

Neither model is enough for:

• Contracts that require physical separation, separate encryption keys or region-specific storage per tenant. These need a database or instance per tenant.
• Workloads where one tenant's load must never affect another's. Both models share one database's CPU, I/O, WAL and connections.

How tenant isolation works at request time

The two models use the same request lifecycle and differ only in which session setting they change.

1. Resolve the tenant. The application identifies the tenant from the authenticated session, for example a claim in the token or the request's subdomain. The tenant must come from authentication, never from a client-supplied parameter that the user can change.
2. Open a transaction. The application takes a connection from the pool and starts a transaction.
3. Set tenant context, scoped to the transaction. Under RLS this is set_config('app.tenant_id', $1, true) or SET LOCAL app.tenant_id = .... Under schema per tenant it is SET LOCAL search_path = tenant_0042 or the equivalent set_config('search_path', ..., true).
4. Run unmodified queries. Application SQL contains no tenant predicate and no schema name. Under RLS, PostgreSQL appends the policy expression to every query on protected tables. Under schema per tenant, unqualified table names resolve through search_path to that tenant's tables.
5. Commit or roll back. The transaction-local setting is discarded, and the connection goes back to the pool with no tenant context. This is why both models work behind a transaction-mode pooler, provided nothing sets tenant context at session level.

Isolation then rests on a few database properties instead of on every query. Under RLS, those are the policies, FORCE ROW LEVEL SECURITY and an application role without ownership or BYPASSRLS. Under schema per tenant, they are the transaction-scoped search_path and the grants on each schema. The sections below cover each model's details.

How row-level security works and how to implement it safely

Row-level security attaches a filter to a table that PostgreSQL applies on every query, so tenant scoping no longer depends on each developer remembering a WHERE clause. You turn it on per table with ALTER TABLE ... ENABLE ROW LEVEL SECURITY. Once enabled, a table with no policies returns nothing to ordinary roles. That default-deny behavior is the property you want.

CREATE POLICY takes two expressions. USING decides which existing rows a query can see, update, or delete. WITH CHECK decides which rows an INSERT or UPDATE may write. Without WITH CHECK, a tenant could insert a row stamped with another tenant's ID.

The policy reads the tenant from a session setting: tenant_id = current_setting('app.tenant_id', true)::uuid. The second argument, missing_ok, makes a missing setting return NULL instead of raising an error. A comparison against NULL matches no rows, so a request that never set a tenant sees nothing. If the setting exists but is empty, the uuid cast raises an error, which is also a safe failure.

Set the tenant with SET LOCAL or set_config('app.tenant_id', $1, true) inside the request's transaction. The value is discarded at commit or rollback, so a pooled connection never carries one tenant's ID into the next request.

Bypass rules decide whether any of this holds:

• Table owners skip policies unless you also run FORCE ROW LEVEL SECURITY.
• Superusers and roles with BYPASSRLS skip policies even when RLS is forced.

The application should therefore connect as a dedicated role that does not own the tables and holds neither attribute. Migrations should run as a separate owner role.

Copy-- Run once as an administrator: separate owner and app roles.
CREATE ROLE migrator LOGIN PASSWORD 'change-me-migrator';
CREATE ROLE app_user LOGIN PASSWORD 'change-me-app'
  NOSUPERUSER NOBYPASSRLS;  -- app must not be able to skip policies

-- Run as migrator, which owns the table.
CREATE TABLE invoices (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id uuid    NOT NULL,
  amount    numeric NOT NULL
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- policies apply to the owner too

CREATE POLICY tenant_isolation ON invoices
  FOR ALL TO app_user
  -- missing_ok = true: an unset tenant yields NULL, which matches no rows
  USING      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  -- rejects INSERT/UPDATE that would write another tenant's ID
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

-- app_user gets data privileges but does not own the table.
GRANT SELECT, INSERT, UPDATE, DELETE ON invoices TO app_user;

-- Per request, connected as app_user through the pool:
BEGIN;
-- is_local = true: the setting is discarded at COMMIT or ROLLBACK
SELECT set_config('app.tenant_id', '3f2b8c1e-9a4d-4e6f-b7a1-2c5d8e9f0a1b', true);
SELECT id, amount FROM invoices;  -- only this tenant's rows
INSERT INTO invoices (tenant_id, amount)
  VALUES ('3f2b8c1e-9a4d-4e6f-b7a1-2c5d8e9f0a1b', 120.00);  -- passes WITH CHECK
COMMIT;

Sets up a migration owner role and a non-owner application role without BYPASSRLS, enables and forces RLS on a table with a fail-closed tenant policy, and scopes each request's tenant with a transaction-local set_config.

Performance of RLS: indexes, policy expressions and EXPLAIN

Every RLS policy becomes an extra predicate on each query that touches the table. A USING (tenant_id = current_setting('app.tenant_id')::uuid) clause is combined with the query's own WHERE clause, so the tenant filter is part of every scan, join and subquery whether or not the developer wrote it. That predicate must be cheap and indexable.

Index for the predicate you always have

Put tenant_id first in composite indexes, such as (tenant_id, created_at) instead of (created_at). Every query already filters on tenant, so a leading tenant_id column narrows the scan to one tenant's rows before any other condition applies. Unique constraints need the same treatment. UNIQUE (tenant_id, email) enforces per-tenant uniqueness and gives you a usable index. A global UNIQUE (email) also leaks across tenants, because one tenant can detect that another tenant already uses an address.

Keep policy expressions boring

Compare the column to a session setting. Avoid joins and subqueries in the policy itself. If you wrap the lookup in a function, mark it STABLE. Do not mark it IMMUTABLE: the planner may treat an IMMUTABLE function's result as a constant at plan time, and a cached plan could then reuse another tenant's value. Writing the call as a scalar subquery, (SELECT current_tenant()), gets it evaluated once per statement instead of once per row.

Verify with EXPLAIN

Run EXPLAIN as the application role, not as a superuser or owner, because those roles may bypass the policy entirely. Check that the tenant condition appears as an index condition. If it only shows up as a filter on a sequential scan, the index is not being used.

Risks

• A missing index hurts everyone. Without an index on tenant_id, every tenant's queries slow down at the same time.
• One large tenant skews statistics. The planner usually doesn't know the tenant value when it builds the plan, so it estimates for an average tenant. That plan can be badly wrong for a tenant that holds most of the table's rows.
• Plans are harder to read. Policy predicates show up in plans even though they never appear in application SQL, which makes plans harder to interpret during incidents.

Copy-- Shared table: tenant_id leads every index and unique constraint.
CREATE TABLE orders (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id  uuid        NOT NULL,
  ref        text        NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, ref)  -- per-tenant uniqueness, and an index that starts with tenant_id
);
CREATE INDEX orders_tenant_created_idx ON orders (tenant_id, created_at);

-- STABLE: constant within a statement, never folded into a cached plan.
-- Do not mark this IMMUTABLE.
CREATE FUNCTION current_tenant() RETURNS uuid
LANGUAGE sql STABLE
AS $$ SELECT current_setting('app.tenant_id')::uuid $$;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;  -- apply the policy to the table owner too

-- Simple comparison. The scalar subquery is evaluated once per statement, not once per row.
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = (SELECT current_tenant()));

GRANT SELECT ON orders TO app_user;  -- app_user: placeholder non-superuser application role

-- Check the plan as the application role. Superusers and BYPASSRLS roles skip the policy.
BEGIN;
SET LOCAL ROLE app_user;
SET LOCAL app.tenant_id = '6f1c2a9e-3b4d-4e5f-8a7b-9c0d1e2f3a4b';
EXPLAIN
SELECT id, ref FROM orders
WHERE created_at >= now() - interval '7 days'
ORDER BY created_at DESC
LIMIT 50;
-- Look for an Index Scan on orders_tenant_created_idx with the tenant_id comparison
-- under "Index Cond". If tenant_id only appears as a "Filter" on a Seq Scan,
-- the index is not being used for the tenant predicate.
ROLLBACK;

Shows a table with tenant_id-leading indexes and unique constraints, a STABLE helper called through a scalar subquery in the policy, and how to run EXPLAIN as the application role to confirm the tenant predicate is an index condition.

How schema per tenant works and what it costs to operate

Each tenant gets its own schema containing an identical set of tables, such as tenant_0042.orders and tenant_0043.orders. Application SQL uses unqualified names, and the application chooses the tenant by setting search_path at the start of each transaction with SET LOCAL. That setting lasts only until commit or rollback, so it cannot outlive the unit of work it was set for.

A plain session-level SET search_path is risky behind a transaction-mode pooler. The pooler hands the server connection to a different client after each transaction, but session state stays on that connection. The next client to receive it inherits the previous tenant's path. Its unqualified queries then run against another tenant's tables with no error. This isolation failure happens silently.

The operating costs grow with tenant count:

• Migrations run N times. A run can fail at tenant 600 of 2,000 and leave the fleet on mixed schema versions. Write migrations to be idempotent and record a version for each schema. Then a rerun can resume from the failure and run in parallel with bounded concurrency.
• The catalog multiplies. Illustrative estimate (assumed numbers, not a benchmark): relations ≈ tenants × (tables + indexes + TOAST tables) = 2,000 × (60 + 120 + 30) = 420,000.
  • pg_dump takes a lock on every table it dumps, so a dump this size may need a higher max_locks_per_transaction.
  • Autovacuum has to spread its workers across all of those tables.
  • Monitoring and admin tools that list relations slow down.
• Tooling assumes one schema. Many ORMs and migration tools expect a single schema and a single version table.

Exact thresholds depend on your workload, so measure them on your own data.

Copy-- Route one unit of work to one tenant. SET LOCAL is reverted at COMMIT or
-- ROLLBACK, so a transaction-mode pooler cannot pass this path to the next client.
BEGIN;
SET LOCAL search_path = tenant_0042;
SELECT id, total FROM orders WHERE status = 'open';  -- resolves to tenant_0042.orders
COMMIT;

-- The same thing as a function call, which drivers can bind as a parameter.
-- The third argument (true) makes the setting transaction-local, like SET LOCAL.
BEGIN;
SELECT set_config('search_path', 'tenant_0043', true);
SELECT id, total FROM orders WHERE status = 'open';  -- resolves to tenant_0043.orders
COMMIT;

-- Do not do this behind a transaction-mode pooler. The setting survives COMMIT
-- and stays on the server connection for whichever client gets it next.
-- SET search_path = tenant_0042;

-- One idempotent migration step, run once per tenant schema. Each statement is
-- safe to repeat, so rerunning after a failure partway through the fleet
-- converges instead of erroring.
BEGIN;
SET LOCAL search_path = tenant_0042;
SET LOCAL lock_timeout = '5s';  -- fail fast rather than queue behind a long lock
CREATE TABLE IF NOT EXISTS schema_version (
  version    integer PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE orders ADD COLUMN IF NOT EXISTS currency text;
CREATE INDEX IF NOT EXISTS orders_currency_idx ON orders (currency);
INSERT INTO schema_version (version) VALUES (17)
  ON CONFLICT (version) DO NOTHING;  -- records this schema's version
COMMIT;

-- Rough size of the catalog: count relations in tenant schemas.
SELECT count(*) FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname LIKE 'tenant\_%';

Transaction-scoped tenant routing with SET LOCAL or set_config, the session-level SET to avoid behind a transaction-mode pooler, an idempotent per-schema migration that records its version, and a query that counts tenant relations in the catalog.

When schema per tenant pays off and when to avoid it

Schema per tenant earns its overhead when you have relatively few tenants, each large enough to justify its own lifecycle. Restoring one customer becomes pg_dump -n tenant_acme followed by a restore into a scratch database. Offboarding becomes DROP SCHEMA tenant_acme CASCADE, which removes the tenant's tables, indexes and sequences in one transaction. Per-tenant custom columns are ordinary ALTER TABLE statements on that tenant's tables. Extensions are installed once per database, so the most you can do is place an extension's objects in a particular schema with CREATE EXTENSION ... SCHEMA.

Data residency follows the same logic. Every schema lives on the same server, so a tenant that needs its data in a specific region must move to its own database. Schema per tenant makes that move straightforward because the tenant's data is already a self-contained unit you can dump and restore.

In a shared schema, the same operations become filtered COPY and DELETE statements across every tenant-scoped table. They must follow foreign keys in the right order, and a missed table leaves orphaned data behind.

Avoid schema per tenant when you have thousands of small tenants. Also avoid it if you ship migrations often, because each one runs N times and can fail partway. Cross-tenant work suffers too. Admin dashboards, billing jobs and analytics end up generating UNION ALL queries across schemas or an ETL pipeline into a separate store.

Alternatives to RLS and schema per tenant

RLS and schema per tenant are not the only options. The alternatives trade isolation, cost and operational effort differently.

• Application-level filtering only. Shared tables with WHERE tenant_id = ? added by the ORM or a query layer. This is the cheapest to build and the weakest to rely on. It works for internal tools or early prototypes. For customer data, treat it as a starting point and add RLS as a backstop. Without that backstop, any missed predicate becomes a leak.
• Database per tenant on a shared instance. Each tenant gets its own database on the same PostgreSQL server. Catalogs, grants and backups are separated, and a cross-tenant query is impossible without an explicit cross-database mechanism. Each database needs its own connections, though, so pooling gets harder as tenant count grows. Resources such as CPU, I/O and WAL are still shared.
• Database or instance per tenant. Separate instances give resource isolation, independent upgrades and maintenance windows, and data placement in a chosen region. This is the strongest option and the most expensive one. You pay for idle capacity per tenant and need automation for provisioning, migrations and monitoring across the whole fleet.
• Shared tables partitioned by tenant. Declarative list or hash partitioning on tenant_id keeps one logical table. List partitioning can give large tenants their own partition, which can then be detached or dropped on its own. Partitioning is not an isolation mechanism, so pair it with RLS. Many partitions also grow the catalog, much like schema per tenant does.
• Hybrid. Shared RLS tables for most tenants, with large or regulated tenants on dedicated databases. The decision checklist below covers this end state.

How each model saves engineering time

The time savings come from moving tenant scoping out of individual queries and into a small number of places that you can review and test once.

Row-level security saves time on:

• Code review. Reviewers no longer have to check every new query, report and ORM scope for a tenant predicate. They review the policy and the request middleware that sets tenant context.
• Migrations. One migration runs once against one set of tables. You need no orchestration, per-schema version tracking or recovery from partial fleet migrations.
• Cross-tenant features. Billing, analytics and support tools query shared tables directly through a separate, explicitly privileged role. You avoid writing fan-out queries or an ETL pipeline.
• Incident risk. A forgotten WHERE clause returns an empty result instead of another customer's data. A missing filter becomes a bug instead of a breach, and that saves the work a breach would cause.

Schema per tenant saves time on:

• Single-tenant operations. Restoring, exporting or deleting one customer is a single pg_dump -n or DROP SCHEMA ... CASCADE. It does not require a script that walks every table in foreign-key order.
• Customer-specific changes. A custom column or index for one large tenant is a local ALTER TABLE that does not affect anyone else.

Illustrative estimate (assumed numbers, not a measurement): assume a team merges 40 changes a month that touch tenant-scoped queries. Assume each change needs an assumed 10 extra minutes of review to confirm every query filters by tenant. That review comes to roughly 400 minutes, or about 6.7 hours a month, that RLS largely removes. The cost of that saving is a one-time setup of policies, roles and isolation tests, plus review of each policy change. Substitute your own change volume and review time before relying on this figure.

Neither model saves time if its guarantees are not tested. The isolation tests below are what make the savings safe to count on.

Testing tenant isolation so failures stay closed

Isolation tests should run against a real PostgreSQL instance and connect with the exact role the application uses in production. Testing as a superuser or the table owner proves nothing, because both skip RLS unless the table has FORCE ROW LEVEL SECURITY.

The core suite seeds two tenants, sets tenant A's context, and attacks tenant B:

• Reads: selecting B's rows by primary key returns zero rows.
• Updates and deletes: statements targeting B's IDs report zero affected rows. RLS filters these silently, so assert on the row count. Checking that no exception was raised proves nothing.
• Inserts: writing a row with B's tenant_id must fail with a row-level security violation from the WITH CHECK clause.
• Missing context: with no tenant setting, queries must return nothing or raise an error. They must never return every row. This catches policies that treat a NULL or empty setting as "no filter".

Add structural assertions that query the catalogs. The app role must not own any tenant table, and it must have rolbypassrls set to false. For connection pooling, set a pool size of one. Check out a connection, set tenant A, and return it. Then check it out again and assert the tenant setting is empty. This verifies that you use SET LOCAL or reset the setting on release.

These tests target the most dangerous property of shared-table tenancy. One mistake affects every customer at once. That mistake might be a forgotten policy, a migration that changed the table owner, or a pool leaking context. Each check takes seconds in CI. Finding the same bug after release means incident response, customer notification, and a forensic audit of which rows were exposed.

Decision checklist, cost impact and migration path

Decision checklist

• Tenant count: a few hundred stable tenants suits either model. Thousands, growing through self-serve signup, favors RLS.
• Size distribution: a long tail with a few very large tenants points to shared tables with the large ones isolated.
• Compliance: contracts requiring physical separation or per-tenant restore usually need a dedicated database. A schema does not satisfy them.
• Cross-tenant workloads: billing, analytics and support search are one query under RLS. Under schema per tenant they become a fan-out across every schema.

Illustrative estimate (assumed numbers, not a benchmark): total tables = tenants × tables per tenant. With an assumed 5,000 tenants × 80 tables, schema per tenant has 400,000 tables. RLS has 80. Indexes, sequences and TOAST relations multiply the catalog entries further. Each migration also runs 5,000 times, and any one of those runs can fail partway.

Hybrid end state

Many teams settle on shared RLS tables for the long tail and move outlier tenants to dedicated databases. This adds a routing layer: a tenant-to-database map, connection selection per request, and migrations that must reach every database.

Switching models later is expensive in either direction. It means rewriting data, changing query paths and rerunning isolation tests on live traffic. Choose the model deliberately before you have many tenants.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to multi-tenant monolith postgresql, this is where it helps:

Saves time

• Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
• One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
• The dependency graph answers "what does this touch?" in seconds instead of a manual audit.

Saves cost

• Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
• Less duplicated configuration to maintain: one shared definition inherited by many services.
• You can size a change's blast radius before merging, avoiding surprise cross-team firefighting.
• OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.