PostgreSQL RLS with JWTs for API Authorization in Multi-Tenant SaaS

October 10, 2025

14 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 Authorization in Multi-Tenant Systems

In most multi-tenant SaaS architectures, the default pattern for authorization is deceptively simple: authenticate a user, fetch a superset of data from the database based on a primary key, and then filter or authorize access within the application layer. A typical service method might look like this:

javascript

// The common, but flawed, application-layer pattern
async function getProject(user, projectId) {
  // 1. Fetch data broadly
  const project = await db.projects.findById(projectId);

  if (!project) {
    throw new NotFoundError('Project not found');
  }

  // 2. Authorize in the application layer
  if (project.organizationId !== user.organizationId) {
    throw new ForbiddenError('Access denied');
  }

  return project;
}

This pattern, while functional for simple cases, introduces significant and often subtle failure modes in complex systems:

Security Vulnerabilities: The most critical flaw is the potential for data leakage. A developer might forget to add the authorization check in a new API endpoint, or a complex query might inadvertently join and expose data from another tenant before the filter is applied. The attack surface is the entire application codebase.

Performance Bottlenecks: The database may perform significant work fetching and joining data that is ultimately discarded by the application layer. The query planner cannot optimize for authorization rules it is unaware of.

Code Duplication and Brittleness: The same authorization logic (project.organizationId === user.organizationId) is repeated across services, controllers, and functions. A change in the authorization model requires a cross-cutting change throughout the codebase, which is error-prone.

Microservices Complexity: In a distributed system, this problem is magnified. Every microservice that interacts with tenant-siloed data must independently and correctly implement the same authorization logic, leading to drift and inconsistency.

This article presents a superior architectural pattern: shifting authorization enforcement directly into the database using PostgreSQL's Row-Level Security (RLS). By making the database itself tenant-aware, we establish a single, robust, and performant source of truth for data access policies.

Core Pattern: JWT Claims as a Source of Truth for Database Sessions

The linchpin of this architecture is the secure propagation of user context from the authentication layer to the database session. We leverage JSON Web Tokens (JWTs) for this purpose. After a user authenticates, the API backend receives a JWT containing claims that define the user's identity and permissions.

json

{
  "sub": "user-uuid-1234",
  "org_id": "org-uuid-abcd",
  "role": "admin",
  "exp": 1678886400
}

The pattern is as follows:

The API backend receives a request with a JWT in the Authorization header.

A middleware verifies the JWT's signature and expiration. This is the authentication step. The application layer's primary security responsibility is to ensure the integrity of this token.

For every database transaction initiated by this request, the application passes the verified JWT claims to the PostgreSQL session using session-level configuration parameters.

PostgreSQL's RLS policies then use these session parameters to filter every single query automatically. This is the authorization step, now owned by the database.

Crucially, we use SET LOCAL within a transaction. This scopes the settings to that specific transaction, preventing context from leaking across requests in a connection pool—a common and dangerous mistake when implementing this pattern.

javascript

// Securely setting transaction-local context in a pooled environment
const client = await pool.connect();
try {
  await client.query('BEGIN');
  
  // Using SET LOCAL ensures these settings only last for the current transaction
  await client.query(`SET LOCAL app.current_user_id = '${req.user.sub}';`);
  await client.query(`SET LOCAL app.current_organization_id = '${req.user.org_id}';`);
  await client.query(`SET LOCAL app.current_user_role = '${req.user.role}';`);

  // Any subsequent query in this transaction is now subject to RLS policies
  const { rows } = await client.query('SELECT * FROM projects;');
  
  await client.query('COMMIT');
  res.json(rows);
} catch (e) {
  await client.query('ROLLBACK');
  throw e;
} finally {
  client.release(); // Returns the connection to the pool
}

The database implicitly trusts the application layer to set these parameters correctly after verifying the JWT. The security boundary is clear: the application authenticates, the database authorizes.

Implementation Deep Dive: Schema and RLS Policies

Let's build a practical implementation based on a common SaaS schema.

1. Schema Definition

Assume the following schema. Notice how organization_id is a foreign key on most tables, representing our tenancy column.

sql

-- organizations table
CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid()
);

-- users table
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id)
);

