PostgreSQL Row Level Security (RLS): Multi-Tenant Access Control in Practice

When Buildwise, a mid-stage SaaS company managing construction project data for hundreds of general contractors, reached out to JusDB last year, they had a serious problem. Their PostgreSQL application was using a single WHERE tenant_id = $1 clause in every query to keep tenant data isolated. The bug was subtle: three endpoints introduced over the previous quarter had been written without that filter. For six weeks, a contractor in Chicago could — with the right API call — see project cost estimates belonging to a firm in Phoenix. The exposure was discovered during a routine security audit, not by a customer, but the engineering team spent two weeks in crisis mode: auditing every query in the codebase, patching leaks, and adding integration tests to every future PR. Row-Level Security (RLS), applied at the database engine itself, would have made that class of bug structurally impossible.

PostgreSQL's Row-Level Security enforces data visibility rules at the storage layer — below the application, below the ORM, below the connection pool. A policy defined with CREATE POLICY rewrites every SELECT, INSERT, UPDATE, and DELETE against a table to include the tenant filter automatically. Your application code can forget the WHERE tenant_id clause and the database will supply it anyway. For multi-tenant SaaS on PostgreSQL, RLS is the closest thing to a silver bullet for tenant data isolation.

This guide covers everything a production engineering team needs: enabling RLS, writing policies with USING and WITH CHECK, setting tenant context via current_setting(), understanding the performance implications, and knowing the critical bypass gotchas that can silently neutralize your policies.

Background

Multi-tenant databases have three dominant isolation strategies: shared schema with a tenant discriminator column, separate schemas per tenant, and separate databases per tenant. Each exists on a spectrum of isolation versus operational complexity.

The shared-schema model — one projects table with a tenant_id column — is overwhelmingly the most common for SaaS products at seed through Series B. It is cheap to operate (one database to manage, one connection pool, one backup job), easy to develop against, and straightforward to migrate. The fatal weakness is that isolation is enforced entirely in application code. Every developer writing a query must remember the tenant filter. Every ORM scope must be set correctly. Every raw SQL query in a migration, analytics job, or background worker must include the right WHERE clause. One missed filter is a data leak.

Row-Level Security moves the enforcement responsibility from application code to the database engine. Policies are defined once, on the table, and PostgreSQL applies them automatically to every query against that table, regardless of which application, which connection, or which user runs it. This is defense-in-depth: even if every layer of your application is wrong, the database will not return rows to the wrong tenant.

RLS was introduced in PostgreSQL 9.5 (released January 2016) and has been production-hardened across thousands of deployments since. Supabase built their entire per-user authorization model on top of it. If you are on PostgreSQL 12 or later — which covers the vast majority of production deployments — you have a mature, well-tested implementation available.

How RLS Works

PostgreSQL's query planner, when executing a query against a table that has RLS enabled, appends the applicable policy's predicate to the query's WHERE clause before generating the execution plan. This happens inside the engine, invisibly to the client.

Consider a table projects with an RLS policy that says only return rows where tenant_id equals the current tenant setting. A developer writes:

SELECT * FROM projects WHERE status = 'active';

PostgreSQL rewrites this internally to something equivalent to:

SELECT * FROM projects
WHERE status = 'active'
  AND tenant_id = current_setting('app.current_tenant_id')::uuid;

The application never sees this rewrite. The extra filter is injected at plan time. The client receives only the rows it is allowed to see, and the query planner uses available indexes on tenant_id the same way it would for an explicit filter.

There are two distinct clauses in a policy:

• USING (expression) — applied on reads. Controls which rows are visible to SELECT, and which rows are eligible for UPDATE and DELETE operations. If a row does not satisfy the USING expression, it is invisible to the current session — it does not appear in results and cannot be updated or deleted.
• WITH CHECK (expression) — applied on writes. Controls which rows can be inserted or updated into the table. If an INSERT or UPDATE would produce a row that does not satisfy the WITH CHECK expression, the operation raises an error. If WITH CHECK is omitted on a permissive policy, the USING expression is used for both.

Policies are either permissive (the default) or restrictive. Permissive policies are OR-combined: if any permissive policy grants access, the row is visible. Restrictive policies are AND-combined with all permissive policies: every restrictive policy must pass for a row to be visible. For most multi-tenant use cases, permissive policies are the right choice.

Setting Up RLS

Step 1: Enable RLS on the Table

RLS is disabled by default on all tables. You enable it per table:

-- Enable RLS on the projects table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- By default, table owners bypass RLS even when it is enabled.
-- For a true shared-schema setup where the app role owns the table,
-- you almost always want to force RLS to apply to the owner too.
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

Step 2: Create the Tenant Isolation Policy

Create a policy that restricts all rows to those belonging to the current tenant. The tenant ID is read from a session-level GUC (Grand Unified Configuration) variable set by the application at connection time:

-- Read policy: only see rows belonging to the current tenant
CREATE POLICY tenant_isolation_policy ON projects
  AS PERMISSIVE
  FOR ALL
  TO app_role                -- only apply to this role; superusers still bypass
  USING (
    tenant_id = current_setting('app.current_tenant_id')::uuid
  )
  WITH CHECK (
    tenant_id = current_setting('app.current_tenant_id')::uuid
  );

This single policy handles all four DML operations. The USING clause ensures you can only read or modify rows for your tenant. The WITH CHECK clause ensures you cannot insert or update a row to belong to a different tenant.

Step 3: Set the Tenant Context at Connection Time

Before any query runs against an RLS-protected table, the application must set the current tenant in the session:

