TL;DR: Move away from fragile, hardcoded role checks by building a robust Role-Based Access Control (RBAC) system from scratch. This guide demonstrates how to design a normalized PostgreSQL schema with five core tables and implement a reusable Express.js middleware in TypeScript to enforce granular resource:action permissions.

⚡ Key Takeaways

• Stop checking hardcoded user roles (e.g., req.user.role === 'admin') in route handlers and instead verify granular permissions to decouple access control from business logic.
• Design a normalized PostgreSQL schema using five core tables (users, roles, permissions, role_permissions, and user_roles) to manage many-to-many access relationships.
• Utilize PostgreSQL's gen_random_uuid() for primary keys and apply ON DELETE CASCADE on junction tables to maintain database integrity.
• Adopt a strict resource:action naming convention (e.g., invoices:read, users:delete) to make routing code immediately understandable and auditable.
• Configure typeRoots in your tsconfig.json to properly extend the Express Request object with custom user payloads for your authorization middleware.

You start your SaaS application with a simple authentication check. Upon login, you issue a JSON Web Token (JWT). In your route handlers, you write if (req.user.role === 'admin') { ... }. It works perfectly—for exactly three weeks.

Soon, the product manager requests a new "Billing Manager" role. They need access to invoices but shouldn't be able to delete user accounts. A week later, you need a "Support" role that can view user profiles but cannot change passwords. Your codebase quickly becomes a tangled mess of nested if statements, hardcoded arrays like ['admin', 'billing_manager', 'super_admin'], and duplicated authorization logic scattered across dozens of controllers.

This approach is fragile, difficult to audit, and prone to security vulnerabilities. If a developer forgets to add a specific role to a specific array on a specific route, unauthorized users gain access to restricted resources.

The solution is a robust Role-Based Access Control (RBAC) architecture decoupled from your business logic. Instead of checking who the user is (their role), your application code should only check what the user is allowed to do (their permissions). Roles simply become database entities that group those permissions together.

In this guide, you will learn how to architect a production-grade RBAC system from scratch using Node.js, TypeScript, and PostgreSQL. We will design the relational database schema, write the SQL queries to aggregate permissions, and build a reusable Express.js middleware to enforce access controls at the route level.

Designing the PostgreSQL Database Architecture

To build a scalable RBAC system, we need to completely decouple users, roles, and permissions. We can achieve this using a normalized relational database design that utilizes Many-to-Many relationships.

Our schema will consist of five core tables:

1. users: Stores account information.
2. roles: Stores role definitions (e.g., "Admin", "Editor").
3. permissions: Stores distinct, granular actionable rights (e.g., "invoices:read", "users:delete").
4. role_permissions: A junction table linking roles to their granted permissions.
5. user_roles: A junction table linking users to their assigned roles.

Here is the SQL script to create this architecture:

-- 1. Create Core Tables (Assumes PostgreSQL 13+ for gen_random_uuid())
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE roles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE permissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL, -- e.g., 'invoices:read', 'users:write'
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Create Junction Tables for Many-to-Many Relationships
CREATE TABLE role_permissions (
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
    permission_id UUID REFERENCES permissions(id) ON DELETE CASCADE,
    PRIMARY KEY (role_id, permission_id)
);

CREATE TABLE user_roles (
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role_id UUID REFERENCES roles(id) ON DELETE CASCADE,
    PRIMARY KEY (user_id, role_id)
);

-- 3. Add Indexes for Query Performance
CREATE INDEX idx_user_roles_user_id ON user_roles(user_id);
CREATE INDEX idx_role_permissions_role_id ON role_permissions(role_id);

Production Note: Always establish a strict naming convention for permissions. A standard approach is resource:action (e.g., articles:create, articles:read, users:delete). This makes it immediately obvious what a permission controls when reading your routing code.

Setting Up the Node.js & TypeScript Project

Before writing the application logic, let's bootstrap a standard Node.js environment configured with TypeScript and the necessary PostgreSQL drivers.

# Initialize the project
npm init -y

# Install dependencies
npm install express pg jsonwebtoken dotenv cors
npm install -D typescript @types/node @types/express @types/pg @types/jsonwebtoken @types/cors ts-node-dev

Initialize and configure your TypeScript environment (tsconfig.json):

{
  "compilerOptions": {
    "target": "ES2022",
    "module": "CommonJS",
    "rootDir": "./src",
    "outDir": "./dist",
    "strict": true,
    "esModuleInterop": true,
    "skipLibCheck": true,
    "forceConsistentCasingInFileNames": true,
    "typeRoots": ["./node_modules/@types", "./src/types"]
  },
  "include": ["src/**/*"]
}