-- projects table
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  organization_id UUID NOT NULL REFERENCES organizations(id),
  name TEXT NOT NULL
);

-- project_members table for role-based access
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,
  PRIMARY KEY (project_id, user_id)
);

-- Create indexes on tenancy and foreign key columns for performance
CREATE INDEX ON users (organization_id);
CREATE INDEX ON projects (organization_id);
CREATE INDEX ON project_members (user_id);

2. Enabling and Forcing RLS

First, we must enable RLS on each table we want to protect. It's also critical to FORCE it, which applies the policies even to the table owner. This prevents a user with table ownership privileges from bypassing the security policies.

sql

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

ALTER TABLE project_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_members FORCE ROW LEVEL SECURITY;

3. Crafting the RLS Policies

Policies are the core of RLS. They are expressions that return a boolean. If the expression returns true for a given row, that row is visible and accessible.

Policy 1: Basic Tenancy Isolation for SELECT

This is the simplest and most fundamental policy. It ensures a user can only see projects belonging to their organization.

sql

-- A helper function to safely get the current organization_id
-- This avoids SQL injection and handles cases where the setting is not present
CREATE OR REPLACE FUNCTION app.current_organization_id() RETURNS UUID AS $$
  SELECT nullif(current_setting('app.current_organization_id', true), '')::uuid;
$$ LANGUAGE sql STABLE;

CREATE POLICY select_projects_for_organization
ON projects
FOR SELECT
USING (organization_id = app.current_organization_id());

Now, if a user with org_id = 'org-uuid-abcd' runs SELECT FROM projects;, PostgreSQL effectively rewrites the query to SELECT FROM projects WHERE organization_id = 'org-uuid-abcd';.

Policy 2: Role-Based Access for Granular Control

Let's introduce more complexity. An admin can see all projects in their organization, but a member can only see projects they are explicitly assigned to via the project_members table.

sql

-- Helper functions for current user and role
CREATE OR REPLACE FUNCTION app.current_user_id() RETURNS UUID AS $$
  SELECT nullif(current_setting('app.current_user_id', true), '')::uuid;
$$ LANGUAGE sql STABLE;

CREATE OR REPLACE FUNCTION app.current_user_role() RETURNS TEXT AS $$
  SELECT nullif(current_setting('app.current_user_role', true), '');
$$ LANGUAGE sql STABLE;

-- First, drop the simpler policy
DROP POLICY IF EXISTS select_projects_for_organization ON projects;

-- Create a more sophisticated, role-based policy
CREATE POLICY select_projects_based_on_role
ON projects
FOR SELECT
USING (
  -- All queries are implicitly scoped to the organization first
  organization_id = app.current_organization_id() AND (
    -- Admins can see all projects in the organization
    app.current_user_role() = 'admin'
    OR
    -- Members can see projects they are a member of
    (app.current_user_role() = 'member' AND EXISTS (
      SELECT 1 FROM project_members pm
      WHERE pm.project_id = projects.id
      AND pm.user_id = app.current_user_id()
    ))
  )
);

This single policy elegantly handles multiple access levels. The query planner is smart enough to optimize the EXISTS subquery, especially with the correct indexes in place.

Policy 3: Policies for INSERT, UPDATE, DELETE with WITH CHECK

Policies aren't just for reading data. The WITH CHECK option is critical for mutations, as it ensures that any new or modified row also satisfies the policy conditions. This prevents a user from one tenant from creating data in another tenant's name or moving data between tenants.

sql

CREATE POLICY insert_projects_for_organization
ON projects
FOR INSERT
WITH CHECK (organization_id = app.current_organization_id());

CREATE POLICY update_projects_for_organization
ON projects
FOR UPDATE
USING (organization_id = app.current_organization_id()) -- Which rows can be updated
WITH CHECK (organization_id = app.current_organization_id()); -- Ensure it stays in the same org

CREATE POLICY delete_projects_for_organization
ON projects
FOR DELETE
USING (organization_id = app.current_organization_id());

Without WITH CHECK on the INSERT policy, a user from org-A could execute INSERT INTO projects (name, organization_id) VALUES ('Leaked Project', 'org-B-uuid'); and successfully create data in another tenant's account.

