PostgreSQL RLS with JSONB for Dynamic Attribute-Based Access Control

The Inevitable Limits of Static RBAC

In any sufficiently complex application, especially multi-tenant SaaS platforms, the authorization model eventually outgrows simple Role-Based Access Control (RBAC). The initial users, roles, and user_roles tables that served you well in the beginning start to fray at the edges. You're faced with requirements that don't map cleanly to a static role:

* "A manager can only approve expenses up to $5,000, but a director can approve up to $20,000."

* "A support_agent from the EU can only view tickets from EU customers to comply with GDPR."

* "An editor can only modify a document if it's in a draft state and belongs to a project they are explicitly assigned to."

Implementing this logic in the application layer leads to a scattered and brittle authorization system. Queries become decorated with an ever-growing list of WHERE clauses, developers forget to apply checks on new endpoints, and ensuring consistency across multiple microservices becomes a Herculean task. The authorization logic becomes divorced from the data it protects.

This is where Attribute-Based Access Control (ABAC) provides a more powerful paradigm. Access is not granted based on a static role, but by evaluating policies against the attributes of the user, the resource being accessed, and the environment.

This article details a robust pattern for implementing a dynamic ABAC system directly in PostgreSQL. We will leverage two of its most powerful features: Row-Level Security (RLS) to enforce access control at the database engine level, and the JSONB data type to store a flexible set of user and object attributes. By pushing authorization to the data layer, we create a single, authoritative source of truth that is atomic, language-agnostic, and incredibly performant when indexed correctly.

Core Architecture: RLS Policies Fueled by JSONB Attributes

The core concept is to centralize user permissions into a single JSONB column. This avoids the endless JOINs and schema migrations associated with traditional permission modeling. We then write RLS policies that consult this JSONB blob to make access decisions.

1. Schema Design

Let's model a simple document management system for a multi-tenant organization.

-- A tenant in our system
CREATE TABLE organizations ( 
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT NOT NULL
);

-- Users belong to an organization and have a JSONB attribute store
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    email TEXT NOT NULL UNIQUE,
    attributes JSONB NOT NULL DEFAULT '{}'::jsonb
);

-- The resources we want to protect
CREATE TABLE documents (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    project_id UUID NOT NULL,
    title TEXT NOT NULL,
    content TEXT,
    status TEXT NOT NULL DEFAULT 'draft', -- e.g., 'draft', 'in_review', 'published'
    region TEXT NOT NULL -- e.g., 'EU', 'US-WEST', 'APAC'
);

-- Enable RLS on the table we want to protect
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
-- By default, access is denied. We must create policies to allow access.
ALTER TABLE documents FORCE ROW LEVEL SECURITY;

The key is the users.attributes column. A user's attributes might look like this:

{
  "clearance_level": 3,
  "regions": ["EU", "US-WEST"],
  "projects": {
    "a1b2c3d4-e5f6-7890-1234-567890abcdef": "editor",
    "b2c3d4e5-f6a7-8901-2345-67890abcdef0": "viewer"
  },
  "can_publish": false
}

This structure is infinitely extensible without schema changes. Need to add department-based access? Simply add a "department": "engineering" key-value pair to the JSONB.

2. Establishing User Context in a Session

RLS policies need to know who is making the query. The most robust way to handle this is by setting a session-level configuration variable when a user authenticates and a connection is established from the pool.

-- From your application's connection logic:
-- SET LOCAL is transaction-scoped, which is ideal.
SET LOCAL "app.current_user_id" = 'user-uuid-goes-here';

Now, within a transaction, we can create helper functions to securely access this context.

-- Helper to get the current user's ID. Returns NULL if not set.
CREATE OR REPLACE FUNCTION auth.current_user_id() RETURNS UUID AS $$
BEGIN
    RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
    WHEN OTHERS THEN
        RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;

-- A more powerful helper to get the entire attributes blob for the current user.
-- This function is the cornerstone of our policies.
CREATE OR REPLACE FUNCTION auth.current_user_attributes() RETURNS JSONB AS $$
DECLARE
    attr_blob JSONB;
BEGIN
    SELECT attributes INTO attr_blob
    FROM public.users
    WHERE id = auth.current_user_id();
    RETURN attr_blob;
END;
$$ LANGUAGE plpgsql STABLE;

Note on STABLE: We mark these functions as STABLE because their output is consistent within a single statement, which allows the query planner to optimize more effectively.

Advanced Policy Authoring with JSONB Operators

Now we can combine these pieces to write expressive RLS policies. An RLS policy is essentially a USING clause that PostgreSQL appends to every query against the table.

Scenario 1: Basic Tenant Isolation

First, a foundational policy: users can only see documents within their own organization. This is a classic multi-tenancy check.

