PostgreSQL RLS & JSONB for Dynamic Attribute-Based Access Control
Beyond RBAC: True Attribute-Based Control in the Data Layer
Traditional Role-Based Access Control (RBAC) is often the first line of defense for application security. However, for complex, multi-tenant SaaS platforms or systems with granular data ownership rules, RBAC's static nature becomes a significant bottleneck. The logic quickly devolves into a proliferation of roles or, worse, gets pushed into the application layer, leading to inconsistent enforcement, code duplication, and security vulnerabilities.
The challenge we're tackling is enforcing rules that depend on the data itself. Consider these requirements:
* A user can edit a document only if their user_id is present in the document's metadata.editors JSON array.
* A user can view an invoice only if their organization's subscription_status (stored on the user's record) is 'active' and the invoice's region matches the user's assigned_region.
* A manager can delete a project only if the project's status is 'archived' and the manager belongs to the admins group for that specific project's tenant_id.
Implementing this in the application layer is brittle. Every query to the database must be carefully constructed with the correct WHERE clauses, a process prone to human error. A far more robust solution is to enforce these rules directly within the database. This is where PostgreSQL's powerful combination of Row-Level Security (RLS) and its first-class JSONB support shines.
This article is a deep dive into the architecture and implementation of such a system. We will not cover the basics of CREATE POLICY. Instead, we will focus on production-ready patterns, performance optimization for complex policies, and navigating the sharp edges you'll encounter when building a dynamic, attribute-based access control (ABAC) system at scale.
The Foundational Schema: Documents and Users
Let's model a simple document management system. Our core tables are users and documents. The key is that we'll use JSONB columns to store dynamic attributes that our security policies will depend on.
-- For UUID generation
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- Users table with JSONB for dynamic attributes
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email TEXT NOT NULL UNIQUE,
attributes JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Documents table with metadata and permissions in JSONB
CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID NOT NULL,
title TEXT NOT NULL,
content TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb, -- e.g., {"status": "draft", "project_id": "..."}
permissions JSONB NOT NULL DEFAULT '{}'::jsonb, -- e.g., {"owners": ["user_id_1"], "editors": ["user_id_2"], "viewers": ["user_id_3"]}
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- Enable RLS on the documents table
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;The Security Context: Securely Passing User State to Policies
RLS policies need to know who is making the request. A common anti-pattern is to create policy functions that take the user ID as an argument. This is insecure and couples your query logic to your security logic.
The robust, standard pattern is to use transaction-scoped configuration parameters. We can set a custom parameter at the beginning of each transaction, which is then securely accessible by any RLS policy triggered within that transaction.
-- In your application's database connection logic (e.g., after acquiring a connection from the pool):
BEGIN;
-- Set the current user's ID. This setting is local to the current transaction.
-- Using `local` ensures it resets at the end of the transaction.
SET LOCAL app.current_user_id = 'the-uuid-of-the-logged-in-user';
-- ... your application queries go here ...
SELECT * FROM documents WHERE title ILIKE '%report%';
COMMIT;
-- The 'app.current_user_id' setting is now gone.This approach has several advantages:
We can access this value within our policies using current_setting('app.current_user_id', true). The second argument, true, makes the function return NULL if the setting is not found, preventing an error and allowing us to handle anonymous access gracefully.
Implementing Complex ABAC Policies with JSONB
Now, let's build policies that leverage this context and our JSONB columns.
First, we need a dedicated, non-privileged role for our application to connect as.
CREATE ROLE app_user LOGIN PASSWORD 'your_secure_password';
GRANT SELECT, INSERT, UPDATE, DELETE ON documents TO app_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;Policy 1: Basic Ownership and Role-based Access
This policy allows access based on whether the user's ID appears in the owners, editors, or viewers arrays within the permissions JSONB column.
CREATE OR REPLACE FUNCTION get_current_user_id() RETURNS UUID AS $$
BEGIN
-- A helper function to cast the setting to UUID and handle NULLs.
RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
-- Handle cases where the setting is not a valid UUID.
WHEN invalid_text_representation THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;
CREATE POLICY select_documents ON documents
FOR SELECT
TO app_user
USING (
-- The contains operator (@>) checks if the left JSONB value contains the right one.
-- We construct a JSONB array with the user's ID to check for its presence.
permissions->'viewers' @> jsonb_build_array(get_current_user_id())::jsonb OR
permissions->'editors' @> jsonb_build_array(get_current_user_id())::jsonb OR
permissions->'owners' @> jsonb_build_array(get_current_user_id())::jsonb
);
CREATE POLICY update_documents ON documents
FOR UPDATE
TO app_user
USING (
-- For updates, we require a higher privilege level.
permissions->'editors' @> jsonb_build_array(get_current_user_id())::jsonb OR
permissions->'owners' @> jsonb_build_array(get_current_user_id())::jsonb
)
WITH CHECK (
-- The WITH CHECK clause is crucial. It ensures that any updated row *still* meets the policy conditions.
-- This prevents a user from editing a document to remove their own access.
permissions->'editors' @> jsonb_build_array(get_current_user_id())::jsonb OR
permissions->'owners' @> jsonb_build_array(get_current_user_id())::jsonb
);
CREATE POLICY delete_documents ON documents
FOR DELETE
TO app_user
USING (
-- Only owners can delete.
permissions->'owners' @> jsonb_build_array(get_current_user_id())::jsonb
);Performance Implications and GIN Indexing
The @> (contains) operator is powerful, but without proper indexing, it will lead to sequential scans over your documents table for every query. This is a performance disaster waiting to happen.
The solution is a GIN (Generalized Inverted Index) index. For our use case, the default jsonb_ops operator class is sufficient.
-- Create a GIN index on the entire permissions column.
CREATE INDEX idx_documents_permissions_gin ON documents USING GIN (permissions);Let's analyze the difference. Imagine a table with 1 million documents.
Query without index:
EXPLAIN ANALYZE SELECT count(*) FROM documents WHERE permissions->'viewers' @> '["some-user-uuid"]'::jsonb;Result (Simplified):
Parallel Seq Scan on documents (cost=0.00..45000.00 rows=4167 width=8) (actual time=50.00..350.00 ms rows=10 loops=2)
Filter: ((permissions -> 'viewers'::text) @> '["some-user-uuid"]'::jsonb)This is a full table scan. The execution time will grow linearly with the table size.
Query with GIN index:
Result (Simplified):
Bitmap Heap Scan on documents (cost=100.00..4000.00 rows=10000 width=8) (actual time=0.5..1.5 ms rows=10 loops=1)
Recheck Cond: ((permissions -> 'viewers'::text) @> '["some-user-uuid"]'::jsonb)
-> Bitmap Index Scan on idx_documents_permissions_gin (cost=0.00..100.00 rows=10000 width=0) (actual time=0.4 ms rows=10 loops=1)
Index Cond: ((permissions -> 'viewers'::text) @> '["some-user-uuid"]'::jsonb)Night and day. The Bitmap Index Scan is incredibly fast, using the inverted index to find matching rows almost instantly, regardless of table size.
Advanced Patterns: `SECURITY DEFINER` and Cross-Table Logic
What if our policy needs to check data from another table? For instance, a user can only see documents from their own tenant_id.
An RLS policy expression is executed for every row being considered. If you put a subquery like (SELECT tenant_id FROM users WHERE id = get_current_user_id()) directly into the policy, PostgreSQL might execute that subquery for every single row in the documents table. This is another performance trap.
The solution is to encapsulate this logic in a STABLE or IMMUTABLE function. PostgreSQL's query planner is smart enough to cache the result of such a function for the duration of a query.
Furthermore, what if the app_user doesn't have SELECT privileges on the users table? We can use a SECURITY DEFINER function. These functions execute with the privileges of the user who defined the function, not the user who called it. This is extremely powerful but must be used with extreme caution.
Let's create a policy that checks tenancy and permissions.
-- This function will be owned by a privileged user, e.g., the database administrator or a dedicated 'deploy' role.
-- It must be created by a user who HAS permission to select from the 'users' table.
CREATE OR REPLACE FUNCTION check_document_access(doc_tenant_id UUID, doc_permissions JSONB) RETURNS BOOLEAN AS $$
DECLARE
current_user_id UUID;
user_tenant_id UUID;
BEGIN
current_user_id := get_current_user_id();
-- If no user is set, deny access.
IF current_user_id IS NULL THEN
RETURN FALSE;
END IF;
-- Fetch the user's tenant_id. This lookup happens only once per query because the function is STABLE.
SELECT u.attributes->>'tenant_id' INTO user_tenant_id FROM users u WHERE u.id = current_user_id;
-- 1. Enforce strict tenancy. A user can NEVER see documents outside their tenant.
IF doc_tenant_id != user_tenant_id THEN
RETURN FALSE;
END IF;
-- 2. Check permissions within the tenant.
IF (
doc_permissions->'viewers' @> jsonb_build_array(current_user_id)::jsonb OR
doc_permissions->'editors' @> jsonb_build_array(current_user_id)::jsonb OR
doc_permissions->'owners' @> jsonb_build_array(current_user_id)::jsonb
) THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
-- IMPORTANT: Set a secure search_path for SECURITY DEFINER functions
-- This prevents a malicious user from creating a table/function with the same name in a different schema
-- that your function might call by mistake.
ALTER FUNCTION check_document_access(UUID, JSONB) SET search_path = public;
-- Revoke all default public privileges on the function for security.
REVOKE ALL ON FUNCTION check_document_access(UUID, JSONB) FROM PUBLIC;
-- Grant explicit execute permission ONLY to the application user.
GRANT EXECUTE ON FUNCTION check_document_access(UUID, JSONB) TO app_user;
-- Now, we can create a much cleaner, more performant, and more secure policy.
-- First, drop the old policies.
DROP POLICY select_documents ON documents;
DROP POLICY update_documents ON documents;
DROP POLICY delete_documents ON documents;
-- Create a single, all-encompassing policy.
CREATE POLICY manage_documents ON documents
FOR ALL -- Applies to SELECT, INSERT, UPDATE, DELETE
TO app_user
USING ( check_document_access(tenant_id, permissions) )
WITH CHECK ( check_document_access(tenant_id, permissions) );This pattern is far superior:
* Encapsulation: The complex authorization logic is in one place.
* Performance: The user lookup is cached per-query.
* Security: app_user does not need direct access to the users table. The SECURITY DEFINER function acts as a secure bridge. The search_path protection is non-negotiable for production systems.
Edge Cases and Production Gotchas
Implementing RLS at scale requires navigating several subtleties.
1. Superusers and the `BYPASSRLS` Attribute
By default, superusers and any role with the BYPASSRLS attribute will completely ignore all RLS policies. This is a backdoor that's necessary for database administration but can be a security risk.
Production Pattern:
* Your application should never connect as a superuser.
* Your app_user role should explicitly be NOBYPASSRLS.
* For administrative tasks or migrations, use a dedicated admin role. Be aware that when running psql or other tools as this role, you will see all data. This can have implications for data privacy regulations like GDPR.
ALTER ROLE app_user WITH NOBYPASSRLS;2. RLS and Views: The `security_barrier` Trap
Views can obscure RLS behavior. Consider this view:
CREATE VIEW public_documents AS
SELECT id, title, content
FROM documents
WHERE metadata->>'is_public' = 'true';If a user queries this view, when is the RLS policy on the underlying documents table applied? Before or after the view's WHERE clause? If the view's WHERE clause is applied first, it could potentially leak information to a function used within the RLS policy, creating a side-channel attack.
To prevent this, you can define the view with the security_barrier attribute.
CREATE OR REPLACE VIEW public_documents WITH (security_barrier = true) AS
SELECT id, title, content
FROM documents
WHERE metadata->>'is_public' = 'true';A security_barrier view enforces that the RLS policies on the underlying base tables are applied before any other WHERE clauses or functions from the view's definition are evaluated. This prevents conditions in the view from filtering rows in a way that could leak data to functions in the RLS policy. It comes with a performance cost, as it can limit the query planner's ability to push down predicates. Use it when the view's WHERE clause could be influenced by an attacker.
3. Row-level Locking (`FOR UPDATE`)
RLS interacts cleanly with row-level locking. When you issue a SELECT ... FOR UPDATE, the RLS policy is applied first to determine the set of visible rows. Then, the lock is attempted only on that filtered subset. This is the expected and correct behavior, ensuring a user cannot lock (and thus block access to) a row they are not supposed to see.
4. Debugging Policies
Debugging RLS is notoriously difficult because policies fail silently. A query simply returns zero rows. Here are some strategies:
* RAISE NOTICE: Temporarily add RAISE NOTICE 'User: %, Doc: %', get_current_user_id(), document_id; inside a policy function to see if it's being executed.
* EXPLAIN: The EXPLAIN command will show the policy's filter being added to the query plan. This is the best way to confirm a policy is being applied.
* Direct Function Testing: Test your policy functions directly. SELECT check_document_access(...) from a privileged account to see what it returns for different inputs.
Complete Production-Ready Example
Let's put it all together in a single, runnable script.
-- 1. SETUP: CLEANUP AND EXTENSIONS
DROP TABLE IF EXISTS documents, users CASCADE;
DROP ROLE IF EXISTS app_user;
DROP FUNCTION IF EXISTS get_current_user_id(), check_document_access(UUID, JSONB);
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
-- 2. CREATE ROLES AND USERS
-- Note: We create a privileged 'app_owner' to own the objects and functions.
CREATE ROLE app_owner LOGIN PASSWORD 'your_secure_password';
-- The unprivileged application user.
CREATE ROLE app_user LOGIN PASSWORD 'your_secure_password';
ALTER ROLE app_user SET ROLE app_owner; -- Allows app_user to operate within app_owner's context if needed, but not by default
ALTER ROLE app_user WITH NOBYPASSRLS;
-- 3. CREATE TABLES (as app_owner)
SET ROLE app_owner;
CREATE TABLE users (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
email TEXT NOT NULL UNIQUE,
attributes JSONB NOT NULL DEFAULT '{}'::jsonb, -- {"tenant_id": "..."}
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE TABLE documents (
id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
tenant_id UUID NOT NULL,
title TEXT NOT NULL,
content TEXT,
metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
permissions JSONB NOT NULL DEFAULT '{}'::jsonb,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
-- 4. PERMISSIONS & INDEXING
GRANT SELECT, INSERT, UPDATE, DELETE ON documents, users TO app_user;
CREATE INDEX idx_documents_permissions_gin ON documents USING GIN (permissions jsonb_path_ops);
CREATE INDEX idx_users_attributes_gin ON users USING GIN (attributes jsonb_path_ops);
-- 5. CREATE HELPER & RLS POLICY FUNCTIONS (as app_owner)
CREATE OR REPLACE FUNCTION get_current_user_id() RETURNS UUID AS $$
BEGIN
RETURN current_setting('app.current_user_id', true)::UUID;
EXCEPTION
WHEN invalid_text_representation THEN
RETURN NULL;
END;
$$ LANGUAGE plpgsql STABLE;
CREATE OR REPLACE FUNCTION check_document_access(doc_tenant_id UUID, doc_permissions JSONB) RETURNS BOOLEAN AS $$
DECLARE
current_user_id UUID := get_current_user_id();
user_tenant_id UUID;
BEGIN
IF current_user_id IS NULL THEN RETURN FALSE; END IF;
SELECT (u.attributes->>'tenant_id')::UUID INTO user_tenant_id FROM users u WHERE u.id = current_user_id;
IF doc_tenant_id != user_tenant_id THEN RETURN FALSE; END IF;
IF (
doc_permissions->'viewers' @> jsonb_build_array(current_user_id)::jsonb OR
doc_permissions->'editors' @> jsonb_build_array(current_user_id)::jsonb OR
doc_permissions->'owners' @> jsonb_build_array(current_user_id)::jsonb
) THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END;
$$ LANGUAGE plpgsql STABLE SECURITY DEFINER;
ALTER FUNCTION check_document_access(UUID, JSONB) OWNER TO app_owner;
ALTER FUNCTION check_document_access(UUID, JSONB) SET search_path = public;
REVOKE ALL ON FUNCTION check_document_access(UUID, JSONB) FROM PUBLIC;
GRANT EXECUTE ON FUNCTION check_document_access(UUID, JSONB) TO app_user;
-- 6. ATTACH RLS POLICY (as app_owner)
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents FORCE ROW LEVEL SECURITY; -- Also applies to table owner
CREATE POLICY manage_documents ON documents
FOR ALL
TO app_user
USING ( check_document_access(tenant_id, permissions) )
WITH CHECK ( check_document_access(tenant_id, permissions) );
RESET ROLE;
-- 7. SEED DATA
-- Create two tenants and four users
DO $$
DECLARE
tenant1_id UUID := uuid_generate_v4();
tenant2_id UUID := uuid_generate_v4();
user1_t1_id UUID;
user2_t1_id UUID;
user3_t2_id UUID;
user4_t2_id UUID;
BEGIN
INSERT INTO users (email, attributes) VALUES ('[email protected]', jsonb_build_object('tenant_id', tenant1_id)) RETURNING id INTO user1_t1_id;
INSERT INTO users (email, attributes) VALUES ('[email protected]', jsonb_build_object('tenant_id', tenant1_id)) RETURNING id INTO user2_t1_id;
INSERT INTO users (email, attributes) VALUES ('[email protected]', jsonb_build_object('tenant_id', tenant2_id)) RETURNING id INTO user3_t2_id;
INSERT INTO users (email, attributes) VALUES ('[email protected]', jsonb_build_object('tenant_id', tenant2_id)) RETURNING id INTO user4_t2_id;
-- Tenant 1 Document: user1 is owner, user2 is viewer
INSERT INTO documents (tenant_id, title, permissions) VALUES (tenant1_id, 'Tenant 1 Report', jsonb_build_object('owners', jsonb_build_array(user1_t1_id), 'viewers', jsonb_build_array(user2_t1_id)));
-- Tenant 2 Document: user3 is editor
INSERT INTO documents (tenant_id, title, permissions) VALUES (tenant2_id, 'Tenant 2 Plan', jsonb_build_object('editors', jsonb_build_array(user3_t2_id)));
END $$;
-- 8. TEST QUERIES (Simulate application connection)
-- Get user IDs for testing
-- In a real app, you'd get these from your session
DO $$ DECLARE user1_id UUID; user2_id UUID; user3_id UUID;
BEGIN
user1_id := (SELECT id FROM users WHERE email = '[email protected]');
user2_id := (SELECT id FROM users WHERE email = '[email protected]');
user3_id := (SELECT id FROM users WHERE email = '[email protected]');
RAISE NOTICE E'\n--- Testing as User 1 (Owner of T1 Doc) ---\n';
SET LOCAL app.current_user_id = user1_id::text;
SET ROLE app_user;
RAISE NOTICE 'Can see % document(s)', (SELECT count(*) FROM documents);
PERFORM * FROM documents WHERE title = 'Tenant 1 Report'; RAISE NOTICE 'Can see T1 doc.';
BEGIN
PERFORM * FROM documents WHERE title = 'Tenant 2 Plan';
RAISE NOTICE 'FAIL: Should not see T2 doc.';
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'PASS: Correctly cannot see T2 doc.';
END;
RESET ROLE;
RAISE NOTICE E'\n--- Testing as User 2 (Viewer of T1 Doc) ---\n';
SET LOCAL app.current_user_id = user2_id::text;
SET ROLE app_user;
RAISE NOTICE 'Can see % document(s)', (SELECT count(*) FROM documents);
UPDATE documents SET title = 'New Title' WHERE title = 'Tenant 1 Report';
RAISE NOTICE 'FAIL: Should not be able to update doc.';
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'PASS: Update correctly denied by WITH CHECK clause.';
RESET ROLE;
RAISE NOTICE E'\n--- Testing as User 3 (Editor of T2 Doc) ---\n';
SET LOCAL app.current_user_id = user3_id::text;
SET ROLE app_user;
RAISE NOTICE 'Can see % document(s)', (SELECT count(*) FROM documents);
UPDATE documents SET title = 'Updated Tenant 2 Plan' WHERE title = 'Tenant 2 Plan';
RAISE NOTICE 'PASS: Update was successful.';
RESET ROLE;
END $$;
Conclusion
By pushing Attribute-Based Access Control into PostgreSQL with RLS and JSONB, we build a more secure, consistent, and maintainable system. The logic is centralized at the data layer, ensuring that no matter how a user accesses the data—via a web app, a mobile API, or a direct reporting tool—the same granular security rules are always enforced.
This pattern is not without its complexities. It requires a deep understanding of PostgreSQL's execution model, careful function design to avoid performance pitfalls, and a rigorous approach to security with SECURITY DEFINER contexts. However, for applications demanding fine-grained, data-dependent authorization, the investment is well worth the payoff in robustness and security.