PostgreSQL RLS & JSONB for Dynamic Attribute-Based Access Control

The Architectural Impasse: When RBAC Fails

In most SaaS applications, Role-Based Access Control (RBAC) is the default starting point. We define roles like admin, editor, and viewer, assign them to users, and litter our application code with checks like if (user.hasRole('editor')). This works for a while. But as systems scale and business requirements become more nuanced, this model begins to fracture.

Consider a multi-tenant document management system. The requirements evolve from simple role checks to complex, data-dependent rules:

• A manager can see all documents created by their direct reports.

Implementing this logic in the application layer leads to a maintenance nightmare. Every data access query must be carefully constructed with complex WHERE clauses, which are easily forgotten or implemented inconsistently across different microservices. This decentralization of security logic is a significant source of bugs and security vulnerabilities.

This is the core problem that Attribute-Based Access Control (ABAC) solves. ABAC makes authorization decisions based on attributes of the user, the resource being accessed, and the environment. The ideal place to enforce these rules consistently and securely is at the data layer itself. This post details a production-grade pattern for implementing a dynamic ABAC system in PostgreSQL using Row-Level Security (RLS) and the JSONB data type.

Core Architecture: RLS, JWT Claims, and JSONB Attributes

Our architecture hinges on three key PostgreSQL features:

Setting the Security Context

RLS policies need to know who is asking for the data. A robust way to achieve this is by passing user attributes from a validated JWT into the PostgreSQL session immediately after a connection is established. We can use a reserved configuration parameter for this.

Let's say our application authenticates a user and generates a JWT with the following claims:

{
  "sub": "user-a1b2c3d4",
  "tenant_id": "t-xyz-789",
  "department": "engineering",
  "clearance_level": 4,
  "projects": ["phoenix", "pegasus"],
  "is_manager": true
}

When our application backend receives a request, it validates the JWT. Then, upon acquiring a database connection, it executes a SET command to store these claims as a JSON string in a session variable. Using a namespaced variable like request.user_attributes is a good practice to avoid conflicts.

-- This command is executed by the application backend for every request.
SET LOCAL request.user_attributes = '{
  "sub": "user-a1b2c3d4",
  "tenant_id": "t-xyz-789",
  "department": "engineering",
  "clearance_level": 4,
  "projects": ["phoenix", "pegasus"],
  "is_manager": true
}';

SET LOCAL ensures this variable is only set for the duration of the current transaction, which is critical when using connection pooling.

Schema Design for ABAC

Now, let's define a simplified schema. We'll have a documents table where each document has a set of access_rules defined in a JSONB column.

CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    owner_id TEXT NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    -- The core of our ABAC implementation
    access_rules JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Sample data
INSERT INTO documents (tenant_id, owner_id, title, access_rules)
VALUES
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'user-a1b2c3d4', 'Project Phoenix Q3 Roadmap',
     '{"department": "engineering", "clearance_level": 3, "projects": ["phoenix"]}'),
    ('a0eebc99-9c0b-4ef8-bb6d-6bb9bd380a11', 'user-d3c2b1a0', 'Project Pegasus Security Audit',
     '{"department": "security", "clearance_level": 5, "projects": ["pegasus"]}'),
    ('f1b4c3a2-1a2b-3c4d-5e6f-7a8b9c0d1e2f', 'user-z9y8x7w6', 'Marketing Campaign Plan',
     '{"department": "marketing", "clearance_level": 2}');

Implementing Dynamic RLS Policies with PL/pgSQL

With the context and schema in place, we can now create the RLS policies. Simple policies can be written inline, but for complex ABAC logic, encapsulating the rules in a PL/pgSQL function is far more maintainable and testable.

First, let's create a helper function to safely retrieve and parse the user attributes from the session.

CREATE OR REPLACE FUNCTION get_current_user_attributes() RETURNS JSONB AS $$
DECLARE
    attributes_str TEXT;
    attributes_jsonb JSONB;
