Granular API Authorization with PostgreSQL RLS and JWT Claims

The Fragility of Application-Layer Authorization

In most modern application architectures, authorization logic resides in the application layer. Whether it's a middleware in an Express.js app, a decorator in a Python framework, or a service class in a Spring Boot application, the pattern is ubiquitous:

• A user's identity is verified (authentication).
• User permissions are loaded.
• For a given resource access, the code explicitly checks if the user has the right to perform the action.

A typical implementation might look like this in a Node.js service:

// A common, yet brittle, application-layer authorization check
async function getProject(projectId, user) {
  const project = await db.query('SELECT * FROM projects WHERE id = $1', [projectId]);

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

  // The explicit authorization check
  if (project.organization_id !== user.organization_id) {
    throw new ForbiddenError('Access denied');
  }

  return project;
}

This approach works for simple cases, but it suffers from several critical flaws in complex, production systems:

* Boilerplate and Repetition: This logic must be meticulously repeated for every query and every mutation. getProject, updateProject, listDocumentsInProject—each requires a similar check.

* High Risk of Human Error: A developer might forget to add the authorization check to a new endpoint or a complex query, leading to a severe data leak. A single oversight can expose all data to all users.

* Inconsistent Logic: As the complexity of permissions grows (e.g., organization admins vs. project managers vs. members), the if statements become a tangled mess, often implemented inconsistently across different parts of the application.

* Data Leakage in Complex Queries: When performing complex JOINs or aggregations, it becomes increasingly difficult to ensure that every piece of joined data adheres to the authorization rules, creating subtle but dangerous vulnerabilities.

This model is fundamentally flawed because it places the security burden on every single line of data-access code. The database, which holds the data, is treated as a passive store, blindly trusting the application to ask for the right things. We can do better by empowering the database to enforce security itself. This post details a robust pattern for offloading authorization logic to PostgreSQL using its powerful Row-Level Security (RLS) feature, driven by claims from a JSON Web Token (JWT).

The Core Pattern: Propagating JWT Claims into the Database Session

The entire system hinges on one critical mechanism: securely passing user-specific information from the validated JWT to the active PostgreSQL connection. We cannot simply inject user ID into our SQL queries—that path leads to SQL injection. Instead, we use PostgreSQL's session-level configuration variables.

Here's the flow:

• The server validates the JWT signature, expiration, and issuer.

• RLS policies within the database read these session variables to make dynamic, row-specific access control decisions.

Implementation: Securely Setting Session Claims

Let's implement a robust function in a Node.js application using the pg library to handle this. This function will wrap a database transaction, ensuring claims are set and cleared correctly.

// db.js - Database connection setup
const { Pool } = require('pg');
const pool = new Pool({
  // your connection config
});

// A higher-order function to create a transaction with JWT claims
async function withTransaction(claims, callback) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // Sanitize and set claims. This is the most critical part.
    const setClaimsQuery = Object.entries(claims).map(([key, value]) => {
      // Use pg_catalog.quote_literal to prevent SQL injection. ABSOLUTELY ESSENTIAL.
      // We namespace our claims to avoid conflicts with built-in PostgreSQL settings.
      return `SELECT set_config('request.jwt.claims.${key}', pg_catalog.quote_literal('${String(value)}'), true);`;
    }).join(' ');

    if (setClaimsQuery) {
      await client.query(setClaimsQuery);
    }

    const result = await callback(client);

    await client.query('COMMIT');
    return result;
  } catch (e) {
    await client.query('ROLLBACK');
    throw e;
  } finally {
    client.release();
  }
}

module.exports = { withTransaction };

Key Implementation Details:

* set_config(key, value, is_local): This PostgreSQL function sets a configuration parameter. The third argument, is_local (set to true), is equivalent to using SET LOCAL. This is crucial. It ensures the setting only persists for the current transaction. Once COMMIT or ROLLBACK is executed, the setting is discarded, preventing claim data from leaking between different requests that might reuse the same database connection from the pool.

