What is row-level security in Postgres?

Row-level security (RLS) is a Postgres feature that lets you attach policies to a table so that each query only sees or changes the rows the current user or tenant is allowed to access. The database enforces the rule, so a missing filter in application code no longer exposes other tenants' data.

Does row-level security replace tenant filters in my application code?

No. Most teams keep tenant filters in the application and add RLS as a second, database-level safety net. If a developer forgets a WHERE clause, RLS still blocks rows from other tenants from being returned.

Will RLS slow down my SaaS database?

Simple policies that compare a tenant_id column to a session setting usually add little overhead, as long as tenant_id is indexed and leads your composite indexes. Complex policies with subqueries or function calls can slow queries, so keep policies simple and check query plans.

Can I use RLS with connection pooling like PgBouncer?

Yes, but you must set the tenant context per transaction, for example with SET LOCAL or set_config with the local flag inside a transaction. Session-level settings can leak between requests when connections are shared in transaction pooling mode.

How do I test that row-level security is working?

Write automated tests that create two tenants, insert data for each, set the context to tenant A and assert that tenant B's rows are invisible for reads, updates and deletes. Run these tests in CI with a database role that does not bypass RLS.

Postgres Row-Level Security for Multi-Tenant SaaS: A Build Guide

Every multi-tenant SaaS product carries one fear that keeps founders awake: a customer logs in and sees another customer's data. It rarely happens because of a dramatic hack. It usually happens because one query, written in a hurry, forgot a WHERE tenant_id = ? clause. In a codebase with hundreds of queries, written by several developers over a few years, that kind of mistake is not a question of if but when.

Postgres row-level security (RLS) gives you a second line of defence. Instead of trusting every query to filter correctly, you teach the database itself which rows each tenant is allowed to see. If the application forgets the filter, the database applies it anyway. This guide explains how RLS works, how to roll it out in an existing SaaS product, and the pitfalls that trip teams up in production.

If you are still deciding between shared tables, separate schemas or separate databases, start with our explainer on data isolation models for multi-tenant SaaS architecture. RLS is most useful in the shared-table model, which is where most early-stage SaaS products begin.

What Row-Level Security Actually Does

RLS is a Postgres feature that attaches policies to a table. A policy is a boolean expression the database evaluates for every row a query touches. Rows where the expression is false are silently filtered out of reads, and writes that would violate the policy are rejected.

A typical tenant policy looks something like this in plain terms: "a row in the invoices table is visible only if its tenant_id matches the tenant ID set for the current request." The application sets that tenant ID at the start of each request or transaction, and Postgres does the rest.

The building blocks

ALTER TABLE ... ENABLE ROW LEVEL SECURITY switches RLS on for a table. Without any policies, a table with RLS enabled returns no rows to roles that are subject to it.
CREATE POLICY defines who can see or change which rows. Policies can apply to SELECT, INSERT, UPDATE, DELETE or ALL commands.
USING clauses filter which existing rows are visible or affected.
WITH CHECK clauses validate new or updated rows, so nobody can insert a row tagged with another tenant's ID.
Session settings such as current_setting('app.tenant_id') carry the tenant context from your application into the policy.

Who bypasses RLS

Table owners and superusers bypass RLS by default. This catches many teams out: their application connects as the same role that owns the tables, so the policies never apply. You either need a separate, less privileged application role or you must use ALTER TABLE ... FORCE ROW LEVEL SECURITY so that even the owner is subject to the policies.

Why Application Filters Alone Are Not Enough

Application-level filtering is still important. It keeps queries efficient and makes intent clear. But it depends on every developer remembering it on every query, including:

Ad hoc reporting queries written for one customer's request.
Background jobs that loop over records and were never reviewed as carefully as API endpoints.
New endpoints added under deadline pressure.
Raw SQL used to work around an ORM limitation.
Code generated quickly with AI assistants and merged without a careful review.

RLS turns tenant isolation from a convention into a rule. That shift matters even more once you add role-based permissions on top. Our guide to designing role-based access control for SaaS permissions covers the layer above tenancy, where you decide what each user inside a tenant can do.

A Real-World Example: A B2B Inventory SaaS Adding RLS

Consider a B2B inventory management SaaS used by a few hundred distributors. It runs on a single Postgres database with shared tables, and every table carries a tenant_id column. The team is small, the product has grown quickly, and a security questionnaire from a larger prospect asks a blunt question: "How do you guarantee one customer cannot access another customer's data?"

The honest answer before RLS was "our developers add a tenant filter to every query." That answer could lose an enterprise deal. A practical plan for a team like this could look like:

Create a dedicated app_user database role that does not own any tables.
Add a small middleware step that opens a transaction and runs SELECT set_config('app.tenant_id', $1, true) with the tenant from the authenticated session.
Enable RLS on a handful of the most sensitive tables first, such as customers, orders and invoices.
Run the full test suite and a staging soak period, watching for queries that suddenly return empty results.
Roll RLS out to the remaining tenant tables in batches.

Empty results during staging are a good sign, not a bad one. Each one points to a code path that was running without tenant context, which is exactly the kind of hidden risk RLS is meant to surface. Once the rollout is complete, the team can answer the questionnaire with confidence: isolation is enforced by the database, tested in CI, and independent of individual queries.

Step-by-Step: Rolling Out RLS in an Existing SaaS

