Design a multi-tenant Postgres schema
Design a Postgres schema for a multi-tenant SaaS product where each tenant's data is completely isolated. You will implement row-level security (RLS) policies, create two test tenants with overlapping data shapes, and verify that queries from one tenant cannot return rows belonging to the other; even with direct SQL access.
Why this matters
Multi-tenancy done wrong is a security incident waiting to happen. Row-level security pushes isolation into the database engine rather than relying on application code to get it right every time. Understanding RLS also makes you dangerous in any SaaS backend context; it is one of the most underused features in Postgres.
Before you start
- Postgres running locally (Docker is fine: docker run -p 5432:5432 -e POSTGRES_PASSWORD=pass postgres)
- psql or a GUI client like DBeaver
- Understanding of what a foreign key and JOIN are
- Basic familiarity with SQL CREATE TABLE and INSERT
Step-by-step guide
- 1
Design the schema
Create three tables: tenants (id, name, created_at), users (id, tenant_id FK, email), and resources (id, tenant_id FK, name, data). Every tenant-scoped table has a tenant_id column. This is the shared-schema, row-level isolation model; one schema, many tenants, policies enforce the wall.
- 2
Enable RLS and write the policy
Run ALTER TABLE resources ENABLE ROW LEVEL SECURITY. Then create a policy: CREATE POLICY tenant_isolation ON resources USING (tenant_id = current_setting('app.current_tenant_id')::uuid). This policy fires on every SELECT, INSERT, UPDATE, and DELETE; the tenant_id must match the session variable.
- 3
Create two tenants and seed data
Insert two tenant rows and create 5 resources for each tenant. Make sure some resource names overlap between tenants (e.g. both have a resource called 'dashboard'); this is the test that catches permissive policies.
- 4
Verify isolation
Set app.current_tenant_id to tenant A's ID and SELECT * FROM resources. You should see exactly 5 rows. Then set it to tenant B and repeat. Then try without setting the variable at all; the query should return zero rows (or error, depending on your default deny policy).
- 5
Test with a bypassing role
Create a Postgres role with BYPASSRLS privilege. Run the same queries with this role and verify you see all rows across both tenants. This confirms RLS is working for normal roles and shows how to implement admin-level access for your own support tooling.