Hierarchical Row-Level Security in PostgreSQL for Multi-Tenant SaaS

Beyond Basic Tenant Isolation: The Need for Hierarchical RLS

For any non-trivial SaaS application, simple tenant isolation via WHERE tenant_id = :current_tenant_id quickly becomes insufficient. Real-world business logic demands a more granular, hierarchical permission model. Consider a typical structure: an Organization has multiple Users. Some users are ORG_ADMINS, others are MEMBERS. Users are grouped into Teams, and Teams are assigned to Projects. An ORG_ADMIN should see all projects in their organization, while a MEMBER should only see projects their team is assigned to.

Implementing this logic exclusively in the application layer is a common but fragile pattern. It's prone to developer error, difficult to audit, and can be bypassed by direct database access or through unforeseen code paths. A far more robust solution is to enforce these complex security rules at the database level using PostgreSQL's Row-Level Security (RLS).

This article is not an introduction to RLS. It assumes you understand the basics of CREATE POLICY and ENABLE ROW LEVEL SECURITY. Instead, we will construct a production-grade, hierarchical RLS system from the ground up, focusing on three critical areas:

1. The Foundation: A Hierarchical Data Model

Our entire RLS strategy hinges on a well-defined data model that explicitly captures the relationships between entities. Let's define the core tables for our multi-tenant SaaS platform.

-- Organizations represent the primary tenant.
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL
);

-- Users belong to one organization.
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    email TEXT NOT NULL UNIQUE,
    -- For recursive manager hierarchy later
    manager_id UUID REFERENCES users(id) ON DELETE SET NULL
);

-- A simple roles enum for users within an organization.
CREATE TYPE org_role AS ENUM ('ORG_ADMIN', 'MEMBER');

-- Junction table for user roles within an organization.
CREATE TABLE user_organization_roles (
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    role org_role NOT NULL,
    PRIMARY KEY (user_id, organization_id)
);

-- Teams are containers for users within 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
);

-- Junction table for team membership.
CREATE TABLE team_members (
    team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    PRIMARY KEY (team_id, user_id)
);

-- Projects are the primary resource we want to protect.
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    -- The user who created the project
    created_by_user_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT
);

-- Junction table assigning teams to projects.
CREATE TABLE project_teams (
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    team_id UUID NOT NULL REFERENCES teams(id) ON DELETE CASCADE,
    PRIMARY KEY (project_id, team_id)
);

-- Add indexes for performance, crucial for RLS lookups.
CREATE INDEX ON users (organization_id);
CREATE INDEX ON user_organization_roles (user_id);
CREATE INDEX ON teams (organization_id);
CREATE INDEX ON team_members (user_id);
CREATE INDEX ON projects (organization_id);
CREATE INDEX ON project_teams (team_id);

This schema establishes clear ownership: Projects belong to Organizations, Users belong to Organizations, and Teams link them together. This structure is the bedrock upon which we'll build our security policies.

2. Establishing Secure Session Context

PostgreSQL's RLS doesn't magically know about your application's logged-in user. We must provide this context securely for every transaction. The common practice of creating a database role for every application user is not scalable for a SaaS application with thousands or millions of users.

The superior pattern is to use a single application database role and pass the application user's identity into the session using runtime configuration parameters.

The SET LOCAL Pattern

We can use SET LOCAL to define a custom parameter that is automatically cleared at the end of the transaction. This is critical when using a connection pool, as it prevents one user's session context from leaking into another's request.

Here's how an application-level middleware (e.g., in Node.js with node-postgres) would set this context after a user authenticates:

// Example Express.js middleware using a connection pool
const { Pool } = require('pg');
const pool = new Pool(); // Configure with your DB details

