Row-Level Security for Multi-Tenant SaaS: PostgreSQL Guide

Every shared-schema multi-tenant SaaS starts the same way. You add a tenant_id column to every table, write a middleware that injects the tenant context, and rely on your ORM or query builder to include WHERE tenant_id = ? on every query. This works until it does not. A junior engineer writes a raw SQL query and forgets the filter. Someone builds an admin endpoint that accidentally bypasses the tenant scope. A migration script runs an UPDATE without a WHERE clause. One slip and you have a cross-tenant data breach that triggers notification obligations under GDPR, CCPA, and whatever regulation your customers care about.

Application-level filtering is a necessary layer, but treating it as your only layer is a liability. You are betting that every developer on your team, including future hires, will never forget a tenant filter on any query, in any context, under any deadline pressure. That is not a reasonable assumption.

PostgreSQL's Row-Level Security (RLS) solves this by pushing tenant isolation into the database engine itself. When RLS is enabled and configured correctly, the database will refuse to return, insert, update, or delete rows that do not belong to the current tenant, regardless of what your application code does. It is a hard boundary, not a convention. Your application code should still scope queries to the correct tenant for clarity and performance, but RLS acts as the safety net that catches every mistake your application layer misses.

If you are building a multi-tenant SaaS architecture on a shared PostgreSQL database, RLS should be a non-negotiable part of your data isolation strategy. The setup cost is measured in hours. The cost of a cross-tenant data leak is measured in lost customers and legal exposure.

RLS is a PostgreSQL feature that lets you define policies controlling which rows a given database role can see or modify. When you enable RLS on a table, PostgreSQL appends your policy conditions to every query against that table, transparently and automatically. No ORM plugin required. No middleware. The database engine handles it.

The setup involves three steps. First, you enable RLS on a table:

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

Second, you create a policy that defines the access rule:

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

Third, your application sets the session variable before running any queries:

SELECT set_config('app.current_tenant', '550e8400-e29b-41d4-a716-446655440000', true);

The true parameter in set_config means the setting is local to the current transaction. This is critical when using connection pooling, because you do not want one request's tenant context leaking into the next request that reuses the same connection.

Policy Types You Need to Know

PostgreSQL supports separate policies for different operations. A USING clause controls which rows are visible for SELECT, UPDATE, and DELETE. A WITH CHECK clause controls which rows can be inserted or updated. For multi-tenant isolation, you typically want both:

CREATE POLICY tenant_select ON orders FOR SELECT USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_insert ON orders FOR INSERT WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_update ON orders FOR UPDATE USING (tenant_id = current_setting('app.current_tenant')::uuid) WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_delete ON orders FOR DELETE USING (tenant_id = current_setting('app.current_tenant')::uuid);

The UPDATE policy needs both clauses: USING controls which rows you can target, and WITH CHECK ensures you cannot change the tenant_id to hijack a row into a different tenant's scope. Without WITH CHECK on updates, a malicious or buggy query could reassign rows to another tenant.

One important detail: RLS is enforced per database role. Table owners and superusers bypass RLS by default. Your application should connect to PostgreSQL using a restricted role, not the superuser role. If you are using the postgres superuser for your application connections, RLS is doing nothing for you.

Setting the tenant context correctly on every database interaction is where most RLS implementations succeed or fail. The approach varies depending on your connection pooling strategy and application framework.

Transaction-Scoped Context (Recommended)

The safest approach is setting the tenant context at the start of every transaction. Use set_config('app.current_tenant', $1, true) where the third argument is true to scope the setting to the current transaction. When the transaction commits or rolls back, the setting disappears. This prevents tenant context from leaking between requests that share a pooled connection.

In practice, your middleware or data access layer should wrap every database operation in a transaction that begins with the tenant context:

BEGIN; SELECT set_config('app.current_tenant', '550e8400-...', true); SELECT * FROM orders WHERE status = 'active'; COMMIT;

This pattern works correctly with PgBouncer in transaction pooling mode, which is the standard for high-throughput SaaS applications. In transaction mode, PgBouncer assigns a server connection for the duration of a transaction, then returns it to the pool. Since your tenant context is transaction-scoped, it is guaranteed to be correct for each request.

Session-Scoped Context (Use with Caution)

If you are using PgBouncer in session pooling mode or direct connections without pooling, you can set the tenant context once per connection with set_config('app.current_tenant', $1, false). This persists for the entire session. The risk here is forgetting to reset the context when the connection is returned to a pool and reused by a different tenant's request. Session-scoped context is simpler but more dangerous.

Supabase and Managed Platforms