CREATE POLICY tenant_isolation ON documents
FOR SELECT
USING (organization_id = (
    SELECT organization_id FROM users WHERE id = auth.current_user_id()
));

While this works, it requires a sub-query for every RLS check. We can optimize this by setting the organization ID in the session as well, but for now, let's focus on the attributes.

Scenario 2: Region-Based Access

Requirement: A user can only see documents from the regions they are assigned to in their attributes.

-- Let's drop the previous one to add a more complex one.
DROP POLICY IF EXISTS tenant_isolation ON documents;

CREATE POLICY select_documents_policy ON documents
FOR SELECT
USING (
    -- Standard tenant isolation
    organization_id = (SELECT organization_id FROM users WHERE id = auth.current_user_id())
    
    -- AND the document's region must be in the user's allowed regions array
    AND region = ANY(SELECT jsonb_array_elements_text(auth.current_user_attributes() -> 'regions'))
);

Here, we use -> 'regions' to extract the regions array from the JSONB blob. Then jsonb_array_elements_text unnests this JSONB array into a set of text rows, which we can use with the ANY operator. This is powerful but can be inefficient. A better way uses the JSONB containment operator @>.

Let's refine our helper and policy for performance. We can't use @> directly on the text region column. Instead, we can construct a JSONB value to check against.

-- More performant version of the region check
CREATE OR REPLACE POLICY select_documents_policy ON documents
FOR SELECT
USING (
    organization_id = (SELECT organization_id FROM users WHERE id = auth.current_user_id())
    AND (auth.current_user_attributes() -> 'regions') @> to_jsonb(region)
);

This is much cleaner and allows for GIN index usage, which we'll cover in the performance section.

Scenario 3: Role-Based Access within a Project

Requirement: A user can see any document in a project where they are an editor or viewer, but can only update a document if they are an editor and the document's status is draft.

This requires two separate policies: one for SELECT (USING) and one for UPDATE (USING for row visibility and WITH CHECK for the update condition).

-- Drop the previous policy to build a new composite one
DROP POLICY IF EXISTS select_documents_policy ON documents;

CREATE POLICY view_project_documents ON documents
FOR SELECT
USING (
    -- User must have the project_id as a key in their 'projects' attribute map
    auth.current_user_attributes() -> 'projects' ? project_id::text
);

-- Policy for updates. This is far more restrictive.
CREATE POLICY update_draft_documents_as_editor ON documents
FOR UPDATE
USING (
    -- The user must be able to see the row in the first place (implicitly covered by SELECT policy)
    true
)
WITH CHECK (
    -- The user's role for this project must be 'editor'
    auth.current_user_attributes() -> 'projects' ->> project_id::text = 'editor'
    
    -- AND the document status must be 'draft'
    AND status = 'draft'
);

Let's break down the WITH CHECK policy:

This demonstrates the power of ABAC: the policy combines attributes of the user (role in project) and attributes of the resource (status).

Performance Deep Dive: Making ABAC Production-Ready

Naively implemented RLS can destroy your database's performance. Every query against a protected table is rewritten, and if your policy contains slow functions or subqueries, every single SELECT, INSERT, UPDATE, or DELETE pays that price. Here’s how to make it fast.

1. Indexing for JSONB Policies

The single most important optimization is to create a GIN index on your attributes column.

CREATE INDEX idx_users_attributes_gin ON users USING GIN (attributes jsonb_path_ops);

Why GIN? A standard B-tree index can't efficiently index the contents of a JSONB document. A GIN (Generalized Inverted Index) index creates an index entry for each key and value within the JSONB, allowing for extremely fast lookups using operators like:

* @> (contains): attributes @> '{"department": "engineering"}'

* ? (key exists): attributes ? 'can_publish'

* ?| (any key exists): attributes ?| array['is_admin', 'is_auditor']

* ?& (all keys exist): attributes ?& array['department', 'region']

The jsonb_path_ops operator class is optimized for the @> operator, while the default jsonb_ops is better for ?, ?|, and ?&. Choose based on your most common policy patterns.

Let's analyze the impact. Consider our policy auth.current_user_attributes() -> 'projects' ? project_id::text. The function auth.current_user_attributes() involves a lookup on the users table:

SELECT attributes FROM public.users WHERE id = auth.current_user_id()

This lookup on users.id is already fast because it's a primary key. However, if our policies needed to scan the users table to find users with certain attributes, the GIN index would be critical.

Example Benchmark:

Imagine a policy that allows a manager to see all documents from users in their department. USING ( (SELECT attributes->>'department' FROM users WHERE users.id = documents.author_id) = (auth.current_user_attributes()->>'department') ). This is a terrible policy because it runs a correlated subquery. A better approach is to denormalize the department onto the documents table. But if you must do such a lookup, a GIN index on (attributes->'department') would be essential.

