PostgreSQL RLS with JWTs for Granular Multi-Tenant API Auth

The Fallacy of Application-Layer Authorization in Multi-Tenant Systems

In most multi-tenant SaaS architectures, the default pattern for data isolation is painstakingly manual. A user authenticates, their tenant_id is fetched, and every subsequent database query is manually decorated with a WHERE tenant_id = ? clause. This is typically handled by middleware, base repository classes, or ORM scopes. While functional, this approach is a ticking time bomb of security vulnerabilities and technical debt.

The Core Problem: This pattern places the entire burden of security on the application developer's unerring discipline. A single forgotten WHERE clause in a complex JOIN or a new, rushed API endpoint can lead to a catastrophic data leak, exposing one tenant's data to another. The logic is scattered, duplicated, and brittle. As role-based access control (RBAC) requirements grow—managers can see their team's data, admins see all tenant data, users only see their own—the application-layer logic becomes a convoluted mess of if/else statements that are difficult to test and reason about.

This article presents a robust alternative: pushing the authorization logic down into the database itself using PostgreSQL's Row-Level Security (RLS). We will implement a pattern where a stateless JWT, the source of truth for user identity, directly drives granular RLS policies. The application becomes a simple proxy, setting the security context at the beginning of a transaction and letting the database enforce the rules. This hardens security, dramatically simplifies application code, and co-locates access policies with the data they protect.

The Architectural Foundation: JWT Claims as PostgreSQL Session Variables

The linchpin of this architecture is the secure transfer of the user's identity and permissions from the stateless application server to the stateful database session. We achieve this by encoding the necessary authorization context into a JWT and then using a middleware to translate these claims into transaction-scoped PostgreSQL session variables.

1. Crafting an RLS-Aware JWT

Your JWT payload must contain all the information an RLS policy might need to make an authorization decision. Avoid the temptation to fetch this data from the database on every request; the JWT is your cached, cryptographically-verified source of truth.

A well-structured payload might look like this:

{
  "sub": "user_a1b2c3d4", // User's unique ID
  "tenant_id": "tenant_e5f6g7h8",
  "role": "manager",
  "permissions": ["invoice:read", "invoice:create"],
  "iat": 1678886400,
  "exp": 1678890000
}

2. The Connection Middleware: Bridging JWT to the DB Session

This is the most critical piece of application code in the entire pattern. When a request with a valid JWT arrives, and a database connection is acquired from the pool, we must configure that connection's session before any other query is run. We use SET LOCAL to ensure these settings are automatically discarded at the end of the transaction, preventing context leakage between requests sharing the same pooled connection.

Here is a production-grade example using Node.js with the pg library. This function wraps the database client to enforce context setting.

// db.js - PostgreSQL connection pool setup
const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

// A higher-order function to get a client with RLS context set
async function getClientWithRLS(jwtPayload, callback) {
  const client = await pool.connect();
  try {
    // Start a transaction
    await client.query('BEGIN');

    // Set session variables as transaction-local. These are cleared on COMMIT/ROLLBACK.
    // Using ::text cast is a good practice for safety.
    await client.query(`SET LOCAL app.current_user_id = '${jwtPayload.sub}'::text;`);
    await client.query(`SET LOCAL app.current_tenant_id = '${jwtPayload.tenant_id}'::text;`);
    await client.query(`SET LOCAL app.current_user_role = '${jwtPayload.role}'::text;`);

    // Execute the business logic within the callback
    const result = await callback(client);

    // Commit the transaction
    await client.query('COMMIT');
    return result;
  } catch (e) {
    // Rollback on error
    await client.query('ROLLBACK');
    throw e;
  } finally {
    // ALWAYS release the client back to the pool
    client.release();
  }
}

module.exports = { getClientWithRLS };

In your Express middleware or service layer, you would use it like this:

// authMiddleware.js
const jwt = require('jsonwebtoken');

function authenticate(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1];
  if (token == null) return res.sendStatus(401);

  jwt.verify(token, process.env.JWT_SECRET, (err, user) => {
    if (err) return res.sendStatus(403);
    req.user = user; // user is the decoded JWT payload
    next();
  });
}

// invoiceController.js
const { getClientWithRLS } = require('./db');

app.get('/invoices', authenticate, async (req, res) => {
  try {
    const invoices = await getClientWithRLS(req.user, async (client) => {
      // Notice: NO `WHERE tenant_id = ?` clause here!
      const { rows } = await client.query('SELECT id, amount, due_date FROM invoices;');
      return rows;
    });
    res.json(invoices);
  } catch (error) {
    console.error('Error fetching invoices:', error);
    res.status(500).send('Internal Server Error');
  }
});

