PostgreSQL RLS with JWTs for Multi-Tenant SaaS Authorization

October 3, 2025

19 min read

Goh Ling Yong

Technology enthusiast and software architect specializing in AI-driven development tools and modern software engineering practices. Passionate about the intersection of artificial intelligence and human creativity in building tomorrow's digital solutions.

The Fallacy of Application-Layer Tenant Isolation

In multi-tenant SaaS development, ensuring strict data isolation is paramount. A single bug leaking data between tenants can be an existential threat. The common approach is to meticulously add WHERE tenant_id = :current_tenant_id to every single database query. While functional, this pattern is fragile and fraught with risk:

Human Error: A developer can easily forget a WHERE clause on a new or modified query, instantly creating a catastrophic data leak vulnerability.

Code Bloat: Repetitive authorization logic clutters the data access layer, making it harder to read, maintain, and reason about.

Decentralized Logic: Authorization rules are scattered across the codebase. A change in access control policy requires a painstaking audit of every relevant query.

This approach places the entire burden of security on the application developer's constant vigilance. A more robust solution enforces data isolation at the last possible line of defense: the database itself.

This article details a production-grade pattern for achieving this using PostgreSQL's Row-Level Security (RLS), driven by claims securely passed from a JSON Web Token (JWT). We will bypass introductory concepts and dive straight into the implementation details, performance trade-offs, and edge cases you will encounter in a real-world system.

Our goal is to create a system where SELECT * FROM projects; is inherently safe, automatically filtered to the current user's tenant, as defined by their authenticated session.

The Core Mechanism: Bridging JWT Claims to the Postgres Session

The entire pattern hinges on a powerful yet underutilized PostgreSQL feature: the ability to set custom configuration parameters within a session. We can use a client's JWT claims to configure the database session for the duration of a single transaction, making those claims available directly within SQL for RLS policies.

Here's the flow:

A user authenticates, receiving a JWT containing claims like tenant_id, user_id, and role.

The user sends an API request with the JWT in the Authorization header.

An API middleware intercepts the request, verifies the JWT, and extracts the claims.

Before executing any business logic, the middleware issues special SET commands to the database connection for that request.

RLS policies within the database read these session variables to make authorization decisions.

The critical command is SET LOCAL. Using SET (without LOCAL) would set the variable for the entire life of the connection. In a pooled connection environment, this would cause catastrophic data leaks as the settings from one user's request would persist for the next user who receives that same connection from the pool. SET LOCAL scopes the setting to the current transaction only, automatically reverting it upon COMMIT or ROLLBACK.

Let's establish the schema we'll be working with.

Sample Multi-Tenant Schema

sql

-- Create a dedicated namespace for our application's session variables
-- This prevents conflicts with standard PostgreSQL settings.

-- Tenants table
CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Users table with a link to a tenant
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL DEFAULT 'member', -- e.g., 'member', 'admin'
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- A resource table that belongs to a tenant
CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Create indexes that will support our RLS policies
CREATE INDEX idx_users_tenant_id ON users(tenant_id);
CREATE INDEX idx_projects_tenant_id ON projects(tenant_id);

Section 1: Implementing RLS Policies

With the schema in place, we can now define the security policies. The first step is to enable RLS on the target tables. By default, even with RLS enabled, no rows are visible or modifiable until a policy is created.

sql

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;

-- IMPORTANT: By default, table owners (the role that created the table)
-- bypass RLS. For true security, we should prevent this.
ALTER TABLE projects FORCE ROW LEVEL SECURITY;
ALTER TABLE users FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY is a critical hardening step. It ensures that even the table owner is subject to RLS policies, preventing accidental bypasses during migrations or administrative tasks run by a privileged user.

Creating the Claim-Aware Helper Function

To avoid repeating code in our policies, we'll create a helper function to safely retrieve and cast a JWT claim from the session settings. Using a function also allows us to add error handling or default values centrally.

sql

CREATE OR REPLACE FUNCTION current_tenant_id() RETURNS UUID AS $$
DECLARE
    tenant_id_str TEXT;
