PostgreSQL RLS for Dynamic, Hierarchy-Based Multi-Tenancy
The Fragility of Application-Layer Tenancy
For any multi-tenant SaaS application, data isolation is not a feature; it's a foundational requirement. The most common implementation pattern involves diligently adding a WHERE organization_id = :current_org_id clause to every single database query. While simple to grasp, this approach is dangerously fragile. A single forgotten WHERE clause in a deeply nested repository method or a complex reporting query can lead to a catastrophic data leak, exposing one tenant's data to another. This is a constant source of anxiety in code reviews and a significant vector for security vulnerabilities.
Application-layer enforcement also struggles with complex tenancy models. Simple siloed tenants are one thing, but what about hierarchical structures? Consider a scenario where a parent corporation needs access to the data of its subsidiaries, but the subsidiaries cannot see the parent's or siblings' data. Or what if user roles within a tenancy dictate access to specific data subsets? Implementing this logic consistently across a large codebase is an exercise in futility, destined to become an unmaintainable web of conditional checks.
This is where PostgreSQL's Row-Level Security (RLS) becomes a game-changer. RLS moves the security boundary from the application into the database itself. It allows you to define fine-grained access policies directly on your tables, which PostgreSQL then automatically and transparently applies to every query. It's a non-bypassable enforcement mechanism. In this post, we will go deep into implementing a sophisticated, hierarchy-aware multi-tenancy model using RLS, covering the advanced patterns and performance considerations required for a production system.
Setting the Stage: The Hierarchical Data Model
To explore RLS in a realistic context, we first need a data model that represents hierarchical tenancy. Our model will consist of organizations that can be nested, users who belong to these organizations with specific roles, and a resource table (projects) that is owned by an organization.
Here is the core schema we'll be working with:
-- Enable necessary extensions
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "ltree";
-- Organizations table with self-referencing parent_id for hierarchy
CREATE TABLE organizations (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
parent_organization_id UUID REFERENCES organizations(id),
name TEXT NOT NULL,
path LTREE, -- For efficient hierarchy queries
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Create an index for ltree path operations
CREATE INDEX idx_organizations_path ON organizations USING GIST (path);
-- A trigger to automatically maintain the ltree path
CREATE OR REPLACE FUNCTION update_organization_path() RETURNS TRIGGER AS $$
DECLARE
parent_path LTREE;
BEGIN
IF NEW.parent_organization_id IS NULL THEN
NEW.path = to_char(NEW.id, 'FMU9999999999999999999999999999999999999')::ltree;
ELSE
SELECT path INTO parent_path FROM organizations WHERE id = NEW.parent_organization_id;
IF NOT FOUND THEN
RAISE EXCEPTION 'Invalid parent_organization_id %', NEW.parent_organization_id;
END IF;
NEW.path = parent_path || to_char(NEW.id, 'FMU999999999999999999999999999999999')::ltree;
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trigger_update_organization_path
BEFORE INSERT OR UPDATE OF parent_organization_id ON organizations
FOR EACH ROW EXECUTE FUNCTION update_organization_path();
-- Users table
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email TEXT NOT NULL UNIQUE,
password_hash TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Junction table for user roles within organizations
CREATE TYPE organization_role AS ENUM ('admin', 'member');
CREATE TABLE organization_memberships (
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
role organization_role NOT NULL DEFAULT 'member',
PRIMARY KEY (user_id, organization_id)
);
-- The resource table we want to protect
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
name TEXT NOT NULL,
data JSONB,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Critical index for RLS performance
CREATE INDEX idx_projects_organization_id ON projects(organization_id);We've intentionally included the ltree extension and a trigger to maintain the path column. While a recursive Common Table Expression (CTE) can also query hierarchies, it's often a performance bottleneck in RLS policies that run on every query. The ltree extension provides highly optimized operators for hierarchy path queries, which will be crucial for performance, as we'll see later.
The Core RLS Mechanism: Transaction-Scoped Context
RLS policies need a way to know who is making the request. The most robust and secure way to pass this context from your application to PostgreSQL is via session-level configuration parameters. We'll define our own custom parameters to hold the current user's identity and their active organization context.
Crucially, we must use SET LOCAL within a transaction. In a typical web application using a connection pool, the same database connection will be reused for requests from different users. Using SET (without LOCAL) would cause the setting to persist on the connection, creating a massive security hole where one user's context could leak into another's request. SET LOCAL ensures the setting is automatically discarded at the end of the transaction.
Here’s a production-grade pattern for a Node.js application using the pg library to manage this context safely:
import { Pool, PoolClient } from 'pg';
// Assume pool is configured and exported elsewhere
const pool = new Pool({
// ... connection config
});
interface UserContext {
userId: string;
organizationId: string;
userRole: 'admin' | 'member';
}
// This is the key function that wraps all database interactions
async function withDBClient<T>(
context: UserContext,
callback: (client: PoolClient) => Promise<T>
): Promise<T> {
const client = await pool.connect();
try {
// Start a transaction
await client.query('BEGIN');
// Set the session variables for RLS. Use SET LOCAL!
// We use pg's parameterized query feature to prevent SQL injection.
await client.query(`SET LOCAL app.current_user_id = $1`, [context.userId]);
await client.query(`SET LOCAL app.current_organization_id = $1`, [context.organizationId]);
await client.query(`SET LOCAL app.current_user_role = $1`, [context.userRole]);
// Execute the actual business logic
const result = await callback(client);
// Commit the transaction
await client.query('COMMIT');
return result;
} catch (error) {
// Rollback on error
await client.query('ROLLBACK');
throw error;
} finally {
// Release the client back to the pool
client.release();
}
}
// Example usage in a service layer
async function getProjectsForUser(context: UserContext): Promise<any[]> {
return withDBClient(context, async (client) => {
// This query does NOT need a `WHERE organization_id = ...` clause.
// RLS will handle it automatically.
const res = await client.query('SELECT id, name FROM projects');
return res.rows;
});
}This withDBClient wrapper is the cornerstone of our RLS integration. It guarantees that every query executed within its callback runs inside a transaction with the correct, non-leaking user context.
Implementing Hierarchical and Role-Based Policies
With our context mechanism in place, we can now define the RLS policies. The strategy is to start with a default-deny rule and then layer on specific ALLOW policies.
-- First, force all existing and future roles to be subject to RLS
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
-- This is our safety net. If no other policy grants access, this one denies it.
CREATE POLICY deny_all ON projects
FOR ALL
USING (false);SELECT Policy: This is the most complex policy. It must grant access if the project's organization_id belongs to the current user's organization or any of its descendants. CREATE POLICY select_projects ON projects
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM organizations
WHERE
-- The project's organization is a descendant of (or is) the user's current organization
organizations.id = projects.organization_id
AND organizations.path <@ (
SELECT path FROM organizations WHERE id = current_setting('app.current_organization_id')::uuid
)
)
); Here, the ltree operator <@ is doing the heavy lifting. It efficiently checks if one path is an ancestor of another. This single line replaces a costly recursive CTE, making the policy performant enough for production use.
INSERT, UPDATE, and DELETE: These policies often have different logic. * INSERT: A user should only be able to insert projects into organizations they are a direct member of. We use the WITH CHECK clause, which enforces the condition on new or updated rows.
CREATE POLICY insert_projects ON projects
FOR INSERT
WITH CHECK (
organization_id = current_setting('app.current_organization_id')::uuid
); * UPDATE: A user can update projects they have SELECT access to, but perhaps only if they are a member of that specific project's organization.
CREATE POLICY update_projects ON projects
FOR UPDATE
USING (
-- The USING clause for UPDATE determines which rows can be updated.
-- We can reuse the same logic as SELECT for visibility.
EXISTS (
SELECT 1 FROM organizations
WHERE organizations.id = projects.organization_id
AND organizations.path <@ (SELECT path FROM organizations WHERE id = current_setting('app.current_organization_id')::uuid)
)
)
WITH CHECK (
-- The WITH CHECK clause ensures you can't move the project to an org you don't have access to.
organization_id = current_setting('app.current_organization_id')::uuid
); * DELETE: Deletion is a destructive action, so let's restrict it to users with the admin role. This demonstrates how to incorporate role-based logic.
CREATE POLICY delete_projects_for_admins ON projects
FOR DELETE
USING (
-- Check for admin role from our session context
current_setting('app.current_user_role', true) = 'admin'
AND EXISTS (
SELECT 1 FROM organizations
WHERE organizations.id = projects.organization_id
AND organizations.path <@ (SELECT path FROM organizations WHERE id = current_setting('app.current_organization_id')::uuid)
)
); Note the true in current_setting('...', true). This tells PostgreSQL not to error if the setting is missing, which can be useful for flexibility.
Performance Deep Dive: The Cost of Security
RLS is not a free lunch. PostgreSQL implements RLS by rewriting your query and appending the policy conditions as a WHERE or WITH CHECK clause. A poorly written policy can devastate performance.
Let's analyze our SELECT policy's performance. Consider the query SELECT * FROM projects WHERE name = 'Project X';
Without RLS, the planner might use an index on the name column. With RLS, the query effectively becomes:
SELECT * FROM projects
WHERE name = 'Project X' AND (
EXISTS (
SELECT 1
FROM organizations
WHERE organizations.id = projects.organization_id
AND organizations.path <@ (
SELECT path FROM organizations WHERE id = '...' -- from current_setting
)
)
);The query planner now has a much more complex job. Here’s how to ensure it remains fast:
ltree vs. Recursive CTE: As mentioned, our use of ltree is the single most important optimization. A EXPLAIN ANALYZE on a policy using a recursive CTE would likely show a high cost and sequential scan, whereas the ltree version can use the GIST index on the path column for near-instantaneous lookups.organizations.id = projects.organization_id. This means the projects.organization_id column must be indexed. Our schema already includes idx_projects_organization_id, but its importance cannot be overstated.VOLATILE, forces the function to be re-evaluated for every row. If your function is deterministic based on its inputs (which RLS context functions usually are), declaring it as STABLE or IMMUTABLE can allow the planner to cache its result within a single query, providing a significant performance boost. CREATE OR REPLACE FUNCTION get_current_org_path() RETURNS ltree AS $$
BEGIN
RETURN (SELECT path FROM organizations WHERE id = current_setting('app.current_organization_id')::uuid);
END;
$$ LANGUAGE plpgsql STABLE;
-- Updated policy using the STABLE function
CREATE POLICY select_projects_optimized ON projects
FOR SELECT
USING (
EXISTS (
SELECT 1
FROM organizations
WHERE organizations.id = projects.organization_id
AND organizations.path <@ get_current_org_path()
)
);Edge Cases and Super-User Scenarios
A production system has needs beyond typical user access. Here’s how to handle common edge cases.
1. The Superuser / DBA
Database administrators and migration scripts often need to bypass RLS entirely. You can grant this permission to a specific role using the BYPASSRLS attribute.
-- Create a role for database administration tasks
CREATE ROLE db_admin LOGIN PASSWORD '...';
-- Grant the bypass privilege
ALTER ROLE db_admin BYPASSRLS;Any user logged in as db_admin will now ignore all RLS policies. Use this privilege with extreme caution.
2. Service Accounts and Internal Tools
Background workers, reporting services, or internal admin panels might need broader access.
* Pattern 1 (The Sledgehammer): Run the service as a role with BYPASSRLS. This is simple but risky, as a compromise of the service grants unrestricted data access.
* Pattern 2 (The Impersonator): The service authenticates as a specific user for the duration of a task. For a job processing data for Organization X, it would set its RLS context to that organization. This is much more secure as it adheres to the defined policies.
* Pattern 3 (The Explicit Grant): Create a dedicated policy for the service role.
-- Example for a reporting service that needs read-only access to all projects
CREATE ROLE reporting_service LOGIN PASSWORD '...';
CREATE POLICY allow_reporting_service ON projects
FOR SELECT
USING (current_user = 'reporting_service'); Because policies are additive (access is granted if any ALLOW policy passes), a user logged in as reporting_service would be granted SELECT access by this policy, even if they fail the tenancy checks of other policies.
3. Explicit Cross-Tenant Sharing
This is the hardest problem. What if a user in Org A needs access to a single project in Org B? Our current model doesn't allow this. The solution is to create an explicit sharing table.
CREATE TABLE project_shares (
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
PRIMARY KEY (project_id, user_id)
);Now, we must update our core SELECT policy to account for these explicit shares. This is typically done with an OR condition.
-- Drop the old policy first
DROP POLICY select_projects ON projects;
-- Create the new, more complex policy
CREATE POLICY select_projects_with_sharing ON projects
FOR SELECT
USING (
-- Condition 1: User has hierarchical access via their organization
EXISTS (
SELECT 1
FROM organizations
WHERE organizations.id = projects.organization_id
AND organizations.path <@ (SELECT path FROM organizations WHERE id = current_setting('app.current_organization_id')::uuid)
)
OR
-- Condition 2: The project has been explicitly shared with the current user
EXISTS (
SELECT 1
FROM project_shares
WHERE project_shares.project_id = projects.id
AND project_shares.user_id = current_setting('app.current_user_id')::uuid
)
);Performance Warning: OR conditions can be challenging for the query planner. Ensure that you have indexes on project_shares(project_id) and project_shares(user_id) to give the planner the best chance of executing this complex policy efficiently.
Conclusion: Robust Security at a Price
Implementing multi-tenancy with PostgreSQL's Row-Level Security is a significant step up in security and robustness compared to application-layer enforcement. It provides a centralized, non-bypassable, and highly flexible mechanism for enforcing complex data access rules directly at the data layer. By leveraging advanced features like the ltree extension and carefully managing transactional context, you can build a system that securely handles sophisticated hierarchical tenancy models.
However, this power comes with complexity and a performance cost. RLS is not a simple drop-in solution. It requires a deep understanding of PostgreSQL's execution planner, careful schema design, strategic indexing, and disciplined application-level integration. For applications where data isolation is a paramount security concern, the investment is well worth it, providing a level of assurance that application-layer checks can never truly match.