PostgreSQL RLS & JSONB for Dynamic Attribute-Based Access Control

The Problem: Centralizing Complex, Dynamic Permissions

In modern multi-tenant SaaS applications, enforcing fine-grained permissions is a recurring architectural challenge. While simple role-based access control (RBAC) can be modeled relationally, today's requirements often demand a more flexible Attribute-Based Access Control (ABAC) model. Permissions are no longer static roles but are derived from a combination of user attributes, resource properties, and environmental context.

A common pattern is to store these dynamic permissions or resource metadata in a jsonb column. This offers schema flexibility, allowing product teams to evolve permission models without costly database migrations. However, this flexibility introduces a critical problem: where and how do you enforce these rules?

The default approach is to pull the jsonb data into the application layer and run the authorization logic there. This is a dangerous path fraught with peril:

This article presents a database-centric solution, leveraging two powerful PostgreSQL features: Row-Level Security (RLS) and the jsonb data type. We will construct a complete, production-grade ABAC system that is secure by default, highly performant, and centralizes authorization logic right next to the data it protects.

This is not a primer on RLS. We assume you understand CREATE POLICY and basic multi-tenancy setups. We will dive directly into the complex interplay of RLS policies with jsonb operators, performance tuning with specialized indexes, and advanced patterns for real-world systems.

Baseline Schema for a Document Management System

Let's model a system where users can manage documents within an organization. Our access rules are complex:

* A user can be a viewer or an editor on a specific document.

* Permissions can be granted based on the user's department attribute.

* Documents have a sensitivity level that interacts with user permissions.

Here is our core schema:

-- For UUIDs
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

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

-- Users and their attributes
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    email TEXT NOT NULL UNIQUE,
    -- User-specific attributes for ABAC
    attributes JSONB DEFAULT '{}'::jsonb
);

-- The core resource we are protecting
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    title TEXT NOT NULL,
    content TEXT,
    -- Resource-specific attributes and permissions
    -- This is the core of our dynamic ABAC model
    acls JSONB DEFAULT '{}'::jsonb
);

-- Enable RLS on the table we want to protect
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

The key is the acls jsonb column in the documents table. We'll use a flexible structure to define access rules. A sample acls object might look like this:

{
  "roles": {
    "editor": ["user_uuid_1", "user_uuid_2"],
    "viewer": ["user_uuid_3"]
  },
  "attributes": {
    "requires_department": "engineering",
    "sensitivity": "confidential"
  }
}

Propagating Application Context to PostgreSQL

RLS policies are executed within a database session. To make user-aware decisions, we must securely pass the current user's identity and attributes from the application to the database. The most robust method is to use session-level configuration variables. These are namespaced, transactional, and cannot be injected by a malicious user via SQL comments.

In your application's database connection middleware, after a user is authenticated, you would run:

-- Use SET LOCAL for transaction-scoped settings
SET LOCAL app.current_user_id = 'the-authenticated-user-uuid';
SET LOCAL app.current_user_attributes = '{"department": "engineering", "clearance_level": 3}';

Now, within any RLS policy in that transaction, we can reliably access this context using current_setting('app.current_user_id', true) and current_setting('app.current_user_attributes', true)::jsonb. The true argument makes the function return NULL if the setting is not defined, preventing errors.

Let's create some sample data to work with:

-- Create an org and some users
INSERT INTO organizations (id, name) VALUES ('10000000-0000-0000-0000-000000000001', 'Stark Industries');

INSERT INTO users (id, organization_id, email, attributes) VALUES 
    ('20000000-0000-0000-0000-000000000001', '10000000-0000-0000-0000-000000000001', '[email protected]', '{"department": "rd", "clearance_level": 5}'),
    ('20000000-0000-0000-0000-000000000002', '10000000-0000-0000-0000-000000000001', '[email protected]', '{"department": "management", "clearance_level": 5}'),
    ('20000000-0000-0000-0000-000000000003', '10000000-0000-0000-0000-000000000001', '[email protected]', '{"department": "security", "clearance_level": 3}');

-- Create some documents with different ACLs
INSERT INTO documents (organization_id, title, acls) VALUES
    ('10000000-0000-0000-0000-000000000001', 'Mark V Armor Specs', 
     '{"roles": {"editor": ["20000000-0000-0000-0000-000000000001"]}, "attributes": {"sensitivity": "top_secret"}}'),
    ('10000000-0000-0000-0000-000000000001', 'Quarterly Financials', 
     '{"roles": {"viewer": ["20000000-0000-0000-0000-000000000001"]}, "attributes": {"requires_department": "management"}}'),
    ('10000000-0000-0000-0000-000000000001', 'Cafeteria Menu', 
     '{}'); -- Public within the org