BEGIN
    tenant_id_str := current_setting('app.jwt.claims.tenant_id', true);
    IF tenant_id_str IS NULL OR tenant_id_str = '' THEN
        RAISE EXCEPTION 'app.jwt.claims.tenant_id is not set';
    END IF;
    RETURN tenant_id_str::UUID;
EXCEPTION
    WHEN UNTOWARD_EXCEPTION_TYPE THEN -- More specific exception handling if needed
        RAISE EXCEPTION 'Invalid UUID format for app.jwt.claims.tenant_id';
END;
$$ LANGUAGE plpgsql STABLE;

current_setting('app.jwt.claims.tenant_id', true): The true argument makes the function return NULL if the setting is not found, rather than throwing an error. This allows us to handle the missing setting gracefully.

STABLE: This tells the query planner that the function's result is stable within a single query scan. It's a performance hint that prevents re-evaluation for every row.

Defining the RLS Policies

Now we can create concise and readable policies using our helper function.

`projects` Table Policies

sql

-- Policy for SELECT, UPDATE, DELETE: Users can only affect projects in their own tenant.
CREATE POLICY tenant_isolation_policy ON projects
    FOR ALL
    USING (tenant_id = current_tenant_id());

-- Policy for INSERT: New projects must be created for the user's current tenant.
-- 'WITH CHECK' is used for INSERT and UPDATE operations.
-- It ensures that the new/updated row conforms to the policy.
CREATE POLICY tenant_insert_policy ON projects
    FOR INSERT
    WITH CHECK (tenant_id = current_tenant_id());

Wait, we have a problem. We defined two policies on projects. For a given command (INSERT, SELECT, etc.), policies are combined with OR. Our tenant_isolation_policy applies to ALL commands, including INSERT. This means an INSERT would be allowed if (tenant_id = current_tenant_id()) OR (tenant_id = current_tenant_id()), which is redundant but correct. A better practice is to be more specific.

Let's refine this for clarity and correctness:

sql

-- Drop the old policy to redefine
DROP POLICY tenant_isolation_policy ON projects;
DROP POLICY tenant_insert_policy ON projects;

-- A single, comprehensive policy is often cleaner.
-- The USING clause applies to SELECT, UPDATE, DELETE.
-- The WITH CHECK clause applies to INSERT, UPDATE.
CREATE POLICY tenant_policy ON projects
    FOR ALL
    USING (tenant_id = current_tenant_id())
    WITH CHECK (tenant_id = current_tenant_id());

This single policy is more robust. It ensures that:

You can only see/update/delete rows matching your tenant (USING).

You can only insert/update rows to have a tenant_id matching your own (WITH CHECK).

Role-Based Access within a Tenant

What if we want admins to be able to see all users within their tenant, but members can only see themselves? We need another claim, app.jwt.claims.role and app.jwt.claims.user_id.

sql

-- Helper functions for other claims
CREATE OR REPLACE FUNCTION current_user_id() RETURNS UUID AS $$
    SELECT current_setting('app.jwt.claims.user_id')::UUID;
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION current_user_role() RETURNS TEXT AS $$
    SELECT current_setting('app.jwt.claims.role');
$$ LANGUAGE sql STABLE;

-- Now, define a more complex policy on the 'users' table
CREATE POLICY user_access_policy ON users
    FOR SELECT
    USING (
        -- Rule 1: You must be in the same tenant.
        tenant_id = current_tenant_id() AND
        (
            -- Rule 2: You are an 'admin' (can see all users in the tenant).
            current_user_role() = 'admin' OR
            -- Rule 3: You are a 'member' viewing your own record.
            id = current_user_id()
        )
    );

-- Policy for updates: You can only update your own user record.
CREATE POLICY user_update_policy ON users
    FOR UPDATE
    USING (id = current_user_id())
    WITH CHECK (id = current_user_id() AND tenant_id = current_tenant_id());

This demonstrates how you can compose complex business logic directly in the database, creating a single source of truth for authorization.

Section 2: Application-Layer Integration

Now, let's implement the middleware that bridges the gap between the HTTP request and the database session. We'll use Node.js with Express and the pg library as an example, but the pattern is identical in any language (Go, Python, Java, etc.).