* pg_catalog.quote_literal(): We wrap the value in quote_literal. This function properly quotes a given string for use as a string literal in an SQL statement. It handles escaping and prevents any possibility of SQL injection through a maliciously crafted JWT claim. Never use string interpolation to inject values directly.

* Namespacing: We use the request.jwt.claims. prefix for our custom settings. This prevents collisions with standard PostgreSQL runtime parameters (like search_path or timezone).

Now, an API endpoint can use this wrapper:

// api/projects.js
const { withTransaction } = require('../db');

app.get('/api/projects/:id', async (req, res) => {
  // Assume req.user is populated by JWT validation middleware
  const claims = {
    sub: req.user.sub,
    org_id: req.user.organization_id,
    role: req.user.role
  };

  try {
    const project = await withTransaction(claims, async (client) => {
      // The query is now clean and free of authorization logic
      const result = await client.query('SELECT * FROM projects WHERE id = $1', [req.params.id]);
      return result.rows[0];
    });

    if (!project) {
      return res.status(404).send('Project not found'); // RLS will return no rows, not an error
    }

    res.json(project);
  } catch (error) {
    res.status(500).send('Internal Server Error');
  }
});

Notice the beauty of this: the application code is now blissfully ignorant of authorization rules. It simply asks for a project by its ID. The database itself will decide if the user making the request is allowed to see that row.

Implementing RLS Policies with JWT Claims

With the mechanism for passing claims in place, we can now define the RLS policies in the database.

Step 1: Schema Setup

Let's consider a standard multi-tenant SaaS schema:

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, -- Corresponds to 'sub' claim from JWT
    organization_id UUID NOT NULL REFERENCES organizations(id),
    email TEXT NOT NULL UNIQUE,
    role TEXT NOT NULL DEFAULT 'member' CHECK (role IN ('member', 'admin'))
);

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

Step 2: A Secure Helper Function for Accessing Claims

Reading claims directly with current_setting() in every policy is verbose and error-prone. We'll create a helper function to simplify this.

CREATE OR REPLACE FUNCTION current_user_claim(claim TEXT) RETURNS TEXT AS $$
BEGIN
    -- The second argument 't' (for true) makes the function return NULL
    -- if the setting is not found, instead of throwing an error.
    RETURN current_setting('request.jwt.claims.' || claim, 't');
EXCEPTION
    -- Catch any other potential errors, though 't' handles most cases.
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

Why STABLE?

We mark this function as STABLE, which is a performance hint to PostgreSQL. It signifies that the function's result will not change within a single statement scan. The planner can then cache its result instead of re-evaluating it for every single row being checked, which is a significant optimization.

Step 3: Creating and Enabling RLS Policies

First, we must enable RLS on the target table. By default, even if policies exist, they are not enforced until RLS is enabled.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;

-- IMPORTANT: By default, no rows are visible. We must add policies to allow access.
-- A superuser or the table owner can still access the table unless RLS is forced.
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY is a vital production hardening step. It ensures that even the table's owner is subject to RLS policies, preventing accidental data access during migrations or administrative tasks.

Now, let's create a policy that allows users to see only the projects belonging to their organization.

CREATE POLICY select_projects_for_organization
ON projects
FOR SELECT
USING (organization_id = (current_user_claim('org_id'))::uuid);

This is remarkably simple yet powerful. The USING clause defines a boolean expression that is evaluated for each row. If the expression returns true, the row is visible to the current query. Here, it checks if the organization_id of a project row matches the org_id passed in from the user's JWT.

Advanced Scenario: Hierarchical Roles and Ownership

The real power of RLS shines when handling more complex permission models. Let's introduce a documents table and a role-based permission system:

An admin of an organization can manage (select, insert, update, delete) any* document within their organization.

* A member can only manage documents they have created themselves.

Schema Extension

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    owner_id UUID NOT NULL REFERENCES users(id),
    title TEXT NOT NULL,
    content TEXT
);

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

The Combined RLS Policy

We can express this complex logic in a single, declarative policy.

