Cedarling PostgreSQL Extension : Requirements, Design, Tasks

Requirements Document

Introduction

The PostgreSQL Cedarling Extension is a database extension that integrates the Cedarling authorization engine with PostgreSQL's Row-Level Security (RLS) to provide fine-grained, policy-based access control at the database row level. The extension enables automatic filtering of database rows based on JWT token claims and Cedar policies, ensuring that users only access data they are authorized to see according to centralized authorization policies.

Requirements

Requirement 1

User Story: As a database administrator, I want to install and configure a PostgreSQL extension that integrates Cedarling authorization, so that I can enforce fine-grained access control policies directly at the database level.

Acceptance Criteria

1. WHEN the extension is built using cargo pgrx THEN the system SHALL create a PostgreSQL-compatible extension package
2. WHEN the extension is installed in PostgreSQL THEN the system SHALL register the cedarling_authorized function
3. WHEN CREATE EXTENSION cedarling_pg is executed THEN the system SHALL make Cedarling functionality available to the database
4. IF the extension installation fails THEN the system SHALL provide clear error messages indicating the cause

Requirement 2

User Story: As a database administrator, I want to enable Row-Level Security with Cedarling policies on tables, so that access control is automatically enforced without requiring application-level changes.

Acceptance Criteria

1. WHEN RLS is enabled on a table with a Cedarling policy THEN the system SHALL automatically filter rows based on authorization decisions
2. WHEN a user queries a table with RLS enabled THEN the system SHALL call the cedarling_authorized function for each row
3. IF the cedarling_authorized function returns false THEN the system SHALL exclude that row from query results
4. WHEN multiple policies exist on a table THEN the system SHALL evaluate all policies according to PostgreSQL RLS rules

Requirement 3

User Story: As an application developer, I want to provide JWT tokens to the extension for authorization decisions, so that the system can make access control decisions based on user identity and claims.

Acceptance Criteria

1. WHEN tokens are set using SET LOCAL cedarling.tokens THEN the system SHALL use those tokens for authorization decisions within the transaction
2. WHEN the transaction ends THEN the system SHALL clear the tokens to prevent unauthorized access
3. IF no tokens are provided THEN the system SHALL deny access by default
4. WHEN invalid JSON is provided for tokens THEN the system SHALL return an error and deny access

Requirement 4

User Story: As a security administrator, I want the extension to evaluate Cedar policies against resource data and JWT claims, so that access decisions are made according to centralized authorization policies.

Acceptance Criteria

1. WHEN cedarling_authorized is called with resource data and tokens THEN the system SHALL construct a Cedar authorization request
2. WHEN the Cedar policy engine evaluates the request THEN the system SHALL return the authorization decision
3. IF the policy evaluation results in "Allow" THEN the function SHALL return true
4. IF the policy evaluation results in "Deny" or encounters an error THEN the function SHALL return false
5. WHEN resource data is provided as JSON THEN the system SHALL parse it into Cedar entity attributes

Requirement 5

User Story: As a database administrator, I want to configure the extension's behavior and logging, so that I can control how authorization is enforced and monitor system activity.

Acceptance Criteria

1. WHEN cedarling.mode is set to 'enforcement' THEN the system SHALL enforce authorization decisions
2. WHEN cedarling.mode is set to 'instrumentation' THEN the system SHALL log decisions but not enforce them
3. WHEN cedarling.strategy is set to 'filter' THEN the system SHALL exclude unauthorized rows
4. WHEN cedarling.strategy is set to 'mask' THEN the system SHALL return masked data for unauthorized rows
5. WHEN cedarling.log_level is configured THEN the system SHALL log events at the specified level

Requirement 6

User Story: As a system administrator, I want the extension to handle errors gracefully and securely, so that authorization failures don't compromise system security or stability.

Acceptance Criteria

1. WHEN the Cedarling engine encounters an error THEN the system SHALL deny access by default
2. WHEN invalid resource data is provided THEN the system SHALL log the error and deny access
3. WHEN JWT token parsing fails THEN the system SHALL deny access and log the failure
4. IF the Cedar policy engine is unavailable THEN the system SHALL deny all access until service is restored

