Keep Your Tenants Apart with Postgres Row Level Security

Why Data Leaks Between Tenants Can Kill Your SaaS

PostgreSQL row level security for multi tenant applications is the database-enforced mechanism that ensures each customer only ever sees their own data — automatically, at the query level, regardless of what your application code does.

Here’s the quick answer on how it works:

  1. Enable RLS on a table — ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
  2. Create a policy — define a USING clause that filters rows by tenant ID
  3. Set tenant context — pass the current tenant’s ID as a session variable before each query
  4. Connect as a non-owner role — so policies are never bypassed by default
  5. Every query is automatically filtered — PostgreSQL injects the policy as a WHERE clause, invisibly

No changes to your application SQL required. If a query forgets a WHERE clause, the database returns zero rows — not another tenant’s data.

Here’s why this matters in practice: imagine a developer ships SELECT * FROM invoices WHERE id = $1 — forgetting to include a tenant filter. Without RLS, that query could return data belonging to any customer. With RLS enabled, PostgreSQL silently restricts the result to only rows owned by the current tenant. The mistake becomes structurally harmless.

For SaaS teams managing analytics dashboards, embedded reporting, or any shared database infrastructure, this is the difference between a security incident and a non-event.

PostgreSQL has included Row Level Security since version 9.5, and it’s supported by Amazon RDS and Aurora PostgreSQL. Despite being available for over a decade, many teams still rely on application-layer WHERE clauses alone — leaving them one forgotten filter away from a cross-tenant data leak.

RLS workflow: tenant context set to policy evaluated to rows filtered to query returns tenant-only data infographic

Postgresql row level security multi tenant vocab to learn:

Multi-Tenant Architecture: Silo vs. Bridge vs. Pool Models

When building a Software as a Service (SaaS) platform, deciding how to partition tenant data is one of the most critical structural decisions you will make. This choice dictates your infrastructure costs, operational complexity, and data security profile.

Silo vs Bridge vs Pool multi-tenant database models

In multi-tenant systems, we generally classify data partitioning into three distinct models:

  • The Silo Model (Database-per-Tenant): In this model, every tenant gets their own completely isolated database instance. This provides the highest level of physical isolation, which is highly prized by enterprise legal teams. However, it scales poorly. If you have 3,000 tenants, you must manage 3,000 connection pools and run 3,000 parallel migrations every time you update your schema.
  • The Bridge Model (Schema-per-Tenant): Tenants share the same database instance but are separated into distinct database schemas. This is often seen as a middle ground, but it frequently combines the worst of both worlds. At scale, schema migrations still require running non-blocking DDL statements across thousands of individual schemas, which can easily lock up database resources and create massive administrative overhead.
  • The Pool Model (Shared Database, Shared Schema): All tenants share the same database and the same tables. Every table containing tenant-specific information includes a tenant identifier column (such as tenantid or accountid). This model is highly cost-effective, incredibly easy to scale, and simplifies global analytics and reporting. However, it relies entirely on logical isolation to prevent cross-tenant data leaks.

Choosing the wrong partitioning model early can lead to severe technical debt. If you start with a simple pooled model but scale into enterprise clients requiring physical data residency, you may find yourself blocked by a massive, multi-quarter migration project. For a deeper dive into these strategic pitfalls, read Multi-Tenant Data Models will betray you if you pick the wrong one early.

Single-Tenant vs. Multi-Tenant Architecture

In a pure single-tenant architecture, each customer has a dedicated application instance and database. While this offers maximum isolation, it severely limits SaaS scalability. Operational complexity grows linearly with your customer count.

Multi-tenant architecture, by contrast, allows thousands of customers to share the same application deployment and hardware resources. This maximizes infrastructure utilization and dramatically simplifies deployments, bug fixes, and system maintenance. The core engineering challenge of multi-tenancy is enforcing logical tenant isolation so robustly that it behaves like physical single-tenant isolation. This is precisely where a postgresql row level security multi tenant setup shines.

How PostgreSQL Row Level Security Multi Tenant Isolation Works

Row Level Security (RLS) acts as an automated, database-enforced safety net. Instead of relying on your application developers to manually append “WHERE tenant_id = X” to every single SQL query, PostgreSQL evaluates access permissions at the engine level.

If a query is executed, the PostgreSQL engine automatically intercepts it, evaluates the active security policies for the target table, and appends the appropriate filtering conditions before retrieving or modifying any rows. This means that even if a developer writes a broad “SELECT * FROM invoices” statement, the database will only return the rows belonging to the active tenant session. To understand more about the underlying mechanics, explore our comprehensive guide on Row Level Security.

Using RLS allows SaaS providers to safely adopt the cost-effective pooled database model without compromising on data security. For a detailed breakdown of how major cloud providers handle this, see the guide on Multi-tenant data isolation with PostgreSQL Row Level Security.

The Core Mechanics of RLS Policies

When you configure RLS on a table, you define policies that evaluate to a Boolean value. If the policy returns true for a given row, that row is processed; if it returns false, the row is silently ignored for reads, or rejects modifications with an error.

