Back to Resources

PostgreSQL RLS with JWTs for Granular API Authorization

18 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.

Decoupling Authorization: From Application Logic to Database Enforcement

In modern multi-tenant architectures, managing authorization—who can see and modify what data—is a critical and complex challenge. The conventional approach involves embedding authorization logic directly into the application layer. Every data access query is meticulously crafted with WHERE clauses, joining through user, role, and tenancy tables to ensure data is properly scoped. While functional, this pattern scatters authorization logic across countless services, repositories, and controllers. It's brittle, prone to error, and a nightmare to audit. A single missing WHERE user_id = ? clause can lead to a catastrophic data leak.

This article explores a more robust and centralized pattern: delegating authorization enforcement to the database itself using PostgreSQL's Row-Level Security (RLS). We will go deep into a production-ready implementation where API authorization, defined by JWT claims, is seamlessly translated into non-bypassable database security policies. This moves the security perimeter from the application—which can have bugs—to the data layer, the ultimate source of truth.

We will not cover the basics of RLS. We assume you understand what CREATE POLICY does. Instead, we will focus on the hard parts: the architectural glue needed to connect a stateless API with a stateful database session, the performance pitfalls of poorly written policies, and the operational patterns required to manage this system in a real-world environment.

The Core Architecture: Bridging JWTs and PostgreSQL Sessions

The central challenge is making the database aware of the application-level user context (e.g., user_id, organization_id, role) that is available in the JWT of an incoming API request. PostgreSQL's RLS policies do not have direct access to HTTP headers. The solution lies in using PostgreSQL's session-level configuration parameters.

For each incoming request, we will:

  • Validate the JWT from the Authorization header.
  • Extract the relevant claims (sub, org_id, role, etc.).
    • Obtain a database connection from the pool.
  • Within a single transaction for that request, set session-local variables using SET LOCAL.
    • Execute business logic queries.
    • RLS policies within the database will read these session variables to make authorization decisions.
    • The transaction is committed or rolled back, and the connection is returned to the pool, automatically discarding the session-local variables.

    This ensures that the user context is securely scoped to a single transaction and a single connection, preventing context leakage between concurrent requests.

    Setting the Stage: A Multi-Tenant Schema

    Let's define a simple but realistic schema for a project management SaaS:

    sql
    -- Use pgcrypto for UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

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

CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL DEFAULT 'member' -- e.g., 'member', 'admin'
);

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

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 TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title TEXT NOT NULL,
    assignee_id UUID REFERENCES users(id) ON DELETE SET NULL
);

-- Enable RLS on all relevant tables
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE project_members ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

    Middleware: The JWT-to-Session Bridge

    Here's a complete, production-grade example using Node.js, Express, and the pg (node-postgres) library. The key component is the middleware that prepares the database session.

    javascript
    // middleware/auth.js
const jwt = require('jsonwebtoken');
const pool = require('../db/pool'); // Your configured node-postgres pool

const JWT_SECRET = process.env.JWT_SECRET;

async function authenticateAndSetContext(req, res, next) {
    const authHeader = req.headers['authorization'];
    const token = authHeader && authHeader.split(' ')[1];

    if (!token) {
        return res.sendStatus(401); // Unauthorized
    }

    let claims;
    try {
        claims = jwt.verify(token, JWT_SECRET);
    } catch (err) {
        return res.sendStatus(403); // Forbidden
    }

    // Attach claims to the request object for potential use in the app layer
    req.user = claims;

    // The critical part: setting DB context
    const client = await pool.connect();
    req.dbClient = client;

    try {
        // BEGIN transaction implicitly handled by some frameworks, but explicit is safer.
        await client.query('BEGIN');

        // Use SET LOCAL to scope settings to the current transaction.
        // This is crucial for connection pooling to work correctly.
        // The settings are automatically cleared when the transaction ends.
        await client.query(`SET LOCAL rls.user_id = '${claims.sub}'`);
        await client.query(`SET LOCAL rls.org_id = '${claims.org_id}'`);
        await client.query(`SET LOCAL rls.role = '${claims.role}'`);
        
        // For debugging: check the current settings
        // const result = await client.query("SELECT current_setting('rls.user_id'), current_setting('rls.org_id')");
        // console.log('DB Context Set:', result.rows[0]);

        next();
    } catch (err) {
        // If setting context fails, rollback and release
        await client.query('ROLLBACK');
        client.release();
        console.error('Failed to set RLS context', err);
        res.status(500).send('Internal Server Error');
    }
}