BEGIN
    -- current_setting can throw an error if the setting is not found.
    -- The 'true' flag makes it return NULL instead.
    attributes_str := current_setting('request.user_attributes', true);

    IF attributes_str IS NULL OR attributes_str = '' THEN
        -- Return a default empty object if no attributes are set.
        -- This ensures guest users or unauthenticated sessions fail checks gracefully.
        RETURN '{}'::jsonb;
    END IF;

    -- Attempt to parse the JSON string.
    BEGIN
        attributes_jsonb := attributes_str::jsonb;
    EXCEPTION WHEN others THEN
        -- Log the error and return an empty object if parsing fails.
        RAISE WARNING 'Invalid JSON in request.user_attributes: %', attributes_str;
        RETURN '{}'::jsonb;
    END;

    RETURN attributes_jsonb;
END;
$$ LANGUAGE plpgsql STABLE;

This function is marked STABLE because it doesn't modify the database and returns the same result for the same inputs within a single statement, which can help the query planner.

The Core ABAC Logic Function

Next, we'll create the main function that compares the user's attributes against a document's access rules. This function will contain our business-specific authorization logic.

CREATE OR REPLACE FUNCTION check_document_access(rules JSONB)
RETURNS BOOLEAN AS $$
DECLARE
    user_attrs JSONB := get_current_user_attributes();
    user_tenant_id TEXT := user_attrs->>'tenant_id';
    doc_tenant_id UUID;
BEGIN
    -- This is a placeholder for the actual document's tenant_id.
    -- In a real policy, we would pass this in or access it directly.
    -- For this function's scope, we'll assume a tenant check happens elsewhere.

    -- Rule 1: User's department must match the document's required department.
    IF rules ? 'department' AND (user_attrs->>'department') IS DISTINCT FROM (rules->>'department') THEN
        RETURN FALSE;
    END IF;

    -- Rule 2: User's clearance level must be >= the document's required level.
    IF rules ? 'clearance_level' AND (user_attrs->>'clearance_level')::int < (rules->>'clearance_level')::int THEN
        RETURN FALSE;
    END IF;

    -- Rule 3: User must have at least one project in common with the document's required projects.
    -- The `?|` operator checks if the left JSONB array contains any elements from the right array.
    IF rules ? 'projects' AND NOT (user_attrs->'projects' ?| (rules->'projects'->>0)::text[]) THEN
        RETURN FALSE;
    END IF;

    -- If all checks pass, grant access.
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql STABLE;

Activating the RLS Policy

Finally, we enable RLS on the documents table and create a policy that uses our function. The policy will enforce a hard tenant isolation rule first, followed by our dynamic ABAC checks.

-- First, enable Row-Level Security on the table.
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;

-- Create the policy for SELECT, UPDATE, DELETE operations.
CREATE POLICY documents_abac_policy ON documents
FOR ALL
USING (
    -- Rule 1: Hard Tenant Isolation (critical for multi-tenant security)
    (tenant_id::text = (get_current_user_attributes()->>'tenant_id'))
    AND
    -- Rule 2: Dynamic Attribute-Based Checks
    check_document_access(access_rules)
);

Now, if a user with the JWT claims from our example performs a SELECT * FROM documents;, the database will transparently apply the policy. It will first filter by tenant_id and then, for each remaining row, execute check_document_access, effectively hiding any documents they are not authorized to see.

The Performance Trap: RLS and Full Table Scans

We have a working ABAC system. But on a table with millions of rows, performance will be abysmal. The RLS policy function check_document_access is, by default, a black box to the query planner. For every SELECT query, PostgreSQL will likely perform a sequential scan on the documents table, applying the policy filter to every single row. This is a classic RLS performance pitfall.

Let's analyze the query plan:

-- Assume we are in a session where request.user_attributes is set.
EXPLAIN ANALYZE SELECT * FROM documents;

Hypothetical Output (Before Optimization):

                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on documents  (cost=0.00..65432.10 rows=12345 width=1024) (actual time=0.123..5432.109 rows=2 loops=1)
   Filter: (check_document_access(access_rules) AND (tenant_id = '...'::uuid))
   Rows Removed by Filter: 999998
 Planning Time: 0.150 ms
 Execution Time: 5432.200 ms