Production Application Integration: A Complete Node.js/Express Example

Let's tie this together with a production-ready Node.js API endpoint.

javascript

// File: db.js
const { Pool } = require('pg');

const pool = new Pool({
  // Your connection details
});

// A higher-order function to wrap routes in a transaction with RLS context
function withRls(handler) {
  return async (req, res, next) => {
    const client = await pool.connect();
    try {
      await client.query('BEGIN');
      
      // Ensure user object from JWT middleware exists
      if (!req.user || !req.user.sub || !req.user.org_id || !req.user.role) {
        throw new Error('User context for RLS is missing.');
      }

      // Use parameterized queries to prevent SQL injection when setting context
      await client.query(`SET LOCAL app.current_user_id = $1`, [req.user.sub]);
      await client.query(`SET LOCAL app.current_organization_id = $1`, [req.user.org_id]);
      await client.query(`SET LOCAL app.current_user_role = $1`, [req.user.role]);

      // Pass the transactional client to the handler
      await handler(req, res, next, client);

      await client.query('COMMIT');
    } catch (err) {
      await client.query('ROLLBACK');
      next(err); // Pass error to Express error handler
    } finally {
      client.release();
    }
  };
}

module.exports = { pool, withRls };

// File: projects.js
const express = require('express');
const { withRls } = require('./db');
const jwtCheck = require('./auth'); // Your JWT verification middleware

const router = express.Router();

router.get('/', jwtCheck, withRls(async (req, res, next, dbClient) => {
  // The application code is now beautifully simple.
  // No `WHERE organization_id = ...` clauses needed.
  // RLS handles it all transparently.
  const { rows } = await dbClient.query('SELECT id, name FROM projects');
  res.json(rows);
}));

router.post('/', jwtCheck, withRls(async (req, res, next, dbClient) => {
  const { name } = req.body;
  // The INSERT policy's WITH CHECK will automatically enforce the correct
  // organization_id, but it's good practice to set it explicitly.
  // If we tried to set a different org_id, the query would fail.
  const { rows } = await dbClient.query(
    'INSERT INTO projects (name, organization_id) VALUES ($1, $2) RETURNING *',
    [name, req.user.org_id]
  );
  res.status(201).json(rows[0]);
}));

module.exports = router;

This withRls wrapper is a powerful pattern. It encapsulates the transactional and context-setting logic, keeping the route handlers clean and focused on business logic. The application code no longer needs to be aware of tenancy, dramatically simplifying development and reducing the risk of security flaws.

Performance Considerations and Query Analysis

RLS is not magic; it modifies your queries. Understanding its performance impact is crucial.

1. Analyze Your Plans with EXPLAIN ANALYZE

Always inspect the query plan. When RLS is active, you will see the policy expression integrated directly into the plan.

sql

-- As a user from org-uuid-abcd
EXPLAIN ANALYZE SELECT * FROM projects;

Output without index on organization_id:

text

Seq Scan on projects  (cost=0.00..45.50 rows=10 width=52) (actual time=0.010..0.450 rows=25 loops=1)
  Filter: (organization_id = '...'::uuid) -- RLS Policy Applied Here!
  Rows Removed by Filter: 9975
Planning Time: 0.150 ms
Execution Time: 0.475 ms

Here, PostgreSQL performs a full table scan (Seq Scan) and then filters out rows that don't match the policy. This is disastrous for large tables.

Output with a B-tree index on organization_id:

text

Bitmap Heap Scan on projects  (cost=4.45..15.50 rows=10 width=52) (actual time=0.025..0.030 rows=25 loops=1)
  Recheck Cond: (organization_id = '...'::uuid) -- RLS Policy Applied Here!
  ->  Bitmap Index Scan on projects_organization_id_idx  (cost=0.00..4.44 rows=10 width=0) (actual time=0.020..0.020 rows=25 loops=1)
        Index Cond: (organization_id = '...'::uuid)
Planning Time: 0.200 ms
Execution Time: 0.050 ms

The difference is night and day. With the index, PostgreSQL can efficiently find only the relevant rows. Rule of thumb: Any column used in an RLS USING or WITH CHECK clause must be indexed.

2. The Peril of Volatile Functions in Policies