Supabase handles much of this automatically. When a user authenticates through Supabase Auth, the JWT contains the user's ID and metadata. You can reference auth.uid() and auth.jwt() directly in your RLS policies. Supabase sets these values from the JWT before executing queries, so you do not need to manage session variables manually. A typical Supabase RLS policy looks like:

CREATE POLICY tenant_isolation ON orders USING (tenant_id = (auth.jwt() ->> 'org_id')::uuid);

This is one of the strongest arguments for using Supabase for multi-tenant SaaS. The RLS integration is first-class, and the footguns around connection pooling and session context are largely eliminated. Neon offers a similar developer experience with its serverless driver, where you can pass tenant context per query without managing persistent connections at all.

The most common objection to RLS is performance overhead. The concern is valid but often overstated. Here is what actually happens and how to mitigate it.

When RLS is enabled, PostgreSQL rewrites every query to include the policy conditions. A simple SELECT * FROM orders WHERE status = 'active' becomes, internally, SELECT * FROM orders WHERE status = 'active' AND tenant_id = current_setting('app.current_tenant')::uuid. The query planner sees both conditions and uses indexes accordingly. If you have the right indexes, the overhead is negligible.

The Index You Must Have

Every table with RLS enabled needs a composite index that leads with tenant_id. For the orders table example:

CREATE INDEX idx_orders_tenant_status ON orders (tenant_id, status);

Without this index, every query does a sequential scan filtered by tenant_id. On a table with 10 million rows across 500 tenants, that means scanning 10 million rows to find the 20,000 that belong to one tenant. With the composite index, PostgreSQL jumps directly to that tenant's rows. The difference is often 100x in query time: 2ms with the index versus 200ms+ without it.

For tables where you frequently query by tenant_id plus a date range (extremely common for SaaS dashboards and reports), consider a BRIN index on the date column alongside the B-tree on tenant_id. BRIN indexes are compact and work well when data is naturally ordered by insertion time.

Measured Overhead

We have benchmarked RLS overhead on PostgreSQL 16 across multiple workloads. On a well-indexed table with 5 million rows, the RLS policy evaluation adds roughly 0.05ms to 0.2ms per query. For most SaaS applications running queries in the 5ms to 50ms range, that overhead is under 1%. On complex queries with multiple joins, the overhead is even less significant relative to total query time.

Where RLS can cause noticeable slowdowns is on queries that join many RLS-enabled tables. Each joined table gets its own policy evaluation. A query joining 6 tables, each with RLS, adds 6 policy evaluations. Optimize by reducing unnecessary joins and making sure every joined table has the tenant_id index.

Partition by Tenant for Large Scale

If individual tables grow past 50 million rows, consider partitioning by tenant_id (or by a hash of tenant_id). PostgreSQL's native partitioning combined with RLS gives you partition pruning: the query planner skips entire partitions that cannot contain the current tenant's data. This is a massive performance win at scale. For tables under 10 million rows, simple indexing is sufficient and partitioning adds unnecessary complexity.

Your ORM needs to set the tenant context before every query. How you do this depends on which ORM you are using and how it manages connections.

Drizzle ORM

Drizzle gives you low-level control over connections, which makes RLS integration straightforward. You can use Drizzle's transaction API to wrap every operation with the tenant context:

await db.transaction(async (tx) => { await tx.execute(sql`SELECT set_config('app.current_tenant', ${tenantId}, true)`); return tx.select().from(orders).where(eq(orders.status, 'active')); });

For cleaner code, wrap this in a utility function that accepts a tenant ID and a callback. Every database operation in your application goes through this function. Drizzle's lightweight design means you are not fighting the framework to inject this behavior. If you are choosing a database technology for your SaaS, PostgreSQL with Drizzle gives you the most control over RLS integration.

Prisma

Prisma's client extensions (available since Prisma 4.16) let you hook into every query. You can create a tenant-scoped Prisma client that automatically sets the session variable:

const tenantPrisma = prisma.$extends({ query: { async $allOperations({ args, query }) { await prisma.$executeRaw`SELECT set_config('app.current_tenant', ${tenantId}, true)`; return query(args); } } });

The caveat with Prisma is connection management. Prisma uses its own connection pool, which does not support PgBouncer's transaction mode cleanly in all configurations. As of Prisma 5.x, you need to set pgbouncer=true in your connection string and use ?pgbouncer=true&connection_limit=1 to avoid prepared statement conflicts. Test this thoroughly in staging before deploying.

Raw SQL and Query Builders

If you are using a lightweight query builder like Kysely or Slonik, the pattern is the same: begin a transaction, set the tenant context, run your queries, commit. These libraries give you full control and zero magic, which is honestly preferable for security-critical code. You can see exactly what SQL is being executed and verify that the tenant context is set before any data access.

