Advanced RLS Policies for Hierarchical Multi-Tenancy in PostgreSQL
The Fragility of Simple RLS in Complex Hierarchies
In the world of multi-tenant SaaS applications, Row-Level Security (RLS) in PostgreSQL is a foundational tool for data isolation. The canonical example is straightforward: a posts table has a tenant_id, and a simple policy ensures users only see their own tenant's data.
-- The classic, simple RLS policy
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
CREATE POLICY select_posts_for_tenant
ON posts FOR SELECT
USING (tenant_id = current_setting('app.tenant_id')::uuid);This pattern is effective for flat tenancy models but shatters when faced with the reality of modern B2B applications: hierarchical permissions. Consider a common structure: an Organization has multiple Teams, and each Team has multiple Projects. A user might be an ORG_ADMIN, granting them access to all projects within their entire organization, or a TEAM_MEMBER, restricting their access to projects within a specific team.
Attempting to model this with the simple policy is untenable. You could try to denormalize the organization_id onto every project, but the policy logic becomes a convoluted mess of OR conditions trying to reconcile a user's various roles. Performance degrades, and security becomes brittle. The fundamental problem is that the access decision for a given row depends not on a single identifier, but on its position within a relational graph.
This article presents a robust, performant, and secure pattern for implementing hierarchical RLS in PostgreSQL. We will build a system that can efficiently answer the question: "Does the current user, with their assigned roles, have access to this specific row through any path in the tenancy hierarchy?"
Step 1: Modeling the Hierarchy
Before writing policies, we need a data model that accurately represents the hierarchy. For this walkthrough, we'll use an Organizations -> Teams -> Projects structure. We also need to model user membership and roles.
Here is our foundational schema:
-- Use pgcrypto for UUIDs
CREATE EXTENSION IF NOT EXISTS "pgcrypto";
-- The top of the hierarchy
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL
);
-- Teams belong to an organization
CREATE TABLE teams (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
name TEXT NOT NULL
);
-- Projects belong to a team
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
name TEXT NOT NULL,
-- For later optimization, we'll denormalize the organization_id
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE
);
-- Standard users table
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE
);
-- Membership and roles are key
CREATE TYPE membership_role AS ENUM ('ORG_ADMIN', 'TEAM_MEMBER');
CREATE TABLE memberships (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
-- A membership can be at the organization or team level
organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
team_id UUID REFERENCES teams(id) ON DELETE CASCADE,
role membership_role NOT NULL,
-- Ensure a membership is tied to either an org or a team, but not both
CONSTRAINT chk_membership_target CHECK (num_nonnulls(organization_id, team_id) = 1)
);
-- Indexes for performance
CREATE INDEX ON teams (organization_id);
CREATE INDEX ON projects (team_id);
CREATE INDEX ON projects (organization_id);
CREATE INDEX ON memberships (user_id);
CREATE INDEX ON memberships (organization_id);
CREATE INDEX ON memberships (team_id);This schema establishes the relationships. A user's access is defined by their entries in the memberships table. An ORG_ADMIN is linked to an organization_id, while a TEAM_MEMBER is linked to a team_id.
Step 2: The Naive Approach - Recursive Lookups in Policies
Our goal is to create a policy on projects that checks if the current user has access. A direct approach might be to write a function that performs this hierarchical check and call it directly from the policy.
Let's define what access means:
A user can access a project if:
ORG_ADMIN of the project's parent organization.TEAM_MEMBER of the project's parent team.We can encapsulate this logic in a function.
CREATE OR REPLACE FUNCTION can_user_access_project(p_project_id UUID, p_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
RETURN EXISTS (
SELECT 1
FROM projects p
JOIN teams t ON p.team_id = t.id
JOIN organizations o ON t.organization_id = o.id
LEFT JOIN memberships m_org ON m_org.organization_id = o.id AND m_org.role = 'ORG_ADMIN'
LEFT JOIN memberships m_team ON m_team.team_id = t.id AND m_team.role = 'TEAM_MEMBER'
WHERE p.id = p_project_id
AND (m_org.user_id = p_user_id OR m_team.user_id = p_user_id)
);
END;
$$ LANGUAGE plpgsql STABLE;A naive RLS policy would then call this function for every row:
-- DO NOT USE THIS IN PRODUCTION
CREATE POLICY select_projects_hierarchical_naive
ON projects FOR SELECT
USING (can_user_access_project(id, current_setting('app.user_id')::uuid));Why is this a performance disaster?
The USING clause is evaluated for every single row of the projects table that the query optimizer considers. If you have a million projects, this function will be invoked up to a million times during a full table scan. The repeated JOINs inside the function create a massive performance bottleneck. This is a classic N+1 query problem, but at the database security layer.
The query plan for a simple SELECT * FROM projects would be catastrophic, showing repeated executions of the function's logic.
Step 3: The Core Pattern - Pre-calculation and Session Memoization
The performant solution is to invert the logic. Instead of checking each row against the user's permissions (row -> user), we should first determine all the entities the user can access and then check if the row belongs to that set (user -> row).
We can calculate this set of accessible entities once per transaction/request and store the result in a session-level variable using PostgreSQL's Grand Unified Configuration (GUC) variables. RLS policies can then perform a simple, fast check against this memoized data.
The pattern:
- At the beginning of an application request, call a single SQL function.
organization_ids and team_ids the user can access based on their memberships.app.accessible_team_ids).- The RLS policies on all tables become trivial checks against these pre-populated variables.
Let's build the context-setting function. This is the heart of our system.
-- This function should be run with the privileges of a user who can read the membership tables.
-- SECURITY DEFINER is necessary if the application user role cannot see these tables directly.
CREATE OR REPLACE FUNCTION setup_session_context(p_user_id UUID)
RETURNS void AS $$
DECLARE
accessible_org_ids_array UUID[];
accessible_team_ids_array UUID[];
BEGIN
-- Use SECURITY DEFINER safely
-- SET search_path = 'public'; -- Uncomment and set to your schema if needed
-- 1. Get all teams the user is a direct member of.
WITH direct_teams AS (
SELECT team_id
FROM memberships
WHERE user_id = p_user_id AND role = 'TEAM_MEMBER' AND team_id IS NOT NULL
),
-- 2. Get all teams belonging to organizations where the user is an admin.
admin_org_teams AS (
SELECT t.id
FROM teams t
JOIN memberships m ON t.organization_id = m.organization_id
WHERE m.user_id = p_user_id AND m.role = 'ORG_ADMIN'
),
-- 3. Combine them into a single set of team IDs.
all_accessible_teams AS (
SELECT team_id FROM direct_teams
UNION
SELECT id FROM admin_org_teams
)
SELECT array_agg(team_id) INTO accessible_team_ids_array FROM all_accessible_teams;
-- 4. Get all organizations the user is an admin of.
SELECT array_agg(organization_id) INTO accessible_org_ids_array
FROM memberships
WHERE user_id = p_user_id AND role = 'ORG_ADMIN' AND organization_id IS NOT NULL;
-- 5. Set the custom GUCs. Use `true` for the is_local parameter to scope it to the current transaction.
-- The `|| '{}'` ensures we don't try to set a NULL value, which would error.
PERFORM set_config('app.accessible_team_ids', array_to_string(accessible_team_ids_array || '{}', ','), true);
PERFORM set_config('app.accessible_org_ids', array_to_string(accessible_org_ids_array || '{}', ','), true);
PERFORM set_config('app.user_id', p_user_id::text, true);
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;Key Implementation Details:
* SECURITY DEFINER: This is critical. The function runs with the privileges of the user who defined it, not the user who invokes it. This allows a low-privilege application user to call this function, which can then read sensitive tables like memberships to calculate permissions. WARNING: This is a security-sensitive feature. You must prevent SQL injection within the function. Since we are only using a UUID input, the risk is low, but it's good practice to set a safe search_path inside the function.
* set_config(name, value, is_local): This sets a custom GUC. is_local = true is crucial; it scopes the setting to the current transaction. When the transaction commits or rolls back, the setting vanishes, ensuring no data leakage between requests.
* array_to_string: GUCs can only store strings. We serialize our UUID arrays into a comma-separated string. The policies will deserialize them.
Step 4: Implementing Performant RLS Policies
With our context-setting function in place, the RLS policies become incredibly simple and fast.
First, let's enable RLS on our target tables.
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;
-- IMPORTANT: By default, no rows are visible. We must create policies.
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
ALTER TABLE teams FORCE ROW LEVEL SECURITY;
ALTER TABLE organizations FORCE ROW LEVEL SECURITY;Now, the policies themselves:
Policy for projects:
CREATE POLICY select_projects_optimized
ON projects FOR SELECT
USING (
team_id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
);Policy for teams:
CREATE POLICY select_teams_optimized
ON teams FOR SELECT
USING (
id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
);Policy for organizations:
An organization is visible if the user is an admin of it, OR if they are a member of any team within it.
CREATE POLICY select_organizations_optimized
ON organizations FOR SELECT
USING (
-- User is a direct admin of the org
id = ANY(string_to_array(current_setting('app.accessible_org_ids', true), ',')::UUID[])
OR
-- User has access to a team within this org (requires a subquery)
EXISTS (
SELECT 1 FROM teams t
WHERE t.organization_id = organizations.id
AND t.id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
)
);Analysis of the Optimized Policies:
* current_setting(name, true): The true argument gracefully handles cases where the setting might not exist, returning NULL instead of throwing an error.
* string_to_array(...)::UUID[]: This efficiently converts our comma-separated string back into a UUID array.
* = ANY(...): This is a highly optimized way to check for membership in an array. PostgreSQL is excellent at handling this operation.
Now, the query plan for SELECT * FROM projects is beautiful. It will show a simple index scan or bitmap scan on projects, with a filter condition that is constant for the duration of the query. The expensive hierarchical lookup is gone.
Step 5: Application Integration
This database-level setup must be driven by the application. For every incoming authenticated API request, the backend must execute setup_session_context before running any business logic queries.
Example in a Node.js Express application with node-postgres:
// Middleware to set the RLS context for every request
app.use(async (req, res, next) => {
if (req.user && req.user.id) {
// Get a client from the connection pool
const client = await pool.getClient();
req.dbClient = client; // Attach client to the request object
try {
// This is the crucial step. Set the context for the transaction.
// We wrap everything in a transaction.
await client.query('BEGIN');
await client.query('SELECT setup_session_context($1)', [req.user.id]);
next();
} catch (err) {
await client.query('ROLLBACK');
client.release();
next(err);
}
} else {
next();
}
});
// In your route handler
app.get('/api/projects', async (req, res, next) => {
try {
// The RLS policies are now automatically enforced for this query
const { rows } = await req.dbClient.query('SELECT id, name FROM projects');
await req.dbClient.query('COMMIT'); // Commit the transaction
res.json(rows);
} catch (err) {
await req.dbClient.query('ROLLBACK');
next(err);
} finally {
if (req.dbClient) {
req.dbClient.release(); // Release the client back to the pool
}
}
});This integration ensures that for the lifetime of that database connection serving the request, all queries are correctly and performantly scoped to the user's permissions.
Step 6: Handling Edge Cases and Write Operations
A robust RLS implementation must also secure write operations (INSERT, UPDATE, DELETE). This is handled by the WITH CHECK clause in policy definitions.
INSERT Operations:
A user should only be able to create a project in a team they have access to.
CREATE POLICY insert_projects_optimized
ON projects FOR INSERT
WITH CHECK (
team_id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
);If a user tries to INSERT a project with a team_id not in their app.accessible_team_ids list, the database will raise a new row violates row-level security policy error.
UPDATE Operations:
This is more complex. A user must not be able to move a project to a team they don't have access to. The USING clause controls which rows are visible/updatable, and the WITH CHECK clause validates the new state of the row after the update.
CREATE POLICY update_projects_optimized
ON projects FOR UPDATE
USING (
-- User must have access to the project's CURRENT team
team_id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
)
WITH CHECK (
-- The project's NEW team must also be accessible
team_id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
);DELETE Operations:
The DELETE policy only needs a USING clause, as it just checks if the user has permission to see (and therefore delete) the row.
CREATE POLICY delete_projects_optimized
ON projects FOR DELETE
USING (
team_id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
);Superuser/Admin Bypass:
For internal tooling or superadmins, you need a way to bypass RLS. By default, table owners and superusers bypass RLS. However, you often have application-level admins who are not Postgres superusers. A common pattern is to grant them a special role and check for it.
-- In your setup_session_context function, you can add a bypass check
PERFORM set_config('app.is_admin', (SELECT is_admin FROM users WHERE id = p_user_id)::text, true);
-- Then, modify your policies
CREATE POLICY select_projects_optimized
ON projects FOR SELECT
USING (
current_setting('app.is_admin', true) = 'true'
OR
team_id = ANY(string_to_array(current_setting('app.accessible_team_ids', true), ',')::UUID[])
);Step 7: Benchmarking the Performance Gain
Let's quantify the difference. We'll populate our tables with 1 organization, 100 teams, 1,000,000 projects, and a single user who is an admin of the organization.
Scenario 1: Naive Function-in-Policy Approach
Query: EXPLAIN ANALYZE SELECT COUNT(*) FROM projects;
-- Result from the naive approach
Aggregate (cost=100018256.42..100018256.43 rows=1 width=8) (actual time=12543.123..12543.124 rows=1 loops=1)
-> Seq Scan on projects (cost=0.00..100015756.42 rows=1000000 width=0) (actual time=0.231..12489.567 rows=1000000 loops=1)
Filter: can_user_access_project(id, '...'::uuid)
Planning Time: 0.156 ms
Execution Time: 12543.201 msExecution Time: ~12.5 seconds. The planner has to call our expensive function for every single row.
Scenario 2: Optimized Session Memoization Approach
First, we run SELECT setup_session_context('...');. Then we run the same query.
Query: EXPLAIN ANALYZE SELECT COUNT(*) FROM projects;
-- Result from the optimized approach
Aggregate (cost=21915.22..21915.23 rows=1 width=8) (actual time=158.432..158.433 rows=1 loops=1)
-> Bitmap Heap Scan on projects (cost=4427.72..20665.22 rows=500000 width=0) (actual time=35.123..135.876 rows=1000000 loops=1)
Recheck Cond: (team_id = ANY ('{uuid1,uuid2,...}'::uuid[]))
-> Bitmap Index Scan on projects_team_id_idx (cost=0.00..4302.72 rows=500000 width=0) (actual time=33.567..33.568 rows=1000000 loops=1)
Index Cond: (team_id = ANY ('{uuid1,uuid2,...}'::uuid[]))
Planning Time: 0.234 ms
Execution Time: 158.512 msExecution Time: ~158 milliseconds. This is nearly an 80x performance improvement. The query plan shows an efficient Bitmap Index Scan using the pre-calculated array of team_ids. This is the difference between a production-ready system and one that will fall over under minimal load.
Conclusion
Implementing hierarchical Row-Level Security in PostgreSQL requires moving beyond basic patterns. By embracing a strategy of one-time permission calculation and session-level memoization, we can build systems that are simultaneously secure, flexible, and highly performant. The key is to shift the complex, graph-traversal logic from a per-row check into a single, upfront operation at the start of a transaction. This setup_session_context pattern, combined with simple, fast RLS policies reading from custom GUCs, provides a robust foundation for any multi-tenant application with complex permissioning needs. While it requires careful implementation and application-level coordination, the resulting performance and security benefits are essential for building scalable, enterprise-grade software.