PostgreSQL RLS & JWTs for Zero-Trust Multi-Tenant APIs
The Fragility of Application-Layer Tenancy
In the world of multi-tenant SaaS applications, data isolation is paramount. The standard approach is deceptively simple: diligently add a WHERE tenant_id = :current_tenant_id clause to every single database query. This application-layer enforcement model works, but it's a house of cards. It relies on every developer, on every feature, for all time, to remember this crucial predicate. A single forgotten clause in a complex JOIN or a hastily written analytics query can lead to catastrophic data leaks across tenants.
ORMs can help, but they aren't a silver bullet. Default scopes can be bypassed, raw SQL queries might not use them, and complex relationship traversals can obscure the underlying filtering logic. The fundamental problem remains: the application is trusted to correctly filter the data it requests. This creates a large surface area for human error.
This article presents a more robust paradigm: a Zero-Trust Database Access Model. We will treat our own API as a potentially untrusted client and enforce data isolation at the last possible moment—inside the database itself. By leveraging PostgreSQL's powerful Row-Level Security (RLS) and stateless JSON Web Tokens (JWTs), we can build a system where it is impossible for the application to request data belonging to another tenant, regardless of developer error.
We will go beyond the basics and dive into the production-ready patterns, edge cases, and performance tuning required to make this architecture succeed at scale.
Part 1: Architectural Blueprint - Schema, Roles, and JWTs
Before we can write a single policy, we need a solid foundation. This includes a well-defined schema, a dedicated low-privilege database role for our API, and a structured JWT payload.
The Multi-Tenant Schema
Let's model a simple project management application. The key is the pervasive tenant_id column, which acts as the primary data partitioning key.
-- Create tenants and users
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE TABLE tenants (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
name TEXT NOT NULL
);
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email TEXT NOT NULL UNIQUE,
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE
);
-- The primary resource we want to protect
CREATE TABLE projects (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
owner_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
name TEXT NOT NULL
);
-- A table for many-to-many access, crucial for advanced policies later
CREATE TABLE project_members (
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role TEXT NOT NULL DEFAULT 'member', -- e.g., 'member', 'admin'
PRIMARY KEY (project_id, user_id)
);
-- Add indexes for performance, which is critical for RLS
CREATE INDEX ON users (tenant_id);
CREATE INDEX ON projects (tenant_id);
CREATE INDEX ON projects (owner_id);
CREATE INDEX ON project_members (project_id);
CREATE INDEX ON project_members (user_id);The API Database Role
We will not connect to the database as a superuser. Instead, we'll create a role with the bare minimum permissions required.
-- Create a role for our API server
CREATE ROLE api_user WITH LOGIN PASSWORD 'a_very_strong_password';
-- Grant CONNECT privilege to the database
GRANT CONNECT ON DATABASE my_app_db TO api_user;
-- Grant USAGE on the schema
GRANT USAGE ON SCHEMA public TO api_user;
-- Grant specific permissions on tables
-- Note: We grant broad permissions here because RLS will be the actual gatekeeper.
GRANT SELECT, INSERT, UPDATE, DELETE ON tenants, users, projects, project_members TO api_user;
-- The API user needs to be able to use sequences for serial primary keys if you have them
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO api_user;This api_user can technically select all rows from projects, but that's about to change.
The JWT Claim Structure
The JWT is the source of truth for the user's identity and tenancy. It must contain the necessary information for our RLS policies to make access control decisions. A well-structured payload is critical.
{
"sub": "a1b2c3d4-e5f6-4a7b-8c9d-0e1f2a3b4c5d", // User ID
"tenant_id": "z9y8x7w6-v5u4-4t3s-2r1q-p0o9n8m7l6k5",
"role": "authenticated", // PostgreSQL role name
"app_role": "admin", // Application-specific role (e.g., 'admin', 'member')
"exp": 1678886400
}* sub: The user's unique ID. Essential for ownership checks.
* tenant_id: The user's primary tenant. The core of our tenancy model.
* role: This must match the name of the PostgreSQL role the connection will assume. We will use this to set the session's role.
* app_role: An application-level role used for more granular permissions within a tenant.
Here's a minimal Node.js/jsonwebtoken example for generating such a token:
import jwt from 'jsonwebtoken';
function generateAuthToken(user) {
const payload = {
sub: user.id,
tenant_id: user.tenant_id,
role: 'api_user', // The DB role we created
app_role: user.role, // e.g., 'admin'
};
return jwt.sign(payload, process.env.JWT_SECRET, { expiresIn: '1h' });
}Part 2: The Core Mechanism: RLS Policies & The Transactional Middleware
This is where the magic happens. We'll enable RLS and create policies that dynamically read claims passed from our application.
Passing JWT Claims to PostgreSQL
How do we get the tenant_id from our JWT into an RLS policy? We can't pass it as a query parameter. The solution is to use session-level configuration variables. Our application will set these variables for the duration of a transaction, ensuring they are scoped only to the current request.
We'll create a small helper function in PostgreSQL to safely access these variables.
-- A helper function to safely access a JWT claim.
-- The `is_missing_ok` parameter prevents an error if the setting is not present.
CREATE OR REPLACE FUNCTION get_jwt_claim(claim_name TEXT) RETURNS TEXT AS $$
BEGIN
RETURN current_setting('astra.jwt_claims.' || claim_name, true);
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;We use a namespace (astra.jwt_claims.) to avoid conflicts with standard PostgreSQL settings.
Implementing RLS Policies
First, we must enable RLS on the target table. Warning: As soon as you enable RLS, the default behavior is to DENY all access. No rows will be returned until a permissive policy is created.
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_members ENABLE ROW LEVEL SECURITY;Now, let's create our first policy. An RLS policy is essentially a WHERE clause that PostgreSQL automatically appends to every query against the table.
-- Policy for SELECT operations on 'projects'
CREATE POLICY select_projects
ON projects
FOR SELECT
USING (tenant_id = get_jwt_claim('tenant_id')::uuid);
-- Policy for INSERT operations
-- 'WITH CHECK' applies to rows being inserted/updated.
-- It prevents a user from inserting a project into another tenant.
CREATE POLICY insert_projects
ON projects
FOR INSERT
WITH CHECK (tenant_id = get_jwt_claim('tenant_id')::uuid);
-- A comprehensive policy for UPDATE and DELETE
-- 'USING' determines which rows are visible for update/delete.
-- 'WITH CHECK' ensures you can't update a row to move it to another tenant.
CREATE POLICY modify_projects
ON projects
FOR ALL -- Covers UPDATE, DELETE
USING (tenant_id = get_jwt_claim('tenant_id')::uuid)
WITH CHECK (tenant_id = get_jwt_claim('tenant_id')::uuid);The Critical API Middleware
Our application logic now needs to perform a crucial sequence of operations for every authenticated API request:
- Verify the JWT.
- Get a client from the connection pool.
- Start a transaction.
- Set the role for the transaction.
- Execute the business logic query.
- Commit the transaction (which also clears the local settings).
Using SET LOCAL is non-negotiable in a connection-pooled environment. A plain SET would permanently alter the session's state, causing the next request that receives that same connection from the pool to inherit the previous user's identity—a massive security hole.
Here is a Node.js/Express middleware example using the pg library:
import { pool } from './db'; // Your configured pg.Pool instance
async function withRls(req, res, next) {
const user = req.user; // Assumes JWT is already verified and decoded onto req.user
if (!user || !user.tenant_id || !user.sub) {
// This should not happen for authenticated routes
return res.status(500).send('User identity not found for RLS.');
}
const client = await pool.connect();
req.dbClient = client; // Attach client to request for use in route handlers
try {
await client.query('BEGIN');
// 1. Set the role for the transaction. This is a security best practice.
// The role in the JWT must match a valid, non-superuser role in the DB.
await client.query(`SET LOCAL ROLE ${client.escapeIdentifier(user.role)}`);
// 2. Set the JWT claims as session variables.
// We are using `set_config` which is the underlying function for SET.
const claims = {
'astra.jwt_claims.tenant_id': user.tenant_id,
'astra.jwt_claims.user_id': user.sub,
'astra.jwt_claims.app_role': user.app_role,
};
for (const [key, value] of Object.entries(claims)) {
await client.query(`SELECT set_config('${key}', '${value}', true);`);
}
// The transaction is now configured with the user's identity.
// Any query run by the route handler using req.dbClient will be subject to RLS.
// We need a way to commit/rollback when the response finishes.
res.on('finish', async () => {
try {
// Only commit if the request was successful
if (res.statusCode >= 200 && res.statusCode < 400) {
await client.query('COMMIT');
} else {
await client.query('ROLLBACK');
}
} finally {
client.release();
}
});
next();
} catch (err) {
console.error('RLS Middleware Error:', err);
await client.query('ROLLBACK');
client.release();
res.status(500).send('Error setting up database session.');
}
}
// Example usage in an Express route
app.get('/api/projects', authenticateJwt, withRls, async (req, res) => {
// Notice: NO `WHERE tenant_id = ?` clause!
const { rows } = await req.dbClient.query('SELECT id, name FROM projects;');
res.json(rows);
});With this middleware in place, our route handler becomes beautifully simple and secure. It's impossible for it to fetch projects from another tenant.
Part 3: Advanced Scenarios & Handling Complexity
Simple tenancy is one thing, but real-world applications have complex access rules.
Edge Case 1: The Super Admin / Support User
How do you grant a support user access to all tenants for debugging? There are two primary patterns.
Pattern A: The Privileged Role
Create a separate, more powerful role that explicitly bypasses RLS.
-- A role for support staff that is exempt from RLS
CREATE ROLE support_user WITH LOGIN PASSWORD 'another_strong_password';
ALTER ROLE support_user BYPASSRLS;
-- Grant necessary permissions
GRANT CONNECT ON DATABASE my_app_db TO support_user;
GRANT USAGE ON SCHEMA public TO support_user;
GRANT SELECT ON projects TO support_user;Your application's authentication logic would issue a JWT with the role claim set to support_user for these individuals. The withRls middleware would then execute SET LOCAL ROLE support_user, and all subsequent queries in that transaction would bypass RLS entirely.
Pros: Clean separation of concerns. Very explicit.
Cons: All-or-nothing access. The user can see everything. Less granular.
Pattern B: The Policy-Based Bypass
Modify the RLS policy itself to check for an administrative app_role.
-- Drop the old policy first
DROP POLICY select_projects ON projects;
-- Create a new, more flexible policy
CREATE POLICY select_projects_with_admin_bypass
ON projects
FOR SELECT
USING (
(tenant_id = get_jwt_claim('tenant_id')::uuid) OR
(get_jwt_claim('app_role') = 'support_admin')
);Pros: More granular. You can still apply other filters. No need for a separate DB role.
Cons: Policy logic becomes more complex. You must be careful with your OR conditions.
For most use cases, Pattern B is preferable as it keeps all access logic defined within the policies themselves.
Edge Case 2: The Invitation System (Many-to-Many Access)
What if a user from Tenant A needs to be granted access to a specific project in Tenant B? Our simple tenant_id check is no longer sufficient. This is where our project_members junction table becomes vital.
We need a more sophisticated policy that checks for either tenancy OR explicit membership.
-- Let's update the project selection policy again
DROP POLICY select_projects_with_admin_bypass ON projects;
CREATE POLICY select_projects_by_membership
ON projects
FOR SELECT
USING (
-- Condition 1: User belongs to the project's tenant (owner-like access)
(tenant_id = get_jwt_claim('tenant_id')::uuid)
OR
-- Condition 2: User is an explicit member of the project
EXISTS (
SELECT 1
FROM project_members pm
WHERE pm.project_id = projects.id
AND pm.user_id = get_jwt_claim('user_id')::uuid
)
);This policy is incredibly powerful. It states that a user can see a project if:
- The project belongs to their primary tenant.
project_members table for that specific project, regardless of tenant.This single, declarative policy handles complex cross-tenant collaboration scenarios without a single line of application code change in the query logic.
Part 4: Performance, Debugging, and Production Readiness
Adopting RLS is not without its operational considerations.
Performance Analysis: Is RLS Slow?
No, RLS is not inherently slow. The PostgreSQL query planner is smart. It effectively rewrites your query by appending the USING clause from the policy as a WHERE condition.
SELECT FROM projects; becomes SELECT FROM projects WHERE tenant_id = 'z9y8x7w6-v5u4-4t3s-2r1q-p0o9n8m7l6k5'; behind the scenes.
The performance of RLS is therefore equivalent to the performance of the WHERE clauses it generates. This means indexing is absolutely critical. The columns used in your RLS policies (tenant_id, user_id, etc.) must have indexes, just as if you were writing the WHERE clauses manually.
Let's analyze a query plan:
-- In psql, first set the claims to simulate an API request
SET astra.jwt_claims.tenant_id = '...';
SET astra.jwt_claims.user_id = '...';
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM projects WHERE name LIKE 'A%';Query Plan without an index on tenant_id:
Seq Scan on projects (cost=0.00..654.50 rows=10 width=84) (actual time=0.021..5.123 rows=100 loops=1)
Filter: ((name ~~ 'A%') AND (tenant_id = '...'::uuid))
Rows Removed by Filter: 99900
Buffers: shared hit=422Here, PostgreSQL scans the entire table and then filters. This is slow.
Query Plan with a B-tree index on tenant_id:
Bitmap Heap Scan on projects (cost=12.55..245.91 rows=10 width=84) (actual time=0.051..0.150 rows=100 loops=1)
Recheck Cond: (tenant_id = '...'::uuid)
Filter: (name ~~ 'A%')
Heap Blocks: exact=95
-> Bitmap Index Scan on projects_tenant_id_idx (cost=0.00..12.53 rows=500 width=0) (actual time=0.032..0.032 rows=500 loops=1)
Index Cond: (tenant_id = '...'::uuid)
Buffers: shared hit=98This is much better. The planner uses the index on tenant_id to quickly find the relevant blocks and then applies the secondary filter. For policies with subqueries (EXISTS), ensure the columns in the subquery's WHERE clause are also indexed.
Debugging RLS Policies
Debugging RLS can be tricky because the filtering is implicit. The best technique is to use psql to impersonate your API user and set the session variables manually.
-- Connect to psql
psql -U your_superuser -d my_app_db
-- Start a transaction and impersonate the user
BEGIN;
SET ROLE api_user;
-- Set the claims for a specific user from Tenant 1
SELECT set_config('astra.jwt_claims.tenant_id', 'tenant-A-uuid', true);
SELECT set_config('astra.jwt_claims.user_id', 'user-1-uuid', true);
-- Now run your queries. You will only see data for Tenant A.
SELECT * FROM projects;
-- Expected: Only projects from Tenant A are returned.
-- Try to insert into another tenant. This should fail due to the WITH CHECK option.
INSERT INTO projects (name, tenant_id, owner_id) VALUES ('Malicious Project', 'tenant-B-uuid', 'user-1-uuid');
-- Expected: ERROR: new row violates row-level security policy for table "projects"
ROLLBACK;This interactive session is invaluable for validating that your policies behave exactly as you expect under different conditions.
Managing Policies in Migrations
RLS policies are DDL and should be managed with your database migration tool (e.g., Flyway, Alembic, node-pg-migrate). Always write policies idempotently using DROP POLICY IF EXISTS ... and CREATE POLICY ....
-- migration-005-add-project-rls.sql
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS select_projects_by_membership ON projects;
CREATE POLICY select_projects_by_membership
ON projects
FOR SELECT
USING (
(tenant_id = get_jwt_claim('tenant_id')::uuid)
OR
EXISTS (
SELECT 1
FROM project_members pm
WHERE pm.project_id = projects.id
AND pm.user_id = get_jwt_claim('user_id')::uuid
)
);
-- ... other policies for INSERT, UPDATE, DELETEConclusion: A Paradigm Shift in Data Security
By moving tenancy enforcement from the application to the database, we achieve a profound shift in our security posture. The application is no longer the trusted guardian of data isolation; the database is. This Zero-Trust model dramatically reduces the risk of cross-tenant data leaks caused by developer oversight.
This pattern offers:
WHERE tenant_id = ? clauses. Query logic becomes cleaner and more focused on business value.WHERE clauses.While it requires a disciplined approach to session management, JWT structure, and database migrations, the PostgreSQL RLS and JWT pattern provides a production-hardened foundation for building the next generation of secure, scalable, and maintainable multi-tenant applications.