PostgreSQL RLS with JWTs for Granular, Database-Enforced API Auth
The Fragility of Application-Layer Authorization
In any multi-tenant or user-scoped application, the most critical security requirement is data isolation. A single bug that omits a WHERE user_id = :current_user_id or WHERE tenant_id = :current_tenant_id clause can lead to a catastrophic data leak, exposing one customer's data to another. For years, the standard approach has been to meticulously enforce this isolation at the application layer.
Every repository method, every service function, every query builder instance must be consciously written to include the necessary filtering. This approach is fundamentally brittle. It relies on every developer, on every commit, for the entire lifetime of the project, to never make a mistake. A new endpoint, a refactored query, a rushed bugfix—any of these can introduce a vulnerability. Code reviews help, but they are also fallible.
This is not a scalable or robust security posture. The principle of defense-in-depth dictates that we should have multiple layers of security. If the application layer fails, another layer should prevent the disaster. This is where we can leverage the power of the database itself.
This article details a production-proven pattern for enforcing data isolation at the database layer using PostgreSQL's Row-Level Security (RLS), driven by claims from a JSON Web Token (JWT). We will bypass high-level theory and dive straight into the implementation details, performance tuning, and edge cases you will encounter in a real-world system.
The Core Pattern: JWT Claims to RLS Policies via Session Variables
The fundamental idea is to make the database aware of the current user's identity and permissions for the duration of a single request. We can achieve this by passing information from the JWT—validated by our API—into the database session. RLS policies within the database then use this session-local information to transparently filter every single SELECT, INSERT, UPDATE, and DELETE operation.
Here is the request lifecycle:
Authorization header.* It validates the JWT's signature and expiration.
* It extracts the claims (e.g., sub for user ID, tenant_id, role).
SET LOCAL command to store the JWT claims as a session-local variable. Using SET LOCAL is critical as it ensures the variable's scope is confined to the current transaction, preventing state leakage between concurrent requests using the same pooled connection. SET LOCAL app.jwt.claims = '{"sub":"user-123","tenant_id":"tenant-abc","role":"admin"}';WHERE tenant_id = ... clauses. For example, a simple SELECT * FROM invoices;.invoices table. These policies read the app.jwt.claims variable and append the necessary filtering conditions to the query plan before execution.app.jwt.claims is automatically discarded, leaving the connection clean for the next request in the pool.This pattern moves the responsibility for data isolation from the developer's active memory to the database's guaranteed execution, creating a powerful security backstop.
Deep Dive: Schema and Policy Implementation
Let's build a practical example. Consider a simple SaaS schema with tenants, users, and invoices.
1. Schema Definition
First, we'll define our tables. Note the consistent use of tenant_id and user_id which will be used by our policies.
-- Create a custom namespace for our auth functions to avoid cluttering the public schema.
CREATE SCHEMA IF NOT EXISTS app_auth;
-- Tenants table
CREATE TABLE tenants (
id TEXT PRIMARY KEY,
name TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- Users table
CREATE TABLE users (
id TEXT PRIMARY KEY,
tenant_id TEXT 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()
);
-- Invoices table
CREATE TABLE invoices (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
tenant_id TEXT NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
user_id TEXT NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- The user who created the invoice
amount_cents INTEGER NOT NULL,
status TEXT NOT NULL DEFAULT 'draft', -- e.g., 'draft', 'paid', 'void'
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
-- CRITICAL: Add indexes for columns used in RLS policies.
CREATE INDEX ON users (tenant_id);
CREATE INDEX ON invoices (tenant_id, user_id);2. Helper Functions for Claim Extraction
Writing policies that manually parse JSON in every invocation is inefficient and verbose. Instead, we create a set of helper functions in our app_auth schema to extract specific claims from the app.jwt.claims session variable. Using current_setting('app.jwt.claims', true) is key; the true argument makes it return NULL if the setting is not found, preventing errors.
-- Helper to get the full claims JSONB object.
-- The function is marked as STABLE, which tells the planner that it will return the
-- same result for all rows within a single statement. This is crucial for performance.
CREATE OR REPLACE FUNCTION app_auth.current_claims() RETURNS JSONB AS $$
SELECT coalesce(current_setting('app.jwt.claims', true), '{}')::jsonb;
$$ LANGUAGE SQL STABLE;
-- Helper to get a specific claim as text.
CREATE OR REPLACE FUNCTION app_auth.current_claim(claim_name TEXT) RETURNS TEXT AS $$
SELECT app_auth.current_claims()->>claim_name;
$$ LANGUAGE SQL STABLE;
-- Specific helpers for commonly used claims.
CREATE OR REPLACE FUNCTION app_auth.current_tenant_id() RETURNS TEXT AS $$
SELECT app_auth.current_claim('tenant_id');
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION app_auth.current_user_id() RETURNS TEXT AS $$
SELECT app_auth.current_claim('sub');
$$ LANGUAGE SQL STABLE;
CREATE OR REPLACE FUNCTION app_auth.current_user_role() RETURNS TEXT AS $$
SELECT app_auth.current_claim('role');
$$ LANGUAGE SQL STABLE;3. Crafting the RLS Policies
With our helper functions in place, we can now create concise and readable policies. First, we must enable RLS on each table.
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE users ENABLE ROW LEVEL SECURITY;Now, let's define the access rules:
member role can only see their own invoices. A user with the admin role can see all invoices within their tenant.Here are the policies for the invoices table:
-- Policy for SELECT operations
CREATE POLICY select_invoices ON invoices
FOR SELECT
USING (
-- The user must be in the correct tenant.
tenant_id = app_auth.current_tenant_id() AND
-- EITHER the user created the invoice OR the user is an admin.
(
user_id = app_auth.current_user_id() OR
app_auth.current_user_role() = 'admin'
)
);
-- Policy for INSERT operations
CREATE POLICY insert_invoices ON invoices
FOR INSERT
WITH CHECK (
-- The new row must belong to the user's current tenant and be created by them.
tenant_id = app_auth.current_tenant_id() AND
user_id = app_auth.current_user_id()
);
-- Policy for UPDATE operations
CREATE POLICY update_invoices ON invoices
FOR UPDATE
USING (
-- This part defines which rows are *targetable* for an update.
-- It's identical to our SELECT policy.
tenant_id = app_auth.current_tenant_id() AND
(
user_id = app_auth.current_user_id() OR
app_auth.current_user_role() = 'admin'
)
)
WITH CHECK (
-- This part defines what the *updated* row must look like.
-- We prevent users from changing the tenant_id of an existing invoice.
tenant_id = app_auth.current_tenant_id()
);
-- Policy for DELETE operations
CREATE POLICY delete_invoices ON invoices
FOR DELETE
USING (
-- Same logic as SELECT/UPDATE for which rows can be targeted.
tenant_id = app_auth.current_tenant_id() AND
(
user_id = app_auth.current_user_id() OR
app_auth.current_user_role() = 'admin'
)
);USING vs. WITH CHECK: This is a critical distinction senior engineers must understand.
* USING (expression): This clause applies to existing rows. For SELECT, it determines which rows are visible. For UPDATE and DELETE, it determines which rows can be targeted by the command.
* WITH CHECK (expression): This clause applies to new or updated rows. For INSERT and UPDATE, it validates the data of the row being written. If the expression evaluates to false, the command will fail with a check_violation error. It prevents users from writing data that they themselves would not be able to see.
Production-Grade API Implementation (Node.js & `node-postgres`)
Now, let's see how to integrate this on the server side. The key challenge is managing the database connection and session state correctly.
The Middleware
This Express.js middleware handles JWT validation and sets the session context for all subsequent database queries in the request.
// middleware/auth.js
const jwt = require('jsonwebtoken');
const { pool } = require('../db'); // Your configured node-postgres pool
const JWT_SECRET = process.env.JWT_SECRET;
async function authAndSetClaims(req, res, next) {
const authHeader = req.headers.authorization;
if (!authHeader || !authHeader.startsWith('Bearer ')) {
return res.status(401).json({ error: 'Unauthorized: No token provided' });
}
const token = authHeader.split(' ')[1];
let claims;
try {
claims = jwt.verify(token, JWT_SECRET);
} catch (err) {
return res.status(401).json({ error: 'Unauthorized: Invalid token' });
}
// CRITICAL: Acquire a client from the pool for the entire request lifecycle.
// We attach it to the request object to be used by route handlers.
const client = await pool.connect();
req.dbClient = client;
try {
// The claims object is stringified to be passed into PostgreSQL.
const claimsJson = JSON.stringify(claims);
// Use SET LOCAL to scope the setting to the current transaction.
// This is safe for concurrent requests using the same connection from the pool.
await client.query(`SET LOCAL app.jwt.claims = '${claimsJson}'`);
// Set a transaction isolation level if needed.
// await client.query('BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED');
// Note: node-postgres runs each query in its own transaction by default unless
// you explicitly use BEGIN/COMMIT. The `SET LOCAL` lasts for the duration of the
// session until the client is released back to the pool.
next();
} catch (err) {
// Ensure the client is released on error.
client.release();
console.error('Failed to set session claims:', err);
res.status(500).json({ error: 'Internal Server Error' });
}
}
// A second middleware to release the client after the request is done.
function releaseClient(req, res, next) {
if (req.dbClient) {
req.dbClient.release();
req.dbClient = null;
}
next();
}
module.exports = { authAndSetClaims, releaseClient };The API Route
Notice how clean the route handler is. There are no WHERE clauses for security. The logic is purely business-focused. The database handles the authorization.
// routes/invoices.js
const express = require('express');
const router = express.Router();
// This route gets all invoices visible to the current user.
// If the user is a 'member', they see only their own invoices.
// If the user is an 'admin', they see all invoices in the tenant.
router.get('/', async (req, res) => {
try {
// We use the client attached to the request by our middleware.
const { rows } = await req.dbClient.query('SELECT id, amount_cents, status FROM invoices');
res.json(rows);
} catch (error) {
console.error('Error fetching invoices:', error);
res.status(500).json({ error: 'Failed to fetch invoices' });
}
});
// This route creates a new invoice.
// The RLS policy's `WITH CHECK` clause ensures the new invoice's tenant_id and user_id are correct.
// An attempt to insert for another user would fail at the DB level.
router.post('/', async (req, res) => {
const { amount_cents, status } = req.body;
if (!amount_cents) {
return res.status(400).json({ error: 'amount_cents is required' });
}
try {
const query = {
text: 'INSERT INTO invoices (amount_cents, status) VALUES ($1, $2) RETURNING *',
values: [amount_cents, status || 'draft'],
};
// The RLS policy will automatically fill in tenant_id and user_id from our helpers
// if we set defaults on the table, or fail if we don't and the WITH CHECK fails.
// A better approach is to have the policy fill it:
// We need to adjust the insert and policy for this.
// Let's assume the policy is as written, requiring the application to provide user_id and tenant_id,
// which the policy then CHECKS.
// A better INSERT query that respects the policy:
const insertQuery = {
text: `INSERT INTO invoices (tenant_id, user_id, amount_cents, status)
VALUES (app_auth.current_tenant_id(), app_auth.current_user_id(), $1, $2)
RETURNING *`,
values: [amount_cents, status || 'draft'],
};
const { rows } = await req.dbClient.query(insertQuery);
res.status(201).json(rows[0]);
} catch (error) {
console.error('Error creating invoice:', error);
// Catching a specific error for RLS violations.
if (error.code === '44000' || error.code === '23514') { // check_violation in older/newer pg versions
return res.status(403).json({ error: 'Forbidden: You do not have permission to create this resource.' });
}
res.status(500).json({ error: 'Failed to create invoice' });
}
});
module.exports = router;Main App File
Finally, tie it all together in your main Express app file.
// app.js
const express = require('express');
const { authAndSetClaims, releaseClient } = require('./middleware/auth');
const invoicesRouter = require('./routes/invoices');
const app = express();
app.use(express.json());
// All routes under /api are protected
app.use('/api', authAndSetClaims);
app.use('/api/invoices', invoicesRouter);
// After all API routes, use the middleware to release the client.
app.use('/api', releaseClient);
const PORT = process.env.PORT || 3000;
app.listen(PORT, () => console.log(`Server running on port ${PORT}`));Performance Considerations and Benchmarking
RLS is not free. Every query against a protected table incurs the overhead of executing the policy functions and appending the policy predicates. However, with proper optimization, this overhead can be minimized.
1. The Cost of RLS
Let's analyze a query plan. Assume we run EXPLAIN ANALYZE SELECT * FROM invoices; as a user.
Without RLS:
Seq Scan on invoices (cost=0.00..25.88 rows=1588 width=68) (actual time=0.006..0.024 rows=1588 loops=1)
Planning Time: 0.057 ms
Execution Time: 0.041 msWith RLS:
Seq Scan on invoices (cost=0.00..33.85 rows=1 width=68) (actual time=0.024..0.054 rows=10 loops=1)
Filter: ((tenant_id = app_auth.current_tenant_id()) AND ((user_id = app_auth.current_user_id()) OR (app_auth.current_user_role() = 'admin'::text)))
Planning Time: 0.150 ms
Execution Time: 0.075 msNotice the Filter condition added by the planner. The cost and execution time have increased. The planner's row estimate is also less accurate because it doesn't know the return values of the stable functions at planning time.
2. Indexing is Non-Negotiable
The single most important optimization is to aggressively index the columns used in your RLS policies. In our example, tenant_id and user_id are in every policy. Our composite index (tenant_id, user_id) is crucial.
With the index in place, the plan for a member role user looks much better:
Index Scan using invoices_tenant_id_user_id_idx on invoices (cost=0.29..8.31 rows=1 width=68) (actual time=0.022..0.023 rows=10 loops=1)
Index Cond: ((tenant_id = app_auth.current_tenant_id()) AND (user_id = app_auth.current_user_id()))
Filter: (app_auth.current_user_role() <> 'admin'::text)
Planning Time: 0.188 ms
Execution Time: 0.045 msThe planner is now smart enough to use an Index Scan based on the parts of the policy it can reason about (tenant_id and user_id), significantly reducing the number of rows it needs to check. The performance is now nearly identical to a manually-written query with a WHERE clause.
3. Function Volatility (`STABLE`)
We marked our helper functions as STABLE. This is a promise to the query planner that, within a single query scan, the function will consistently return the same result. This allows the planner to evaluate the function once and cache its result, rather than re-evaluating it for every single row. If we had omitted STABLE, the performance penalty would be substantial on large tables.
* VOLATILE (default): The function's value can change at any time. It will be re-evaluated for every row. Use for functions with side effects like random() or NOW().
* STABLE: The function's result is constant within a single table scan.
IMMUTABLE: The function's result depends only on its arguments. Given the same inputs, it will always* return the same result. This is the most optimizable.
Our current_claim() functions are correctly marked STABLE because they depend on a session variable that is constant for the duration of a query.
Advanced Scenarios and Edge Cases
Real-world systems present complexities that the basic pattern doesn't cover.
1. The Superuser and Table Owner Problem
By default, RLS policies do not apply to table owners or superusers. This is a potential security hole. If your API connects to the database with a user that owns the tables, your RLS policies will be silently bypassed.
Solution:
api_user) that is granted only SELECT, INSERT, UPDATE, DELETE on the necessary tables. This role should not own any tables. ALTER TABLE invoices FORCE ROW LEVEL SECURITY;This is a powerful safeguard, but be aware that it can make administrative tasks and migrations more complex, as even the owner will be subject to policies.
2. Access for Background Workers and Cron Jobs
A background worker processing a queue doesn't have a user's JWT. How can it operate on tenant data? There are several patterns:
* Privileged Role: The worker connects as a privileged role (e.g., worker_user) for which you define no RLS policies, or policies that are USING (true). This is simple but requires careful management of the role's permissions.
* Explicitly Pass Context: The job payload can include the tenant_id or user_id. The worker then sets the app.jwt.claims session variable just like the API would, effectively impersonating the user for the scope of that job. This is often the cleanest and most secure approach.
* Temporarily Disable RLS: For trusted, internal processes, you can temporarily disable RLS for a session by setting session_replication_role to replica. This is a big hammer and should be used with extreme caution.
SET session_replication_role = 'replica';
-- Perform operations that need to bypass RLS
UPDATE invoices SET status = 'overdue' WHERE ...;
-- Re-enable RLS
SET session_replication_role = 'origin';3. Debugging RLS Policies
RLS can be difficult to debug because it fails silently. A query that returns no rows could be correct, or it could be due to a faulty policy. Here are some debugging strategies:
* RAISE NOTICE: Add RAISE NOTICE statements to your PL/pgSQL helper functions to print the values of the claims being read. RAISE NOTICE 'current_tenant_id: %', app_auth.current_tenant_id();
* Direct Function Testing: In a psql session, you can manually set the claims and test your functions directly:
BEGIN;
SET LOCAL app.jwt.claims = '{"sub":"user-456","tenant_id":"tenant-xyz","role":"member"}';
SELECT app_auth.current_tenant_id(); -- Should return 'tenant-xyz'
EXPLAIN ANALYZE SELECT * FROM invoices;
ROLLBACK;* auto_explain Module: Configure PostgreSQL's auto_explain module to log the execution plans of slow queries. The logged plan will include the RLS filter being applied, which can help you spot issues.
4. Cross-Tenant Admin Access
How do you build a super-admin panel for your support team that needs to see data across all tenants? The RLS model seems to prevent this.
Solution: Do not try to subvert the RLS model for this use case. Instead, the super-admin application should connect to the database as a different database user (e.g., super_admin_user) that does not have RLS policies applied to it, or has policies that explicitly allow cross-tenant access (e.g., USING (true) for a specific role claim in the JWT).
Conclusion: A Robust Defense-in-Depth Strategy
Implementing authorization with PostgreSQL RLS and JWTs is a significant architectural decision. It moves a critical security function from the fallible application layer to the robust, mandatory enforcement of the database. While it introduces new complexities in debugging and managing privileged access, the security benefits are immense.
By centralizing your data access rules directly alongside the data itself, you create a powerful, unified security model that is difficult to bypass. You eliminate entire classes of vulnerabilities caused by developer oversight in application code. For any senior engineer building a multi-tenant system, this pattern should be a primary consideration for a modern, defense-in-depth security architecture.