Note the typeRoots array. We will need this shortly to extend the Express Request object with our custom user payload, a common hurdle when implementing custom middleware in TypeScript.

Set up your database connection pool using the pg package:

// src/db/index.ts
import { Pool } from 'pg';
import dotenv from 'dotenv';

dotenv.config();

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 20, // Maximum number of connections in the pool
  idleTimeoutMillis: 30000,
});

// Helper function to execute queries safely
export const query = async (text: string, params?: unknown[]) => {
  const start = Date.now();
  const res = await pool.query(text, params);
  const duration = Date.now() - start;
  console.log('Executed query', { text, duration, rows: res.rowCount });
  return res;
};

Fetching User Permissions Dynamically

When a user makes a request to a protected endpoint, we need to determine what actions they are permitted to perform. There are two primary architectural patterns for handling this:

1. Fat Tokens (JWT Payload): Embed an array of permission strings directly into the JWT upon login.
2. Database/Cache Lookup: The JWT only contains the user_id. The server fetches the permissions from the database or a cache on every request.

While embedding permissions inside the JWT is fast, it is a dangerous anti-pattern for SaaS applications. If you revoke an administrator's access, their token remains fully authorized until it expires. You cannot instantly invalidate their permissions.

Instead, we will fetch permissions dynamically. Here is the SQL query required to aggregate a user's permissions across all their assigned roles:

SELECT DISTINCT p.name AS permission_name
FROM permissions p
JOIN role_permissions rp ON p.id = rp.permission_id
JOIN user_roles ur ON rp.role_id = ur.role_id
WHERE ur.user_id = $1;

Let's wrap this logic inside a TypeScript service class:

// src/services/rbac.service.ts
import { query } from '../db';

interface PermissionRow {
  name: string;
}

export class RBACService {
  /**
   * Retrieves all unique permissions for a given user ID.
   */
  static async getUserPermissions(userId: string): Promise<string[]> {
    const sql = `
      SELECT DISTINCT p.name
      FROM permissions p
      JOIN role_permissions rp ON p.id = rp.permission_id
      JOIN user_roles ur ON rp.role_id = ur.role_id
      WHERE ur.user_id = $1;
    `;

    try {
      const result = await query(sql, [userId]);
      // Map the resulting rows into a flat array of strings
      return result.rows.map((row) => (row as PermissionRow).name);
    } catch (error) {
      console.error('Error fetching user permissions:', error);
      throw new Error('Database error while fetching permissions');
    }
  }
}

By utilizing DISTINCT, we ensure that if a user has two different roles that both grant the invoices:read permission, the resulting array only includes it once.

Extending the Express Request Object in TypeScript

To pass the user's ID and resolved permissions down to our route handlers, we must attach them to the req object. In TypeScript, the standard express.Request type doesn't recognize our custom properties. We need to use Declaration Merging to extend it.

Create a type definition file:

// src/types/express/index.d.ts
export {};

declare global {
  namespace Express {
    export interface Request {
      user?: {
        id: string;
        email: string;
      };
      permissions?: string[];
    }
  }
}

Now, TypeScript will compile seamlessly when we assign req.permissions = [...] inside our middleware.

Building the Authentication and Authorization Middlewares

A scalable RBAC pipeline generally requires two separate middlewares executed sequentially:

1. authenticate: Verifies the JWT and attaches the user_id to the request.
2. requirePermissions: A factory function that takes an array of required permissions, fetches the user's actual capabilities, and compares them.

1. The Authentication Middleware

// src/middleware/auth.middleware.ts
import { Request, Response, NextFunction } from 'express';
import jwt from 'jsonwebtoken';

interface JwtPayload {
  id: string;
  email: string;
}

export const authenticate = (req: Request, res: Response, next: NextFunction): void => {
  const authHeader = req.headers.authorization;

  if (!authHeader || !authHeader.startsWith('Bearer ')) {
    res.status(401).json({ error: 'Unauthorized: No token provided' });
    return;
  }

  const token = authHeader.split(' ')[1];

  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET as string) as JwtPayload;
    
    // Attach user payload to the request object
    req.user = decoded;
    next();
  } catch (error) {
    res.status(401).json({ error: 'Unauthorized: Invalid or expired token' });
    return;
  }
};

2. The RBAC Middleware

Next, we build the core of our authorization system. This middleware is designed to accept an array of required permissions. It fetches the user's permissions and verifies that they possess all required capabilities.