Regardless of your ORM choice, never rely solely on the ORM's built-in filtering (like Prisma's where: { tenantId }) without also enabling RLS. The ORM filter is your primary control. RLS is your safety net. Both should be active.

RLS policies are security-critical code. They need dedicated tests, not just a quick manual check after deployment.

How to Test RLS Policies

Write integration tests that create two tenants, insert data for both, set the session context to Tenant A, and verify that only Tenant A's data is returned. Then switch context to Tenant B and verify the same. Test all four operations: SELECT, INSERT, UPDATE, DELETE. A solid test suite should verify these scenarios:

• Positive access: Tenant A can read, create, update, and delete their own rows.
• Negative access: Tenant A cannot read, update, or delete Tenant B's rows.
• Cross-tenant insert prevention: Tenant A cannot insert a row with Tenant B's tenant_id.
• Tenant ID mutation prevention: Tenant A cannot UPDATE a row and change its tenant_id to Tenant B's ID.
• No context set: When no tenant context is set, the query returns zero rows (not all rows).

That last test is particularly important. If your RLS policy uses current_setting('app.current_tenant', true) (the second argument makes it return NULL instead of erroring when the setting does not exist), and your policy is USING (tenant_id = current_setting(...)::uuid), then a NULL comparison will correctly return no rows. But if your policy accidentally uses a different comparison or a fallback value, an unset context could return all rows. Test this explicitly.

Pitfall 1: Forgotten Tables

The most common RLS failure is simply forgetting to enable it on a table. You add a new feature, create a new table with a tenant_id column, write your migration, and forget the ALTER TABLE ... ENABLE ROW LEVEL SECURITY and CREATE POLICY lines. That table is now completely unprotected. Build a CI check that queries pg_tables and pg_policies to verify every table with a tenant_id column has RLS enabled and at least one policy defined. Fail the build if any table is missing.

Pitfall 2: Superuser Bypass

PostgreSQL superusers and table owners bypass RLS by default. If your application connects as the postgres user (which is the default on many managed databases), your RLS policies are not enforced. Create a dedicated application role with restricted privileges: CREATE ROLE app_user LOGIN; GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;. Your application should connect as this role, never as the superuser.

Pitfall 3: Migration Gaps

When you add a new column or modify a table, your migration might temporarily disable RLS or drop and recreate policies. If a deployment runs the migration and then crashes before recreating the policy, you have a window where the table is unprotected. Structure your migrations so RLS policies are created in the same transaction as the table modification, and never disable RLS as part of a migration unless absolutely necessary.

Pitfall 4: Aggregate Queries and Admin Endpoints

Internal admin dashboards and background jobs often need to query across all tenants. If these run through the same database role with RLS enabled, they will only see one tenant's data (or no data if no context is set). Create a separate database role for admin operations that has BYPASSRLS privilege, and use this role only for trusted internal services. Never expose this role to user-facing application code.

You have three options for tenant data isolation in a shared-schema SaaS. Understanding when each one is the right fit saves you from over-engineering or under-protecting your system.

Application-Level Filtering Only

Every query includes a WHERE tenant_id = ? filter enforced by your ORM or middleware. This is the simplest approach and requires zero PostgreSQL-specific configuration. It works with any database, including MySQL, MongoDB, or DynamoDB. The downside is obvious: one missed filter and you have a data breach. There is no safety net. For very early-stage products (pre-revenue, single developer, no compliance requirements), this is acceptable as a starting point. Plan to add RLS before you have paying customers handling sensitive data.

Application Filtering Plus RLS

This is the recommended approach for 90% of shared-schema SaaS products. Your application still scopes every query to the current tenant for performance and clarity. RLS acts as the database-level enforcement that catches any mistakes. The additional setup cost is a few hours of writing policies and configuring the session context. The ongoing cost is remembering to add RLS policies when you create new tables, which you automate with CI checks.

For a deeper comparison of shared-schema versus isolated approaches, see our guide on database per tenant vs shared schema for multi-tenant SaaS.

Database Per Tenant

Each customer gets their own database. Isolation is absolute: there is no tenant_id column, no RLS policy, no chance of cross-tenant data leakage through a query bug. This is the right choice when compliance mandates physical data separation (HIPAA, FedRAMP), when individual tenants need independent scaling, or when your contract values are high enough ($1,000+/month) to justify the infrastructure cost. The tradeoff is operational complexity: connection routing, per-tenant migrations, per-tenant backups, and per-tenant monitoring.

When to Choose What