There are two primary clauses used when defining RLS policies:

  • USING clause: This defines the condition applied to existing rows in the table. It is evaluated during SELECT, UPDATE, and DELETE operations. If a row does not match the USING clause, it is treated as if it does not exist.
  • WITH CHECK clause: This defines the condition applied to new rows being inserted or modified. It is evaluated during INSERT and UPDATE operations. If an application attempts to write a row that violates this condition, PostgreSQL throws an explicit security violation error.

By default, tables do not enforce row-level security. To activate it, you must run:

ALTER TABLE table_name ENABLE ROW LEVEL SECURITY;

However, simply enabling RLS is not enough. By default, the table owner and superusers bypass RLS policies. To guarantee that policies are enforced even on the table owner, you must also execute:

ALTER TABLE table_name FORCE ROW LEVEL SECURITY;

Security Considerations: Table Owners and Superusers

A common pitfall when implementing RLS is connecting your application to the database using a superuser role or the table owner account. PostgreSQL superusers and any database role created with the BYPASSRLS attribute are completely immune to table policies.

To ensure your RLS policies are actually enforced, your application must connect using a dedicated, limited-privilege database role (often named app_user or application).

If you need to perform administrative tasks, run background migrations, or execute cross-tenant reporting, you can use security definer functions. These PL/pgSQL functions run with the privileges of the user who created them (the owner), allowing you to safely bypass RLS boundaries for specific, highly controlled operations without exposing the entire database to application-layer bugs.

Step-by-Step Guide to Implementing RLS Policies

Let’s walk through a practical implementation of a postgresql row level security multi tenant database. We will design a schema, configure tenant context, and write policies that automatically isolate data.

Step-by-step SQL commands to enable RLS and apply policies

To get started, we need a robust foundation. For a comprehensive overview of setting up tenant infrastructure, refer to How to Secure Multi-Tenant Data with Row-Level Security in PostgreSQL.

Designing a PostgreSQL Row Level Security Multi Tenant Schema

The first rule of multi-tenant schema design is consistency: every tenant-scoped table must include a tenant identifier column (e.g., tenantid or accountid). This identifier should be a non-nullable UUID or integer referencing your central tenants table.

For example, a standard schema might look like this:

CREATE TABLE tenants ( id UUID PRIMARY KEY DEFAULT genrandomuuid(), name TEXT NOT NULL );

CREATE TABLE projects ( id UUID PRIMARY KEY DEFAULT genrandomuuid(), tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE, name TEXT NOT NULL );

To ensure this scales efficiently, you must create composite indexes on your tenant-scoped tables. Every index on a shared table should start with the tenant_id column:

CREATE INDEX idxprojectstenantsearch ON projects (tenantid, name);

This structure allows the PostgreSQL query planner to perform partition pruning, immediately discarding rows from other tenants and locating the relevant data with minimal disk I/O. For further reading on schema design patterns, see Multi Tenant Row Level Security.

Implementing Session Variables for Tenant Context

Instead of creating a separate PostgreSQL database user for every single tenant—which destroys connection pooling and wastes database resources—the industry standard is to use session variables.

PostgreSQL allows you to define custom runtime parameters using session-scoped or transaction-scoped variables via the current_setting and set_config functions.

First, we define our RLS policy on the projects table using a session variable named app.current_tenant_id:

CREATE POLICY tenantisolationpolicy ON projects FOR ALL TO application USING (tenantid = NULLIF(currentsetting(‘app.currenttenantid’, true), ”)::UUID);

In this policy, the second argument of current_setting is set to true, which ensures that if the variable has not been initialized yet, PostgreSQL returns NULL instead of throwing an error.

Before executing any queries on behalf of a tenant, your application must set this variable within the current transaction block:

BEGIN; SELECT setconfig(‘app.currenttenant_id’, ‘123e4567-e89b-12d3-a456-426614174000’, true); SELECT * FROM projects; COMMIT;

Setting the third argument of set_config to true makes the parameter local to the current transaction. This is critical for preventing security leaks when using connection poolers. For more detailed code snippets and implementation strategies, check out Postgres Row Security.

Handling Migrations, Views, and Triggers

As your SaaS application grows, managing database migrations and schema updates requires careful planning. If you use migration tools like Alembic or Prisma, you must ensure that your migration runner connects with administrative privileges (bypassing RLS) so it can execute DDL changes without restriction.

When working with database views, standard views in PostgreSQL run with the privileges of the view owner. If you create a view over an RLS-protected table, you must ensure the view is configured to respect RLS, or define it as a security barrier view.

Additionally, to prevent developers from accidentally inserting rows with the wrong tenant ID, or forgetting to populate it entirely, you can implement a database trigger:

CREATE FUNCTION settenantidfromcontext() RETURNS TRIGGER AS $$ BEGIN NEW.tenantid := NULLIF(currentsetting(‘app.currenttenantid’, true), ”)::UUID; RETURN NEW; END; $$ LANGUAGE plpgsql;

