PostgreSQL RLS for Complex SaaS Authorization Models
Beyond `user_id = current_user_id()`: The Reality of Production RLS
For senior engineers building multi-tenant SaaS platforms, PostgreSQL's Row-Level Security (RLS) is an incredibly powerful tool. It promises to move authorization logic directly into the database layer, ensuring data access rules are consistently enforced regardless of the calling application, a direct query, or an analytics tool. However, the introductory examples you'll find online often stop at a deceptively simple pattern:
-- The 'Hello World' of RLS
CREATE POLICY user_can_only_see_own_data ON documents
USING (user_id = auth.uid());This works beautifully for simple, user-siloed data. But in a real-world SaaS application, authorization is never this straightforward. Your requirements likely involve:
* Multi-tenancy: Users belong to an organization or tenant.
* Hierarchical Roles: An admin can see all documents in a tenant, while a member can only see documents they created.
* Team-Based Access: A user can be part of multiple teams within a tenant, and access is granted based on team membership.
* Dynamic Permissions: Access rights are configurable and can change without a code deployment.
Attempting to model these complexities with naive RLS policies can lead to a performance nightmare, with convoluted JOINs inside policy expressions and function calls that are re-evaluated for every single row. This article dives into the advanced patterns used in production to build performant, scalable, and maintainable authorization systems with PostgreSQL RLS.
We will dissect three production-grade patterns, moving from a stateless JWT-based approach to a highly optimized, permission-caching strategy.
Prerequisite Schema
Let's establish a common schema that reflects a typical SaaS application. We have tenants, users, roles, and documents.
-- Create extensions if they don't exist
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Tenants (Organizations)
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name TEXT NOT NULL
);
-- Users
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email TEXT NOT NULL UNIQUE
);
-- Roles (e.g., 'admin', 'member')
CREATE TABLE roles (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL UNIQUE
);
INSERT INTO roles (name) VALUES ('admin'), ('member');
-- Junction table for user, tenant, and role membership
CREATE TABLE user_tenant_roles (
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
role_id INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
PRIMARY KEY (user_id, tenant_id, role_id)
);
-- The resource we want to protect
CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
title TEXT NOT NULL,
content TEXT
);
-- Enable RLS on the documents table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- Create indexes that will be crucial for RLS performance
CREATE INDEX ON documents (tenant_id);
CREATE INDEX ON documents (owner_id);
CREATE INDEX ON user_tenant_roles (user_id, tenant_id);Pattern 1: JWT Claims and Transaction-Local Settings (GUCs)
Our first advanced pattern avoids storing session state in the database. Instead, we leverage the stateless nature of JWTs. The application's API middleware will be responsible for validating the JWT and then passing essential claims (like user_id, tenant_id) to PostgreSQL for every transaction.
The most robust way to pass this per-request data is via session-level configuration parameters, also known as Grand Unified Configuration (GUC) variables.
The Application Middleware Logic
Imagine a Node.js Express middleware. On every authenticated request, it performs these steps:
- Verify the JWT.
userId and tenantId from the token payload.SET LOCAL statements to create transaction-scoped variables.// Example Express.js middleware
const { Pool } = require('pg');
const jwt = require('jsonwebtoken');
const pool = new Pool(); // Configure with your DB credentials
async function authMiddleware(req, res, next) {
const authHeader = req.headers.authorization;
if (!authHeader || !authHeader.startsWith('Bearer ')) {
return res.status(401).send('Unauthorized');
}
const token = authHeader.split(' ')[1];
try {
const payload = jwt.verify(token, process.env.JWT_SECRET);
const { userId, tenantId } = payload;
if (!userId || !tenantId) {
return res.status(403).send('Forbidden: Missing claims');
}
// Attach a custom client to the request object
// This client will have the session variables set for its transaction
req.dbClient = await pool.connect();
// IMPORTANT: Use SET LOCAL. It scopes the setting to the current transaction only.
// This is critical for connection pooling to work correctly.
await req.dbClient.query(`
SET LOCAL app.current_user_id = '${userId}';
SET LOCAL app.current_tenant_id = '${tenantId}';
`);
next();
} catch (err) {
if (req.dbClient) req.dbClient.release();
return res.status(401).send('Unauthorized: Invalid token');
}
}
// Example route using the middleware
app.get('/documents', authMiddleware, async (req, res) => {
try {
const { rows } = await req.dbClient.query('SELECT id, title FROM documents;');
res.json(rows);
} catch (err) {
console.error(err);
res.status(500).send('Server Error');
} finally {
if (req.dbClient) req.dbClient.release(); // Release client back to the pool
}
});Key Production Insight: Using SET LOCAL is non-negotiable in a pooled connection environment. SET (without LOCAL) would set the variable for the entire session, polluting the connection when it's returned to the pool and reused by another user's request. SET LOCAL guarantees the setting is automatically discarded at COMMIT or ROLLBACK.
The Corresponding RLS Policy
Now, the RLS policy can be surprisingly clean. It uses the current_setting() function to retrieve these transaction-local variables. Note the explicit casting to prevent injection vulnerabilities and ensure type safety.
-- A helper function to safely get the current user ID
-- Returns NULL if the setting is not available or malformed
CREATE OR REPLACE FUNCTION get_current_user_id() RETURNS UUID AS $$
BEGIN
RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;
-- A helper function to safely get the current tenant ID
CREATE OR REPLACE FUNCTION get_current_tenant_id() RETURNS UUID AS $$
BEGIN
RETURN current_setting('app.current_tenant_id', true)::UUID;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;
-- Now, the policy itself
CREATE POLICY select_documents_policy ON documents FOR SELECT
USING (
tenant_id = get_current_tenant_id()
);This policy only ensures tenant isolation. What about roles? We could pass a role claim from the JWT and add it to the policy, but this becomes cumbersome as logic grows. This leads us to our next pattern.
Pattern 2: Performant RBAC with `LEAKPROOF` Helper Functions
To handle roles like admin vs. member, we need to query our user_tenant_roles table within the policy. The naive approach is to put a subquery directly in the USING clause.
-- ANTI-PATTERN: Inefficient RLS policy with a subquery
CREATE POLICY select_documents_policy_inefficient ON documents FOR SELECT
USING (
tenant_id = get_current_tenant_id() AND
(
-- Condition for admins: can see all docs in the tenant
EXISTS (
SELECT 1 FROM user_tenant_roles utr
JOIN roles r ON utr.role_id = r.id
WHERE utr.user_id = get_current_user_id()
AND utr.tenant_id = get_current_tenant_id()
AND r.name = 'admin'
)
OR
-- Condition for members: can only see their own docs
owner_id = get_current_user_id()
)
);While this works functionally, it forces the query planner to execute this complex subquery for every row being considered, which can be disastrous for performance. A better approach is to encapsulate this logic in a PostgreSQL function.
The Pitfall: Row-by-Row Function Execution
Let's create a function to check a user's role.
CREATE OR REPLACE FUNCTION user_has_role(p_user_id UUID, p_tenant_id UUID, p_role_name TEXT)
RETURNS BOOLEAN AS $$
SELECT EXISTS (
SELECT 1 FROM user_tenant_roles utr
JOIN roles r ON utr.role_id = r.id
WHERE utr.user_id = p_user_id
AND utr.tenant_id = p_tenant_id
AND r.name = p_role_name
);
$$ LANGUAGE sql;
-- Policy using the function
CREATE POLICY select_documents_policy_with_func ON documents FOR SELECT
USING (
tenant_id = get_current_tenant_id() AND
(
user_has_role(get_current_user_id(), get_current_tenant_id(), 'admin')
OR
owner_id = get_current_user_id()
)
);This looks cleaner, but we've just hidden the performance problem. By default, PostgreSQL might re-evaluate user_has_role for every single row of the documents table. For a table with a million rows, that's a million function calls!
The Solution: `STABLE`, `IMMUTABLE`, and `LEAKPROOF`
We need to give the query planner hints about our function's behavior.
* VOLATILE (default): The function can do anything, including modifying the database. It must be re-evaluated on every call.
STABLE: The function cannot modify the database and is guaranteed to return the same results for the same arguments within a single statement*. This is perfect for our get_current_user_id() and user_has_role() functions, as the user and tenant context won't change mid-query.
* IMMUTABLE: The function is guaranteed to return the same result for the same arguments, forever. Think sin(x) or lower('TEXT'). This is too strong for our use case.
There's one more crucial keyword: LEAKPROOF. An RLS policy might be applied before a WHERE clause. If a function in a policy throws an error based on its inputs, it could potentially leak information about rows that the user shouldn't even know exist. Marking a function LEAKPROOF is a promise to the planner that it doesn't throw errors based on its arguments. This allows the planner to more aggressively optimize, including pushing down predicates before the RLS check.
Let's fix our function:
-- PRODUCTION-GRADE HELPER FUNCTION
CREATE OR REPLACE FUNCTION check_user_is_admin(p_user_id UUID, p_tenant_id UUID)
RETURNS BOOLEAN AS $$
SELECT EXISTS (
SELECT 1 FROM user_tenant_roles utr
JOIN roles r ON utr.role_id = r.id
WHERE utr.user_id = p_user_id
AND utr.tenant_id = p_tenant_id
AND r.name = 'admin'
);
$$ LANGUAGE sql STABLE LEAKPROOF; -- The magic keywords!
-- The performant policy
DROP POLICY IF EXISTS select_documents_policy_with_func ON documents;
CREATE POLICY select_documents_policy_performant ON documents FOR SELECT
USING (
tenant_id = get_current_tenant_id() AND
(
-- This will now be evaluated only once per query!
check_user_is_admin(get_current_user_id(), get_current_tenant_id())
OR
owner_id = get_current_user_id()
)
);With STABLE, the planner understands it can cache the result of check_user_is_admin() for the duration of the query, transforming a potentially O(N) operation into an O(1) one (where N is the number of rows in the table).
Performance Benchmark: The Impact of `STABLE`
Let's prove it. We'll generate some test data and run EXPLAIN ANALYZE.
-- Setup: 1 tenant, 2 users (1 admin, 1 member), 1 million documents
-- (Assume data has been populated)
-- As the admin user:
BEGIN;
SET LOCAL app.current_user_id = '...admin_uuid...';
SET LOCAL app.current_tenant_id = '...tenant_uuid...';
-- With the VOLATILE function (default)
EXPLAIN ANALYZE SELECT count(*) FROM documents;
-- Result will show a very high cost and loop count on the function call
-- With the STABLE function
EXPLAIN ANALYZE SELECT count(*) FROM documents;
-- Result will show the function is evaluated once ('InitPlan') and the cost is dramatically lower.
ROLLBACK;The difference is staggering. On a moderately sized table, the query can go from seconds to milliseconds just by adding STABLE LEAKPROOF.
Pattern 3: Caching Full Permission Sets in a JSONB GUC
Pattern 2 is great, but what if your authorization logic is even more complex? Imagine Attribute-Based Access Control (ABAC), where permissions depend on tags, document status, time of day, etc. Encoding all this logic in SQL functions can become a new maintenance burden.
The ultimate pattern for complex scenarios is to pre-compute the user's permissions in the application layer and pass them to PostgreSQL as a structured object, like a JSONB blob.
The Application Middleware Logic (Enhanced)
Our middleware now does more work:
userId, tenantId.- Serialize this permission set into a JSONB string.
- Set this single JSONB string as a transaction-local GUC.
// ... inside authMiddleware ...
const { userId, tenantId } = payload;
req.dbClient = await pool.connect();
// 1. Fetch user roles and permissions (this could be complex logic)
const permsQuery = `
SELECT json_build_object(
'roles', COALESCE(json_agg(DISTINCT r.name), '[]'::json),
'is_admin', EXISTS(SELECT 1 FROM user_tenant_roles utr JOIN roles r ON utr.role_id = r.id WHERE utr.user_id = $1 AND utr.tenant_id = $2 AND r.name = 'admin')
-- Add more complex permissions here, e.g., project access
) as permissions
FROM user_tenant_roles utr
JOIN roles r ON utr.role_id = r.id
WHERE utr.user_id = $1 AND utr.tenant_id = $2;
`;
const { rows } = await req.dbClient.query(permsQuery, [userId, tenantId]);
const permissions = rows[0]?.permissions || { roles: [], is_admin: false };
// 2. Serialize and set the GUC
const permissionsJson = JSON.stringify(permissions);
// 3. Set transaction variables
// Use a prepared statement or proper escaping for the JSON string to prevent injection
await req.dbClient.query(`
SET LOCAL app.current_user_id = '${userId}';
SET LOCAL app.current_tenant_id = '${tenantId}';
SET LOCAL app.permissions = '${permissionsJson}';
`);
next();
// ... rest of middlewareNow, for every transaction, PostgreSQL has a local variable app.permissions that looks like this: {"roles":["member"], "is_admin":false}.
The Ultra-Lean RLS Policies
The RLS policies become incredibly simple and fast. They are just doing key lookups in a JSONB object.
-- Helper function to get the JSONB permissions object
CREATE OR REPLACE FUNCTION get_current_permissions() RETURNS JSONB AS $$
DECLARE
perms_setting TEXT;
BEGIN
perms_setting := current_setting('app.permissions', true);
-- Return a default empty object if not set
RETURN COALESCE(perms_setting::JSONB, '{}'::JSONB);
EXCEPTION
-- Handle cases where the setting is not valid JSON
WHEN invalid_text_representation THEN
RETURN '{}'::JSONB;
END;
$$ LANGUAGE plpgsql STABLE LEAKPROOF;
-- The new, simplified policy
DROP POLICY IF EXISTS select_documents_policy_performant ON documents;
CREATE POLICY select_documents_policy_jsonb ON documents FOR SELECT
USING (
tenant_id = get_current_tenant_id() AND
(
-- Fast JSONB boolean check
(get_current_permissions() ->> 'is_admin')::boolean
OR
-- Fallback for non-admins
owner_id = get_current_user_id()
)
);Why is this better for complex cases?
JOINs to calculate permissions happen once per API request. The RLS policy, which runs on a potentially hot table, only performs a very fast JSONB lookup.can_view_financials? You add it to the JSONB object in your middleware and update the policy on the financials table. No complex SQL function refactoring is needed.Edge Cases and Final Production Considerations
* Superuser and BYPASSRLS: Remember that RLS does not apply to table owners, superusers, or roles with the BYPASSRLS attribute. Your application's runtime database user should be a non-superuser with NOBYPASSRLS. Reserve superuser access for migrations and emergency administrative tasks only.
Indexing for Policies: The predicates in your USING clause must be supported by indexes. In all our examples, the check tenant_id = get_current_tenant_id() is highly performant only because* we have an index on tenant_id. Without it, PostgreSQL would have to do a full table scan, even with RLS.
* FOR ALL vs USING: We've focused on SELECT policies (USING). For INSERT, UPDATE, and DELETE, you also have the WITH CHECK clause, which ensures that new or modified rows also conform to the policy.
* Views and RLS: RLS policies on underlying tables are enforced when a view is queried. This is a powerful way to expose a simplified or aggregated data model via a view while being confident that the underlying security rules are still in effect.
Conclusion
Row-Level Security in PostgreSQL is a production-ready feature for building secure, multi-tenant applications. However, moving beyond trivial examples requires a deep understanding of the query planner, transaction-level settings, and function volatility.
By progressing from simple JWT claims with GUCs to STABLE LEAKPROOF helper functions, and finally to caching full permission sets in JSONB, you can build an authorization layer that is not only robust and secure but also highly performant and scalable. The key is to shift the complex, per-request authorization logic out of the row-by-row policy evaluation and into a one-time setup at the beginning of the transaction. This architectural choice is the difference between an RLS implementation that scales and one that grinds your database to a halt.