Shared Schema Multi-Tenancy: Isolate SaaS Customers with PostgreSQL RLS

Shared Schema Multi-Tenancy for Solo SaaS: Isolate Customers with PostgreSQL RLS Without a Second Database

You can isolate every customer’s data in a single PostgreSQL database without giving each tenant its own schema or instance. Shared-schema multi-tenancy with row-level security (RLS) is the default that most indie SaaS products should start with in 2026. It keeps migrations, backups, and connection pools simple while still preventing one customer from reading another’s rows.

The pattern is straightforward: add a tenant_id column to every table that holds customer data, set a session variable on each request, and let PostgreSQL enforce the filter. Done correctly, application code cannot accidentally leak data even if a developer forgets a WHERE clause.

Why indie founders should start with shared schema

Three isolation models exist:

• Database-per-tenant gives the strongest physical separation but multiplies connection overhead, backup jobs, and migration scripts. It only makes sense after you have clear enterprise contracts that demand it.
• Schema-per-tenant improves logical separation and can work up to a few hundred tenants, yet catalog bloat and migration tooling quickly become painful for a solo operator.
• Shared schema with RLS scales to hundreds of thousands of tenants, keeps one migration path, and lets you run ordinary SQL tools against the whole database.

For a bootstrapped product that starts with five paying customers and hopes for five hundred, the shared-schema path removes operational drag. You ship one schema. You back up one database. You restore one database. When a customer asks for an export, you run a filtered query instead of cloning an entire instance.

The minimal working setup

Start with the tables that will hold tenant data. Every such table needs a non-null tenant_id column that is indexed.

```sql CREATE TABLE projects ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id uuid NOT NULL, name text NOT NULL, created_at timestamptz DEFAULT now() );

CREATE INDEX projects_tenant_id_idx ON projects (tenant_id);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY projects_tenant_isolation ON projects USING (tenant_id = current_setting('app.current_tenant')::uuid) WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid); ```

The policy uses current_setting so the application controls the tenant context. In your request handler (Node, Go, Python, whatever) you open a transaction, set the variable, and then run queries:

``sql BEGIN; SET LOCAL app.current_tenant = '4f2c9e1a-...'; -- all subsequent queries in this transaction see only that tenant SELECT * FROM projects; COMMIT; ``

SET LOCAL is important if you use a connection pool in transaction mode (common with PgBouncer or Supabase). The setting disappears at the end of the transaction, so the next request on the same connection starts clean.

Index and policy hygiene that actually matters

A missing index on tenant_id turns every query into a sequential scan once you have more than a few thousand rows. Create the index in the same migration that adds the column.

Policies themselves can become slow if they call volatile functions repeatedly. Prefer a simple comparison against current_setting. If you need a membership check (user belongs to project), resolve the allowed IDs once at the start of the request and store them in a temporary table or session variable rather than calling a function on every row.

``sql -- Good: one lookup, then compare CREATE POLICY projects_member_isolation ON projects USING ( tenant_id = current_setting('app.current_tenant')::uuid AND id IN (SELECT project_id FROM user_project_access WHERE user_id = current_setting('app.current_user')::uuid) ); ``

Wrap the user lookup in a stable function if the planner needs help, but test the plan with EXPLAIN ANALYZE under realistic data.

Background jobs and admin paths

Scheduled jobs and admin dashboards are where isolation usually breaks. A cron that does SELECT * FROM invoices without setting the tenant variable will either return zero rows (if RLS is enforced for the role) or every row (if you bypassed RLS).

Two practical patterns:

1. Run jobs as a role that still requires the session variable, and set it explicitly for each tenant you process.
2. Create a separate bypass_rls role used only by trusted background workers, and audit every query that uses it.

Never let the application’s default database role bypass RLS. Grant the bypass role only to the worker process identity.

Migration and rollback strategy

Because everything lives in one schema, ordinary migration tools (Flyway, Alembic, Prisma, sqlx) continue to work. When you add a new table:

1. Add the tenant_id column and index.
2. Enable RLS and create the policy in the same migration.
3. Backfill existing rows if you are converting a single-tenant product.
4. Deploy the application change that sets the session variable before you turn the policy on for write traffic.