Requirement 7

User Story: As a developer, I want the extension to provide good performance for authorization decisions, so that database queries remain responsive under normal load.

Acceptance Criteria

1. WHEN the extension is first loaded THEN the system SHALL cache the Cedarling instance for reuse
2. WHEN repeated authorization requests are made with the same parameters THEN the system SHALL cache and reuse results when appropriate
3. WHEN processing large result sets THEN the system SHALL maintain acceptable query performance
4. WHEN multiple concurrent requests occur THEN the system SHALL handle them efficiently without blocking

Requirement 8

User Story: As a developer, I want to test authorization logic during development, so that I can verify policies work correctly before production deployment.

Acceptance Criteria

1. WHEN using the cedarling_authorized function directly in WHERE clauses THEN the system SHALL evaluate authorization for testing purposes
2. WHEN testing with sample data and tokens THEN the system SHALL return consistent authorization decisions
3. IF testing mode is enabled THEN the system SHALL provide detailed logging of authorization decisions
4. WHEN switching between development and production modes THEN the system SHALL maintain security boundaries

Design Document

Overview

The PostgreSQL Cedarling Extension is a native PostgreSQL extension that integrates the Cedarling authorization engine with PostgreSQL's Row-Level Security (RLS) system. The extension provides a bridge between database queries and Cedar policy evaluation, enabling fine-grained, policy-based access control at the database row level.

The extension operates by intercepting row-level access through PostgreSQL's RLS mechanism and delegating authorization decisions to the Cedarling engine, which evaluates Cedar policies against JWT token claims and resource attributes.

Architecture

High-Level Architecture

graph TB
    Client[Database Client] --> PG[PostgreSQL Server]
    PG --> RLS[Row-Level Security]
    RLS --> Ext[Cedarling Extension]
    Ext --> CL[Cedarling Engine]
    CL --> CP[Cedar Policies]
    
    subgraph "PostgreSQL Process"
        PG
        RLS
        Ext
        CL
    end
    
    subgraph "Policy Store"
        CP
        Schema[Cedar Schema]
        Entities[Default Entities]
    end

Loading

Component Interaction Flow

1. Query Execution: Client executes SQL query against table with RLS enabled
2. RLS Activation: PostgreSQL RLS system activates for each row
3. Extension Invocation: RLS policy calls cedarling_authorized() function
4. Token Extraction: Extension retrieves JWT tokens from session variables
5. Resource Construction: Extension builds Cedar resource entity from row data
6. Policy Evaluation: Cedarling engine evaluates Cedar policies
7. Decision Return: Authorization decision (Allow/Deny) returned to RLS
8. Row Filtering: PostgreSQL includes/excludes row based on decision

Extension Architecture

graph TB
    subgraph "PostgreSQL Extension"
        API[Extension API Layer]
        Cache[Authorization Cache]
        Config[Configuration Manager]
        Token[Token Handler]
        Resource[Resource Builder]
        
        API --> Cache
        API --> Token
        API --> Resource
        Config --> API
    end
    
    subgraph "Cedarling Integration"
        CL[Cedarling Instance]
        Policies[Policy Store]
        Schema[Schema Validator]
        
        API --> CL
        CL --> Policies
        CL --> Schema
    end

Loading

Components and Interfaces

Core Extension Function

The primary interface is the cedarling_authorized function:

cedarling_authorized(resource_data TEXT, token_bundle TEXT) RETURNS BOOLEAN

Parameters:

• resource_data: JSON string representing the database row as a Cedar entity
• token_bundle: JSON string containing JWT tokens for authorization

Return Value:

• true: Access granted (row included in results)
• false: Access denied (row excluded from results)

Configuration System

The extension uses PostgreSQL's GUC (Grand Unified Configuration) system for runtime configuration:

-- Required: JWT tokens for current transaction
SET LOCAL cedarling.tokens = '{"access_token": "...", "id_token": "..."}';

-- Optional: Operation mode
SET cedarling.mode = 'enforcement';  -- 'enforcement' | 'instrumentation'

