SaaS Multi-Tenant Architecture Implemented with PostgreSQL RLS (Row-Level Security) 2026

"It's just a PoC at first, so simple WHERE clause filtering is fine"—SaaS products launched with this mindset all too frequently suffer accidents where data belonging to other tenants becomes visible after feature additions just three months later. Fragility in multi-tenant design is technical debt that exposes itself the further implementation progresses.

PostgreSQL's Row-Level Security (RLS) is a mechanism that enforces a solution at the database layer. Even if you forget to add a tenant WHERE clause in one spot in your app, the DB automatically applies the tenant filter. In this article, we outline design patterns and pitfalls when using RLS in SaaS custom development.

Three Multi-Tenancy Models and Where RLS Fits

There are three primary models for SaaS multi-tenant design.

In custom development projects for startups or seed-stage companies, cost efficiency makes the pool model the top candidate. Combining this with RLS represents the standard as of 2026.

Minimal RLS Configuration: Three Steps

1. Add Tenant ID Column and Set Policies

-- 全テーブルに tenant_id を持たせる前提
ALTER TABLE projects ADD COLUMN tenant_id uuid NOT NULL;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

current_setting('app.tenant_id') is the standard PostgreSQL mechanism to retrieve the tenant ID from session variables.

2. Always Set the Session Variable on the Application Side

// リクエストごとに最初のクエリで実行
await db.execute(sql`SET LOCAL app.tenant_id = ${session.tenantId}`);

Using SET LOCAL ensures it is automatically cleared after the transaction completes. Always issue this inside a transaction.

3. Define Exceptions for Superusers and Migrations

The postgres role bypasses RLS by default. Separate roles so that application roles are stripped of bypass privileges and only migration roles retain administrative privileges.

CREATE ROLE app_user NOBYPASSRLS;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;

Five Traps to Avoid in Pool-Model RLS

Trap 1 — Compatibility with Connection Pooling

When using PgBouncer's transaction mode or serverless drivers like Supabase / Neon, missing a SET LOCAL call causes operations to execute in another tenant's context because connections are reused. Setting up middleware that reliably sets this upon request arrival is mandatory.

Trap 2 — Privilege Roles in Migrations

When running migrations from CI, teams often inadvertently connect using the postgres role, deploying to production in a state where "in reality, no one tested RLS." Connect to the CI test DB using app_user to mirror production conditions.

Trap 3 — Forgetting RLS on JOIN Target Tables

A frequent mistake is configuring RLS only on projects while forgetting tasks. A robust approach is to define policies on all tables and set up an automated check in CI that queries the pg_policies view to verify all required tables are covered.

Trap 4 — Boundaries on INSERT / UPDATE

The USING clause filters during SELECT operations, while the WITH CHECK clause validates during INSERT and UPDATE operations. The ironclad rule is to write both.

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

Trap 5 — Compatibility with ORMs

As mentioned in Drizzle ORM Migration Guide 2026, lighter ORMs offer greater affinity with native database features. With Prisma, you issue SET LOCAL via raw queries, whereas with Drizzle, you can build a structure that reliably passes session variables inside db.transaction.

Testing Strategy: Guaranteeing in CI That Other Tenants' Data Is Invisible

The most vital test is the negative test verifying that "selecting tenant B's records within tenant A's session returns 0 rows." Automate this using Vitest / Jest table-driven tests and establish a mechanism to run it on every PR.

test.each(operations)("テナント越境を防ぐ: %s", async (op) => {
  await setTenant(tenantA);
  await op.create();           // テナント A で作成
  await setTenant(tenantB);
  expect(await op.findAll()).toHaveLength(0);  // B からは見えない
});

Combining this type of test with an "E2E automation infrastructure" to penetrate all the way through the API layer represents the ideal quality assurance model in custom development.

Conclusion: "Not Over-Engineering in the App" Is the Key to Multi-Tenant Design

When you try to enforce multi-tenancy boundaries solely within the application layer, humans inevitably make mistakes. Pushing boundaries into the DB so the app only has to pass session variables allows you to achieve both development velocity for new features and rock-solid safety.

At GleamHub, we assist clients from initial SaaS design through RLS policy formulation and testing infrastructure setup. If you are building a new startup SaaS or looking to multi-tenant an existing system, please feel free to reach out through our contact page.