// This middleware should run AFTER your authentication middleware
async function setRlsContext(req, res, next) {
    // Assume `req.user` is populated by your auth logic (e.g., from a JWT)
    // req.user = { id: '...', organizationId: '...' }
    if (!req.user || !req.user.id || !req.user.organizationId) {
        // If no user, proceed without setting context. 
        // RLS policies should deny access to unauthenticated requests.
        return next(); 
    }

    // Get a client from the pool for this request
    const client = await pool.connect();
    // Attach the client to the request object so downstream handlers can use it
    req.dbClient = client;

    try {
        // Use a transaction to ensure settings are applied before any other query
        await client.query('BEGIN');

        // SET LOCAL is crucial. It scopes the setting to the current transaction.
        // We use text format for UUIDs.
        await client.query(`SELECT set_config('rls.user_id', $1::text, true)`, [req.user.id]);
        await client.query(`SELECT set_config('rls.org_id', $1::text, true)`, [req.user.organizationId]);

        // The `is_local` parameter (third argument) being `true` makes it a `SET LOCAL`.
        // If `false`, it would be a `SET` which persists for the life of the session (bad for connection pools).

        // We must handle the end of the request to release the client
        res.on('finish', async () => {
            try {
                // Commit transaction if successful, otherwise it will be rolled back on error
                if (res.statusCode < 400) {
                    await client.query('COMMIT');
                } else {
                    await client.query('ROLLBACK');
                }
            } finally {
                client.release();
            }
        });

        next();
    } catch (err) {
        // On error, rollback and release the client
        await client.query('ROLLBACK');
        client.release();
        next(err);
    }
}

// app.use(authenticationMiddleware);
// app.use(setRlsContext);

Now, within any transaction, we can reliably access the current user's ID and organization ID using current_setting('rls.user_id', true).

3. Crafting the Hierarchical Permission Function

With our context established, we can write the core logic. While it's possible to write RLS policies with complex subqueries, encapsulating the logic in a PL/pgSQL function is more maintainable, reusable, and easier to test.

Our goal is a function has_project_access(project_id UUID) that returns true if the current user is allowed to see the project, based on these rules:

• The user must be in the same organization as the project.

CREATE OR REPLACE FUNCTION has_project_access(p_project_id UUID) 
RETURNS BOOLEAN AS $$
DECLARE
    current_user_id UUID;
    current_org_id UUID;
    user_role org_role;
    project_org_id UUID;
BEGIN
    -- Safely get the current user and org IDs from the session context.
    -- The second argument `true` makes it return NULL if the setting is not found, preventing an error.
    current_user_id := current_setting('rls.user_id', true)::UUID;
    current_org_id := current_setting('rls.org_id', true)::UUID;

    -- If there's no user context, deny access immediately.
    IF current_user_id IS NULL OR current_org_id IS NULL THEN
        RETURN FALSE;
    END IF;

    -- Get the project's organization to ensure it matches the user's.
    -- This is a critical security check.
    SELECT organization_id INTO project_org_id FROM projects WHERE id = p_project_id;
    IF project_org_id != current_org_id THEN
        RETURN FALSE;
    END IF;

    -- Get the user's role within the organization.
    SELECT role INTO user_role FROM user_organization_roles
    WHERE user_id = current_user_id AND organization_id = current_org_id;

    -- Rule 2: Org Admins have access to all projects in their org.
    IF user_role = 'ORG_ADMIN' THEN
        RETURN TRUE;
    END IF;

    -- Rule 3: Members must be on a team assigned to the project.
    IF user_role = 'MEMBER' THEN
        RETURN EXISTS (
            SELECT 1
            FROM team_members tm
            JOIN project_teams pt ON tm.team_id = pt.team_id
            WHERE tm.user_id = current_user_id
              AND pt.project_id = p_project_id
        );
    END IF;

    -- Default deny
    RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

Critical Function Keywords:

* LANGUAGE plpgsql: Standard procedural language for PostgreSQL.

* STABLE: A performance hint to the query planner. It signifies that the function's result is consistent for the same arguments within a single query scan. It cannot modify the database.

SECURITY DEFINER: This is paramount. It means the function executes with the privileges of the user who defined the function, not the user who invokes* it. This prevents users from being blocked from reading the underlying tables (user_organization_roles, team_members) that the function needs to check permissions.

4. Applying the RLS Policies

Now we can apply this logic to our projects table.

-- First, enable RLS on the table.
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- This policy applies to all commands (SELECT, INSERT, UPDATE, DELETE).
-- The USING clause is checked for existing rows.
CREATE POLICY project_access_policy ON projects
AS PERMISSIVE FOR ALL
TO public
USING (has_project_access(id));