-- Set the tenant context for this session (non-local: persists for connection lifetime)
SELECT set_config('app.current_tenant_id', '3f2a9b1c-4d5e-6f7a-8b9c-0d1e2f3a4b5c', false);

-- Or using SET (equivalent, also session-level)
SET app.current_tenant_id = '3f2a9b1c-4d5e-6f7a-8b9c-0d1e2f3a4b5c';

-- Now all queries against projects see only this tenant's rows
SELECT id, name, status FROM projects WHERE status = 'active';
-- Returns: only rows where tenant_id = '3f2a9b1c-...'

In application code, set this immediately after acquiring a connection from the pool:

-- In a transaction (set_config third param = true means local to transaction)
BEGIN;
SELECT set_config('app.current_tenant_id', $1, true);  -- $1 = tenant UUID from JWT/session
-- All queries in this transaction are automatically tenant-scoped
SELECT * FROM projects WHERE status = 'active';
INSERT INTO projects (name, tenant_id) VALUES ('New Site', $1);  -- WITH CHECK enforces this
COMMIT;

Step 4: Add a Covering Index on the Tenant Column

-- Every RLS-protected table needs an index on the tenant discriminator column.
-- Without this, every query does a full table scan filtered by the RLS predicate.
CREATE INDEX CONCURRENTLY idx_projects_tenant_id
  ON projects (tenant_id);

-- For queries that also filter by status or other common columns,
-- a composite index can eliminate the heap fetch entirely.
CREATE INDEX CONCURRENTLY idx_projects_tenant_status
  ON projects (tenant_id, status)
  INCLUDE (id, name, created_at);

Step 5: Supabase-Style Patterns (auth.uid())

Supabase exposes a helper function auth.uid() that returns the UUID of the currently authenticated user from the JWT, making RLS policies on user-owned data extremely clean:

-- Supabase-style: each user owns their own rows
CREATE POLICY "Users see only their own rows" ON user_files
  FOR ALL
  TO authenticated
  USING (owner_id = auth.uid())
  WITH CHECK (owner_id = auth.uid());

-- For multi-tenant: map users to tenants via a memberships table
CREATE POLICY "Tenant members see tenant data" ON projects
  FOR ALL
  TO authenticated
  USING (
    tenant_id IN (
      SELECT tenant_id FROM memberships WHERE user_id = auth.uid()
    )
  )
  WITH CHECK (
    tenant_id IN (
      SELECT tenant_id FROM memberships WHERE user_id = auth.uid()
    )
  );

Performance Considerations

RLS policies add a filter predicate to every query. This is free when the predicate is supported by an index and cheap to evaluate. It is not free when the table is large and no index exists on the discriminator column — in that case, PostgreSQL performs a full sequential scan of the entire table, filtered to the tenant's rows after the scan.

At Buildwise's scale — approximately 40 million rows in the projects table across 850 tenants — an unindexed tenant_id filter would scan all 40 million rows for every query. With the correct CREATE INDEX ON projects (tenant_id), the planner jumps directly to the ~47,000 rows belonging to the queried tenant. Query time went from 4.2 seconds (sequential scan) to 8 milliseconds (index scan) on their largest table.

Two additional performance notes worth knowing:

Policy expressions are evaluated per-row during planning. Keep policy expressions simple. An expression like tenant_id = current_setting('app.current_tenant_id')::uuid is evaluated once per plan, not once per row — PostgreSQL treats the GUC as a constant. But a complex subquery in the policy expression will be re-evaluated as part of every query plan, so cache it in a stable function or ensure it is fast.

Use EXPLAIN (ANALYZE, BUFFERS) to verify the policy predicate is being pushed down to an index scan, not applied as a filter after a sequential scan:

-- Verify the RLS predicate is hitting the index, not causing a seq scan
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT id, name FROM projects WHERE status = 'active';

-- You want to see: Index Scan using idx_projects_tenant_id
-- You do NOT want to see: Seq Scan on projects (filter: tenant_id = ...)

Comparison Table

For most SaaS products at startup through growth stage, RLS on a shared-schema is the right balance of security, developer experience, and operational simplicity.

Key Takeaways

Working with JusDB on PostgreSQL

Implementing Row-Level Security correctly in a production multi-tenant application requires more than writing the right CREATE POLICY statements. It means auditing your connection pool configuration for GUC leakage, identifying every SECURITY DEFINER function that bypasses RLS, verifying index coverage on all discriminator columns, and ensuring your migration tooling sets the tenant context correctly. Getting any one of these wrong silently breaks isolation — often without a visible error, only a missing WHERE clause in a query plan.

JusDB works with SaaS engineering teams to design, implement, and audit multi-tenant PostgreSQL architectures including full RLS rollouts. This includes policy design, connection pool configuration for RLS compatibility, performance analysis to ensure tenant-scoped queries use indexes efficiently, and security review of the bypass surface area. If you are adding RLS to an existing codebase — where the blast radius of missed bypasses is highest — that audit work is especially important.

PostgreSQL Consulting Services Talk to a PostgreSQL Expert

Related Articles

• PostgreSQL Explained: A Complete Guide for Modern Applications — foundational PostgreSQL concepts covering MVCC, transactions, and the query planner that underpin how RLS policies are evaluated
• PostgreSQL Table Partitioning with pg_partman — partition your RLS-protected tables by tenant or time range for query performance at scale
• Database Schema Design Fundamentals for Scalability — how to model multi-tenant schemas so RLS policies are simple and indexes are effective