c77_rbac PostgreSQL Extension

A PostgreSQL extension that provides Role-Based Access Control (RBAC) with Row-Level Security (RLS) for enterprise applications. This extension pushes authorization logic to the database layer, ensuring consistent security across all application frameworks and direct database access.

Features

• Database-Centric Authorization: Authorization rules enforced at the database level
• Row-Level Security: Fine-grained access control on individual rows
• Scope-Based Permissions: Support for department, region, or any custom scope
• Global Admin Support: Special global/all scope for administrative access
• Framework Agnostic: Works with any application framework (Laravel, Rails, Django, etc.)
• Dynamic Schema Support: Works with any PostgreSQL schema
• Performance Optimized: Includes indexes and efficient access checks
• Bulk Operations: High-performance batch assignment for large user bases
• Complete CRUD: Full create, read, update, delete operations for roles and permissions
• Audit Support: Timestamp tracking and comprehensive reporting views

Version 1.1 Enhancements

🆕 New in Version 1.1:

• Bulk Operations: c77_rbac_bulk_assign_subjects() for batch user assignments
• Removal Functions: c77_rbac_revoke_subject_role() and c77_rbac_revoke_feature()
• Admin Sync: c77_rbac_sync_admin_features() and c77_rbac_sync_global_admin_features()
• Enhanced Error Handling: Comprehensive validation with helpful error messages
• Performance Optimization: Better indexes and optimized query functions
• Management Views: c77_rbac_user_permissions and c77_rbac_summary for reporting
• Audit Tracking: Timestamps on all role assignments and feature grants

Requirements

• PostgreSQL 14 or later
• Superuser access for initial installation

Installation

New Installation (Recommended)

1. Copy extension files to PostgreSQL directory:

   sudo cp c77_rbac.control /usr/share/postgresql/14/extension/
   sudo cp c77_rbac--1.1.sql /usr/share/postgresql/14/extension/
   

2. Install the extension (requires superuser):

   -- Connect as superuser
   CREATE DATABASE your_db;
   CREATE USER app_user WITH PASSWORD 'secure_password';
   
   -- Install extension
   \c your_db
   CREATE EXTENSION c77_rbac;
   
   -- Grant necessary privileges to application user
   GRANT CREATE ON DATABASE your_db TO app_user;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO app_user;
   GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO app_user;
   GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO app_user;
   

Upgrading from Version 1.0

If you have version 1.0 already installed:

1. Copy upgrade files:

   sudo cp c77_rbac.control /usr/share/postgresql/14/extension/
   sudo cp c77_rbac--1.0--1.1.sql /usr/share/postgresql/14/extension/
   

2. Upgrade the extension:

   ALTER EXTENSION c77_rbac UPDATE TO '1.1';
   

Core Concepts

1. Subjects (Users)