With this single policy, our rules are enforced for all operations. A user trying to SELECT a project they can't access will get an empty result. An UPDATE or DELETE on a forbidden project will fail as if the row doesn't exist.

The WITH CHECK Clause for Inserts/Updates

The USING clause covers reads and writes on existing rows. For INSERT and UPDATE operations, we also need to ensure the user isn't creating or moving data into a state they wouldn't be able to see. In our current setup, the USING clause effectively doubles as a WITH CHECK because the has_project_access function depends on the row's final state.

However, for more complex scenarios, you might need an explicit WITH CHECK. For example, ensuring a user can only create projects assigned to teams they are a member of:

-- A more explicit policy for INSERTS
ALTER POLICY project_access_policy ON projects
USING (has_project_access(id));

CREATE POLICY project_insert_policy ON projects
AS PERMISSIVE FOR INSERT
TO public
WITH CHECK (
    -- The new project must be in the user's organization
    organization_id = current_setting('rls.org_id', true)::UUID
    -- The creator must be the current user
    AND created_by_user_id = current_setting('rls.user_id', true)::UUID
);

This ensures that even if a user tries to INSERT a project with a different organization_id, the WITH CHECK clause will fail the operation.

5. Performance Deep Dive: RLS is Not Free

Implementing RLS without considering performance is a recipe for disaster. Every query against a protected table will now invoke your policy logic. A slow policy means a slow application.

Let's analyze a simple query:

EXPLAIN ANALYZE SELECT * FROM projects WHERE name LIKE 'A%';

Without RLS, the plan might be a simple Index Scan on the projects table.

With RLS, the plan will look vastly different. You will see a Filter condition that includes (has_project_access(id)). PostgreSQL will call this function for every row that passes the initial name LIKE 'A%' filter.

Optimization 1: LEAKPROOF and Costing

For functions that are STABLE and contain no sensitive information in their arguments, you can add the LEAKPROOF attribute.

CREATE OR REPLACE FUNCTION has_project_access(p_project_id UUID)
RETURNS BOOLEAN AS $$ ... $$ 
LANGUAGE plpgsql STABLE LEAKPROOF SECURITY DEFINER;

LEAKPROOF is a powerful hint to the planner. It indicates that the function has no side effects and its result depends only on its arguments. This allows the planner to potentially reorder operations and apply the RLS filter earlier and more efficiently, especially in complex joins. It's a critical optimization for RLS functions.

You can also assign a COST to the function to give the planner a more accurate idea of its execution expense, though the planner is often good at estimating this for simple PL/pgSQL.

Optimization 2: Inlining the Policy (Function vs. Subquery)

While functions are great for maintainability, they can introduce overhead. For some hot paths, inlining the logic directly into the policy as a subquery can yield better performance because it gives the planner more information to optimize the query as a whole.

Let's rewrite our policy without the function:

-- First, drop the old policy
DROP POLICY project_access_policy ON projects;

CREATE POLICY project_access_policy_inline ON projects
AS PERMISSIVE FOR ALL
TO public
USING (
    -- Basic tenancy check
    organization_id = current_setting('rls.org_id', true)::UUID
    AND (
        -- Rule 2: User is an ORG_ADMIN
        EXISTS (
            SELECT 1 FROM user_organization_roles
            WHERE user_id = current_setting('rls.user_id', true)::UUID
              AND role = 'ORG_ADMIN'
        )
        OR
        -- Rule 3: User is a member of an assigned team
        EXISTS (
            SELECT 1
            FROM team_members tm
            JOIN project_teams pt ON tm.team_id = pt.team_id
            WHERE tm.user_id = current_setting('rls.user_id', true)::UUID
              AND pt.project_id = projects.id
        )
    )
);

Trade-offs:

* Performance: The inlined subquery version can often be faster. The planner can see the entire logic and may be able to merge joins or choose more optimal query paths. Run EXPLAIN ANALYZE on your specific queries to verify.