-- Optional: Access strategy  
SET cedarling.strategy = 'filter';   -- 'filter' | 'mask'

-- Optional: Logging level
SET cedarling.log_level = 'info';    -- 'debug' | 'info' | 'warn' | 'error'

Token Management

Token Bundle Format

{
  "access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9...",
  "id_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9...",
  "userinfo_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9..."
}

Token Lifecycle

1. Token Setting: Tokens set via SET LOCAL cedarling.tokens
2. Transaction Scope: Tokens automatically cleared at transaction end
3. Validation: Tokens validated during first authorization call
4. Caching: Parsed token claims cached for transaction duration

Resource Entity Construction

The extension converts PostgreSQL row data into Cedar entity format:

Input Row Data

{
  "id": 123,
  "name": "John Doe", 
  "grad_year": 2024,
  "department": "Engineering"
}

Cedar Entity Output

{
  "uid": "Student::\"123\"",
  "attrs": {
    "name": "John Doe",
    "grad_year": 2024,
    "department": "Engineering"
  }
}

Cedarling Integration

Policy Store Integration

The extension integrates with Cedarling's policy store system:

• Schema Loading: Cedar schema loaded at extension initialization
• Policy Loading: Cedar policies loaded from policy store
• Entity Loading: Default entities loaded for policy evaluation
• Trusted Issuers: JWT issuer configurations loaded for token validation

Authorization Request Construction

For each row authorization, the extension constructs a Cedar authorization request:

AuthorizationRequest {
    principal: extract_principal_from_tokens(tokens),
    action: Action::new("Read"), // Configurable action mapping
    resource: build_resource_entity(row_data),
    context: build_context(tokens, session_info),
}

Data Models

Extension Configuration Model

pub struct ExtensionConfig {
    pub mode: OperationMode,
    pub strategy: AccessStrategy,
    pub log_level: LogLevel,
    pub policy_store_path: Option<String>,
    pub cache_size: usize,
    pub cache_ttl: Duration,
}

pub enum OperationMode {
    Enforcement,    // Enforce authorization decisions
    Instrumentation, // Log decisions but don't enforce
}

pub enum AccessStrategy {
    Filter,  // Exclude unauthorized rows
    Mask,    // Return masked data for unauthorized rows
}

Token Context Model

pub struct TokenContext {
    pub access_tokens: Vec<AccessToken>,
    pub id_tokens: Vec<IdToken>,
    pub userinfo_tokens: Vec<UserinfoToken>,
    pub tx_tokens: Vec<TxToken>,
    pub validation_time: SystemTime,
}

pub struct AccessToken {
    pub issuer: String,
    pub subject: String,
    pub audience: Vec<String>,
    pub scopes: Vec<String>,
    pub expires_at: SystemTime,
    pub claims: HashMap<String, serde_json::Value>,
}

Authorization Cache Model

pub struct AuthorizationCache {
    entries: HashMap<CacheKey, CacheEntry>,
    max_size: usize,
    ttl: Duration,
}

pub struct CacheKey {
    pub resource_hash: u64,
    pub token_hash: u64,
    pub policy_version: String,
}

pub struct CacheEntry {
    pub decision: bool,
    pub timestamp: SystemTime,
    pub hit_count: u32,
}

Error Handling

Error Categories

1. Configuration Errors: Invalid GUC settings, missing policy store
2. Token Errors: Invalid JWT format, expired tokens, missing required claims
3. Resource Errors: Invalid JSON format, schema validation failures
4. Policy Errors: Policy evaluation failures, missing policies
5. System Errors: Memory allocation failures, I/O errors

Error Handling Strategy

Fail-Safe Security Model

• Default Deny: All errors result in access denial
• Error Logging: All errors logged with appropriate detail level
• Graceful Degradation: System continues operating with reduced functionality

Error Response Patterns

pub enum AuthorizationError {
    TokenValidation(String),
    ResourceConstruction(String),
    PolicyEvaluation(String),
    SystemError(String),
}

impl AuthorizationError {
    pub fn should_deny(&self) -> bool {
        true // All errors result in denial for security
    }
    