The JWT Authorization Middleware

The core of the implementation is a middleware that runs after authentication but before the route handler. It acquires a database client from the pool and sets the session variables for the transaction.

javascript

// lib/db.js - Database pool setup
const { Pool } = require('pg');

const pool = new Pool({
    connectionString: process.env.DATABASE_URL,
});

module.exports = { pool };

// middleware/db-session-context.js
const { pool } = require('../lib/db');
const jwt = require('jsonwebtoken'); // Example library

// This is the key function to set session context.
async function setSessionContext(client, claims) {
    // Use a transaction to ensure SET LOCAL is scoped correctly.
    // The BEGIN command is implicitly handled by many drivers when you start
    // running queries on a checked-out client.
    // We MUST use parameterized queries to prevent SQL injection from claims!
    await client.query('SET LOCAL app.jwt.claims.tenant_id = $1', [claims.tenant_id]);
    await client.query('SET LOCAL app.jwt.claims.user_id = $1', [claims.user_id]);
    await client.query('SET LOCAL app.jwt.claims.role = $1', [claims.role]);
}

// The Express middleware
const dbSessionContext = async (req, res, next) => {
    const authHeader = req.headers.authorization;
    if (!authHeader || !authHeader.startsWith('Bearer ')) {
        // This should be handled by an upstream auth middleware,
        // but we add a check for safety.
        return next();
    }

    const token = authHeader.split(' ')[1];
    let claims;
    try {
        // In a real app, verify the token signature!
        claims = jwt.verify(token, process.env.JWT_SECRET);
    } catch (err) {
        return res.status(401).send('Invalid token');
    }

    // Attach a function to the request object to get a context-aware DB client.
    // We do this instead of attaching the client directly to prevent the client
    // from being used before the context is set.
    req.getDbClient = async () => {
        if (req.dbClient) {
            return req.dbClient;
        }

        const client = await pool.connect();
        req.dbClient = client; // Store for reuse within the same request

        try {
            await setSessionContext(client, claims);
        } catch (err) {
            // If setting context fails, release the client and throw.
            client.release();
            delete req.dbClient;
            throw new Error(`Failed to set database session context: ${err.message}`);
        }

        // Ensure the client is always released when the response finishes.
        res.on('finish', () => {
            if (req.dbClient) {
                req.dbClient.release();
            }
        });

        return client;
    };

    next();
};

module.exports = { dbSessionContext };

Usage in a Route Handler

Now, your route handlers become beautifully simple. They don't need to know about tenants or users; they just execute queries.

javascript

// routes/projects.js
const express = require('express');
const router = express.Router();

router.get('/', async (req, res, next) => {
    try {
        const client = await req.getDbClient();
        // This query is now SAFE. It will only return projects for the
        // tenant specified in the JWT, thanks to RLS.
        const { rows } = await client.query('SELECT id, name FROM projects');
        res.json(rows);
    } catch (err) {
        next(err);
    }
});

router.post('/', async (req, res, next) => {
    const { name } = req.body;
    if (!name) {
        return res.status(400).send('Project name is required');
    }

    try {
        const client = await req.getDbClient();
        // The INSERT is also SAFE. The RLS WITH CHECK option will throw an
        // error if we tried to insert for a different tenant_id, and our
        // policy ensures the correct tenant_id is used.
        const { rows } = await client.query(
            'INSERT INTO projects (name) VALUES ($1) RETURNING *',
            [name]
        );
        res.status(201).json(rows[0]);
    } catch (err) {
        // A policy violation will result in a 'new row violates row-level security policy' error
        if (err.code === '44000') { // check_violation in some contexts
            return res.status(403).send('Forbidden: RLS policy violation');
        }
        next(err);
    }
});

module.exports = router;

Notice the complete absence of WHERE tenant_id = ... in the business logic. The authorization is now transparently handled by the database.

Section 3: Advanced Patterns and Performance

This pattern is powerful, but it's not without its complexities and performance considerations.

Performance Impact of RLS