CREATE POLICY manage_documents_based_on_role
ON documents
FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE
USING (
    -- Rule 1: 'admin' can access any document in their organization.
    -- We JOIN against projects to get the organization_id.
    (
        current_user_claim('role') = 'admin' AND
        EXISTS (
            SELECT 1 FROM projects p
            WHERE p.id = documents.project_id
            AND p.organization_id = (current_user_claim('org_id'))::uuid
        )
    )
    OR
    -- Rule 2: 'member' can only access documents they own.
    (
        current_user_claim('role') = 'member' AND
        owner_id = (current_user_claim('sub'))::uuid
    )
)
WITH CHECK (
    -- The WITH CHECK clause is applied for INSERT and UPDATE operations.
    -- It ensures new or modified rows also adhere to the policy.
    (
        current_user_claim('role') = 'admin' AND
        EXISTS (
            SELECT 1 FROM projects p
            WHERE p.id = documents.project_id
            AND p.organization_id = (current_user_claim('org_id'))::uuid
        )
    )
    OR
    (
        current_user_claim('role') = 'member' AND
        owner_id = (current_user_claim('sub'))::uuid
    )
);

USING vs. WITH CHECK

This is a critical distinction for senior engineers:

USING (expression): This applies to rows that already exist* in the table. It's used for commands like SELECT, UPDATE, and DELETE to determine which rows are visible or modifiable.

WITH CHECK (expression): This applies to rows that are being created or modified*. It's used for INSERT and UPDATE commands. If the new or updated row data does not satisfy the WITH CHECK expression, the command will fail with a policy violation error.

In our example, the logic is identical, so we repeat it. This ensures a member cannot create a new document and assign it to another user, nor can they update an existing document to change its owner.

Performance Considerations and Optimization

While RLS is incredibly powerful, a poorly written policy can devastate database performance. RLS policies are effectively WHERE clauses injected into your queries by the planner. Here's how to keep them fast.

1. Indexing for Policy Predicates

Any column used in your RLS policies is a prime candidate for an index. In our manage_documents_based_on_role policy, the database will frequently filter on:

* documents.owner_id (for the 'member' rule)

* documents.project_id (for the 'admin' rule)

* projects.organization_id (for the 'admin' rule)

Ensure these columns are indexed:

CREATE INDEX idx_documents_owner_id ON documents(owner_id);
CREATE INDEX idx_documents_project_id ON documents(project_id);
-- A foreign key on projects.organization_id would typically create an index automatically.

Let's analyze the impact. Consider a SELECT * FROM documents WHERE id = '...';

* Without RLS: The planner uses the primary key index on id. Very fast.

* With RLS and no indexes: The planner finds the row by id, then for that single row, it evaluates the policy. For the 'admin' part, it might need to do a slow sequential scan on the projects table. For the 'member' part, it checks owner_id. This is okay for a single row lookup.

The real danger is SELECT FROM documents;: The planner must evaluate the policy for every single row in the documents table. Without indexes on owner_id and project_id, this will lead to catastrophic sequential scans.

EXPLAIN ANALYZE is your non-negotiable tool. Always analyze your common queries with RLS enabled to ensure the planner is using indexes effectively within the policy checks.

2. The Cost of Subqueries and `EXISTS`

The EXISTS subquery in our admin policy is generally efficient, but on extremely large tables, it can add overhead. The planner is smart, but it's still an extra step.

An alternative pattern is to denormalize the organization_id onto the documents table itself.

ALTER TABLE documents ADD COLUMN organization_id UUID;
-- You would populate this with a trigger or in application logic.
-- Ensure it's indexed!
CREATE INDEX idx_documents_organization_id ON documents(organization_id);

The policy becomes much simpler and faster, avoiding the JOIN or EXISTS:

-- A faster, denormalized version of the policy
CREATE POLICY manage_documents_based_on_role_optimized
ON documents
FOR ALL
USING (
    (current_user_claim('role') = 'admin' AND organization_id = (current_user_claim('org_id'))::uuid)
    OR
    (current_user_claim('role') = 'member' AND owner_id = (current_user_claim('sub'))::uuid)
)
WITH CHECK (...); -- same logic