    pub fn log_level(&self) -> LogLevel {
        match self {
            Self::SystemError(_) => LogLevel::Error,
            Self::PolicyEvaluation(_) => LogLevel::Warn,
            _ => LogLevel::Info,
        }
    }
}

Error Recovery Mechanisms

1. Token Refresh: Automatic retry with refreshed tokens
2. Policy Reload: Reload policies on evaluation errors
3. Cache Invalidation: Clear cache on persistent errors
4. Fallback Policies: Use default deny policies when primary policies fail

Testing Strategy

Unit Testing

Function-Level Tests

• Token Parsing: Test JWT token validation and claim extraction
• Resource Construction: Test row data to Cedar entity conversion
• Policy Evaluation: Test Cedar policy evaluation with mock data
• Configuration: Test GUC parameter handling and validation

Component Integration Tests

• Cedarling Integration: Test extension-to-Cedarling communication
• Cache Behavior: Test authorization result caching and invalidation
• Error Handling: Test error scenarios and recovery mechanisms

Integration Testing

PostgreSQL Integration Tests

• RLS Integration: Test Row-Level Security policy execution
• Transaction Behavior: Test token lifecycle and transaction boundaries
• Concurrent Access: Test multi-user concurrent authorization
• Performance: Test authorization performance under load

End-to-End Testing

• Application Scenarios: Test realistic application authorization flows
• Multi-Token Scenarios: Test complex multi-issuer token scenarios
• Policy Updates: Test dynamic policy updates and cache invalidation

Security Testing

Authorization Testing

• Access Control: Verify unauthorized access is properly denied
• Token Validation: Test token tampering and expiration scenarios
• Policy Bypass: Attempt to bypass authorization through various means
• Injection Attacks: Test SQL injection and JSON injection resistance

Performance Testing

• Load Testing: Test performance under high query volume
• Memory Usage: Monitor memory consumption and leak detection
• Cache Efficiency: Measure cache hit rates and performance impact

Test Data Management

Test Policy Store

test-policy-store/
├── metadata.json
├── schema.cedarschema
├── policies/
│   ├── student-access.cedar
│   ├── admin-access.cedar
│   └── instructor-access.cedar
├── entities/
│   └── test-roles.json
└── trusted-issuers/
    └── test-issuer.json

Test Database Schema