Core Implementation: Crafting JSONB-Aware RLS Policies

Our goal is to create a single, comprehensive policy for SELECT operations on documents. This policy will evaluate multiple conditions.

A user can see a document if:

• They belong to the same organization.
• AND any of the following are true:

a. The document has no specific ACLs (it's public within the org).

b. Their user ID is in the viewer or editor role array.

c. The document requires a department, and the user's attributes JSONB contains that department.

To manage complexity, we'll encapsulate this logic in a helper function. This is a crucial pattern for maintainable and testable RLS.

CREATE OR REPLACE FUNCTION check_document_access(doc_acls jsonb, doc_org_id uuid)
RETURNS boolean AS $$
DECLARE
    current_user_id uuid;
    current_user_attributes jsonb;
    current_user_org_id uuid;
BEGIN
    -- Safely get session variables
    current_user_id := current_setting('app.current_user_id', true)::uuid;
    current_user_attributes := current_setting('app.current_user_attributes', true)::jsonb;

    -- Fetch the user's org_id once from the users table for security
    -- Do not trust a session variable for this!
    SELECT organization_id INTO current_user_org_id FROM users WHERE id = current_user_id;

    -- Rule 1: User must be in the same organization as the document.
    -- This is a critical, non-negotiable security boundary.
    IF doc_org_id <> current_user_org_id THEN
        RETURN false;
    END IF;

    -- Rule 2a: Public document (empty ACLs)
    IF doc_acls = '{}'::jsonb THEN
        RETURN true;
    END IF;

    -- Rule 2b: Check for direct role assignment
    -- The `?|` operator checks if any string in the array exists as a top-level key in the left-hand JSONB.
    -- Here we check if the user's ID is a value in either the editor or viewer arrays.
    IF jsonb_path_exists(doc_acls, '$.roles.*[*] ? (@ == $user_id)', jsonb_build_object('user_id', current_user_id)) THEN
       RETURN true;
    END IF;

    -- Rule 2c: Check for attribute-based access
    IF doc_acls->'attributes'->>'requires_department' IS NOT NULL AND 
       current_user_attributes->>'department' = doc_acls->'attributes'->>'requires_department' THEN
        RETURN true;
    END IF;

    -- Default deny
    RETURN false;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

Key Implementation Details:

* SECURITY DEFINER: This is critical. It ensures the function runs with the privileges of the user who defined it, not the user invoking it. This allows the function to query the users table, which the calling user might not have direct access to.

* STABLE: This tells the PostgreSQL planner that the function's result is consistent within a single scan for the same inputs, allowing for better optimization.

jsonb_path_exists: This is a powerful SQL/JSON path function. The expression '$.roles.[] ? (@ == $user_id)' is highly efficient. It checks all arrays () under roles for any element (*) that equals (? (@ == ...) our passed-in user ID. This is more robust than separate checks for editor and viewer.

* Centralized Org Check: We fetch the user's organization_id from the users table inside the function. Never trust a session variable for the primary tenancy check. An attacker could try to set app.organization_id themselves. By deriving it from the authenticated app.current_user_id, we prevent horizontal privilege escalation.

Now, we can create a clean, readable policy:

CREATE POLICY select_documents
    ON documents
    FOR SELECT
    USING (check_document_access(acls, organization_id));

Let's test it:

-- As Tony Stark (user ID '2000...0001')
BEGIN;
SET LOCAL app.current_user_id = '20000000-0000-0000-0000-000000000001';
SET LOCAL app.current_user_attributes = '{"department": "rd"}';

-- Should see 'Mark V Armor Specs' (editor role) and 'Cafeteria Menu' (public)
-- Should NOT see 'Quarterly Financials' (requires 'management' department)
SELECT title FROM documents;
-- Expected output:
--         title
-- ----------------------
--  Mark V Armor Specs
--  Cafeteria Menu

COMMIT;

-- As Pepper Potts (user ID '2000...0002')
BEGIN;
SET LOCAL app.current_user_id = '20000000-0000-0000-0000-000000000002';
SET LOCAL app.current_user_attributes = '{"department": "management"}';

-- Should see 'Quarterly Financials' (attribute match) and 'Cafeteria Menu' (public)
-- Should NOT see 'Mark V Armor Specs'
SELECT title FROM documents;
-- Expected output:
--         title
-- ------------------------
--  Quarterly Financials
--  Cafeteria Menu

COMMIT;

The logic works. But how does it perform on a million-row table?

Performance Deep Dive: The GIN Index Imperative

Without proper indexing, the check_document_access function will be executed for every single row in the documents table. This results in a Sequential Scan, the death of performance for large tables.

Let's analyze the query plan:

-- Simulate being Tony Stark
SET app.current_user_id = '20000000-0000-0000-0000-000000000001';
SET app.current_user_attributes = '{"department": "rd"}';

EXPLAIN ANALYZE SELECT title FROM documents;

On a large table, you would see something like this:

                                                     QUERY PLAN
--------------------------------------------------------------------------------------------------------------------
 Seq Scan on documents  (cost=0.00..15500.00 rows=333 width=36) (actual time=0.15..250.50 rows=2 loops=1)
   Filter: check_document_access(acls, organization_id)
   Rows Removed by Filter: 999998
 Planning Time: 0.1 ms
 Execution Time: 250.60 ms

The planner cannot see inside our PL/pgSQL function. It assumes it has to run the function for every row. The key to optimizing this is to use an index that supports the operators inside our function.

The most powerful index for jsonb is the GIN (Generalized Inverted Index). A GIN index creates an index entry for each key and value within the jsonb document, allowing for extremely fast lookups.

Let's create a GIN index on the acls column:

CREATE INDEX idx_documents_acls_gin ON documents USING GIN (acls);

This index is a game-changer, but only if our policy can use it. The original check_document_access function is a black box to the planner. We need to refactor our policy to expose indexable operators directly in the USING clause.

-- First, drop the old policy
DROP POLICY select_documents ON documents;

-- Create a new, index-aware policy
CREATE POLICY select_documents_performant
    ON documents
    FOR SELECT
    USING (
        -- Broad, non-negotiable filter (uses standard b-tree index on organization_id)
        organization_id = (SELECT u.organization_id FROM users u WHERE u.id = current_setting('app.current_user_id', true)::uuid)
        AND (
            -- Rule 2a: Public document
            acls = '{}'::jsonb
            OR
            -- Rule 2b: Role-based access (INDEXABLE BY GIN)
            -- The @> operator checks if the left JSONB contains the right JSONB.
            -- We construct a JSONB path to check if the user's ID exists in either role array.
            acls @> jsonb_build_object('roles', jsonb_build_object('editor', jsonb_build_array(current_setting('app.current_user_id', true))))
            OR
            acls @> jsonb_build_object('roles', jsonb_build_object('viewer', jsonb_build_array(current_setting('app.current_user_id', true))))
            OR
            -- Rule 2c: Attribute-based access (INDEXABLE BY GIN)
            acls @> jsonb_build_object('attributes', jsonb_build_object('requires_department', (current_setting('app.current_user_attributes', true)::jsonb)->>'department'))
        )
    );

Why is this faster?

* @> (Contains Operator): This is the key. The GIN index is specifically designed to accelerate @>, ?, ?|, and ?& operators. Our query now uses acls @> '{"roles": {"editor": ["user-id"]}}'. PostgreSQL can use the GIN index to instantly find all documents that contain this key-value structure, instead of scanning the whole table.

* Planner Visibility: All logic is now in the USING clause, visible to the query planner. It can see the @> operators and match them to the available GIN index.

Let's re-run the EXPLAIN ANALYZE:

                                                          QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on documents  (cost=24.00..36.00 rows=10 width=36) (actual time=0.05..0.06 rows=2 loops=1)
   Recheck Cond: ( ... complex condition ... )
   ->  BitmapOr  (cost=24.00..24.00 rows=10 width=0) (actual time=0.04..0.04 loops=1)
         ->  Bitmap Index Scan on idx_documents_acls_gin  (cost=0.00..8.00 rows=1 width=0) (Index Cond: (acls @> ...))
         ->  Bitmap Index Scan on idx_documents_acls_gin  (cost=0.00..8.00 rows=1 width=0) (Index Cond: (acls @> ...))
         ->  Bitmap Index Scan on idx_documents_acls_gin  (cost=0.00..8.00 rows=1 width=0) (Index Cond: (acls @> ...))
 Planning Time: 0.3 ms
 Execution Time: 0.1 ms

The difference is staggering. Instead of a Seq Scan, we now have a Bitmap Index Scan on our GIN index. The execution time drops from milliseconds (or seconds on a huge table) to microseconds. This is the difference between a production-ready system and one that will collapse under load.

Advanced Edge Cases and Production Patterns

1. `INSERT` and `UPDATE` with `WITH CHECK`

RLS isn't just for reading data. You need to protect writes. A FOR UPDATE policy can reuse the USING clause, but you also need a WITH CHECK clause to prevent users from creating or modifying data into a state they cannot see.

For example, we want to prevent a user from changing a document's organization_id to one they don't belong to.

CREATE POLICY update_documents
    ON documents
    FOR UPDATE
    USING (check_document_access(acls, organization_id)) -- Can you see the row to update it?
    WITH CHECK (organization_id = (SELECT u.organization_id FROM users u WHERE u.id = current_setting('app.current_user_id', true)::uuid)); -- Is the new state valid?

CREATE POLICY insert_documents
    ON documents
    FOR INSERT
    WITH CHECK (organization_id = (SELECT u.organization_id FROM users u WHERE u.id = current_setting('app.current_user_id', true)::uuid));

The WITH CHECK expression is evaluated against the proposed new row. If it returns false, the operation is rejected. This creates a powerful data integrity guarantee at the database level.

2. Superuser and Background Job Bypass

Sometimes, administrative tasks or background workers need to operate on the entire table, bypassing RLS. There are two primary mechanisms:

* The BYPASSRLS Attribute: You can grant this attribute to specific roles. ALTER ROLE my_service_role BYPASSRLS;. This role will now completely ignore all RLS policies. Use this with extreme caution, as it's an all-or-nothing switch.

* Disabling RLS: For a specific session or transaction, a superuser can run ALTER TABLE documents DISABLE ROW LEVEL SECURITY; and then re-enable it. This is often used in migration scripts.

A safer pattern for service accounts is to have them set a specific session variable that the policy explicitly checks for.

-- In the policy's USING clause
...
OR current_setting('app.is_superuser', true) = 'true'
...

This is more granular than BYPASSRLS as it can be controlled on a per-policy basis.

3. Performance Cliff: Overly Complex JSONB and `jsonb_path_ops`

The default GIN index (jsonb_ops) indexes every key and value. For very large or deeply nested JSONB documents, this can create a bloated index. If your access patterns are always based on specific paths (e.g., you only ever query by roles.editor or attributes.sensitivity), you can use the jsonb_path_ops operator class, which creates a more compact index.

-- Potentially smaller, faster index if queries are predictable
CREATE INDEX idx_documents_acls_gin_path_ops ON documents USING GIN (acls jsonb_path_ops);

However, jsonb_path_ops only supports the @> operator. It does not support existence operators like ?. You must benchmark both to see which is better for your specific workload.

4. Testing and Debugging Policies

Debugging RLS can be challenging because the logic is hidden. The best approach is to write isolated tests.

-- Example Test Case
BEGIN;
-- Set context for a specific user who should be denied access
SET LOCAL app.current_user_id = '20000000-0000-0000-0000-000000000003'; -- Happy Hogan
SET LOCAL app.current_user_attributes = '{"department": "security"}';

-- Assert that this user cannot see the confidential Mark V specs
-- This query should return 0 rows.
SELECT COUNT(*) FROM documents WHERE title = 'Mark V Armor Specs';

ROLLBACK;

Final Recommendation: When to Use This Pattern

Combining RLS and jsonb for ABAC is an exceptionally powerful pattern for applications that require:

* Strong Security Guarantees: Centralizing logic in the database prevents entire classes of application-level security bugs.

* Flexible and Evolvable Permission Models: The jsonb schema can be changed and extended without database migrations.

* High Performance for Read-Heavy Workloads: With proper GIN indexing, the performance of RLS-protected reads is nearly identical to a standard indexed query.

However, it's not a silver bullet. Consider the trade-offs:

* Increased Database Complexity: Authorization logic now lives in SQL, which may be less familiar to some developers. It requires careful testing and a disciplined approach to schema and policy management.

* Potential for Write-Side Complexity: While SELECT policies can be made highly performant, complex WITH CHECK clauses on INSERT/UPDATE can add overhead. The logic must be carefully designed to remain efficient.

For systems where data segregation and fine-grained access control are paramount, moving authorization logic into the database with PostgreSQL RLS and jsonb is a production-hardened architectural choice that pays significant dividends in security, scalability, and maintainability.