RLS is not free. It adds overhead to query planning. Every time a query is run against an RLS-enabled table, PostgreSQL must incorporate the policy's conditions into the query plan. Since our policy depends on current_setting(), which can change for every query, the planner may be less able to cache generic plans.

Let's analyze the impact.

Query without RLS:

sql

EXPLAIN ANALYZE SELECT * FROM projects WHERE name = 'Project X';

Sample Output:

text

Index Scan using projects_name_idx on projects (cost=0.42..8.44 rows=1 width=58) (actual time=0.025..0.026 rows=1 loops=1)
  Index Cond: (name = 'Project X'::text)
Planning Time: 0.150 ms
Execution Time: 0.045 ms

Query with RLS:

First, set the context:

sql

SET LOCAL app.jwt.claims.tenant_id = '...';
EXPLAIN ANALYZE SELECT * FROM projects WHERE name = 'Project X';

Sample Output:

text

Index Scan using projects_name_idx on projects (cost=0.42..8.45 rows=1 width=58) (actual time=0.035..0.036 rows=1 loops=1)
  Index Cond: (name = 'Project X'::text)
  Filter: (tenant_id = current_tenant_id())
Planning Time: 0.250 ms
Execution Time: 0.055 ms

In this trivial example, the overhead is minimal. However, on complex queries with many joins across multiple RLS-enabled tables, the planning time can increase. The key takeaway is that the RLS policy condition (tenant_id = current_tenant_id()) is added as a Filter. If you have an index on (tenant_id, name), PostgreSQL is smart enough to use it, turning the filter into part of the index scan itself, which is much more efficient.

Key Performance Strategy: Ensure your indexes support your RLS policies. The most common pattern is to have a composite index starting with tenant_id on all tenant-scoped tables.

Edge Case: Cross-Tenant Access for a Single User

What if a user can belong to multiple tenants and needs to switch between them? Or what if a support user needs access to a specific list of tenants?

Approach 1: Issue a new JWT. The user selects a tenant in the UI, and the application issues a new, short-lived JWT with the new tenant_id claim. This is the cleanest and most secure approach, as each request is explicitly scoped to a single tenant.

Approach 2: Array-based claims. For more complex scenarios, you could pass an array of accessible tenants in the JWT.

JWT Payload:

json

{
  "user_id": "user-uuid",
  "accessible_tenants": ["tenant-uuid-1", "tenant-uuid-2"]
}

Your session context setup and RLS policy would need to change.

Middleware:

javascript

// claims.accessible_tenants is ['tenant-uuid-1', 'tenant-uuid-2']
await client.query(
    'SET LOCAL app.jwt.claims.accessible_tenants = $1',
    [JSON.stringify(claims.accessible_tenants)]
);

RLS Policy:

sql

-- Helper to parse the JSON array
CREATE OR REPLACE FUNCTION current_accessible_tenants() RETURNS UUID[] AS $$
    SELECT ARRAY(SELECT jsonb_array_elements_text(current_setting('app.jwt.claims.accessible_tenants')::jsonb))::UUID[];
$$ LANGUAGE sql STABLE;

-- Updated policy
CREATE POLICY multi_tenant_access_policy ON projects
    FOR ALL
    USING (tenant_id = ANY(current_accessible_tenants()));

This is significantly more flexible but adds complexity and can be slightly less performant than a direct equality check. The tenant_id = ANY(...) clause can still efficiently use an index on tenant_id.

Edge Case: Superuser / System-Level Access

Your application will inevitably need a way to bypass RLS for administrative tasks, support tools, or data migrations.

DO NOT simply connect with a superuser role. This is a massive security risk.

The Correct Pattern: Create a specific role for this purpose and grant it the BYPASS RLS attribute.

sql

-- Create a role that can bypass RLS. It should have a strong, unique password
-- and should NOT be the same role your application uses for normal operations.
CREATE ROLE internal_support WITH LOGIN PASSWORD 'a-very-strong-password' BYPASSRLS;

-- Grant it necessary permissions on the tables
GRANT SELECT, INSERT, UPDATE, DELETE ON projects, users TO internal_support;

Your application's internal tooling would then connect to the database using this internal_support role. The main application API should never use this role. This creates a clear separation of concerns and privileges.