// Middleware to commit/rollback and release the client
async function transactionHandler(req, res, next) {
    if (!req.dbClient) {
        return; // Should not happen if auth middleware ran
    }

    // This logic runs after the route handler has finished.
    // We listen for the 'finish' event on the response.
    res.on('finish', async () => {
        try {
            if (res.statusCode >= 200 && res.statusCode < 400) {
                await req.dbClient.query('COMMIT');
            } else {
                await req.dbClient.query('ROLLBACK');
            }
        } catch (err) {
            console.error('Transaction commit/rollback failed', err);
            // If commit fails, we can't really recover the client response.
            // The rollback on error is the critical part.
        } finally {
            req.dbClient.release();
        }
    });
    next();
}

module.exports = { authenticateAndSetContext, transactionHandler };

    Key Implementation Details:

  • SET LOCAL is Non-Negotiable: Using SET (without LOCAL) would set the variable for the entire session. In a pooled environment, that session (connection) could be reused by another user's request, leading to catastrophic data leakage. SET LOCAL scopes the setting to the current transaction only. When the transaction ends (COMMIT or ROLLBACK), the setting is reverted.
  • Transaction Management: The entire request lifecycle, from setting the context to executing business logic, must be wrapped in a single database transaction. The transactionHandler middleware ensures that the transaction is properly closed and the connection is released back to the pool.
  • Namespace Configuration: We use a custom namespace rls. (e.g., rls.user_id) for our settings. This avoids potential conflicts with built-in PostgreSQL parameters.

    • Crafting Performant and Secure RLS Policies

    With the context-passing mechanism in place, we can now write the RLS policies.

    Basic Tenancy and Ownership Policies

    First, let's enforce basic multi-tenancy. Users should only be able to see data within their own organization.

    sql
    -- A helper function to get the current user's ID, handling NULLs gracefully.
CREATE OR REPLACE FUNCTION current_user_id() RETURNS UUID AS $$
    SELECT nullif(current_setting('rls.user_id', true), '')::UUID;
$$ LANGUAGE SQL STABLE;

-- Helper for organization ID
CREATE OR REPLACE FUNCTION current_org_id() RETURNS UUID AS $$
    SELECT nullif(current_setting('rls.org_id', true), '')::UUID;
$$ LANGUAGE SQL STABLE;

-- Helper for role
CREATE OR REPLACE FUNCTION current_role() RETURNS TEXT AS $$
    SELECT nullif(current_setting('rls.role', true), '');
$$ LANGUAGE SQL STABLE;

-- Policy for projects: any user can see any project within their organization.
CREATE POLICY select_projects ON projects
    FOR SELECT
    USING (organization_id = current_org_id());

-- Policy for users: a user can see other users in their own organization.
CREATE POLICY select_users ON users
    FOR SELECT
    USING (organization_id = current_org_id());

-- An admin can insert/update/delete projects. A member cannot.
CREATE POLICY manage_projects ON projects
    FOR ALL -- Applies to INSERT, UPDATE, DELETE
    USING (organization_id = current_org_id()) -- Existing rows for UPDATE/DELETE
    WITH CHECK (organization_id = current_org_id() AND current_role() = 'admin'); -- New/updated rows

    Analysis:

    * Helper Functions: Using functions like current_user_id() is cleaner than repeating current_setting(...) everywhere. The true argument in current_setting prevents an error if the setting is not found, returning NULL instead, which nullif handles.

    USING vs. WITH CHECK: A common point of confusion. The USING clause applies to rows that already exist in the table. It filters what can be selected, updated, or deleted. The WITH CHECK clause applies to rows that are being inserted or updated*. It validates the new data.

    Advanced Policy: Granular Access Based on Relationships

    Now for a more complex scenario. A member role should only be able to see tasks for projects they are explicitly assigned to. An admin can see all tasks in the organization.

    This requires a subquery in the policy, which is where performance can become a major concern.

    sql
    CREATE POLICY select_tasks ON tasks
    FOR SELECT
    USING (
        -- Admins can see all tasks in their organization
        (current_role() = 'admin' AND project_id IN (SELECT id FROM projects WHERE organization_id = current_org_id()))
        OR
        -- Members can only see tasks in projects they are a member of
        (current_role() = 'member' AND project_id IN (
            SELECT project_id FROM project_members WHERE user_id = current_user_id()
        ))
    );

CREATE POLICY insert_tasks ON tasks
    FOR INSERT
    WITH CHECK (
        -- You can only insert tasks into projects you are a member of.
        project_id IN (SELECT project_id FROM project_members WHERE user_id = current_user_id())
    );

    When a user with the member role runs SELECT * FROM tasks;, PostgreSQL implicitly rewrites the query to:

    sql
    SELECT * FROM tasks
