PostgreSQL RLS for Complex Hierarchical SaaS Authorization
Beyond Tenant ID: Architecting Granular Authorization with PostgreSQL RLS
In modern multi-tenant SaaS applications, authorization logic often becomes a sprawling, complex beast residing in the application layer. While effective, this approach is prone to bugs, difficult to audit, and can introduce significant performance overhead as every data access point must be meticulously guarded by application-level checks. PostgreSQL's Row-Level Security (RLS) offers a compelling alternative: shifting authorization logic directly into the database, ensuring that security policies are enforced consistently and transparently, regardless of the client application.
This article is not an introduction to RLS. We assume you're familiar with CREATE POLICY and basic tenant isolation. Instead, we will dissect the implementation of a far more complex, hierarchical authorization model—one that mirrors the reality of many B2B SaaS products—and tackle the advanced performance, testing, and operational challenges that arise in production.
Our goal is to build a robust system where a user's access to a resource (e.g., a task) depends on their membership and role within a nested structure: Organization -> Project -> Task. We will go deep into the weeds of policy design, function performance, and interaction with critical infrastructure like connection poolers.
The Scenario: A Hierarchical Project Management SaaS
Let's define our data model. We're building a system where users belong to organizations. Within an organization, users are assigned to projects with specific roles (admin, editor, viewer).
Here is our core schema:
-- Use pgcrypto for UUIDs
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- Organizations are the top-level tenants
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL
);
-- Standard user table
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE
);
-- Projects belong to an organization
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
name TEXT NOT NULL
);
-- Tasks belong to a project
CREATE TABLE tasks (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
title TEXT NOT NULL,
completed BOOLEAN DEFAULT FALSE
);
-- Membership junction table with roles
CREATE TYPE project_role AS ENUM ('admin', 'editor', 'viewer');
CREATE TABLE project_memberships (
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role project_role NOT NULL,
PRIMARY KEY (project_id, user_id)
);
-- Create indexes for performance
CREATE INDEX ON projects (org_id);
CREATE INDEX ON tasks (project_id);
CREATE INDEX ON project_memberships (user_id);The authorization requirements are:
- Users can only see organizations they are a member of (indirectly, via project membership).
- Users can only see projects they are explicitly members of.
- Users can see all tasks within projects they are a member of.
admin or editor role can create, update, or delete tasks.admin role can create new projects within an organization.Step 1: Establishing Secure Session Context
RLS policies rely on session-specific context. The most common pattern is to use SET commands to create runtime parameters. We'll define two settings: app.current_user_id and app.current_org_id.
It's critically important that these settings can only be set by a trusted role at the beginning of a transaction and are not modifiable by the user's session role.
// Node.js with 'pg' library - Setting context for a request
const { Pool } = require('pg');
const pool = new Pool(); // assumes standard env vars
async function executeQueryAsUser(userId, orgId, query, params) {
const client = await pool.connect();
try {
// Start a transaction
await client.query('BEGIN');
// Set the session context securely. Use LOCAL to scope it to the transaction.
// This is VITAL for connection pooling (more on this later).
await client.query(`SET LOCAL app.current_user_id = '${userId}'`);
await client.query(`SET LOCAL app.current_org_id = '${orgId}'`);
// The application role should not have bypassrls privileges.
// We assume the connection pool connects as a privileged role initially,
// then sets the role for the transaction.
await client.query('SET ROLE my_app_user');
const result = await client.query(query, params);
await client.query('COMMIT');
return result.rows;
} catch (e) {
await client.query('ROLLBACK');
throw e;
} finally {
client.release();
}
}This pattern ensures that the user context is transaction-scoped and reset upon commit or rollback, preventing context leakage in a connection-pooled environment.
Step 2: Advanced Policy Implementation with Helper Functions
Writing complex joins inside every policy is repetitive and error-prone. A much cleaner and more maintainable approach is to encapsulate authorization logic within SECURITY DEFINER functions.
Warning: SECURITY DEFINER functions execute with the privileges of the user who defined them, not the calling user. This is powerful but dangerous. Always SET search_path TO "$user", public, your_schema at the top of these functions to prevent search path hijacking attacks.
Let's create a helper function to check a user's role in a given project.
CREATE OR REPLACE FUNCTION get_project_role(p_project_id UUID, p_user_id UUID)
RETURNS project_role AS $$
DECLARE
user_role project_role;
BEGIN
-- SECURITY DEFINER functions should have a secure search path
SET search_path = public;
SELECT role INTO user_role
FROM project_memberships
WHERE project_id = p_project_id AND user_id = p_user_id;
RETURN user_role;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;Performance Note: We've marked this function as STABLE, which tells the query planner that it returns the same result for the same arguments within a single scan. This is appropriate here. Using IMMUTABLE would be incorrect, as memberships can change. Using VOLATILE (the default) would be a performance disaster, as it would prevent the planner from optimizing calls and could cause the function to be re-evaluated for every row.
Now, let's implement our RLS policies.
-- First, enable RLS on all relevant tables
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_memberships ENABLE ROW LEVEL SECURITY;
-- Helper function to get the current user ID safely
CREATE OR REPLACE FUNCTION current_user_id() RETURNS UUID AS $$
BEGIN
RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
-- Return NULL if the setting is not available, preventing query failure
WHEN OTHERS THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;
-- Policy for `tasks`
CREATE POLICY select_tasks ON tasks
FOR SELECT USING (
-- A user can select a task if they are a member of the project it belongs to.
EXISTS (
SELECT 1 FROM project_memberships
WHERE project_id = tasks.project_id AND user_id = current_user_id()
)
);
CREATE POLICY modify_tasks ON tasks
FOR ALL -- Covers INSERT, UPDATE, DELETE
USING (
-- The USING clause applies to existing rows for UPDATE/DELETE
EXISTS (
SELECT 1 FROM project_memberships
WHERE project_id = tasks.project_id AND user_id = current_user_id()
)
)
WITH CHECK (
-- The WITH CHECK clause applies to new/updated rows for INSERT/UPDATE
get_project_role(tasks.project_id, current_user_id()) IN ('admin', 'editor')
);
-- Policy for `projects`
CREATE POLICY select_projects ON projects
FOR SELECT USING (
-- A user can select a project if they are a member of it.
EXISTS (
SELECT 1 FROM project_memberships
WHERE project_id = projects.id AND user_id = current_user_id()
)
);
CREATE POLICY insert_projects ON projects
FOR INSERT WITH CHECK (
-- To insert a project, the user must be an 'admin' of the *organization*.
-- This is a more complex check we'll need another helper for.
is_org_admin(projects.org_id, current_user_id())
);
-- Let's define `is_org_admin`. For simplicity, we'll say an org admin
-- is anyone who is an admin on *any* project within that org.
-- A real-world scenario might have an explicit `organization_memberships` table.
CREATE OR REPLACE FUNCTION is_org_admin(p_org_id UUID, p_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
SET search_path = public;
RETURN EXISTS (
SELECT 1
FROM project_memberships pm
JOIN projects p ON pm.project_id = p.id
WHERE p.org_id = p_org_id
AND pm.user_id = p_user_id
AND pm.role = 'admin'
);
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
-- Policy for `organizations`
CREATE POLICY select_organizations ON organizations
FOR SELECT USING (
-- A user can see an org if they are a member of any project within it.
EXISTS (
SELECT 1
FROM projects p
JOIN project_memberships pm ON p.id = pm.project_id
WHERE p.org_id = organizations.id AND pm.user_id = current_user_id()
)
);This setup provides granular, hierarchical control. The logic is co-located with the data, and the application code can now perform simple SELECT * FROM tasks queries, confident that only authorized data will be returned.
Performance Deep Dive: The Hidden Cost of RLS
RLS is not free. Every query against an RLS-enabled table has its WHERE clause implicitly modified by the policy. This can have profound effects on the query planner.
Consider a query: SELECT * FROM tasks WHERE completed = false;
With RLS enabled, the planner effectively sees:
SELECT * FROM tasks
WHERE completed = false
AND (
EXISTS (
SELECT 1 FROM project_memberships
WHERE project_id = tasks.project_id AND user_id = '...' -- from current_user_id()
)
);This subquery will be executed for every potential row scan. If your indexes are not well-designed, this can lead to catastrophic performance degradation.
Benchmarking RLS Overhead
Let's analyze the plan for a query to fetch tasks.
Without RLS:
EXPLAIN ANALYZE SELECT * FROM tasks WHERE project_id = 'some-project-uuid';
Index Scan using tasks_project_id_idx on tasks (cost=0.42..8.44 rows=1 width=53) (actual time=0.015..0.016 rows=1 loops=1)
Index Cond: (project_id = 'some-project-uuid'::uuid)
Planning Time: 0.086 ms
Execution Time: 0.033 msWith RLS:
-- (Assume context is set for a user who is a member of 'some-project-uuid')
EXPLAIN ANALYZE SELECT * FROM tasks WHERE project_id = 'some-project-uuid';
Index Scan using tasks_project_id_idx on tasks (cost=0.42..16.48 rows=1 width=53) (actual time=0.045..0.046 rows=1 loops=1)
Index Cond: (project_id = 'some-project-uuid'::uuid)
Filter: (EXISTS (SELECT 1 FROM project_memberships WHERE ((project_id = tasks.project_id) AND (user_id = current_user_id()))))
SubPlan 1
-> Index Only Scan using project_memberships_pkey on project_memberships (cost=0.42..8.44 rows=1 width=0) (actual time=0.015..0.015 rows=1 loops=1)
Index Cond: (project_id = tasks.project_id) AND (user_id = 'user-uuid-from-context'::uuid)
Heap Fetches: 0
Planning Time: 0.215 ms
Execution Time: 0.089 msIn this simple case, the overhead is minimal because our RLS check is also highly selective and well-indexed. However, imagine a policy that requires a multi-table join. The planner might struggle to find an optimal join order, especially if the policy's logic is opaque (hidden inside a PL/pgSQL function).
Optimization Strategy: Caching Permissions in Session Context
For very high-throughput systems, repeatedly checking memberships can become a bottleneck. An advanced pattern is to pre-calculate a user's permissions at the beginning of a session and store them in a custom GUC (Grand Unified Configuration) variable.
For example, we can store a JSONB array of project IDs the user has access to.
// In the application, at the start of a request
const client = await pool.connect();
// ... begin transaction ...
// 1. Fetch user's accessible projects
const res = await client.query(`
SELECT json_agg(project_id) as projects
FROM project_memberships
WHERE user_id = $1
`, [userId]);
const accessibleProjects = res.rows[0].projects || [];
// 2. Set this array in the session context
await client.query(`SET LOCAL app.accessible_projects = '${JSON.stringify(accessibleProjects)}'`);
await client.query(`SET LOCAL app.current_user_id = '${userId}'`);
// ... set role, execute business logic ...Now, the RLS policy can be simplified and made much faster:
-- Helper to parse the JSONB array from the setting
CREATE OR REPLACE FUNCTION accessible_projects() RETURNS JSONB AS $$
BEGIN
RETURN current_setting('app.accessible_projects', true)::JSONB;
EXCEPTION
WHEN OTHERS THEN
RETURN '[]'::JSONB;
END;
$$ LANGUAGE plpgsql STABLE;
-- New, faster policy for `tasks`
DROP POLICY select_tasks ON tasks;
CREATE POLICY select_tasks ON tasks
FOR SELECT USING (
tasks.project_id::text IN (SELECT value FROM jsonb_array_elements_text(accessible_projects()))
);This approach trades one complex query at the start of the request for much simpler, faster checks on every subsequent query. The jsonb_array_elements_text is less intuitive than an EXISTS subquery, but it avoids repeated joins, which can be a huge win.
Edge Case: RLS vs. Connection Poolers (PgBouncer)
This is the single most common pitfall for production RLS deployments. If you use PgBouncer in transaction pooling mode, your SET commands will not work as expected. A connection is returned to the pool after each transaction, and the next transaction for a different user might receive that same connection, inheriting the previous user's app.current_user_id! This is a catastrophic security failure.
The Solution:
SET LOCAL: As shown in our Node.js example, SET LOCAL scopes the setting to the current transaction only. When the transaction ends (COMMIT/ROLLBACK), the setting is automatically reverted. This is the bare minimum requirement.DISCARD ALL or RESET ALL command just before returning a connection to the pool, but this can be fragile. The SET LOCAL approach is strongly preferred.SET LOCAL is used correctly.Edge Case: Testing RLS Policies
Testing RLS is notoriously difficult because the policies are invisible to standard ORMs and query builders. You cannot simply insert data and expect it to be there in the next SELECT if your test runner's role doesn't satisfy the policy.
A Robust Testing Pattern:
Use a transactional testing approach where each test impersonates the desired user role.
-- test_setup.sql
-- Create a superuser role for the test runner
CREATE ROLE test_runner LOGIN SUPERUSER PASSWORD '...';
-- Create application roles
CREATE ROLE my_app_user NOLOGIN;
-- Grant basic CRUD permissions to the application role
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO my_app_user;
-- Create a user for testing
INSERT INTO users (id, email) VALUES ('a1a1a1a1-...', '[email protected]');
INSERT INTO users (id, email) VALUES ('b2b2b2b2-...', '[email protected]');// In your test suite (e.g., using Jest and node-pg)
describe('Task RLS Policies', () => {
let client;
beforeAll(async () => {
// Connect as the privileged test_runner
client = await connectAsTestRunner();
});
beforeEach(async () => {
// All tests run inside a transaction that will be rolled back
await client.query('BEGIN');
// Seed data as the superuser
// ... create org, project, tasks, and memberships for admin and viewer
});
afterEach(async () => {
await client.query('ROLLBACK');
});
it('should allow an editor to update a task', async () => {
// Set the context for the 'editor' user
await client.query(`SET LOCAL app.current_user_id = '...'`); // editor's UUID
await client.query('SET ROLE my_app_user');
// This UPDATE should succeed
const promise = client.query(`UPDATE tasks SET title = 'New Title' WHERE id = '...'`);
await expect(promise).toResolve();
// Reset role to superuser to verify the change
await client.query('RESET ROLE');
const { rows } = await client.query('SELECT title FROM tasks WHERE id = ...');
expect(rows[0].title).toBe('New Title');
});
it('should PREVENT a viewer from updating a task', async () => {
// Set the context for the 'viewer' user
await client.query(`SET LOCAL app.current_user_id = '...'`); // viewer's UUID
await client.query('SET ROLE my_app_user');
// This UPDATE should fail due to the RLS WITH CHECK clause
const promise = client.query(`UPDATE tasks SET title = 'New Title' WHERE id = '...'`);
await expect(promise).rejects.toThrow('new row violates row-level security policy for table "tasks"');
});
});This pattern provides perfect isolation:
* Tests run in parallel without interfering.
* Data is seeded by a superuser that bypasses RLS.
* Each test explicitly sets the user context and role to simulate an application request.
* The transaction rollback ensures a clean slate for the next test.
Conclusion: RLS as a Foundational Security Layer
Implementing a hierarchical authorization model with PostgreSQL RLS is a powerful technique for centralizing and enforcing security logic at the data layer. It moves your security posture from a distributed, easy-to-miss application concern to a foundational, non-bypassable database guarantee.
However, this power demands expertise. Senior engineers adopting RLS must move beyond simple policy creation and master its interaction with the query planner, understand the nuances of function volatility, design leak-proof session context management for connection poolers, and build sophisticated, role-aware testing frameworks.
By treating RLS as a core architectural component and not an afterthought, you can build highly secure, auditable, and surprisingly performant multi-tenant systems where the database itself becomes the ultimate guardian of your data.