// src/middleware/rbac.middleware.ts
import { Request, Response, NextFunction } from 'express';
import { RBACService } from '../services/rbac.service';

/**
 * Middleware factory to enforce permission checks
 * @param requiredPermissions Array of permission strings required for the route
 */
export const requirePermissions = (requiredPermissions: string[]) => {
  return async (req: Request, res: Response, next: NextFunction): Promise<void> => {
    try {
      if (!req.user) {
        res.status(401).json({ error: 'User not authenticated' });
        return;
      }

      // Fetch user's permissions from the database
      const userPermissions = await RBACService.getUserPermissions(req.user.id);
      
      // Attach to request object for potential use in downstream controllers
      req.permissions = userPermissions;

      // Ensure the user has EVERY permission required by the route
      const hasAllPermissions = requiredPermissions.every(permission => 
        userPermissions.includes(permission)
      );

      if (!hasAllPermissions) {
        res.status(403).json({ 
          error: 'Forbidden: Insufficient permissions',
          required: requiredPermissions 
        });
        return;
      }

      next();
    } catch (error) {
      console.error('RBAC Middleware Error:', error);
      res.status(500).json({ error: 'Internal server error during authorization' });
      return;
    }
  };
};

Tip: In the middleware above, we used requiredPermissions.every(...). This means the user must possess all listed permissions. If you have use cases where a user needs permission A or permission B, you can create an alternative middleware that utilizes requiredPermissions.some(...).

Applying Permissions to Express Routes

With our architecture in place, securing our endpoints becomes incredibly declarative and easy to audit. Let's wire up a sample Express router for an invoicing service.

// src/routes/invoices.routes.ts
import { Router, Request, Response } from 'express';
import { authenticate } from '../middleware/auth.middleware';
import { requirePermissions } from '../middleware/rbac.middleware';

const router = Router();

// Enforce authentication on all routes within this router
router.use(authenticate);

// Users with 'invoices:read' can access this endpoint
router.get('/', 
  requirePermissions(['invoices:read']), 
  (req: Request, res: Response) => {
    res.json({ message: 'List of invoices returned' });
});

// Only users with 'invoices:create' can hit this endpoint
router.post('/', 
  requirePermissions(['invoices:create']), 
  (req: Request, res: Response) => {
    res.status(201).json({ message: 'Invoice created successfully' });
});

// Requires BOTH read and delete permissions simultaneously
router.delete('/:id', 
  requirePermissions(['invoices:read', 'invoices:delete']), 
  (req: Request, res: Response) => {
    res.json({ message: `Invoice ${req.params.id} deleted` });
});

export default router;

This represents the true power of RBAC. The controller logic is completely unaware of "Roles." If the marketing team needs a new "Auditor" role that can only read invoices, you don't touch the codebase. You simply insert a new row into the roles table, link it to the invoices:read permission in role_permissions, and assign that role to the auditor in user_roles.

Scaling the Architecture: Caching Permissions with Redis

If you implement the code exactly as written above, your application will perform three database JOIN operations on every single authenticated HTTP request. For a low-traffic internal tool, this is perfectly fine. PostgreSQL is exceptionally fast, and a well-indexed query will execute in under 5 milliseconds.

However, if you are building a high-traffic SaaS application, this will severely bottleneck your database. When we design enterprise systems through our Backend Development API Services, we immediately introduce a caching layer for authorization data.

Redis is the perfect tool for this scenario. When a user logs in, we fetch their permissions from PostgreSQL and cache the resulting array in Redis as a serialized JSON string.

Here is how you modify the RBACService to implement a robust "Cache-Aside" pattern:

// src/services/rbac.service.ts
import { query } from '../db';
import { createClient } from 'redis';

// Initialize Redis Client (Ensure this is integrated properly into your app's startup lifecycle)
const redisClient = createClient({ url: process.env.REDIS_URL });
redisClient.connect().catch(console.error);

export class RBACService {
  static async getUserPermissions(userId: string): Promise<string[]> {
    const cacheKey = `user:${userId}:permissions`;

    try {
      // 1. Try to fetch from Redis Cache
      const cachedPermissions = await redisClient.get(cacheKey);
      
      if (cachedPermissions) {
        return JSON.parse(cachedPermissions);
      }

      // 2. If Cache Miss, fetch from PostgreSQL
      const sql = `
        SELECT DISTINCT p.name
        FROM permissions p
        JOIN role_permissions rp ON p.id = rp.permission_id
        JOIN user_roles ur ON rp.role_id = ur.role_id
        WHERE ur.user_id = $1;
      `;
      
      const result = await query(sql, [userId]);
      const permissions = result.rows.map(row => (row as { name: string }).name);

      // 3. Save to Redis Cache with a Time-To-Live (TTL) of 15 minutes (900 seconds)
      await redisClient.setEx(cacheKey, 900, JSON.stringify(permissions));

      return permissions;
    } catch (error) {
      console.error('Error in getUserPermissions:', error);
      throw error;
    }
  }