Audit your schema. List every table that holds tenant data and confirm each one has a non-null tenant_id column. Child tables that only reference a parent (for example, order_items pointing to orders) are much easier to protect if you denormalise tenant_id onto them too.
Index tenant_id properly. Make tenant_id the leading column in the composite indexes your common queries use. RLS adds a tenant condition to every query, so indexes that start with tenant_id help the planner a great deal.
Create a non-owner application role. Your migrations can run as the owner, while the running application connects as a role with only the grants it needs. Alternatively, use FORCE ROW LEVEL SECURITY if you must keep a single role.
Decide how to pass tenant context. The most common approach is a custom setting such as app.tenant_id, set with set_config(..., true) so it only lasts for the current transaction. This is important if you use a connection pooler in transaction mode.
Write the policies. Start with one policy per table that covers ALL commands, using the same expression in both USING and WITH CHECK. Keep it simple: compare tenant_id to the current setting cast to the correct type.
Handle the missing-context case. Decide what happens if no tenant is set. Returning zero rows is the safe default. Some teams make the setting lookup raise an error instead, so missing context fails loudly in logs rather than quietly showing blank screens.
Plan for legitimate cross-tenant access. Internal admin tools, analytics jobs and migrations sometimes need to see all tenants. Give these a separate role with BYPASSRLS, used only by specific services and audited carefully.
Write isolation tests. Create two tenants in your test database, seed data for both, set context to tenant A and assert that tenant B's rows cannot be read, updated, deleted or inserted. Run these in CI on every pull request.
Roll out gradually. Enable RLS table by table in staging, then production. Monitor error rates and empty-result anomalies after each batch.
Document it. Write a short internal page explaining how tenant context works, which roles bypass RLS, and how to add policies to new tables. Add a migration checklist item so new tables never ship without a policy.

Common Pitfalls and How to Avoid Them

Context leaking between requests

If you set tenant context at the session level and use a pooler that hands the same connection to different requests, one tenant's context can leak into another tenant's request. Always set context inside a transaction with the local flag, and reset it if your framework holds connections across requests.

Slow policies

Policies that call functions with subqueries, such as "is this user a member of this tenant according to the memberships table," run for every row. That can turn a quick query into a slow one. Resolve membership once in the application, set a simple tenant ID, and keep the policy a plain equality check. If you are already seeing slow queries, run EXPLAIN ANALYZE before and after enabling the policy to see exactly what it costs.

Forgetting new tables

RLS only protects tables where it is enabled. Add a simple CI check that queries pg_tables and pg_class.relrowsecurity and fails the build if any table with a tenant_id column has RLS disabled.

Views and functions that run as their owner

Views and SECURITY DEFINER functions can execute with the privileges of their owner, which may bypass RLS. On recent Postgres versions you can create views with the security_invoker option so they respect the caller's policies. Review any security definer function carefully.

Background jobs without context

Queue workers often process jobs for many tenants. Make each job carry its tenant ID and set context at the start of the job's transaction, exactly as the web request path does.

RLS does not replace good application design. It makes a single forgotten filter a harmless bug instead of a data breach.

Key Benefits of Row-Level Security

Defence in depth: tenant isolation no longer depends on every query being perfect.
Stronger security answers: you can tell enterprise buyers and auditors that isolation is enforced at the database layer and tested automatically.
Safer use of raw SQL and reporting: analysts and developers can write queries without accidentally crossing tenant boundaries.
Faster reviews: code reviewers can focus on business logic instead of hunting for missing tenant filters.
Cleaner audit story: combined with access logs, RLS gives a clear account of who could see what. Pair it with well-designed audit logs for a complete compliance picture.

When RLS Might Not Be the Right Fit

RLS is a strong default for shared-table SaaS, but it is not always the answer. If you have a small number of very large enterprise tenants with strict data residency needs, separate databases per tenant may be simpler to explain and operate. If your data layer is spread across several databases or services, RLS protects only the Postgres parts. And if your ORM or framework makes it hard to control transactions, you may need some refactoring before RLS can be applied safely.

For many growing B2B products, a hybrid approach works well: shared tables with RLS for most customers, and dedicated databases for a few enterprise accounts that pay for that isolation.

How Mavani Solution Approaches Tenant Isolation

When we design multi-tenant products, we treat tenant isolation as an architectural decision made on day one, not a patch added after the first security questionnaire. Our SaaS development team typically combines tenant-aware application code, Postgres RLS, isolation tests in CI and clear documentation, so new developers inherit the safety net rather than having to rebuild it.

Conclusion

Postgres row-level security is one of the most practical security upgrades a multi-tenant SaaS can make. It costs relatively little to implement, it fits naturally into the shared-table model most startups already use, and it turns your most dangerous class of bug into a non-event. Start with a dedicated application role, set tenant context per transaction, keep policies simple, and back everything with automated isolation tests. Once it is in place, you can grow your customer base and your team with far more confidence that each customer's data stays exactly where it belongs.

Frequently Asked Questions

What is row-level security in Postgres?: Row-level security (RLS) is a Postgres feature that lets you attach policies to a table so that each query only sees or changes the rows the current user or tenant is allowed to access. The database enforces the rule, so a missing filter in application code no longer exposes other tenants' data.
Does row-level security replace tenant filters in my application code?: No. Most teams keep tenant filters in the application and add RLS as a second, database-level safety net. If a developer forgets a WHERE clause, RLS still blocks rows from other tenants from being returned.
Will RLS slow down my SaaS database?: Simple policies that compare a tenant_id column to a session setting usually add little overhead, as long as tenant_id is indexed and leads your composite indexes. Complex policies with subqueries or function calls can slow queries, so keep policies simple and check query plans.
Can I use RLS with connection pooling like PgBouncer?: Yes, but you must set the tenant context per transaction, for example with SET LOCAL or set_config with the local flag inside a transaction. Session-level settings can leak between requests when connections are shared in transaction pooling mode.
How do I test that row-level security is working?: Write automated tests that create two tenants, insert data for each, set the context to tenant A and assert that tenant B's rows are invisible for reads, updates and deletes. Run these tests in CI with a database role that does not bypass RLS.