-- Test tables with various data types and relationships
CREATE TABLE students (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    grad_year INTEGER,
    department VARCHAR(50),
    gpa DECIMAL(3,2),
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE courses (
    id SERIAL PRIMARY KEY,
    code VARCHAR(10) NOT NULL,
    title VARCHAR(200),
    instructor_id INTEGER,
    department VARCHAR(50),
    credits INTEGER
);

CREATE TABLE enrollments (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    grade CHAR(2),
    enrolled_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (student_id, course_id)
);

Test JWT Tokens

{
  "test_tokens": {
    "student_token": {
      "access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9...",
      "claims": {
        "sub": "student123",
        "role": "Student",
        "department": "Engineering",
        "grad_year": 2024
      }
    },
    "admin_token": {
      "access_token": "eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiJ9...",
      "claims": {
        "sub": "admin456",
        "role": "Administrator",
        "permissions": ["read:all", "write:all"]
      }
    }
  }
}

Continuous Integration

Automated Test Pipeline

1. Build Tests: Compile extension against multiple PostgreSQL versions
2. Unit Tests: Run Rust unit tests with coverage reporting
3. Integration Tests: Run PostgreSQL integration tests in containers
4. Security Tests: Run automated security scanning and penetration tests
5. Performance Tests: Run benchmark tests and performance regression detection

Test Environment Matrix

• PostgreSQL Versions: 12, 13, 14, 15, 16
• Operating Systems: Linux (Ubuntu, CentOS), macOS, Windows
• Rust Versions: Stable, Beta, MSRV (Minimum Supported Rust Version)
• Cedarling Versions: Current stable, development branch

Implementation Plan

• 1. Set up PostgreSQL extension project structure and build system

  • Create Cargo.toml with pgrx dependency and Cedarling integration
  • Configure pgrx extension metadata and PostgreSQL version compatibility
  • Set up basic extension initialization and cleanup functions
  • Requirements: 1.1, 1.2, 1.3

• 2. Implement core extension configuration system

  • Create GUC (Grand Unified Configuration) parameter definitions for cedarling.tokens, cedarling.mode, cedarling.strategy, and cedarling.log_level
  • Implement configuration validation and error handling for invalid parameter values
  • Write unit tests for configuration parameter parsing and validation
  • Requirements: 5.1, 5.2, 5.3, 5.4, 5.5

• 3. Implement JWT token handling and validation

  • Create TokenContext struct and parsing logic for JWT token bundles from JSON
  • Implement token validation including expiration checks and claim extraction
  • Add support for multiple token types (access_token, id_token, userinfo_token)
  • Write unit tests for token parsing, validation, and error scenarios
  • Requirements: 3.1, 3.2, 3.3, 3.4

• 4. Implement resource entity construction from PostgreSQL row data

  • Create ResourceBuilder that converts JSON row data to Cedar entity format
  • Implement JSON parsing and validation for resource data input
  • Add entity type mapping and attribute conversion logic
  • Write unit tests for resource construction with various data types and edge cases
  • Requirements: 4.5

• 5. Integrate Cedarling engine and implement authorization logic

  • Create CedarlingWrapper that manages Cedarling instance lifecycle and caching
  • Implement authorization request construction from tokens and resource data
  • Add Cedar policy evaluation and decision processing
  • Write unit tests for authorization request building and policy evaluation
  • Requirements: 4.1, 4.2, 4.3, 4.4

• 6. Implement the main cedarling_authorized PostgreSQL function

  • Create the SQL-callable function signature with TEXT parameters and BOOLEAN return
  • Implement function logic that orchestrates token parsing, resource building, and authorization
  • Add comprehensive error handling with fail-safe deny behavior
  • Write integration tests for the complete function with various input scenarios
  • Requirements: 4.1, 4.2, 4.3, 4.4, 6.1, 6.2, 6.3, 6.4

• 7. Implement authorization result caching system

  • Create AuthorizationCache with configurable size and TTL settings
  • Implement cache key generation from resource and token hashes
  • Add cache hit/miss logic and performance monitoring
  • Write unit tests for cache behavior, eviction, and performance
  • Requirements: 7.1, 7.2, 7.3, 7.4

• 8. Implement comprehensive error handling and logging

  • Create AuthorizationError enum with different error categories and logging levels
  • Implement PostgreSQL-compatible error reporting and logging integration
  • Add fail-safe security behavior for all error conditions
  • Write unit tests for error scenarios and recovery mechanisms
  • Requirements: 6.1, 6.2, 6.3, 6.4

• 9. Create PostgreSQL Row-Level Security integration examples

  • Write SQL scripts demonstrating RLS policy creation with cedarling_authorized function
  • Create example table schemas and policy configurations for common use cases
  • Implement transaction-scoped token management examples
  • Write integration tests for RLS policy execution and row filtering
  • Requirements: 2.1, 2.2, 2.3, 2.4

• 10. Implement extension installation and configuration scripts

  • Create SQL extension definition files and control scripts
  • Write installation documentation and setup procedures
  • Add extension upgrade and migration scripts
  • Write integration tests for extension installation and configuration
  • Requirements: 1.1, 1.2, 1.3, 1.4

• 11. Create comprehensive test suite for development and production scenarios

  • Implement test policy store with sample Cedar policies and schemas
  • Create test database schemas with realistic data relationships
  • Write end-to-end tests covering student access control scenarios from original design
  • Add performance benchmarks and load testing for authorization decisions
  • Requirements: 8.1, 8.2, 8.3, 8.4

• 12. Implement security hardening and production readiness features

  • Add input sanitization and SQL injection protection
  • Implement secure token handling and memory management
  • Create security audit logging and monitoring capabilities
  • Write security tests including penetration testing scenarios
  • Requirements: 6.1, 6.2, 6.3, 6.4, 7.4