  /**
   * Call this function whenever a user's role is updated to clear their cache instantly.
   */
  static async invalidateUserPermissionsCache(userId: string): Promise<void> {
    await redisClient.del(`user:${userId}:permissions`);
  }
}

Now, your database is queried only once every 15 minutes per active user, drastically reducing CPU load and latency. When an admin changes a user's role, you simply call RBACService.invalidateUserPermissionsCache(userId), ensuring the system instantly enforces the new security parameters on their next request.

Advanced Considerations: Moving to ABAC

While RBAC handles 90% of business logic perfectly, developers often hit a wall when requirements become context-sensitive.

For example: "A user can only delete an invoice IF they are the author of that specific invoice."

RBAC cannot solve this natively. The permission invoices:delete only establishes that they are generally allowed to delete invoices, not which specific invoices they own.

To solve this, you transition toward Attribute-Based Access Control (ABAC). This usually involves fetching the resource from the database inside the controller, and then explicitly checking ownership:

// Inside your controller
const invoiceResult = await query('SELECT * FROM invoices WHERE id = $1', [req.params.id]);
const invoice = invoiceResult.rows[0];

if (!invoice) {
    return res.status(404).json({ error: 'Invoice not found' });
}

// Combine RBAC (handled by middleware) with ABAC (handled in controller)
if (invoice.author_id !== req.user?.id && !req.permissions?.includes('invoices:delete_any')) {
    return res.status(403).json({ error: 'You do not have permission to delete this specific resource' });
}

// Proceed with deletion...

By combining a robust RBAC middleware with context-aware ABAC controller logic, you can build security systems capable of handling even the most complex enterprise requirements.

Frequently Asked Questions

Why use five separate tables instead of just adding a "role" column to the users table?

Adding a single role column inevitably leads to hardcoded authorization logic and nested if statements scattered across your controllers. A five-table normalized schema completely decouples users, roles, and permissions, allowing your code to check what a user can do rather than who they are. This makes your application highly scalable, secure, and much easier to audit.

What is the best naming convention for RBAC permissions in the database?

The industry standard approach is using a resource:action format, such as invoices:read, articles:create, or users:delete. This strict naming convention makes it immediately obvious what specific rights a permission grants. It also keeps your routing and middleware code clean and highly readable when enforcing access controls.

How do I handle TypeScript errors when adding the user payload to the Express Request object?

You need to extend the default Express Request interface by defining a custom type declaration file. In your tsconfig.json, configure the typeRoots array to include your custom types directory alongside the default node_modules (e.g., "typeRoots": ["./node_modules/@types", "./src/types"]). This allows TypeScript to properly recognize your custom user payload and permissions during middleware execution.

Can SoftwareCrafting help migrate an existing SaaS app from hardcoded JWT roles to this RBAC architecture?

Yes, SoftwareCrafting specializes in refactoring fragile, hardcoded authentication systems into robust, database-driven RBAC architectures. We can help you seamlessly migrate your existing user data, design the optimized PostgreSQL schema, and rewrite your middleware without causing downtime for your current users.

Won't joining five PostgreSQL tables on every API request hurt application performance?

While PostgreSQL handles relational joins very efficiently thanks to proper indexing, querying the database on every single route can introduce latency under heavy load. To optimize this, you can cache a user's aggregated permissions in a fast in-memory store like Redis upon login. Alternatively, you can embed the permissions array directly into the JWT payload, provided you implement a strategy for token invalidation.

What if my application requires multi-tenant RBAC or Attribute-Based Access Control (ABAC)?

The schema provided in this guide is a foundational RBAC implementation, but complex B2B SaaS platforms often require strict tenant-isolation or dynamic attribute-based rules. If your authorization needs have outgrown standard RBAC, the backend experts at SoftwareCrafting can design and implement advanced, custom access control systems tailored specifically to your complex business logic.

📎 Full Code on GitHub Gist: The complete schema.sql from this post is available as a standalone GitHub Gist — copy, fork, or embed it directly.