• Identified by an external_id (typically your application's user ID)
• Can have multiple roles with different scopes

2. Roles

• Named collections of features (permissions)
• Can be scoped to specific contexts (department, region, etc.)

3. Features

• Specific permissions that can be checked in policies
• Examples: view_reports, edit_users, delete_records

4. Scopes

• Context for role assignments
• Examples: department/engineering, region/north, global/all

Basic Usage

1. Define Features and Roles

-- Define features (permissions)
SELECT public.c77_rbac_grant_feature('manager', 'view_reports');
SELECT public.c77_rbac_grant_feature('manager', 'edit_reports');
SELECT public.c77_rbac_grant_feature('admin', 'manage_users');

-- Sync all features to admin role automatically
SELECT public.c77_rbac_sync_admin_features();

2. Assign Users to Roles

-- Single assignment
SELECT public.c77_rbac_assign_subject('123', 'manager', 'department', 'engineering');

-- Bulk assignment (NEW in v1.1)
SELECT * FROM public.c77_rbac_bulk_assign_subjects(
    ARRAY['101','102','103','104','105'],
    'employee',
    'department', 
    'sales'
);

-- Global admin assignment
SELECT public.c77_rbac_assign_subject('1', 'admin', 'global', 'all');

3. Remove Role Assignments (NEW in v1.1)

-- Remove specific role assignment
SELECT public.c77_rbac_revoke_subject_role('123', 'manager', 'department', 'engineering');

-- Remove feature from role
SELECT public.c77_rbac_revoke_feature('temp_role', 'temporary_access');

4. Apply Row-Level Security

-- Apply RLS policy to a table
SELECT public.c77_rbac_apply_policy(
    'myschema.reports',     -- table name (can be schema-qualified)
    'view_reports',         -- required feature
    'department',           -- scope type
    'department_id'         -- column containing scope value
);

5. Set User Context and Query

-- Set the current user for RLS checks
SET "c77_rbac.external_id" TO '123';

-- Now queries automatically filter based on permissions
SELECT * FROM myschema.reports;  -- Only shows reports for user's department

Management and Reporting (NEW in v1.1)

Check User Permissions

-- Get all roles for a user
SELECT * FROM public.c77_rbac_get_user_roles('123');

-- Get all features for a role
SELECT * FROM public.c77_rbac_get_role_features('manager');

-- View comprehensive permissions
SELECT * FROM public.c77_rbac_user_permissions 
WHERE external_id = '123';

System Overview

-- Get system statistics
SELECT * FROM public.c77_rbac_summary;

-- Check specific permission
SELECT public.c77_rbac_can_access('edit_reports', '123', 'department', 'engineering');

Available Functions

Core Functions

• c77_rbac_assign_subject(external_id, role, scope_type, scope_id) - Assign role to user
• c77_rbac_grant_feature(role, feature) - Grant feature to role
• c77_rbac_can_access(feature, external_id, scope_type, scope_id) - Check access
• c77_rbac_apply_policy(table, feature, scope_type, column) - Apply RLS policy

Bulk Operations (NEW in v1.1)

• c77_rbac_bulk_assign_subjects(external_ids[], role, scope_type, scope_id) - Batch assign roles
• c77_rbac_revoke_subject_role(external_id, role, scope_type, scope_id) - Remove role assignment
• c77_rbac_revoke_feature(role, feature) - Remove feature from role

Admin Management

• c77_rbac_sync_admin_features() - Sync all features to admin role
• c77_rbac_sync_global_admin_features() - Sync features to all global/all roles

Reporting and Management (NEW in v1.1)

• c77_rbac_get_user_roles(external_id) - Get all roles for a user
• c77_rbac_get_role_features(role_name) - Get all features for a role

Maintenance Functions

• c77_rbac_show_dependencies() - Show all dependencies on the extension
• c77_rbac_remove_all_policies() - Remove all RLS policies
• c77_rbac_cleanup_for_removal(remove_data) - Prepare for extension removal

Framework Integration Examples

Laravel Integration

// Middleware to set user context
public function handle($request, Closure $next)
{
    if (Auth::check()) {
        DB::statement('SET "c77_rbac.external_id" TO ?', [Auth::id()]);
    }
    return $next($request);
}

// Bulk assign roles to users
$userIds = ['101', '102', '103', '104', '105'];
$results = DB::select("
    SELECT * FROM public.c77_rbac_bulk_assign_subjects(?, ?, ?, ?)
", [json_encode($userIds), 'student', 'program', 'driver_education']);

// Check permissions
$canEdit = DB::selectOne("
    SELECT public.c77_rbac_can_access(?, ?, ?, ?) AS allowed
", ['edit_reports', Auth::id(), 'department', 'engineering'])->allowed;

Schema-Aware Usage

-- Works with any schema
CREATE SCHEMA finance;
CREATE TABLE finance.accounts (...);

-- Apply RLS with schema qualification
SELECT public.c77_rbac_apply_policy(
    'finance.accounts',
    'view_finance',
    'department',
    'dept_id'
);

Real-World Example: Court Education System

Setup Court Roles and Features

-- Define court-specific roles and features
SELECT public.c77_rbac_grant_feature('court_admin', 'manage_all_programs');
SELECT public.c77_rbac_grant_feature('court_admin', 'view_all_participants');
SELECT public.c77_rbac_grant_feature('judge', 'approve_completions');
SELECT public.c77_rbac_grant_feature('counselor', 'update_progress');
SELECT public.c77_rbac_grant_feature('participant', 'view_own_progress');

-- Sync admin features
SELECT public.c77_rbac_sync_admin_features();

Bulk Enroll Participants

-- Enroll multiple participants in a DUI education program
SELECT * FROM public.c77_rbac_bulk_assign_subjects(
    ARRAY['P001','P002','P003','P004','P005'],
    'participant',
    'program',
    'dui_education_2025_q1'
);

Apply Security Policies

-- Participants can only see their own data
SELECT public.c77_rbac_apply_policy(
    'participant_progress',
    'view_own_progress',
    'participant',
    'participant_id'
);

-- Court staff can see their court's participants
SELECT public.c77_rbac_apply_policy(
    'participants',
    'view_court_participants',
    'court',
    'assigned_court'
);

Performance Considerations

Built-in Optimizations (Enhanced in v1.1)

• Hash indexes on frequently queried columns
• Composite indexes for common access patterns
• Optimized permission checking functions
• Efficient bulk operations

Best Practices

-- Use bulk operations for large datasets
SELECT * FROM public.c77_rbac_bulk_assign_subjects(
    array_agg(user_id::text), 'role_name', 'scope_type', 'scope_id'
) FROM large_user_table;

-- Cache permission checks in your application
-- Check EXPLAIN ANALYZE for query performance
EXPLAIN ANALYZE SELECT * FROM protected_table;

Uninstallation

1. Check dependencies:

   SELECT * FROM public.c77_rbac_show_dependencies();
   

2. Remove policies and optionally data:

   -- Just remove policies
   SELECT public.c77_rbac_remove_all_policies();
   
   -- Or remove policies and all RBAC data
   SELECT public.c77_rbac_cleanup_for_removal(true);
   

3. Drop the extension:

   DROP EXTENSION c77_rbac CASCADE;
   

Best Practices

1. Feature Naming Convention:

   • Use prefixes: view_*, edit_*, delete_*, manage_*
   • Be specific: view_financial_reports vs view_reports

2. Admin Setup:

   • Use c77_rbac_sync_admin_features() after adding new features
   • Document all features in your application

3. Performance:

   • Use bulk operations for large datasets
   • Consider caching permission checks in your application
   • Monitor query performance with EXPLAIN ANALYZE

4. Security:

   • Always use parameterized queries
   • Reset session variables in connection pools
   • Audit role assignments regularly using the new reporting views

Error Handling (Enhanced in v1.1)

The extension now provides comprehensive error handling:

-- Helpful error messages with hints
SELECT public.c77_rbac_assign_subject('', 'role', 'scope', 'id');
-- ERROR: external_id cannot be NULL or empty
-- HINT: Provide a valid user identifier

-- Bulk operations show individual results
SELECT * FROM public.c77_rbac_bulk_assign_subjects(
    ARRAY['valid_user', '', 'another_valid_user'],
    'role', 'scope', 'id'
);
-- Returns table showing success/failure for each user

Troubleshooting

No Data Returned

1. Check if c77_rbac.external_id is set correctly
2. Use the new management views to verify permissions:

   SELECT * FROM public.c77_rbac_user_permissions WHERE external_id = 'your_user_id';
   

Performance Issues

1. Check the system summary:

   SELECT * FROM public.c77_rbac_summary;
   

2. Use optimized functions (automatically used in v1.1)
3. Verify indexes exist using \d table_name

Changelog

Version 1.1 (Current)

• ✅ Added bulk assignment operations
• ✅ Added removal/revoke functions
• ✅ Enhanced error handling with helpful messages
• ✅ Added admin sync functions
• ✅ Performance optimizations and better indexes
• ✅ Added management views and utility functions
• ✅ Added audit tracking with timestamps

Version 1.0

• ✅ Core RBAC functionality
• ✅ Row-Level Security integration
• ✅ Basic role and feature management
• ✅ Scope-based permissions

Contributing

This extension is designed to be framework-agnostic. When contributing:

• Keep the core extension simple and focused
• Add framework-specific features to companion extensions
• Include tests for new functionality
• Update documentation for new features

License

MIT License - See LICENSE file for details

Support

• Create an issue for bugs or feature requests
• Check existing issues before creating new ones
• Include PostgreSQL version and reproduction steps for bugs
• Use the new management views to provide system information when reporting issues

---

For comprehensive usage examples and Laravel integration, see USAGE.md.