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

🧠 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');

🏗️ 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

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;

🛠️ 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

Framework-agnostic security that works everywhere!

This extension is designed to work with any application framework. Keep the core extension simple and focused while adding framework-specific features to companion extensions.

View Git Repository →