PostgreSQL Hierarchical RLS: Advanced Multi-Tenant Security Patterns

The Architectural Ceiling of Simple Tenant ID Checks

In the world of multi-tenant SaaS applications, the standard approach to data isolation is often a tenant_id column on every relevant table. The application layer then diligently adds a WHERE tenant_id = ? clause to every single query. While functional, this pattern is brittle. A single forgotten WHERE clause in a complex JOIN or a subquery can lead to catastrophic data leakage. PostgreSQL's Row-Level Security (RLS) offers a robust, database-enforced solution to this problem.

However, most RLS tutorials stop at the simple tenant_id check. This falls short for modern applications that require hierarchical permissions. Consider a common B2B SaaS model:

* An Organization signs up.

* The Organization has multiple Teams.

* Each Team has multiple Users.

* Users create Projects, which contain Tasks.

In this model, the access rules are nuanced:

A simple WHERE tenant_id = current_tenant_id() policy is completely inadequate here. This article dissects the implementation of a sophisticated, hierarchical RLS system that models this complexity directly within the database, providing a powerful and secure foundation for your application.

We will build a complete system, covering secure context passing from the application, crafting multi-layered policies, enforcing write integrity, and critically, analyzing and optimizing the performance of these policies in a production environment.

The Data Model: A Foundation for Hierarchy

Let's establish our schema. This structure is foundational to the policies we'll write. Note the clear hierarchical relationships flowing from organizations down to tasks.

-- Enable pgcrypto for UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Organizations: The top-level tenant
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
);

-- Users: The actors in our system, with roles
CREATE TYPE user_role AS ENUM ('member', 'org_admin');

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    team_id UUID REFERENCES teams(id) ON DELETE SET NULL,
    email TEXT NOT NULL UNIQUE,
    role user_role NOT NULL DEFAULT 'member'
);

-- Projects: A resource owned by a team within an organization
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    name TEXT NOT NULL
);

-- Tasks: A sub-resource belonging 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 NOT NULL DEFAULT FALSE
);

-- Create indexes to support our RLS policies later
CREATE INDEX ON teams (organization_id);
CREATE INDEX ON users (organization_id, team_id);
CREATE INDEX ON projects (organization_id, team_id);
CREATE INDEX ON tasks (project_id);

Section 1: Establishing Secure Application Context

RLS policies need to know who is asking. Hard-coding user IDs into policies is impossible. Instead, we must pass context from our application to the database for the duration of a request. The most secure and scalable method is using PostgreSQL's session configuration parameters.

Attempting to create a database role for every application user is an anti-pattern in large-scale SaaS. It's an administrative nightmare and scales poorly. A better approach is to have the application connect with a single, non-privileged role and set session variables that RLS policies can then read.

The JWT-to-Session Pipeline

In a typical web application, a user's identity is encapsulated in a JWT. This token contains claims we can use to bootstrap our RLS context. Our goal is to reliably transfer these claims into PostgreSQL session variables at the beginning of every database transaction.

Here's a production-grade pattern using Node.js with Express and node-postgres (pg). This middleware intercepts every authenticated request, extracts JWT claims, and sets them as transaction-local variables.

// middleware/set-db-context.js
const { pool } = require('../db'); // Your configured pg.Pool instance

// This middleware assumes a previous middleware has verified the JWT
// and attached the decoded payload to req.user

