Database-Backed Permissions: Dynamic ACLs, Row-Level Security, and Policy Engines

Dynamic, database-backed permission systems allow enterprise applications to enforce fine-grained access control lists (ACLs) and attribute-based access control (ABAC) at runtime without requiring static configuration file redeployments.

This guide details database schema design for dynamic permissions, PostgreSQL Row-Level Security (RLS), Open Policy Agent (OPA) integration, and multi-tier permission caching.

---

1. Quick-Reference: Permission Storage Models

+-----------------------------------------------------------------------------------------+
|                               PERMISSION SCHEMA PATTERNS                                |
+-----------------------------------------------------------------------------------------+
| Model                  | Advantages                        | Performance Considerations |
+------------------------+-----------------------------------+----------------------------+
| Normalized ACL Tables  | Granular per-resource overrides   | Requires JOIN on every read|
| Row-Level Security(RLS)| Enforced in DB engine kernel      | Query planner optimization |
| Policy Engine (OPA)    | Decoupled Rego business logic     | In-memory sub-ms latency   |
| Precomputed Bitmasks   | Ultra-fast bitwise AND operations | Static role ceiling limits |
+-----------------------------------------------------------------------------------------+

---

2. PostgreSQL Row-Level Security (RLS) Implementation

-- Enabling RLS on Wiki Pages
ALTER TABLE wiki_pages ENABLE ROW LEVEL SECURITY;

-- Policy: Users can read public pages or pages in their tenant group
CREATE POLICY page_read_policy ON wiki_pages
    FOR SELECT
    USING (
        is_public = TRUE 
        OR tenant_id = CURRENT_SETTING('app.current_tenant_id')::UUID
        OR EXISTS (
            SELECT 1 FROM page_acl 
            WHERE page_acl.page_id = wiki_pages.id 
              AND page_acl.user_id = CURRENT_SETTING('app.current_user_id')::UUID
              AND page_acl.permission = 'READ'
        )
    );

---

3. High-Speed Permission Caching

To prevent database bottlenecks, permissions are evaluated once and cached in Redis with a 5-minute TTL. Invalidation events are published when user roles or group memberships change.