Note the profound simplification in the controller. The query is a simple SELECT * FROM invoices. The application code is now blissfully unaware of multi-tenancy; the database handles it transparently and securely.

Implementing Core RLS Policies for Multi-Tenancy

With the application-side plumbing in place, let's define the database rules. We'll start with a basic schema.

-- schema.sql

CREATE TABLE tenants (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL
);

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL DEFAULT 'viewer' -- e.g., 'viewer', 'manager', 'admin'
);

CREATE TABLE invoices (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id),
    created_by_id UUID NOT NULL REFERENCES users(id),
    amount NUMERIC(10, 2) NOT NULL,
    due_date DATE NOT NULL
);

Now, we create our first, most fundamental policy: tenant isolation. This policy ensures that any query (SELECT, INSERT, UPDATE, DELETE) on the invoices table can only interact with rows matching the app.current_tenant_id session variable.

-- Enable RLS on the table. This is a critical, often-forgotten step.
-- Without this, no policies will be enforced.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- By default, if RLS is enabled, access is denied. We need a permissive policy to grant access.
CREATE POLICY tenant_isolation ON invoices
    FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

USING vs. WITH CHECK:

The current_setting() function is the database-side counterpart to our SET LOCAL command. It safely reads the session variable. The ::uuid cast is crucial for type-matching against our tenant_id column.

Advanced Role-Based Access Control with Multiple Policies

Simple tenant isolation is powerful, but real-world applications require more granular, role-based rules. PostgreSQL's RLS is elegantly designed for this. We can create multiple PERMISSIVE policies on the same table, and a user is granted access if any of the policies for the given command evaluate to true.

Let's implement the following business rules:

First, we drop our simple policy and create a more sophisticated set.

-- Drop the old policy to replace it with role-specific ones
DROP POLICY IF EXISTS tenant_isolation ON invoices;

-- Policy for Admins: Unrestricted access within the tenant
CREATE POLICY admin_full_access ON invoices
    FOR ALL
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid AND current_setting('app.current_user_role') = 'admin')
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid AND current_setting('app.current_user_role') = 'admin');

-- Policy for Managers: Can read all, but only write their own
CREATE POLICY manager_access ON invoices
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid AND current_setting('app.current_user_role') = 'manager');

CREATE POLICY manager_write_own ON invoices
    FOR INSERT, UPDATE, DELETE
    USING (created_by_id = current_setting('app.current_user_id')::uuid AND current_setting('app.current_user_role') = 'manager')
    WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid); -- Still ensure they can't insert into another tenant

-- Policy for Viewers: Read-only access within the tenant
CREATE POLICY viewer_read_only ON invoices
    FOR SELECT
    USING (tenant_id = current_setting('app.current_tenant_id')::uuid AND current_setting('app.current_user_role') = 'viewer');

This demonstrates the power of composition. Instead of one monolithic policy with complex CASE statements, we define separate, understandable policies for each role and command. A user with the 'manager' role will satisfy the USING clause of manager_access for a SELECT query, and the USING clause of manager_write_own for a DELETE query (but only on their own invoices).

Performance Considerations and Optimization

While RLS is a powerful security feature, a naive implementation can cripple your application's performance. The expressions in your USING clauses are added to the query plan for every single query on an RLS-enabled table. Optimization is not optional.

1. Indexing for RLS Predicates

This is the single most important performance optimization. Any column used in an RLS policy's USING clause must be indexed. In our example, tenant_id, created_by_id, and role (on the users table) are prime candidates.

-- CRITICAL: Index the columns used in RLS policies.
CREATE INDEX idx_invoices_tenant_id ON invoices(tenant_id);
CREATE INDEX idx_invoices_created_by_id ON invoices(created_by_id);

Let's analyze the impact with EXPLAIN ANALYZE. Consider a query SELECT * FROM invoices WHERE id = ? by a 'manager'.

Without an index on tenant_id:

-- EXPLAIN ANALYZE SELECT * FROM invoices WHERE id = 'some_invoice_id';
Seq Scan on invoices  (cost=0.00..5890.00 rows=1 width=128) (actual time=0.020..25.123 rows=1 loops=1)
  Filter: ((id = 'some_invoice_id'::uuid) AND (tenant_id = current_setting('app.current_tenant_id')::uuid) AND (current_setting('app.current_user_role') = 'manager'::text))
  Rows Removed by Filter: 99999
Planning Time: 0.150 ms
Execution Time: 25.145 ms

PostgreSQL is forced to do a full table scan (Seq Scan), checking the RLS policy against every single row, even though we are querying by primary key. This is a performance disaster.