This is a classic trade-off between data normalization and query performance. For security-critical, high-frequency checks like RLS, denormalization is often the right choice.

Edge Cases and Production Hardening

Deploying RLS to production requires attention to several subtle but critical details.

1. The Connection Pooling Trap

This is the most common and dangerous pitfall when using RLS with session variables. Many production environments use transaction-level connection poolers like PgBouncer in its default transaction mode.

* How it works: When your application runs BEGIN, PgBouncer assigns a real database connection. When you run COMMIT or ROLLBACK, PgBouncer returns that connection to its pool, wiping its session state (RESET ALL).

* The problem: If your SET LOCAL command is in a separate statement before your main query, it might be executed on a different underlying connection than the query itself, or its state will be wiped between transactions. Your RLS policies will fail silently because the claims will be NULL.

Solutions:

* Session Pooling: Configure your pooler (e.g., PgBouncer) to use session pooling mode. This dedicates a database connection to a client for the entire duration the client is connected. This is the most reliable solution but can require more database connections.

* Application-Level Pooling: Most modern application-level pools (like node-pg's pool) handle this correctly. When you pool.connect(), you get a client object that represents a stable connection. As long as you use that same client for your BEGIN, SET LOCAL ..., query, and COMMIT, you are safe.

* In-Transaction Setting: Our withTransaction wrapper function is designed to be safe with transaction-level pooling because the BEGIN, set_config, and business query all happen sequentially within the same transaction, on the same underlying connection assigned by the pooler.

2. Superusers and the `BYPASSRLS` Privilege

By default, database superusers and roles with the BYPASSRLS attribute are exempt from RLS. This is a feature for administrative tasks, but a security risk if misconfigured.

Production Rule: Your application's runtime database user must NOT be a superuser and must NOT have the BYPASSRLS privilege. Create a dedicated, least-privilege role for your application.

CREATE ROLE api_user WITH LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE my_db TO api_user;
GRANT USAGE ON SCHEMA public TO api_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON projects, documents TO api_user;
-- Explicitly ensure bypass is off
ALTER ROLE api_user NOBYPASSRLS;

3. Views and Security Definers

RLS behavior with views can be surprising. Consider a view:

CREATE VIEW user_documents AS
SELECT d.id, d.title, u.email as owner_email
FROM documents d
JOIN users u ON d.owner_id = u.id;

When a user queries this view, which RLS policies apply? Those of the user querying the view, or those of the user who created the view?

By default, it's the view owner's permissions (security_definer). This is often not what you want. You can change this behavior:

CREATE OR REPLACE VIEW user_documents WITH (security_invoker = true) AS ...

With security_invoker = true, the RLS policies of the underlying tables (documents and users) are applied based on the identity of the user running the query, which is exactly the behavior we need for our JWT-driven RLS model.

Conclusion: A Unified, Secure Authorization Model

By moving authorization logic from the application layer to the database via PostgreSQL RLS and JWT claims, we achieve a paradigm shift in application security. This pattern isn't a silver bullet, but for data-centric, multi-tenant applications, the benefits are profound:

* Centralized Truth: Authorization rules live with the data they protect, ensuring they are applied consistently to every single query, whether from your API, a reporting tool, or an admin console.

* Drastically Reduced Boilerplate: Application code becomes simpler, cleaner, and focused on business logic, not repetitive security checks.

* Elimination of a Class of Vulnerabilities: It becomes structurally impossible for a developer to forget an authorization check and leak data. If a row is not permitted by a policy, the database simply pretends it doesn't exist.

* Performance: When implemented with careful attention to indexing and policy design, RLS can be as fast or even faster than application-layer checks, as the filtering happens at the lowest possible level, close to the data itself.

This advanced pattern requires a deep understanding of both your application's security model and PostgreSQL's internal workings. But for senior engineers building the next generation of secure, scalable software, it represents a powerful tool for creating a truly robust and defensible data layer.