async function setDbContext(req, res, next) {
    // We only need context for authenticated routes
    if (!req.user) {
        return next();
    }

    const { userId, orgId, teamId, role } = req.user;

    // Check for essential claims
    if (!userId || !orgId || !role) {
        return res.status(400).send('Incomplete authentication context.');
    }

    // Acquire a client from the pool for this request
    const client = await pool.connect();
    req.dbClient = client; // Attach client to request object

    try {
        // CRITICAL: Use SET LOCAL to scope settings to the current transaction.
        // This prevents context from leaking between requests when using a connection pool.
        await client.query('BEGIN');

        // Use prepared statements to prevent SQL injection, even for settings.
        // The 'true' flag in current_setting() below will return NULL if not set,
        // so we must handle that in our policies.
        await client.query(`SET LOCAL app.current_user_id = $1`, [userId]);
        await client.query(`SET LOCAL app.current_organization_id = $1`, [orgId]);
        await client.query(`SET LOCAL app.current_role = $1`, [role]);

        // teamId can be null for an org_admin not assigned to a specific team
        if (teamId) {
            await client.query(`SET LOCAL app.current_team_id = $1`, [teamId]);
        }

        res.on('finish', async () => {
            // This event listener ensures we clean up, even if the request handler fails.
            if (req.dbClient) {
                try {
                    // COMMIT or ROLLBACK would have already been called by the route handler.
                    // If not, we should probably roll back here as a safety measure.
                    // For simplicity, we just release.
                } finally {
                    req.dbClient.release();
                }
            }
        });

        next();
    } catch (err) {
        // If context setting fails, release the client and send an error.
        client.release();
        res.status(500).send('Failed to set database context.');
    }
}

// In your route handler, you would use req.dbClient
// and explicitly commit or rollback.

// Example route:
app.get('/projects', authMiddleware, setDbContext, async (req, res) => {
    try {
        const { rows } = await req.dbClient.query('SELECT * FROM projects');
        await req.dbClient.query('COMMIT');
        res.json(rows);
    } catch (err) {
        await req.dbClient.query('ROLLBACK');
        res.status(500).send('Error fetching projects');
    }
});

Key Architectural Decision: SET LOCAL vs. SET

* SET: Sets a variable for the entire session. In a connection-pooled environment, a session (connection) can be reused by a different user for a subsequent request. This would cause catastrophic context leakage, where User A sees User B's data.

SET LOCAL: Sets a variable only for the current transaction*. When the transaction ends (COMMIT or ROLLBACK), the setting is reverted. This is the only safe option for connection-pooled applications.

Section 2: Crafting Hierarchical RLS Policies

With our context established, we can now define the RLS policies. The core principle is to translate our business access rules into SQL predicates.

First, we must enable RLS on each table we want to protect.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
-- Repeat for users, teams, etc.

The `USING` Clause for Read Access (`SELECT`)

The USING clause is the heart of RLS for read operations (SELECT, and also for defining the scope of UPDATE/DELETE). It's a predicate that PostgreSQL implicitly adds to the WHERE clause of any query against the table. If the expression evaluates to true for a given row, that row is visible.

Let's create the policy for the projects table.

-- A helper function to safely get a UUID setting. Returns NULL if not set or invalid.
CREATE OR REPLACE FUNCTION get_setting_uuid(setting_name TEXT) RETURNS UUID AS $$
BEGIN
    RETURN current_setting(setting_name, true)::UUID;
EXCEPTION WHEN OTHERS THEN
    RETURN NULL;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Policy for projects
CREATE POLICY select_projects ON projects
FOR SELECT
USING (
    -- Rule 1: The project must belong to the user's organization.
    -- This is our primary security boundary.
    organization_id = get_setting_uuid('app.current_organization_id')
    AND (
        -- Rule 2: An 'org_admin' can see all projects in their organization.
        current_setting('app.current_role', true) = 'org_admin'
        OR
        -- Rule 3: A 'member' can only see projects belonging to their specific team.
        team_id = get_setting_uuid('app.current_team_id')
    )
);

This single policy elegantly handles our hierarchical requirements:

* If current_setting('app.current_role', true) = 'org_admin', the second part of the AND clause becomes true, and the team_id check is ignored. The admin sees all projects within their organization_id.

* If the user is not an org_admin, the policy then requires that the project's team_id matches the user's team_id from the session context.

Chained Policies and Implicit Permissions