Section 4: Security Hardening and Pitfalls

While powerful, this pattern has sharp edges.

1. Connection Pool Poisoning Revisited

I must reiterate: NEVER use SET in a pooled environment. Always use SET LOCAL. A single SET command will permanently taint a connection until it's closed, leading to one user executing queries with another user's permissions. This is the single most dangerous mistake you can make with this pattern.

Your middleware's cleanup logic is also critical. Ensure the connection is always released back to the pool, which implicitly ends the transaction and clears the LOCAL settings.

2. SQL Injection via Claims

In our Node.js example, we used parameterized queries to set the session variables: client.query('SET LOCAL ... = $1', [claim]). This is non-negotiable.

Never construct the SET query using string concatenation:

javascript

// DANGEROUS - DO NOT DO THIS
const tenantId = claims.tenant_id; // e.g., 'foo'; DROP TABLE users; --'
await client.query(`SET LOCAL app.jwt.claims.tenant_id = '${tenantId}'`);

This would allow an attacker who can control their JWT payload to execute arbitrary SQL.

3. The `SECURITY DEFINER` Trap

Sometimes, you may need a policy to check against a table the current user doesn't have direct access to. You might be tempted to write a SECURITY DEFINER function, which executes with the privileges of the user who defined the function, not the user who is calling it.

sql

-- A contrived example
CREATE FUNCTION is_tenant_active(check_tenant_id UUID) RETURNS BOOLEAN AS $$
    SELECT status = 'active' FROM tenants WHERE id = check_tenant_id;
$$ LANGUAGE sql SECURITY DEFINER;

-- Policy
CREATE POLICY active_tenant_only ON projects
    USING (is_tenant_active(tenant_id));

This is extremely dangerous if not handled with extreme care. A SECURITY DEFINER function is a primary vector for privilege escalation attacks in PostgreSQL. An attacker could exploit it by manipulating the search_path to make your function call a malicious function instead of the intended one.

Hardening SECURITY DEFINER functions:

Set a safe search_path: Always begin your SECURITY DEFINER function with SET search_path = pg_catalog, public; to prevent search path hijacking.

Use it sparingly: Only use SECURITY DEFINER when there is absolutely no other way. 99% of RLS use cases do not require it.

Conclusion: A Paradigm Shift in Application Security

Implementing Row-Level Security driven by JWT claims is a paradigm shift, not just a technical pattern. It moves authorization logic from a scattered, error-prone application layer into a centralized, non-bypassable enforcement point within the database.

The initial setup is more complex than adding WHERE clauses. It requires a deeper understanding of PostgreSQL's session management, transaction scope, and security model. However, the long-term benefits for a multi-tenant SaaS are immense:

Provable Security: Data isolation is guaranteed at the lowest level. A forgotten WHERE clause in the application can no longer cause a data leak.

Simplified Application Code: Business logic becomes cleaner and more focused, liberated from the cross-cutting concern of tenant filtering.

Centralized Authorization Logic: Your RLS policies become the single source of truth for data access rules, making audits and modifications vastly simpler and safer.

By carefully managing session context, writing performant policies, and hardening against security pitfalls, you can build a highly secure and maintainable multi-tenant architecture that scales with your application's complexity.

The Fallacy of Application-Layer Tenant Isolation

The Core Mechanism: Bridging JWT Claims to the Postgres Session

Sample Multi-Tenant Schema

Section 1: Implementing RLS Policies

Creating the Claim-Aware Helper Function

Defining the RLS Policies

`projects` Table Policies

Role-Based Access within a Tenant

Section 2: Application-Layer Integration

The JWT Authorization Middleware

Usage in a Route Handler

Section 3: Advanced Patterns and Performance

Performance Impact of RLS

Edge Case: Cross-Tenant Access for a Single User

Edge Case: Superuser / System-Level Access

Section 4: Security Hardening and Pitfalls

1. Connection Pool Poisoning Revisited

2. SQL Injection via Claims

3. The `SECURITY DEFINER` Trap

Conclusion: A Paradigm Shift in Application Security

Found this article helpful?