The Seq Scan and high execution time confirm our fears. The database is reading the entire table from disk.

The Solution: GIN Indexing on JSONB

To optimize this, we need to give the query planner a way to efficiently find rows that match our JSONB criteria. The ideal index type for this is a GIN (Generalized Inverted Index). A GIN index creates an index entry for each key-value pair (or array element) within the JSONB document, allowing for extremely fast lookups.

Let's create a GIN index on our access_rules column:

CREATE INDEX idx_documents_access_rules_gin ON documents USING GIN (access_rules);

However, simply creating the index is not enough. The query planner cannot use this index because our access logic is hidden inside the check_document_access function. We must refactor our RLS policy to use JSONB operators directly, allowing the planner to see the access patterns and utilize the GIN index.

Refactoring the RLS Policy for Index-Awareness

Let's rewrite the policy to be more explicit.

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

-- Create a new, performance-optimized policy
CREATE POLICY documents_abac_policy_optimized ON documents
FOR ALL
USING (
    -- Hard tenant isolation remains the same. A b-tree index on `tenant_id` is essential.
    (tenant_id::text = (get_current_user_attributes()->>'tenant_id'))
    AND
    -- We now express the logic using indexable JSONB operators.
    (
        -- Rule 1: Department check. The `<@` operator checks if the left JSONB is contained within the right.
        -- We construct a JSONB object on the fly to check for containment.
        (jsonb_build_object('department', get_current_user_attributes()->>'department') <@ access_rules)
        AND
        -- Rule 2: Clearance level. This is tricky to make indexable. A function is often still needed.
        -- We will address this with a partial index strategy later.
        ((access_rules->>'clearance_level')::int <= (get_current_user_attributes()->>'clearance_level')::int)
        AND
        -- Rule 3: Project check. The `@>` (contains) operator is highly efficient with GIN.
        (access_rules->'projects' IS NULL OR access_rules @> jsonb_build_object('projects', get_current_user_attributes()->'projects'))
    )
);

This refactored policy is more verbose but exposes the access logic to the planner. The planner can now use the GIN index for the department and projects checks. The numeric comparison on clearance_level is still problematic for a standard GIN index.

Hypothetical Output (After GIN Index and Policy Refactor):

                                                    QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on documents  (cost=123.45..4567.89 rows=15) (actual time=0.543..1.234 rows=2 loops=1)
   Recheck Cond: (tenant_id = '...'::uuid)
   Filter: ((access_rules->>'clearance_level')::int <= ...)
   ->  Bitmap Index Scan on idx_documents_access_rules_gin  (cost=0.00..123.40 rows=50) (actual time=0.500..0.500 rows=10 loops=1)
         Index Cond: (access_rules @> '{"department": "engineering"}'::jsonb)
 Planning Time: 0.250 ms
 Execution Time: 1.350 ms

The query plan has dramatically changed. It now uses a Bitmap Index Scan on our GIN index to quickly find candidate rows based on the department, and then applies the remaining filters. The execution time has dropped from over 5 seconds to just over 1 millisecond—a staggering improvement.

Advanced Scenarios and Edge Cases

Real-world systems have more complexity. Here’s how to handle common advanced requirements.

Handling Hierarchical Permissions

What if a manager needs to see documents owned by their direct reports? This requires joining against another table within the RLS policy.

Let's assume a user_hierarchy table:

CREATE TABLE user_hierarchy (
    user_id TEXT PRIMARY KEY,
    manager_id TEXT
);

We can add a sub-query to our RLS policy to check for this condition.

-- Add this to the USING clause with an OR condition.
-- ... OR (owner_id IN (SELECT user_id FROM user_hierarchy WHERE manager_id = (get_current_user_attributes()->>'sub')))

Be extremely cautious with sub-queries in RLS policies. They execute for every row and can become a new performance bottleneck. Ensure the sub-query is highly optimized (e.g., user_hierarchy has an index on manager_id).