CREATE TRIGGER autotenantidtrigger BEFORE INSERT ON projects FOR EACH ROW EXECUTE FUNCTION settenantidfrom_context();

This trigger guarantees that during any INSERT operation, the tenant_id is automatically populated from the active transaction context, perfectly matching the automatic filtering behavior of your RLS read policies.

Integrating RLS with Application Frameworks and Connection Pools

Implementing RLS at the database level is only half the battle; your application backend must be configured to seamlessly pass the tenant context with every single database query.

When using server-side connection pooling tools like pgBouncer, multiple application requests share a small pool of persistent database connections. If you set a session variable using SET, that variable remains active on the connection even after the request finishes, potentially exposing the next tenant’s request to a major data leak. To eliminate this risk, you must always use SET LOCAL or set_config(..., true) within an explicit database transaction, ensuring the tenant context is automatically wiped clean the moment the transaction completes.

For a complete architectural checklist on avoiding connection pool leaks, read PostgreSQL Row-Level Security: 7 Proven Steps, Zero Leaks.

Connecting Application Frameworks to a PostgreSQL Row Level Security Multi Tenant Database

Modern ORMs and web frameworks can be extended to handle tenant context automatically, removing the burden from your business logic.

If you are using Prisma ORM, you can leverage Prisma Client Extensions to intercept queries and prepend the tenant context transaction block automatically. This ensures that every query executed by your Prisma client is safely scoped without requiring manual SQL wrapping in your service files.

For Python developers using FastAPI and SQLAlchemy, you can implement a dependency that fetches the tenant ID from the incoming JWT or request headers, opens a database session, and immediately executes a SET LOCAL app.current_tenant_id statement.

For a production-ready template demonstrating these integration patterns in Next.js and Supabase, explore Multi-Tenant SaaS Architecture with Postgres RLS: A Working Pattern.

Performance and Scalability Implications of RLS

A common concern among engineers is whether RLS introduces a performance penalty. Because RLS policies act as automated WHERE clauses, they do not add significant execution overhead—provided your indexes are designed correctly.

If you query an RLS-protected table without a composite index starting with tenant_id, PostgreSQL will be forced to perform a full table scan across all tenants’ data to evaluate the policy. This can quickly lead to high query latencies and CPU spikes as your database grows to millions of rows.

To debug and optimize your RLS performance, always use the EXPLAIN ANALYZE command:

EXPLAIN ANALYZE SELECT * FROM projects;

The output will show you exactly how the RLS policy is being rewritten and whether the query planner is successfully utilizing your composite indexes. For more information on scaling row-level security across enterprise-grade datasets, see Centralized Row Level Security.

Frequently Asked Questions about PostgreSQL RLS

We have compiled some of the most common questions and troubleshooting tips from developers building multi-tenant SaaS platforms on PostgreSQL.

What is the difference between using database users and session variables for RLS?

Using database users (roles) means creating a separate PostgreSQL role for every single tenant (e.g., tenantcompanya). While this provides native isolation, it makes connection pooling virtually impossible because connection poolers cannot share database connections across different users.

Using session variables (via current_setting) allows your application to connect using a single, shared database role while dynamically passing the active tenant’s context. This is highly scalable, compatible with pgBouncer, and is the industry-standard approach for modern SaaS applications.

Does Row Level Security affect query performance at scale?

Only if your indexing strategy is neglected. With composite indexes that lead with the tenant identifier column, the query planner can immediately discard irrelevant rows. The performance overhead of RLS in a well-indexed database is negligible (typically under 1-2%), making it highly suitable for high-throughput production workloads.

When should I use schema-per-tenant instead of RLS?

While a shared schema with RLS is the ideal default for 99% of SaaS applications, a schema-per-tenant or database-per-tenant model may be preferable if:

  • You serve highly regulated enterprise clients (e.g., in healthcare or banking) with strict legal contracts mandating physical data separation.
  • Your customers require highly customized database schemas, custom tables, or direct database access.
  • You must support physical data residency requirements, where a customer’s data must reside on servers physically located in a specific geographic region.

Conclusion

Implementing a robust postgresql row level security multi tenant architecture is the single most effective way to secure your shared SaaS database. By moving the responsibility of tenant isolation from vulnerable application-layer code directly into the database engine, you create an immutable security boundary that protects your business from catastrophic data leaks.

At Embedportal, we understand how challenging it can be to coordinate secure data isolation across your entire software stack—especially when it comes to reporting and analytics.

Our white-label embedding platform allows you to embed multi-vendor BI dashboards (including Tableau, Power BI, QuickSight, and Metabase) with unified branding, single sign-on (SSO), and robust row-level security in under an hour. We handle the complex mapping of tenant identities so your users only ever see the data they are authorized to view, leaving you free to focus on building your core product.

Ready to secure your reporting layer? Secure your embedded dashboards with Embedportal’s Row Level Security and explore our resources on Multi Tenant Analytics to scale your SaaS with confidence.

Related Posts

Scroll to Top