With an index on tenant_id:

-- Now with CREATE INDEX idx_invoices_tenant_id ON invoices(tenant_id);
Index Scan using invoices_pkey on invoices  (cost=0.42..8.44 rows=1 width=128) (actual time=0.035..0.036 rows=1 loops=1)
  Index Cond: (id = 'some_invoice_id'::uuid)
  Filter: ((tenant_id = current_setting('app.current_tenant_id')::uuid) AND (current_setting('app.current_user_role') = 'manager'::text))
  Rows Removed by Filter: 0
Planning Time: 0.210 ms
Execution Time: 0.055 ms

The query planner is now smart enough to use the primary key index (invoices_pkey) first to find the row, and then apply the RLS filter. If you had a composite index on (tenant_id, id), it might be even more efficient. The key is that the planner can use indexes to satisfy the RLS predicates quickly without a full scan.

2. The `current_setting()` Function and Query Planning

The current_setting() function is marked as STABLE, not IMMUTABLE. This means its value is stable within a single query, but can change between queries. This has a subtle but important impact on the query planner. The planner cannot cache results based on this function's output across different queries. This is generally the desired behavior, but it means there's a small, non-zero overhead for invoking it. In performance-critical hot paths, this can be a factor, but for 99% of web application use cases, the security and simplicity gains far outweigh this minor cost.

Handling Edge Cases and Production Pitfalls

The real world is messy. Here’s how to handle the inevitable complexities.

1. The Connection Pooling Trap

As shown in our Node.js example, managing session variables with connection pools is fraught with peril. A developer might be tempted to set the variables once when the connection is established. This is wrong and will cause data leakage. The same physical connection will be reused by different users. The SET LOCAL ... command within a transaction is the only safe way. Every time you check out a connection from the pool to serve a user request, you must wrap it in a transaction and set the RLS context for that transaction.

2. Superuser and System Access

Your background workers, database migration scripts, or internal admin tools need to operate on all data, bypassing RLS. There are two primary approaches:

CREATE ROLE internal_service_role LOGIN PASSWORD '...';
ALTER ROLE internal_service_role BYPASSRLS;

-- Now, any connection made with this role will ignore all RLS policies.

3. Cross-Tenant Data Access (The Support Admin Problem)

What happens when a legitimate user, like a customer support administrator, needs to view data for a specific tenant they don't belong to? Our current model breaks down. The solution is to introduce a separate, privileged pathway for this access.

-- Highly complex policy, use with caution
USING (
    (tenant_id = current_setting('app.current_tenant_id')::uuid) OR
    (EXISTS (
        SELECT 1 FROM support_sessions
        WHERE support_user_id = current_setting('app.current_user_id')::uuid
        AND target_tenant_id = invoices.tenant_id
        AND expires_at > now()
    ))
)

This couples your RLS policies to another table, which can have significant performance implications and make the system harder to reason about. Option A is almost always the safer, cleaner choice.

4. The Debugging Black Box

When a query returns an empty set, is it because no matching data exists, or because an RLS policy silently filtered it? This can be maddening to debug.

Strategy: Your application logs are your best friend. Whenever you execute a query, log the RLS context variables (app.current_user_id, app.current_tenant_id, etc.) along with the query itself. When debugging, you can then manually replicate the exact session state in a psql client to understand why rows are being filtered.

-- Manually simulating a user session for debugging in psql
BEGIN;
SET LOCAL app.current_user_id = 'user_a1b2c3d4';
SET LOCAL app.current_tenant_id = 'tenant_e5f6g7h8';
SET LOCAL app.current_user_role = 'viewer';

-- Now run the problematic query
SELECT * FROM invoices WHERE id = 'some_invoice_id';

-- Use EXPLAIN to see the RLS filter being applied
EXPLAIN SELECT * FROM invoices WHERE id = 'some_invoice_id';

ROLLBACK;

Conclusion: A Paradigm Shift in Application Security

Moving authorization logic from the application to the database via PostgreSQL RLS is more than just a technical pattern; it's a paradigm shift. It forces a clear separation of concerns, treating the database not as a dumb data store, but as an active partner in enforcing security policy. The application layer is simplified, focusing on business logic instead of the repetitive and error-prone task of data scoping.

The trade-offs are real: increased database complexity, the necessity of rigorous performance tuning, and new debugging challenges. However, for mature, multi-tenant SaaS applications where data isolation is a paramount security requirement, the benefits are overwhelming. By co-locating security rules with the data they protect, you create a system that is more secure, less complex, and easier to maintain in the long run. RLS is not a silver bullet, but it is one of the sharpest tools available for building truly robust, secure data architectures.