WHERE tasks.project_id IN (SELECT project_id FROM project_members WHERE user_id = 'user-uuid-from-jwt');

    This is powerful, but it comes at a cost.

    Performance Deep Dive: The Perils of RLS Subqueries

    Subqueries in RLS policies are executed for every query against the protected table. The PostgreSQL query planner can sometimes struggle to optimize these, especially with complex joins or large tables. Let's analyze the select_tasks policy for a member.

    Running EXPLAIN ANALYZE SELECT * FROM tasks; for a member might produce a plan like this:

    text
    ->  Nested Loop  (cost=0.57..24.75 rows=1 width=116) (actual time=0.033..0.045 rows=10 loops=1)
      ->  HashAggregate  (cost=0.29..0.30 rows=1 width=16) (actual time=0.019..0.020 rows=3 loops=1)
            Group Key: project_members.project_id
            ->  Index Scan using project_members_pkey on project_members  (cost=0.28..0.29 rows=1 width=16) (actual time=0.013..0.014 rows=3 loops=1)
                  Index Cond: (user_id = 'uuid-of-current-user')
      ->  Index Scan using ix_tasks_project_id on tasks  (cost=0.29..24.44 rows=1 width=116) (actual time=0.006..0.007 rows=3 loops=3)
            Index Cond: (project_id = project_members.project_id)

    In this case, the planner is smart. It executes the subquery on project_members first (which is very fast due to the index on user_id), gets a small list of project_ids, and then uses an index scan on tasks. This is efficient.

    However, imagine a more complex policy:

    USING (project_id IN (SELECT ... FROM ... WHERE ... JOIN ...))

    The planner might choose a less optimal join strategy, potentially leading to full table scans inside the RLS filter for every single row access.

    Optimization Strategy 1: `SECURITY DEFINER` Functions

    We can encapsulate the complex logic into a function and mark it as SECURITY DEFINER. This can sometimes help the planner by hiding complexity and allowing for more aggressive caching of results.

    A SECURITY DEFINER function executes with the privileges of the user who defined it, not the user who calls it. This is powerful but requires careful security considerations to prevent privilege escalation.

    sql
    -- Create a function to check project membership. It's marked as STABLE because
-- for a given input, it always returns the same result within a single query.
CREATE OR REPLACE FUNCTION is_project_member(p_project_id UUID, p_user_id UUID)
RETURNS BOOLEAN AS $$
BEGIN
    -- IMPORTANT: Always check for NULL inputs in SECURITY DEFINER functions
    IF p_project_id IS NULL OR p_user_id IS NULL THEN
        RETURN FALSE;
    END IF;
    RETURN EXISTS (SELECT 1 FROM project_members WHERE project_id = p_project_id AND user_id = p_user_id);
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

-- Grant execute permissions to your application's database role
GRANT EXECUTE ON FUNCTION is_project_member(UUID, UUID) TO my_app_role;

-- Now, rewrite the policy using the function
DROP POLICY IF EXISTS select_tasks ON tasks;
CREATE POLICY select_tasks ON tasks
    FOR SELECT
    USING (
        (current_role() = 'admin' AND project_id IN (SELECT id FROM projects WHERE organization_id = current_org_id()))
        OR
        (current_role() = 'member' AND is_project_member(project_id, current_user_id()))
    );

    Benefits:

    * Encapsulation: The logic is now reusable and easier to test.

    * Planner Hints: Sometimes, the planner can better reason about a simple boolean function call than a complex subquery.

    * Potential for Caching: PostgreSQL is more likely to cache the results of a STABLE function.

    SECURITY DEFINER Warning: The function runs as its owner. Ensure it doesn't perform any dynamic SQL or operations that could be exploited. In our case, it's a simple, parameterized SELECT, which is safe.

    Optimization Strategy 2: Pre-calculating Permissions

    For extremely high-throughput systems where RLS performance is paramount, you can pre-calculate and store permissions. We can modify our session context setup to fetch all accessible project_ids for a user at the beginning of the request and store them in a temporary session variable as an array.

    Modified Middleware:

    javascript
    // In authenticateAndSetContext middleware...
// After setting the basic rls.* variables

const accessibleProjectsResult = await client.query(
    'SELECT array_agg(project_id) AS projects FROM project_members WHERE user_id = $1',
    [claims.sub]
);
const accessibleProjects = accessibleProjectsResult.rows[0].projects || [];