If something goes wrong, you can ALTER TABLE ... DISABLE ROW LEVEL SECURITY in seconds. That is far safer than coordinating rollbacks across hundreds of schemas.

When the shared model stops being enough

Move to schema-per-tenant or database-per-tenant only when you have concrete requirements:

• A customer contract demands physical isolation.
• You need different PostgreSQL versions or extensions per tenant.
• Regulatory rules require separate encryption keys that you cannot implement with column-level encryption.

Until one of those appears, the operational cost of multiple schemas usually exceeds the benefit. Most indie SaaS products never reach that point.

Practical checklist for this week

• Add tenant_id uuid NOT NULL and an index to every customer-facing table.
• Enable RLS and create matching USING + WITH CHECK policies.
• Set app.current_tenant with SET LOCAL inside every request transaction.
• Confirm background jobs either set the variable or use an audited bypass role.
• Run a query as one tenant and verify you cannot see another tenant’s rows.
• Keep the default application role from having BYPASSRLS.

Shared-schema multi-tenancy with PostgreSQL row-level security is not a compromise. It is the architecture that lets a solo founder keep shipping features instead of managing database farms. Start there, measure the actual pain, and only add isolation layers when a paying customer or a regulator forces the issue.

Handling inserts and updates correctly

Read policies alone are not enough. An attacker (or a buggy background job) can still insert a row with a different tenant_id if the WITH CHECK clause is missing or too weak. Always include both:

``sql CREATE POLICY projects_tenant_isolation ON projects FOR ALL USING (tenant_id = current_setting('app.current_tenant')::uuid) WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid); ``

When the application inserts, it must still set the correct tenant_id itself. The policy only rejects mismatches; it does not auto-populate the column. In practice this means your ORM or query builder should always stamp tenant_id from the authenticated session, never from user input.

Testing isolation before you ship

Write a short automated test that:

1. Creates two tenants and a few rows for each.
2. Sets the session variable to tenant A and confirms tenant B rows are invisible.
3. Attempts an UPDATE or INSERT that targets the wrong tenant_id and expects a policy violation error.
4. Runs the same queries as a role that bypasses RLS and confirms the bypass works only for that role.

Run this test in CI on every schema change. The cost is a few seconds; the cost of a leak is far higher.

Connection pooling and SET LOCAL

If you use PgBouncer in transaction pooling mode (the common recommendation for serverless or high-connection environments), SET LOCAL is the only safe way to set the tenant. A plain SET survives the transaction and can leak to the next client that reuses the connection.

Confirm your pooler is in transaction mode and that every code path that touches tenant data wraps the work in an explicit transaction that sets the variable first. Frameworks such as Prisma, Drizzle, and SQLAlchemy all support this pattern with a small middleware or interceptor.

Observability that proves isolation

Add a simple metric or log line that records the tenant_id on every request. Periodically sample production queries with EXPLAIN (ANALYZE, BUFFERS) filtered to a single tenant and confirm the plan uses the tenant_id index. If you ever see a sequential scan on a large table that has an index on tenant_id, the policy or the query is not using the column correctly.

Store the raw current_setting('app.current_tenant') value in your request logs for a short retention period so you can reconstruct which tenant a slow query belonged to.

Converting an existing single-tenant product

If you already have users and data:

1. Add the nullable tenant_id column and index.
2. Backfill every existing row with a single default tenant_id (or create one tenant per current user if that matches your model).
3. Make the column NOT NULL.
4. Deploy the application change that sets the session variable.
5. Enable RLS and the policy in a follow-up migration once you have verified the variable is set on every code path.
6. Monitor error rates for a day; policy violations usually surface as empty result sets or permission errors rather than crashes.

This staged rollout keeps the product online while you close the isolation gap.

Common mistakes that leak data

• Forgetting to set the session variable on a new endpoint or webhook handler.
• Using a connection from the pool without an explicit transaction.
• Granting the application role BYPASSRLS just for debugging.
• Writing a policy that calls a volatile function on every row.
• Letting an admin UI query without a tenant filter.

Each of these has caused real incidents. The checklist exists to catch them before customers do.

Start with the shared schema. Measure. Only split when a concrete requirement appears.