Postgres’s schema-per-tenant model is more about isolating data and simplifying database management than it is about strict security boundaries.
Let’s see it in action. Imagine you have a multi-tenant application where each tenant, say "Acme" and "BetaCorp," gets their own dedicated schema.
-- Tenant Acme's schema
CREATE SCHEMA acme;
CREATE TABLE acme.users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO acme.users (name) VALUES ('Alice');
-- Tenant BetaCorp's schema
CREATE SCHEMA betacorporation;
CREATE TABLE betacorporation.users (
id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
INSERT INTO betacorporation.users (name) VALUES ('Bob');
When a request comes in for Acme, your application logic would dynamically set the search_path to acme:
SET search_path TO acme, public;
SELECT * FROM users; -- This will only query acme.users
And for BetaCorp:
SET search_path TO betacorporation, public;
SELECT * FROM users; -- This will only query betacorporation.users
This approach allows for straightforward tenant data segregation. Each tenant’s data is entirely contained within its own schema, meaning acme.users has no visibility into betacorporation.users. This is a significant advantage for data isolation and makes tasks like backups, restores, or even dropping a single tenant’s data incredibly simple. Instead of complex DELETE statements across a single giant table, you just DROP SCHEMA betacorporation CASCADE;.
The core problem this solves is managing shared resources and data in a way that respects individual tenant boundaries without the overhead of separate databases for each tenant. Each schema acts like a mini-database within your single Postgres instance. This means you get the isolation benefits without the management complexity of managing hundreds or thousands of separate database instances. You can run migrations against all tenant schemas with a single command by iterating through a list of schema names.
Internally, Postgres handles this by using the search_path parameter. When you set search_path to acme, public, Postgres will first look for unqualified table names (like users) within the acme schema. If it doesn’t find it there, it will then look in the public schema. This mechanism is what allows you to SELECT * FROM users and have it resolve to the correct tenant’s table based on the current connection’s search_path.
You can also leverage Postgres’s Row Level Security (RLS) policies, though it’s often an afterthought with schema-per-tenant. For instance, you could create a policy on users that ensures a user can only see their own id and name, even if they somehow managed to query across schemas (which the search_path largely prevents for unqualified names).
-- Example RLS policy (applied to each tenant's users table)
ALTER TABLE acme.users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON acme.users
FOR ALL USING (true); -- Simplistic example: in reality, you'd filter by user ID or tenant ID
ALTER TABLE betacorporation.users ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation_policy ON betacorporation.users
FOR ALL USING (true);
One of the most powerful, yet often overlooked, aspects of this design is the ability to use PostgreSQL’s extension capabilities per schema. For example, if a specific tenant requires advanced full-text search capabilities via pg_trgm or tsvector, you can enable and configure it just for their schema without impacting other tenants. This allows for fine-grained feature provisioning and resource allocation.
The next step in scaling this pattern often involves considering how to handle shared global data or how to manage the lifecycle of a tenant’s schema as your application grows.