• Pre-revenue to $50K ARR: Application filtering only. Ship fast, add RLS before your first enterprise deal.
• $50K to $5M ARR: Application filtering plus RLS. This covers the vast majority of B2B SaaS products.
• $5M+ ARR or regulated industries: Application filtering plus RLS for most tenants, database per tenant for enterprise customers with isolation requirements. Offer isolation as a premium feature.

The cost difference is stark. RLS on a shared database adds zero infrastructure cost. Database per tenant adds $13 to $370+ per month per tenant depending on instance size. At 500 tenants, that is the difference between $840/month (shared with RLS) and $6,500+/month (database per tenant).

Supabase deserves its own section because it has done more to make RLS accessible than any other platform. If you are starting a new SaaS project and choosing between Supabase, Neon, or raw PostgreSQL on RDS, the RLS developer experience on Supabase is a genuine competitive advantage.

Built-In Auth Integration

Supabase Auth issues JWTs that are automatically available in your RLS policies via auth.uid() and auth.jwt(). You do not need to manage session variables or worry about connection pooling context. A policy like CREATE POLICY tenant_isolation ON orders USING (tenant_id = (SELECT org_id FROM profiles WHERE id = auth.uid())) works out of the box. The JWT is validated and the user context is set before any query executes.

Supabase Dashboard for Policy Management

The Supabase dashboard includes a visual RLS policy editor. You can enable RLS, create policies, and test them directly in the browser. For teams without deep PostgreSQL experience, this lowers the barrier significantly. You still need to understand what the policies do (do not blindly click through the UI), but the tooling reduces syntax errors and makes it easy to audit which tables have policies enabled.

Supavisor Connection Pooling

Supabase replaced PgBouncer with Supavisor, their custom connection pooler built in Elixir. Supavisor supports transaction-mode pooling with session variable passthrough, which means RLS context set via set_config works correctly even with pooled connections. On raw PgBouncer, you need to carefully configure ignore_startup_parameters and use transaction-scoped settings. Supavisor handles this automatically.

Limitations to Know

Supabase's free tier supports RLS fully, but the Pro tier ($25/month) is where you get the connection pooling, daily backups, and monitoring that production SaaS products need. Supabase also limits you to PostgreSQL, so if you need a secondary datastore (Redis for caching, Elasticsearch for search), you are managing those separately. And the RLS policies in Supabase are still standard PostgreSQL RLS, so everything in this guide about testing, indexing, and pitfalls applies equally.

Here is a concrete checklist for adding RLS to your multi-tenant SaaS. Follow this in order and you will have production-ready tenant isolation in a day or two of focused work.

Setup Phase

• Create a restricted database role for your application. Do not use the superuser. Grant only SELECT, INSERT, UPDATE, DELETE on application tables.
• Add tenant_id columns to every table that stores tenant-specific data. Use UUID type, not integer, to prevent enumeration attacks.
• Create composite indexes starting with tenant_id on every tenant-scoped table. At minimum: (tenant_id), and ideally (tenant_id, created_at) or (tenant_id, status) based on your query patterns.
• Enable RLS on every tenant-scoped table: ALTER TABLE [table] ENABLE ROW LEVEL SECURITY;
• Create policies for SELECT, INSERT, UPDATE, and DELETE on each table.

Application Integration

• Update your data access layer to call set_config('app.current_tenant', tenantId, true) at the start of every transaction.
• Verify connection pooling compatibility. If using PgBouncer in transaction mode, use transaction-scoped settings (third argument = true). If using Supabase, Supavisor handles this for you.
• Keep application-level tenant filtering in your ORM queries. Do not remove WHERE tenant_id clauses just because RLS exists. Both layers should be active.

Testing and CI

• Write integration tests covering positive access, negative access, cross-tenant insert prevention, tenant ID mutation prevention, and unset context behavior.
• Add a CI check that queries pg_tables and pg_policies to verify every table with a tenant_id column has RLS enabled.
• Test with the application database role, not the superuser. Your tests should use the same role your production application uses.

Monitoring

• Monitor RLS policy evaluation time using pg_stat_statements. Watch for queries where policy evaluation adds more than 1ms.
• Set up alerts for any query that returns rows from multiple tenants (this should never happen with RLS enabled).
• Audit new table migrations to ensure RLS policies are created in the same migration that creates the table.

RLS is one of those rare engineering investments that pays for itself immediately. The setup cost is a few hours. The alternative, a cross-tenant data breach, can cost you your biggest customers and months of recovery work. If you are running a multi-tenant SaaS on PostgreSQL without RLS, adding it should be at the top of your backlog.

Need help implementing RLS for your multi-tenant SaaS, or designing your tenant isolation strategy from scratch? We have built secure multi-tenant systems for dozens of SaaS companies across healthcare, fintech, and enterprise B2B. Book a free strategy call and we will map out the right approach for your product.