PostgreSQL RLS for Dynamic, Policy-Based Data Filtering in SaaS
The Fallacy of Application-Layer Tenant Isolation
In multi-tenant SaaS architecture, the cardinal sin is data leakage between tenants. The standard approach involves meticulously adding a WHERE tenant_id = :current_tenant_id clause to every single database query. While functional, this pattern is fragile and anathema to senior engineers. It relies on developer discipline—a variable that trends towards zero as complexity and team size grow. A single forgotten WHERE clause in a complex JOIN or a deeply nested subquery can expose your entire dataset. This is not a theoretical risk; it's an inevitability.
Row-Level Security (RLS) in PostgreSQL offers a paradigm shift. It moves access control from the fallible application layer to the hardened database layer. RLS provides a transparent, non-bypassable enforcement mechanism that guarantees a given database connection can only see the data it is authorized to see, regardless of the queries being executed.
This article assumes you understand the basic concept of RLS. We will not cover CREATE POLICY ... FOR SELECT USING (tenant_id = ...). Instead, we will architect a production-grade, dynamic, and performant RLS system that handles complex user roles, scales efficiently, and navigates the treacherous waters of connection pooling and advanced database features.
Our Scenario: A Multi-Tenant Project Management SaaS
Throughout this post, we'll build an access control system for a SaaS platform with the following entities:
admin, member) within a specific tenant.Our goal is to enforce these rules at the database level:
- Users can only see data belonging to tenants they are a member of.
admin can see all projects.member can only see projects they are explicitly assigned to (a future extension we'll model for).Section 1: Foundational RLS with Session Context
The most common RLS pattern relies on setting session-local configuration variables. The application authenticates a user, determines their user_id and tenant_id, and sets these values for the duration of the database connection or transaction.
Schema Setup
Let's start with a robust schema.
-- Create tenants and users
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL UNIQUE
);
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE
);
-- Membership table defining roles within a tenant
CREATE TYPE tenant_role AS ENUM ('admin', 'member');
CREATE TABLE memberships (
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
role tenant_role NOT NULL DEFAULT 'member',
PRIMARY KEY (user_id, tenant_id)
);
-- The resource we want to protect
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
name TEXT NOT NULL
);
-- Enable RLS on the projects table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- Force RLS for the table owner as well, a crucial security step
ALTER TABLE projects FORCE ROW LEVEL SECURITY;The Basic Policy
Our first policy will use current_setting() to read context variables we'll set from our application. We'll namespace them with app. to avoid conflicts.
-- This policy checks if the project's tenant_id matches the one set in the session
CREATE POLICY select_projects_policy
ON projects
FOR SELECT
USING (tenant_id = current_setting('app.current_tenant_id')::UUID);Application-Layer Integration (Node.js/TypeScript)
Here's how a backend service would manage this context within a request lifecycle. We use a middleware pattern to ensure this is set for every query within a transaction.
import { Pool, PoolClient } from 'pg';
const pool = new Pool({ /* connection details */ });
interface AppContext {
userId: string;
tenantId: string;
role: 'admin' | 'member';
}
// Middleware to set RLS context for a transaction
async function withRlsContext<T>(
context: AppContext,
callback: (client: PoolClient) => Promise<T>
): Promise<T> {
const client = await pool.connect();
try {
await client.query('BEGIN');
// Use SET LOCAL to scope the settings to the current transaction
// This is CRITICAL for use with connection pools
const setTenantQuery = {
text: `SET LOCAL app.current_tenant_id = $1;`,
values: [context.tenantId],
};
const setUserQuery = {
text: `SET LOCAL app.current_user_id = $1;`,
values: [context.userId],
};
const setRoleQuery = {
text: `SET LOCAL app.current_user_role = $1;`,
values: [context.role],
};
await client.query(setTenantQuery);
await client.query(setUserQuery);
await client.query(setRoleQuery);
// Execute the actual business logic
const result = await callback(client);
await client.query('COMMIT');
return result;
} catch (e) {
await client.query('ROLLBACK');
throw e;
} finally {
client.release();
}
}
// Example Usage in an API endpoint
async function getProjectsForUser(context: AppContext) {
return await withRlsContext(context, async (client) => {
// Notice: NO `WHERE tenant_id = ...` clause here!
// RLS handles it transparently.
const res = await client.query('SELECT id, name FROM projects');
return res.rows;
});
}With this setup, a call to getProjectsForUser will automatically filter projects. If we inspect the query plan:
-- In a psql session after setting the variable:
SET app.current_tenant_id = 'some-tenant-uuid';
EXPLAIN ANALYZE SELECT id, name FROM projects; QUERY PLAN
------------------------------------------------------------------------------------------------------------------------
Seq Scan on projects (cost=0.00..35.50 rows=10 width=52) (actual time=0.012..0.013 rows=5 loops=1)
Filter: (tenant_id = (current_setting('app.current_tenant_id'::text))::uuid)
Rows Removed by Filter: 995
Planning Time: 0.078 ms
Execution Time: 0.025 msThe Filter line confirms that PostgreSQL is injecting our security policy directly into the query plan. The application code remains clean and secure by default.
Section 2: Dynamic Policies with Security Functions
The simple tenant_id check is a good start, but real-world authorization is more granular. We need to handle user roles.
An admin of a tenant should see all projects in that tenant. A member should only see projects they are assigned to (we'll add a project_assignments table later for this). Let's implement the admin part first.
We could write a complex USING clause, but this quickly becomes unmanageable. The superior pattern is to encapsulate the logic in a PostgreSQL function.
-- A more advanced policy that checks role
CREATE OR REPLACE FUNCTION is_tenant_admin(tenant_id_to_check UUID)
RETURNS BOOLEAN AS $$
BEGIN
-- Check if the current user is an admin of the specified tenant
RETURN EXISTS (
SELECT 1
FROM memberships
WHERE
user_id = current_setting('app.current_user_id')::UUID AND
tenant_id = tenant_id_to_check AND
role = 'admin'
);
END;
$$ LANGUAGE plpgsql;
-- Let's drop the old policy and create a new, more powerful one
DROP POLICY select_projects_policy ON projects;
CREATE POLICY tenant_access_policy
ON projects
FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE
USING (
-- All users must belong to the correct tenant
tenant_id = current_setting('app.current_tenant_id')::UUID
)
WITH CHECK (
-- For INSERT/UPDATE, ensure the tenant_id isn't being changed to something they don't have access to
tenant_id = current_setting('app.current_tenant_id')::UUID
);
-- A separate, more permissive policy for admins
CREATE POLICY admin_full_access_policy
ON projects
FOR SELECT
USING (
is_tenant_admin(current_setting('app.current_tenant_id')::UUID)
);There's a subtle but critical flaw here. PostgreSQL applies policies with OR. A user only needs to satisfy one policy to see a row. This means a non-admin member will satisfy tenant_access_policy and see all projects in the tenant, which is not what we want. We need to combine the logic.
Correct, Combined Policy Logic:
-- Let's create a more comprehensive check function
CREATE OR REPLACE FUNCTION check_project_visibility(project_tenant_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
current_user_id UUID := current_setting('app.current_user_id', true)::UUID;
current_tenant_id UUID := current_setting('app.current_tenant_id', true)::UUID;
current_user_role tenant_role := current_setting('app.current_user_role', true)::tenant_role;
BEGIN
-- Basic check: is the user even in the correct tenant context?
IF project_tenant_id != current_tenant_id THEN
RETURN FALSE;
END IF;
-- Admins can see everything in their tenant
IF current_user_role = 'admin' THEN
RETURN TRUE;
END IF;
-- Future logic for 'member' role would go here, e.g.:
-- IF current_user_role = 'member' THEN
-- RETURN EXISTS (SELECT 1 FROM project_assignments pa WHERE pa.project_id = ... AND pa.user_id = current_user_id);
-- END IF;
-- Default to deny
RETURN FALSE;
END;
$$ LANGUAGE plpgsql;
-- Now, let's create a single, clear policy
DROP POLICY IF EXISTS tenant_access_policy ON projects;
DROP POLICY IF EXISTS admin_full_access_policy ON projects;
CREATE POLICY projects_visibility_policy
ON projects
FOR SELECT
USING ( check_project_visibility(tenant_id) );
-- Don't forget policies for mutation
CREATE POLICY projects_mutation_policy
ON projects
FOR INSERT, UPDATE, DELETE
USING ( is_tenant_admin(tenant_id) ); -- Only admins can modify projects for now
WITH CHECK ( is_tenant_admin(tenant_id) );This is cleaner, but it introduces a significant performance risk. This function will be executed for every single row in the projects table during a scan. For large tables, this can be devastatingly slow.
Section 3: Performance Tuning with `SECURITY DEFINER` and `LEAKPROOF`
The performance overhead of row-by-row function calls is the primary reason many engineers abandon RLS. However, we can mitigate this with advanced function attributes.
Problem 1: Permissions
By default, functions execute with the permissions of the invoking user (SECURITY INVOKER). In our case, the application user might only have SELECT on projects, but not on the memberships table. Our check_project_visibility function would fail with a permission denied error.
The solution is SECURITY DEFINER. This attribute makes the function execute with the permissions of the user who defined the function (typically a superuser or schema owner).
Problem 2: Query Planner Optimization
PostgreSQL's query planner is conservative about functions. It assumes a function's result might depend on its arguments in some unpredictable way, or that it might have side effects. This prevents it from making crucial optimizations, like pushing down filter conditions or caching results.
The LEAKPROOF attribute is a promise to the planner. It asserts that the function has no side effects and its result depends only on its arguments, and that it throws no errors based on argument values that could reveal information. A LEAKPROOF function can be evaluated earlier and its results cached, dramatically improving performance.
The Optimized Function
Let's refactor our function with these attributes. It's also best practice to make functions STABLE if they don't modify the database and return the same result for the same arguments within a single scan.
CREATE OR REPLACE FUNCTION check_project_visibility(project_tenant_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
-- Using `true` as the second argument to current_setting returns NULL if not found,
-- preventing an error. Coalesce to a sentinel UUID to handle this case gracefully.
current_user_id UUID := current_setting('app.current_user_id', true)::UUID;
current_tenant_id UUID := current_setting('app.current_tenant_id', true)::UUID;
current_user_role tenant_role := current_setting('app.current_user_role', true)::tenant_role;
BEGIN
-- If context is not set, deny access immediately.
IF current_user_id IS NULL OR current_tenant_id IS NULL THEN
RETURN FALSE;
END IF;
IF project_tenant_id != current_tenant_id THEN
RETURN FALSE;
END IF;
-- This check is now safe because the function runs as its owner,
-- who has permission on the memberships table.
IF current_user_role = 'admin' THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE LEAKPROOF SECURITY DEFINER;Benchmarking the Difference
Let's simulate a table with 1 million projects across 1000 tenants. We'll query for projects in one tenant which has 1000 projects.
Without LEAKPROOF:
EXPLAIN ANALYZE SELECT count(*) FROM projects;-- The planner executes the function for every row.
Aggregate (cost=16825.00..16825.01 rows=1 width=8) (actual time=345.123..345.124 rows=1 loops=1)
-> Seq Scan on projects (cost=0.00..16700.00 rows=500000 width=0) (actual time=0.035..298.456 rows=1000000 loops=1)
Filter: check_project_visibility(tenant_id)
Execution time: 345.201 msWith LEAKPROOF:
-- Recreate function with LEAKPROOF and re-run the query
EXPLAIN ANALYZE SELECT count(*) FROM projects;-- The planner is smarter. It can evaluate the `current_setting` parts once.
Finalize Aggregate (cost=14201.00..14201.01 rows=1 width=8) (actual time=85.678..85.679 rows=1 loops=1)
-> Gather (cost=14200.00..14200.01 rows=2 width=8) (actual time=85.123..85.670 rows=3 loops=1)
Workers Planned: 2
Workers Launched: 2
-> Partial Aggregate (cost=13200.00..13200.01 rows=1 width=8) (actual time=79.345..79.346 rows=1 loops=3)
-> Parallel Seq Scan on projects (cost=0.00..13125.00 rows=30000 width=0) (actual time=0.025..75.980 rows=333333 loops=3)
Filter: check_project_visibility(tenant_id)
Execution time: 85.750 msThe performance gain is ~4x in this synthetic test. In real-world scenarios with more complex queries and joins, the difference can be orders of magnitude. The planner can now push the RLS predicate down, use indexes more effectively, and avoid repeated, expensive computations.
RULE: Any function used in an RLS policy MUST be STABLE (or IMMUTABLE), LEAKPROOF, and almost always SECURITY DEFINER.
Section 4: Edge Cases and Advanced Scenarios
Production systems are messy. Here's how to handle the inevitable complexities.
Edge Case 1: Super Admin Access
Your internal support team needs to view data across all tenants. How do you bypass RLS for them?
Option A: The BYPASS RLS Attribute
Create a special role for super admins and grant it the BYPASS RLS privilege.
CREATE ROLE super_admin BYPASSRLS;
GRANT super_admin TO my_support_user;When my_support_user connects, RLS policies are simply not applied. This is simple and effective but is a very blunt instrument. It's all or nothing.
Option B: A Bypass in the Policy Function
A more granular approach is to build the bypass into your security function itself.
-- In your check_project_visibility function:
DECLARE
is_super_admin BOOLEAN := current_setting('app.is_super_admin', true) = 'true';
BEGIN
IF is_super_admin THEN
RETURN TRUE;
END IF;
-- ... rest of the logic
END;Your application backend can identify a super admin at login and set this app.is_super_admin session variable. This allows for more complex scenarios, like a super admin who can view all data but not modify it, by using this flag differently in SELECT vs. UPDATE policies.
Edge Case 2: RLS vs. Foreign Key Constraints
This is a subtle but critical interaction that can cause maddening bugs. Consider a tasks table:
CREATE TABLE tasks (
id UUID PRIMARY KEY,
project_id UUID NOT NULL REFERENCES projects(id),
title TEXT
);
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tasks_policy ON tasks FOR SELECT
USING ( project_id IN (SELECT id FROM projects) );The policy states a user can see a task if they can see its parent project. Now, imagine a member user (who can't see Project B) tries to create a task for Project B:
-- User is a member of tenant 'acme', but not assigned to project_b_id
INSERT INTO tasks (id, project_id, title) VALUES (gen_random_uuid(), 'project_b_id', 'A new task');Expected Error: "Permission denied by row-level security policy"
Actual Error: ERROR: insert or update on table "tasks" violates foreign key constraint "tasks_project_id_fkey"
Why? RLS policies on the projects table are applied before the foreign key check. The database checks if project_b_id exists. Due to RLS, for this user, it appears not to exist. The database then fails with a foreign key violation, completely masking the real security issue.
Solution: The WITH CHECK clause on an INSERT policy must validate the relation before the database does. Your INSERT policy on tasks should explicitly check if the user has rights to the target project_id.
CREATE POLICY tasks_insert_policy ON tasks FOR INSERT
WITH CHECK ( project_id IN (SELECT id FROM projects) );Now, the RLS system correctly checks if the user can see the target project as part of the insert policy, and if not, it will raise the correct "policy violation" error.
Section 5: The Elephant in the Room - Connection Pooling
Our SET LOCAL pattern is robust and secure, but it has a massive Achilles' heel: it is fundamentally incompatible with transaction-level connection pooling.
Tools like PgBouncer, in their most performant transaction mode, will hand a client a random connection from the pool for each transaction. If you SET LOCAL a variable in one transaction, the next transaction for the same application user might get a different connection from the pool that doesn't have that setting. Your RLS will fail silently, either denying all access or, worse, using stale context from a previous user's transaction on that connection.
This is the single biggest architectural challenge when implementing RLS.
Solution 1: Session Pooling
The simplest solution is to configure your pooler (e.g., PgBouncer) in session pooling mode. In this mode, a client receives a connection and holds it until it disconnects. This preserves session state, so SET (not even SET LOCAL) works as expected.
Solution 2: JWTs and Stateless RLS Functions
A more scalable and modern approach is to make your RLS functions stateless by passing the required context directly into the query. This sounds like we're back where we started, but we can do it securely.
user_id, tenant_id, role.pgjwt or a custom PL/pgSQL function to verify the JWT's signature inside the database. This is critical; the database must not trust the payload without verification.Here's how the RLS function changes:
-- A function to get a claim from a JWT. (Implementation requires a JWT extension)
CREATE OR REPLACE FUNCTION get_jwt_claim(token TEXT, claim TEXT) RETURNS TEXT ...;
CREATE OR REPLACE FUNCTION check_project_visibility_jwt(project_tenant_id UUID, token TEXT)
RETURNS BOOLEAN AS $$
DECLARE
-- Extract claims from the token. The function must verify the signature!
token_tenant_id UUID := get_jwt_claim(token, 'tenantId')::UUID;
token_role tenant_role := get_jwt_claim(token, 'role')::tenant_role;
BEGIN
IF project_tenant_id != token_tenant_id THEN
RETURN FALSE;
END IF;
IF token_role = 'admin' THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE LEAKPROOF;
-- The policy now uses this new function
CREATE POLICY projects_policy_jwt ON projects FOR SELECT
USING ( check_project_visibility_jwt(tenant_id, current_setting('app.jwt_token')) );The application code now only needs to set one thing per transaction: the JWT itself.
// In our withRlsContext function
...
await client.query('BEGIN');
const rawJwt = getJwtFromRequestHeaders(); // Fetch the token
await client.query(`SET LOCAL app.jwt_token = $1`, [rawJwt]);
const result = await callback(client);
await client.query('COMMIT');
... This pattern is compatible with transaction-level pooling because the only piece of state (app.jwt_token) is set at the start of every transaction. It's stateless from the perspective of the connection pool.
Conclusion: RLS as an Architectural Cornerstone
Row-Level Security in PostgreSQL is not just a feature; it's an architectural choice. When implemented correctly, it provides an unparalleled level of security and simplifies the application data access layer immensely. The naive implementation, however, is fraught with performance traps and architectural incompatibilities.
For senior engineers building multi-tenant systems, mastering advanced RLS is a non-negotiable skill. The key takeaways are:
STABLE, LEAKPROOF, and SECURITY DEFINER to give the query planner the ability to optimize their execution. Always verify with EXPLAIN ANALYZE.By moving security enforcement to the database, you build a foundation of trust. Your application code becomes simpler, your attack surface shrinks, and you can sleep better at night knowing that a forgotten WHERE clause won't be the cause of your next catastrophic data breach.