2. The `SECURITY DEFINER` vs. `SECURITY INVOKER` Minefield

Our auth.current_user_attributes() function has a subtle, critical flaw. If we were to enable RLS on the users table itself (e.g., to prevent users from seeing each other), that policy would apply inside our function! This creates a circular dependency: to check the policy on documents, we call a function that tries to read from users, which triggers the RLS policy on users, which might itself call a function... a recipe for disaster.

The solution is SECURITY DEFINER.

CREATE OR REPLACE FUNCTION auth.current_user_attributes() RETURNS JSONB AS $$
-- ... function body as before ...
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

A SECURITY DEFINER function executes with the permissions of the user who defined the function, not the user who invokes it. This allows the function to bypass the RLS policies of the calling user, enabling it to read the necessary row from public.users to fetch the attributes.

CRITICAL SECURITY WARNING: SECURITY DEFINER is a powerful tool that can be easily misused. You must ensure that such functions are completely secure and do not leak data. For instance, never make the user ID an argument to the function. Always source it from a secure context like current_setting. Also, it's wise to set a secure search_path for the function to prevent hijacking:

ALTER FUNCTION auth.current_user_attributes() SET search_path = public;

This prevents the function from accidentally calling a malicious user-defined function in another schema.

3. Analyzing Policy Performance with `EXPLAIN ANALYZE`

If a query is slow, your first step should always be EXPLAIN ANALYZE. When RLS is active, you will see the policy's conditions injected directly into the query plan.

-- In a session where app.current_user_id is set
EXPLAIN ANALYZE SELECT * FROM documents WHERE project_id = '...';

Output without optimization: You might see a slow Seq Scan on users inside the policy function call for every single row being evaluated from documents.

Output with optimization: You should see an Index Scan on users (using its primary key) happening just once, and the resulting attribute value being used in the filter on documents.

Pay close attention to the cost and timing of nodes related to your RLS functions. If they are being called per-row and are slow, that's your bottleneck.

Edge Cases and Production Patterns

1. Superuser / Admin Access

How do you bypass RLS for administrative tasks, internal tools, or support staff? There are two primary methods:

* The BYPASSRLS Attribute: You can grant this attribute to a specific database role. Any user connected with that role will bypass all RLS policies. This is a big hammer but is often the cleanest solution for trusted internal roles.

    ALTER ROLE my_admin_role BYPASSRLS;

* Policy-based Bypass: Modify your policies to include a bypass condition based on a user attribute.

    CREATE POLICY my_policy ON my_table FOR SELECT USING (
        (auth.current_user_attributes() ->> 'is_admin')::boolean
        OR
        ( -- normal policy logic here -- )
    );

2. Debugging Policies: The Wall of Silence

One of the most frustrating aspects of RLS is that when a row is filtered, it's simply gone. There's no error, no message. It's just an empty result set. This makes debugging a nightmare.

Strategies for Debugging:

    CREATE VIEW admin_views.all_documents AS
    SELECT * FROM public.documents;
    
    -- Grant access only to the admin role
    GRANT SELECT ON admin_views.all_documents TO my_admin_role;

3. Managing Policies in Migrations

RLS policies are DDL and should be managed with your database migration tool (e.g., Flyway, Sqitch, Alembic). Always write idempotent scripts.

-- A sample migration script
BEGIN;

-- Drop the policy if it exists to ensure a clean state
DROP POLICY IF EXISTS view_project_documents ON public.documents;

-- Recreate the policy with the new/updated logic
CREATE POLICY view_project_documents ON public.documents
FOR SELECT
USING (
    auth.current_user_attributes() -> 'projects' ? project_id::text
);

COMMIT;

This ensures that your migrations can be run repeatedly without errors.

Conclusion: A Centralized, Data-Centric Authorization Model

By combining PostgreSQL's Row-Level Security with the JSONB data type, we can architect a sophisticated Attribute-Based Access Control system that lives alongside the data it protects. This pattern offers significant advantages over application-layer enforcement:

* Centralized Logic: The policies are defined in one place, not scattered across a dozen microservices.

* Language Agnostic: The rules are enforced no matter what language or tool connects to the database.

* Atomicity: Data mutations and permission checks occur within the same atomic transaction.

* Performance: With proper indexing and function design, the overhead of RLS can be negligible.

This approach is not a silver bullet. It requires a deep understanding of PostgreSQL, careful performance tuning, and a robust strategy for debugging and administration. But for applications with complex, dynamic authorization requirements, pushing the logic down into the database provides a level of security, consistency, and power that is difficult to achieve otherwise.