The Importance of `WITH CHECK` Policies

A USING clause applies to rows that are returned. For INSERT and UPDATE, we need a WITH CHECK clause to prevent users from creating or modifying data in a way that would make it invisible to them.

For example, a user could try to UPDATE a document and set its clearance_level to a value higher than their own. The USING clause wouldn't prevent this, but the row would immediately disappear from their view. A WITH CHECK policy prevents this operation from succeeding in the first place.

-- Create a separate policy for INSERT/UPDATE with a CHECK clause.
CREATE POLICY documents_abac_writer_policy ON documents
FOR INSERT, UPDATE
WITH CHECK (
    (tenant_id::text = (get_current_user_attributes()->>'tenant_id'))
    AND
    check_document_access(access_rules)
);

It's often cleaner to use the same logic for both, which is why our initial FOR ALL policy included it implicitly. However, sometimes write rules are stricter than read rules, justifying separate policies.

Bypassing RLS: The `BYPASSRLS` Attribute

Sometimes, you need to bypass RLS entirely. This is common for internal processes, database migrations, or superuser maintenance tasks. You can grant this powerful privilege to specific roles.

ALTER ROLE migration_user BYPASSRLS;

Use this attribute with extreme care. Any user with this role will see all data in the table, regardless of any active policies. It's a common practice to have a dedicated, highly restricted role for such operations.

RLS and Views: The `security_barrier` Trap

When you create a view on a table with RLS enabled, a clever query planner might try to push down predicates from the outer query into the view's definition. This can sometimes leak information by altering the conditions under which the RLS policy is evaluated. To prevent this, use the security_barrier option on your views.

CREATE VIEW engineering_documents WITH (security_barrier = true) AS
SELECT id, title, created_at
FROM documents
WHERE access_rules @> '{"department": "engineering"}';

A security_barrier view acts as a firewall. The RLS policy on the underlying documents table is applied first, and only the rows that pass the policy are then evaluated against the view's WHERE clause.

Production Patterns and Architectural Considerations

The Connection Pooling Conundrum

Our use of SET LOCAL request.user_attributes works perfectly with session-based connection poolers (like the one built into the Go pq driver) or when a process holds a connection. However, it can fail with transaction-level poolers like PgBouncer in its default configuration.

PgBouncer can return a different session from its pool for each new transaction, meaning our SET LOCAL variable will be lost. There are two primary solutions:

For most modern applications, configuring the pooler for session-based pooling is the more pragmatic approach when RLS context is required.

Testing and Debugging RLS

Testing RLS is non-trivial. Your test suite must be able to impersonate different users.

A common pattern in integration tests is to create roles for test users and use SET ROLE within a transaction to execute queries as that user.

BEGIN;
-- Set the attributes for a specific user persona
SET LOCAL request.user_attributes = '{"tenant_id": "t-123", ...}';

-- Run assertions
ASSERT (SELECT count(*) FROM documents) = 5; -- Assert this user can see 5 documents

-- Switch to another persona
SET LOCAL request.user_attributes = '{"tenant_id": "t-456", "clearance_level": 1, ...}';
ASSERT (SELECT count(*) FROM documents) = 0; -- Assert this restricted user sees nothing

ROLLBACK;

Using EXPLAIN (ANALYZE, VERBOSE) is your best friend for debugging. It will show you exactly which policies are being applied and how they are affecting the query plan.

Conclusion

Implementing Attribute-Based Access Control directly in PostgreSQL by combining Row-Level Security and JSONB is a powerful, production-ready pattern for senior engineers. It centralizes complex authorization logic at the data layer, ensuring consistency, security, and maintainability that is difficult to achieve at the application level.

While the initial setup is more complex than simple application-level checks, the long-term benefits are immense. However, success hinges on a deep understanding of the performance implications. Naive RLS policies that hide logic within functions will inevitably lead to full table scans. By carefully crafting policies to use indexable operators and creating appropriate GIN indexes on JSONB attribute columns, you can build a system that is both highly secure and exceptionally performant.