Multi-Tenant DB Isolation: Schema vs Row-Level Security

For PostgreSQL multi-tenant databases, row-level security (RLS) is generally the better choice for most applications because it centralizes tenant isolation in the database itself, while schema-per-tenant offers stronger physical separation at the cost of operational complexity. This article explains both approaches, their trade-offs, and how to decide which fits your use case.

Background: The Multi-Tenant Isolation Problem

When you build a software-as-a-service (SaaS) application, you typically serve many customers (tenants) from a single database instance. The core challenge is ensuring that Tenant A can never read or modify Tenant B’s data. There are three common strategies in PostgreSQL:

• Database-per-tenant: Each tenant gets its own database. Maximum isolation, but expensive to manage at scale.
• Schema-per-tenant: Each tenant gets its own schema within one database. Good isolation with shared infrastructure.
• Row-level security (RLS): All tenants share the same tables, but policies restrict which rows each user can see.

This article focuses on the last two, as they are the most common choices for a single PostgreSQL instance.

How Schema-Per-Tenant Works

In the schema-per-tenant model, you create a separate schema for each tenant, and within that schema you create the same set of tables (e.g., orders, users, products). A typical setup looks like:

CREATE SCHEMA tenant_1;
CREATE TABLE tenant_1.orders (...);
CREATE SCHEMA tenant_2;
CREATE TABLE tenant_2.orders (...);

To access data, you set the search_path to the tenant’s schema before running queries. For example:

SET search_path TO tenant_1;
SELECT * FROM orders;

Each tenant’s data is physically separated into its own namespace. This makes it easy to drop a tenant (just drop the schema) and to restore or back up a single tenant (using schema-level dumps). However, you must manage the schema creation and migration process for every new tenant, and you cannot easily run cross-tenant queries without explicitly referencing each schema.

How Row-Level Security Works

Row-level security (RLS) was introduced in PostgreSQL 9.5. It allows you to define policies on a table that restrict which rows are visible or modifiable based on the current user or session variable. A typical setup:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.tenant_id')::int);

Here, app.tenant_id is a custom setting that you set at the start of each session, often via a connection pooler or middleware. All tenants share the same orders table, but each query only sees rows where tenant_id matches the session’s value. This centralizes isolation logic in the database, so you cannot accidentally forget a WHERE clause in your application code.

RLS works with both SELECT, INSERT, UPDATE, and DELETE policies. You can also use WITH CHECK to ensure that new rows always have the correct tenant_id.

Comparing Isolation and Security

The level of isolation differs significantly:

• Schema-per-tenant: Provides strong logical separation. A tenant cannot see another tenant’s tables unless you grant them access. However, if an attacker can execute arbitrary SQL (e.g., via SQL injection), they might be able to change the search_path or use fully-qualified names to access other schemas.
• RLS: Provides row-level filtering that is enforced by the database. Even if an attacker can run arbitrary queries, the RLS policy will filter out rows they are not authorized to see. However, RLS does not prevent a user from seeing that other tenants’ rows exist (e.g., via count queries or timing attacks), and it requires careful policy design to avoid leaks.

For security-critical applications, RLS is often considered more robust because it is harder to bypass accidentally. But schema-per-tenant offers a cleaner mental model and is easier to audit.

Operational Considerations

Operationally, the two approaches differ in several ways:

• Schema-per-tenant: Adding a new tenant requires creating a new schema and running migrations on it. This can be automated, but it adds complexity. Backups and restores are simpler per tenant, but you have many schemas to manage.
• RLS: Adding a tenant is just inserting a row into a tenants table. Migrations are done once on shared tables. However, you need to ensure that every query sets the app.tenant_id correctly, and you must be careful with connection pooling (e.g., using SET LOCAL inside a transaction).

Performance-wise, RLS adds a small overhead on every query due to policy checks. Schema-per-tenant can actually improve performance because each tenant’s data is smaller and indexes are more focused, but the overhead of managing many schemas can negate that.

How to Choose

There is no one-size-fits-all answer, but here are some guidelines:

• Choose schema-per-tenant if: you have a small number of large tenants (e.g., enterprise clients), you need to offer per-tenant customizations (like custom columns), or you need to easily drop or archive a tenant’s data.
• Choose RLS if: you have many small tenants (e.g., a B2C SaaS), you want to minimize operational overhead, or you want to ensure that developers cannot accidentally leak data across tenants.
• Hybrid approach: Some teams use a shared schema with RLS for most tenants, but move large or sensitive tenants to their own schema later.

In practice, RLS is the default recommendation for new SaaS applications because it scales better and reduces the risk of human error. Schema-per-tenant is still a valid choice for specific niches.

Common Pitfalls and Misconceptions

Here are some common issues developers run into:

• Forgetting to set the tenant context: With RLS, if you forget to set app.tenant_id, the policy may fail or return no rows. Use a connection pooler that sets this automatically, or use SET LOCAL within a transaction.
• Policy bypass with table owners: By default, the table owner bypasses RLS. You need to ALTER TABLE ... FORCE ROW LEVEL SECURITY to prevent the owner from bypassing it.
• Schema-per-tenant and search_path injection: If you build queries by concatenating the schema name, an attacker could inject a schema name. Always use parameterized queries or quote identifiers.
• Indexing with RLS: RLS does not automatically create indexes on tenant_id. You must add an index on the tenant column to avoid full table scans.

A common misconception is that RLS is slow. In practice, the overhead is often less than 5% for typical queries, and it is far faster than the application-level filtering that many developers would otherwise write.

Frequently Asked Questions

Can I use both schema-per-tenant and RLS together?

Yes, you can. For example, you could have a shared schema with RLS for most tenants, and a separate schema for a few large tenants that need custom fields. This hybrid approach gives you flexibility, but it adds complexity in routing queries to the right schema.

Does RLS work with connection pooling?

Yes, but you must be careful. Use SET LOCAL inside a transaction to set the tenant context, and reset it after each transaction. Many connection poolers like PgBouncer support this pattern.

How do I migrate from schema-per-tenant to RLS?

Migrating is non-trivial but doable. You need to add a tenant_id column to all shared tables, backfill it from the schema name, then enable RLS and create policies. This is a significant data migration and should be tested thoroughly.

Which approach is more secure against SQL injection?

RLS is generally more secure because it provides a last line of defense. Even if an attacker can inject SQL, the RLS policy will restrict which rows they can see. With schema-per-tenant, an attacker might be able to access other schemas if they can change the search_path.

Does schema-per-tenant allow cross-tenant queries?

Not easily. You would need to query each schema separately or use UNION ALL across schemas. RLS makes cross-tenant analytics harder as well, because you would need to disable RLS or use a superuser, which is risky.

What is the best practice for setting the tenant ID in RLS?

Use a session variable like app.tenant_id and set it at the beginning of each request. Never trust the client to send the tenant ID; always derive it from the authenticated user’s session.

Related

• Kodi vs Plex: Which Media Server Is Right?
• Email vs Fax: How Each Works, Compared
• Portable Rubber Dams vs Sandbags: Safe Flood Choice