* Maintainability: The function-based approach is far more DRY (Don't Repeat Yourself) and easier to reason about and test. The inlined version can become a copy-paste nightmare if the logic is needed for multiple tables.

Recommendation: Start with a well-defined, LEAKPROOF function. If and only if performance analysis shows the function call itself is a bottleneck for a critical query, consider inlining the policy for that specific hot path.

6. Advanced Scenario: Recursive Manager Hierarchy

What if a manager should be able to see all projects created by their direct and indirect reports? Our manager_id column in the users table sets us up for this. We can solve this with a recursive Common Table Expression (CTE) inside a new security function.

CREATE OR REPLACE FUNCTION is_in_management_chain(p_target_user_id UUID)
RETURNS BOOLEAN AS $$
DECLARE
    current_user_id UUID := current_setting('rls.user_id', true)::UUID;
BEGIN
    IF current_user_id IS NULL THEN
        RETURN FALSE;
    END IF;

    -- A user is always in their own "management chain"
    IF current_user_id = p_target_user_id THEN
        RETURN TRUE;
    END IF;

    RETURN EXISTS (
        WITH RECURSIVE subordinates AS (
            -- Non-recursive term: Start with the current user
            SELECT id FROM users WHERE id = current_user_id
            UNION ALL
            -- Recursive term: Join users to their managers
            SELECT u.id
            FROM users u
            INNER JOIN subordinates s ON u.manager_id = s.id
        )
        SELECT 1 FROM subordinates WHERE id = p_target_user_id
    );
END;
$$ LANGUAGE plpgsql STABLE LEAKPROOF SECURITY DEFINER;

Now, we can add a new policy (or modify the existing one) to grant access based on this hierarchy:

-- Create a new permissive policy for managers
CREATE POLICY project_manager_access_policy ON projects
AS PERMISSIVE FOR SELECT -- Only for reads, perhaps
TO public
USING (
    is_in_management_chain(created_by_user_id)
);

Because we defined the policies as PERMISSIVE, a row is visible if any permissive policy returns true. So, a user can see a project if project_access_policy passes OR if project_manager_access_policy passes. This allows for powerful, layered security rules.

Edge Case: security_barrier Views

A subtle but critical security risk arises when using RLS with views. The query planner may try to be clever and push down conditions from an outer query into the view's definition before the RLS policy is evaluated. This can leak information.

Consider a view:

CREATE VIEW project_details AS SELECT id, name FROM projects;

And a malicious query:

SELECT * FROM project_details WHERE id = (SELECT project_id FROM some_other_table WHERE secret_condition);

If the planner pushes the id = ... condition down, it might be evaluated before the RLS policy, potentially leaking whether a project exists based on the secret_condition.

To prevent this, use the security_barrier attribute on the view:

ALTER VIEW project_details SET (security_barrier = true);

This forces PostgreSQL to evaluate the view's RLS policies first, before applying any filters or joins from the outer query, closing this potential side-channel attack.

Conclusion: A Robust, Performant Security Layer

By embedding hierarchical permission logic directly into PostgreSQL using Row-Level Security, you create a single, auditable, and non-bypassable source of truth for data access. This approach significantly hardens your application's security posture compared to relying solely on application-layer checks.

However, this power comes with the responsibility of rigorous performance engineering. The key takeaways for a production-ready implementation are:

* Model Explicitly: Your database schema must clearly define ownership and hierarchies.

* Isolate Context: Use the SET LOCAL pattern with connection pools to safely transmit user identity to the database per-transaction.

* Encapsulate Logic: Start with SECURITY DEFINER, STABLE, LEAKPROOF PL/pgSQL functions for maintainability.

* Analyze Everything: Use EXPLAIN ANALYZE relentlessly. Understand the impact of your policies on query plans.

* Optimize Deliberately: Only move from functions to inlined subqueries when performance data proves it's necessary for a specific, critical workload.

* Index for Policies: Ensure your RLS functions and subqueries are supported by appropriate indexes on foreign keys and user/role lookup tables.

By following these advanced patterns, you can leverage PostgreSQL RLS to build highly secure, scalable, and complex multi-tenant SaaS applications where the data layer itself is the ultimate guardian of your users' data.