Policies that use VOLATILE functions (e.g., random(), now()) or complex subqueries can wreak havoc on performance. The query planner cannot easily cache results or create optimal plans. Keep policies STABLE or IMMUTABLE whenever possible. Our helper functions were explicitly marked STABLE because their result is consistent within a single scan.

Advanced Patterns and Edge Cases

Real-world systems require handling exceptions to the rules.

1. The "Superuser" / Internal Support Access Dilemma

Your internal support team may need to access a specific tenant's data for debugging. How do you grant this access without disabling RLS?

Solution: Create a bypass policy that checks for a specific role. This policy should be defined before the general tenancy policy, as policies are checked in alphabetical order by name (for a given event/command combination).

sql

-- A policy to grant access to internal support staff
-- 'a' prefix ensures it is checked before 's' in select_projects_based_on_role
CREATE POLICY a_allow_support_access
ON projects
FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE
USING (app.current_user_role() = 'internal_support')
WITH CHECK (app.current_user_role() = 'internal_support');

In your application, you would issue a special, short-lived JWT with the internal_support role to authenticated support staff. This is far more secure and auditable than temporarily disabling RLS or using a shared superuser account.

2. Handling Users in Multiple Organizations

What if a user can belong to multiple tenants? The JWT payload might look like this:

json

{
  "sub": "user-uuid-1234",
  "org_ids": ["org-uuid-abcd", "org-uuid-efgh"],
  "active_org_id": "org-uuid-abcd",
  "role": "admin"
}

Your application would set the active_org_id for normal operations. For a feature that needs to aggregate data across all of a user's organizations, you can pass the array.

javascript

// In the RLS wrapper
const orgsForDb = `{${req.user.org_ids.join(',')}}`; // Format for Postgres array literal
await client.query(`SET LOCAL app.current_user_orgs = $1`, [orgsForDb]);

Then, the RLS policy can use the = ANY operator:

sql

CREATE POLICY select_projects_for_multiple_organizations
ON projects
FOR SELECT
USING (
  organization_id = ANY(current_setting('app.current_user_orgs')::uuid[])
);

Be mindful that this can be less performant than a simple equality check, but it's a powerful pattern for cross-tenant visibility when required.

3. The Importance of a Default DENY Policy

What happens if no policy matches? By default, access is denied. However, it's a best practice to make this explicit with a default DENY policy. This acts as a fail-safe.

sql

-- 'z' prefix ensures this is the last policy evaluated
CREATE POLICY z_deny_all
ON projects
FOR ALL
USING (false);

This policy always evaluates to false, ensuring that if all other policies fail to grant access, the operation is blocked. This prevents accidental data exposure if a more permissive policy is misconfigured or deleted.

Conclusion: A Paradigm Shift for Authorization

By moving authorization from the application into the PostgreSQL database with RLS, we fundamentally harden the security and simplify the architecture of multi-tenant applications. The database becomes the ultimate arbiter of data access, providing a single, consistent, and highly performant enforcement point.

This pattern yields significant benefits:

Enhanced Security: The attack surface for authorization bypass vulnerabilities is drastically reduced to the small portion of code that sets the session context.

Simplified Application Code: Business logic is freed from repetitive, error-prone tenancy checks.

Improved Performance: The query planner can use indexes and statistics to optimize data access with full knowledge of the security constraints.

Centralized and Auditable Policies: Authorization rules are co-located with the data they protect, written in declarative SQL, making them easier to review and reason about.

Adopting RLS is a paradigm shift. It requires treating the database not just as a passive data store, but as an active participant in the application's security model. For senior engineers building the next generation of scalable and secure SaaS platforms, mastering PostgreSQL RLS is no longer a niche skill—it is an essential tool for building truly robust systems.

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

Core Pattern: JWT Claims as a Source of Truth for Database Sessions

Implementation Deep Dive: Schema and RLS Policies

1. Schema Definition

2. Enabling and Forcing RLS

3. Crafting the RLS Policies

Production Application Integration: A Complete Node.js/Express Example

Performance Considerations and Query Analysis

Advanced Patterns and Edge Cases

Conclusion: A Paradigm Shift for Authorization

Found this article helpful?