// PostgreSQL arrays need to be formatted as '{item1,item2}'
const projectsArrayLiteral = `{${accessibleProjects.join(',')}}`;

await client.query(`SET LOCAL rls.accessible_project_ids = '${projectsArrayLiteral}'`);

    Modified RLS Policy:

    sql
    -- Helper function to read the array setting
CREATE OR REPLACE FUNCTION accessible_project_ids() RETURNS UUID[] AS $$
    SELECT nullif(current_setting('rls.accessible_project_ids', true), '{}')::UUID[];
$$ LANGUAGE SQL STABLE;

-- New, faster policy for tasks
DROP POLICY IF EXISTS select_tasks ON tasks;
CREATE POLICY select_tasks ON tasks
    FOR SELECT
    USING (
        (current_role() = 'admin' AND project_id IN (SELECT id FROM projects WHERE organization_id = current_org_id()))
        OR
        (current_role() = 'member' AND project_id = ANY(accessible_project_ids()))
    );

    Analysis:

    Performance: This is significantly faster. The project_id = ANY(...) check is extremely efficient. The expensive subquery on project_members is now run only once* per API request, not once per table access.

    * Cost: This adds one extra query at the start of every request and increases the complexity of the middleware.

    * Trade-off: This is a classic optimization trade-off. It's the right choice for read-heavy endpoints where task data is accessed frequently. For endpoints that don't touch tasks, the initial query is wasted overhead.

    Edge Cases and Production Considerations

    Superuser and Internal Service Access

    How do background workers, cron jobs, or internal administrative tools bypass RLS? They don't have a user JWT.

  • Dedicated DB Role with BYPASSRLS: The most secure method. Create a specific role for your internal services and grant it the BYPASSRLS attribute.
    • sql
        CREATE ROLE internal_service_role LOGIN PASSWORD '...';
    ALTER ROLE internal_service_role BYPASSRLS;
    -- Grant necessary permissions on tables
    GRANT SELECT, INSERT ON tasks TO internal_service_role;

    Your background worker would then connect to the database using this role. All RLS policies will be ignored for its session.

  • Temporarily Disabling RLS: A more dangerous approach is to disable RLS for a specific operation. This requires superuser privileges and should be used sparingly.
    • sql
        SET session_replication_role = 'replica'; -- A common way to disable triggers and RLS
    -- ... perform operations ...
    SET session_replication_role = 'origin';

    Debugging and Testing

    Debugging RLS can be tricky because the filtering is implicit. To test policies directly in psql, you can simulate the application's session setup:

    sql
    -- Connect to psql as your application user
BEGIN;

-- Impersonate a specific user
SET LOCAL rls.user_id = '...';
SET LOCAL rls.org_id = '...';
SET LOCAL rls.role = 'member';

-- Now run your queries. You will only see what this user is allowed to see.
SELECT * FROM tasks;
-- > (Returns only tasks for projects the user is a member of)

-- Test an insert that should fail
INSERT INTO tasks (project_id, title) VALUES ('project-id-they-are-not-in', 'malicious task');
-- > ERROR: new row violates row-level security policy for table "tasks"

ROLLBACK;

    This workflow is invaluable for writing and verifying complex policies without needing to run the full application stack.

    Final Architectural Verdict

    Implementing authorization with PostgreSQL RLS and JWTs is a powerful pattern that offers significant security and maintainability benefits. It creates a single, auditable source of truth for data access rules, enforced at the lowest possible layer.

    Adopt this pattern when:

    * Your application has a complex, data-centric authorization model (e.g., multi-tenancy, hierarchical permissions).

    * You need to ensure consistent authorization rules across multiple backend services or APIs accessing the same database.

    * Security is paramount, and you want to minimize the risk of authorization bugs in application code leading to data leaks.

    Be cautious and prepared for:

    Performance Tuning: You must* be proficient with EXPLAIN ANALYZE and understand how RLS impacts query plans. Naive policies can cripple your database.

    * Increased Database Coupling: Authorization logic now lives in your database schema. This requires careful migration management and developers who are comfortable working in both the application and database layers.

    * Debugging Complexity: When a query returns no data, it could be because there is no data or because an RLS policy filtered it out. Debugging requires direct database inspection.

    By centralizing authorization enforcement in the database, you treat your application layer as an untrusted client, which is a core tenet of modern zero-trust security. While it's not a silver bullet, for the right class of applications, this architectural shift from application-layer enforcement to data-layer enforcement is a profound step towards building more secure, scalable, and maintainable systems.

    Found this article helpful?

    Share it with others who might benefit from it.

    More Articles