Now consider the tasks table. A task belongs to a project. A user should only be able to see tasks if they can see the parent project. We don't need to repeat the entire role logic. We can leverage the fact that RLS policies on other tables will still be in effect within a subquery.

CREATE POLICY select_tasks ON tasks
FOR SELECT
USING (
    -- A user can see a task if and only if they can see the project it belongs to.
    EXISTS (
        SELECT 1
        FROM projects
        WHERE projects.id = tasks.project_id
        -- The RLS policy 'select_projects' is AUTOMATICALLY APPLIED to this subquery.
    )
);

This is a powerful and maintainable pattern. The security logic for projects is defined in one place. The tasks policy simply defers to it. If you later change the rules for who can see a project (e.g., adding a project_manager role), the tasks policy automatically inherits the new logic without modification.

Section 3: Enforcing Data Integrity with `WITH CHECK`

RLS isn't just for reading data; it's also for controlling writes (INSERT, UPDATE). This is handled by the WITH CHECK clause.

USING: Applies to rows that are visible or can be acted upon* (SELECT, UPDATE, DELETE).

WITH CHECK: Applies to new or updated row data. It ensures that a row, after* an INSERT or UPDATE, would still be visible according to the policy's predicate.

`INSERT` Policies: Preventing Data Misappropriation

A user should only be able to create a project for their own team within their own organization. A WITH CHECK policy enforces this at the database level, making it impossible for a compromised API or a buggy client to insert data into the wrong tenant's scope.

CREATE POLICY insert_projects ON projects
FOR INSERT
WITH CHECK (
    -- The new project's organization_id MUST match the user's organization.
    organization_id = get_setting_uuid('app.current_organization_id')
    AND (
        -- An org_admin can create a project for any team in the org.
        -- We must trust the application to provide a valid team_id from that org.
        current_setting('app.current_role', true) = 'org_admin'
        OR
        -- A member MUST create the project in their own team.
        team_id = get_setting_uuid('app.current_team_id')
    )
);

If a member from Team A tries to INSERT a project with team_id set to Team B's ID, this policy will cause the database to raise a new row violates row-level security policy error, even if both teams are in the same organization.

`UPDATE` Policies: Preventing Privilege Escalation

UPDATE policies are often the most complex. They need to define both which rows can be targeted (USING) and what the final state of the row can be (WITH CHECK). It's common practice to combine them into a single FOR ALL or FOR UPDATE policy.

CREATE POLICY update_projects ON projects
FOR UPDATE
-- The USING clause is the same as our SELECT policy. You can only update what you can see.
USING (
    organization_id = get_setting_uuid('app.current_organization_id')
    AND (
        current_setting('app.current_role', true) = 'org_admin'
        OR
        team_id = get_setting_uuid('app.current_team_id')
    )
)
-- The WITH CHECK clause ensures the updated row remains valid.
WITH CHECK (
    -- You can NEVER change a project's organization.
    -- This prevents an org_admin from moving a project to a different tenant.
    organization_id = get_setting_uuid('app.current_organization_id')
    -- An org_admin is allowed to re-assign a project to a different team within the same org.
    -- A member cannot re-assign projects.
);

This UPDATE policy demonstrates a crucial security consideration. The USING clause lets an org_admin see (and therefore target for UPDATE) all projects in their organization. The WITH CHECK clause, however, acts as a second line of defense. It ensures that even if an admin can target any project, they cannot execute an update that would move the project to a different organization_id, effectively preventing them from exfiltrating data to another tenant they might control.

Section 4: Performance, Optimization, and Edge Cases

Implementing RLS is not free. Every query against a protected table is rewritten by the planner to include the policy predicates. Poorly written policies can have a severe performance impact.

`EXPLAIN ANALYZE` is Your Ground Truth

Before and after enabling RLS, you must analyze your key queries. Let's look at a query for an org_admin:

EXPLAIN ANALYZE SELECT * FROM projects;

Without RLS:

Seq Scan on projects  (cost=0.00..25.88 rows=1588 width=88) (actual time=0.005..0.025 rows=1588 loops=1)
Planning Time: 0.057 ms
Execution Time: 0.043 ms

With RLS:

Seq Scan on projects  (cost=0.00..33.85 rows=529 width=88) (actual time=0.012..0.055 rows=530 loops=1)
  Filter: ((organization_id = '...'::uuid) AND (((current_setting('app.current_role'::text, true)) = 'org_admin'::text) OR (team_id = '...'::uuid)))
Planning Time: 0.125 ms
Execution Time: 0.078 ms

The key is the Filter line. PostgreSQL has added our policy directly to the query plan. In this case, the cost is minimal. However, if the policy contained a slow function or a complex subquery, this cost could become substantial.

Indexing for Your Policies

Your RLS predicates are just as important as your application's WHERE clauses. They must be supported by indexes.

Our policy USING (organization_id = ... AND ( ... OR team_id = ...)) heavily relies on organization_id and team_id. The composite index we created earlier, CREATE INDEX ON projects (organization_id, team_id);, is critical for making these lookups fast.

`LEAKPROOF` Functions and `SECURITY DEFINER`

Sometimes, a policy requires a complex check that is best encapsulated in a function. Be extremely careful.

CREATE OR REPLACE FUNCTION is_user_in_org(p_user_id UUID, p_org_id UUID) RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (SELECT 1 FROM users WHERE id = p_user_id AND organization_id = p_org_id);
END;
$$ LANGUAGE plpgsql;

If this function is used in a policy, the query planner might try to be clever and execute it only for some rows, potentially leaking information about which rows caused the function to be called. To prevent this, for any function used in a security policy that accesses other tables, you should define it as LEAKPROOF.

Furthermore, if the function needs to access a table the current application user doesn't have direct SELECT permissions on (because all access is governed by RLS), you must define it with SECURITY DEFINER. This makes the function execute with the privileges of the user who defined the function (typically a superuser), not the user who called it.

CREATE OR REPLACE FUNCTION is_user_in_org(p_user_id UUID, p_org_id UUID) RETURNS BOOLEAN AS $$
BEGIN
    RETURN EXISTS (SELECT 1 FROM users WHERE id = p_user_id AND organization_id = p_org_id);
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER LEAKPROOF;

* STABLE: Tells the planner the function returns the same result for the same arguments within a single scan.

* SECURITY DEFINER: Runs as the owner. Use with extreme caution.

* LEAKPROOF: A promise to the planner that the function has no side effects and its return value depends only on its arguments, preventing it from leaking data from rows it was not supposed to see.

Edge Case: Superusers and `BYPASSRLS`

Superusers and any role granted the BYPASSRLS attribute will completely ignore all RLS policies. This is a massive security switch. Your primary application database role should NEVER have this privilege.

Reserve this for a separate administrative/migration role. When you run pg_dump, for example, you must connect as a role that can bypass RLS to get a complete backup.

Edge Case: Views and `security_barrier`

If you create a view on a table with RLS, there's a subtle risk. The query planner might rearrange the query and push down parts of the main query's WHERE clause to be evaluated before the RLS policy's predicate. In some esoteric cases, this can be exploited to infer data.

To prevent this, you can define a view with the security_barrier option.

CREATE VIEW secure_projects WITH (security_barrier = true) AS
SELECT id, name, team_id, organization_id
FROM projects;

This forces PostgreSQL to evaluate the RLS policies for the underlying table (projects) before evaluating any WHERE clauses from the query that uses the view, ensuring security is always applied first.

Conclusion: A Production Checklist for Hierarchical RLS

Implementing a hierarchical RLS system is a significant architectural decision that pays dividends in security and maintainability. It moves security from a fragile, application-level concern to a robust, database-enforced guarantee.

Before deploying to production, validate your implementation against this checklist: