c77_rbac Usage Guide - Part 4: Real-World Examples and Performance

This is Part 4 of the comprehensive c77_rbac usage guide, covering complete real-world implementations and performance optimization.

Complete Guide Structure:

Part 1: Core Concepts and Basic Usage
Part 2: Advanced Usage Scenarios
Part 3: Framework Integration
Part 4: Real-World Examples and Performance (this document)
Part 5: Security Best Practices and Troubleshooting

Real-World Example: Court Education System
Real-World Example: Multi-Tenant SaaS Platform
Real-World Example: Enterprise Document Management
Performance Optimization

Real-World Example: Court Education System

Complete Court System Implementation

This example shows a full implementation for a court education system with participants, programs, and court staff.

-- Step 1: Create the application tables
CREATE SCHEMA court_system;

CREATE TABLE court_system.courts (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    jurisdiction TEXT NOT NULL,
    address TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE court_system.programs (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    type TEXT NOT NULL, -- 'dui_education', 'anger_management', etc.
    duration_weeks INTEGER NOT NULL,
    court_id INTEGER REFERENCES court_system.courts(id),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE court_system.participants (
    id SERIAL PRIMARY KEY,
    external_user_id TEXT NOT NULL, -- Links to your main user table
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    case_number TEXT,
    assigned_court_id INTEGER REFERENCES court_system.courts(id),
    enrollment_date DATE DEFAULT CURRENT_DATE,
    status TEXT DEFAULT 'active', -- 'active', 'completed', 'terminated'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE court_system.enrollments (
    id SERIAL PRIMARY KEY,
    participant_id INTEGER REFERENCES court_system.participants(id),
    program_id INTEGER REFERENCES court_system.programs(id),
    enrolled_date DATE DEFAULT CURRENT_DATE,
    completion_date DATE,
    status TEXT DEFAULT 'enrolled', -- 'enrolled', 'in_progress', 'completed', 'dropped'
    progress_percentage INTEGER DEFAULT 0,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE court_system.attendance (
    id SERIAL PRIMARY KEY,
    enrollment_id INTEGER REFERENCES court_system.enrollments(id),
    session_date DATE NOT NULL,
    attended BOOLEAN DEFAULT false,
    notes TEXT,
    recorded_by TEXT, -- User ID who recorded attendance
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Set up RBAC roles and features
-- Court Administration
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('court_admin', 'generate_court_reports');
SELECT public.c77_rbac_grant_feature('court_admin', 'manage_court_users');
SELECT public.c77_rbac_grant_feature('court_admin', 'export_court_data');

-- Judges
SELECT public.c77_rbac_grant_feature('judge', 'view_court_participants');
SELECT public.c77_rbac_grant_feature('judge', 'approve_program_completions');
SELECT public.c77_rbac_grant_feature('judge', 'access_court_reports');
SELECT public.c77_rbac_grant_feature('judge', 'view_case_progress');

-- Court Clerks
SELECT public.c77_rbac_grant_feature('court_clerk', 'enroll_participants');
SELECT public.c77_rbac_grant_feature('court_clerk', 'view_court_participants');
SELECT public.c77_rbac_grant_feature('court_clerk', 'update_participant_info');
SELECT public.c77_rbac_grant_feature('court_clerk', 'generate_basic_reports');

-- Program Coordinators
SELECT public.c77_rbac_grant_feature('program_coordinator', 'manage_program_type');
SELECT public.c77_rbac_grant_feature('program_coordinator', 'view_program_participants');
SELECT public.c77_rbac_grant_feature('program_coordinator', 'update_program_progress');
SELECT public.c77_rbac_grant_feature('program_coordinator', 'generate_program_reports');

-- Counselors/Instructors
SELECT public.c77_rbac_grant_feature('counselor', 'view_assigned_participants');
SELECT public.c77_rbac_grant_feature('counselor', 'update_participant_progress');
SELECT public.c77_rbac_grant_feature('counselor', 'mark_attendance');
SELECT public.c77_rbac_grant_feature('counselor', 'create_session_notes');

-- Participants
SELECT public.c77_rbac_grant_feature('participant', 'view_own_progress');
SELECT public.c77_rbac_grant_feature('participant', 'access_program_materials');
SELECT public.c77_rbac_grant_feature('participant', 'view_own_attendance');

-- Step 3: Sync admin features
SELECT public.c77_rbac_sync_admin_features();

-- Step 4: Apply RLS policies
-- Participants can only see their own data
SELECT public.c77_rbac_apply_policy(
    'court_system.participants',
    'view_own_progress',
    'participant',
    'external_user_id'
);

-- Court staff can see participants in their court
SELECT public.c77_rbac_apply_policy(
    'court_system.participants',
    'view_court_participants', 
    'court',
    'assigned_court_id'
);

-- Program staff can see participants in their program type
SELECT public.c77_rbac_apply_policy(
    'court_system.enrollments',
    'view_program_participants',
    'program_type',
    'program_id'
);

-- Attendance records follow enrollment permissions
SELECT public.c77_rbac_apply_policy(
    'court_system.attendance',
    'view_assigned_participants',
    'enrollment',
    'enrollment_id'
);

-- Step 5: Assign initial users
-- System administrator
SELECT public.c77_rbac_assign_subject('1', 'court_admin', 'global', 'all');

-- Superior Court judge
SELECT public.c77_rbac_assign_subject('101', 'judge', 'court', '1');

-- Superior Court clerk
SELECT public.c77_rbac_assign_subject('201', 'court_clerk', 'court', '1');

-- DUI program coordinator
SELECT public.c77_rbac_assign_subject('301', 'program_coordinator', 'program_type', 'dui_education');

-- Anger management counselor
SELECT public.c77_rbac_assign_subject('401', 'counselor', 'program_type', 'anger_management');

-- Step 6: Bulk enroll participants in a program
-- First, let's create some test participants
INSERT INTO court_system.participants (external_user_id, first_name, last_name, case_number, assigned_court_id)
VALUES 
    ('P001', 'John', 'Doe', 'DUI-2024-001', 1),
    ('P002', 'Jane', 'Smith', 'DUI-2024-002', 1),
    ('P003', 'Mike', 'Johnson', 'DUI-2024-003', 1),
    ('P004', 'Sarah', 'Williams', 'AM-2024-001', 1),
    ('P005', 'Tom', 'Brown', 'AM-2024-002', 1);

-- Bulk assign participant role to all participants
SELECT * FROM public.c77_rbac_bulk_assign_subjects(
    ARRAY['P001', 'P002', 'P003', 'P004', 'P005'],
    'participant',
    'court',
    '1'
);

-- Step 7: Example queries showing RLS in action

-- As a participant (P001), only sees own data
SET "c77_rbac.external_id" TO 'P001';
SELECT first_name, last_name, case_number FROM court_system.participants;
-- Returns only John Doe's record

-- As court clerk, sees all participants in their court
SET "c77_rbac.external_id" TO '201';
SELECT first_name, last_name, case_number FROM court_system.participants;
-- Returns all participants in court 1

-- As global admin, sees everything
SET "c77_rbac.external_id" TO '1';
SELECT first_name, last_name, case_number FROM court_system.participants;
-- Returns all participants

-- Step 8: Advanced reporting with RBAC
CREATE VIEW court_system.participant_progress_report AS
SELECT 
    p.first_name,
    p.last_name,
    p.case_number,
    pr.name as program_name,
    e.status as enrollment_status,
    e.progress_percentage,
    e.enrolled_date,
    e.completion_date,
    COUNT(a.id) as total_sessions,
    COUNT(CASE WHEN a.attended THEN 1 END) as attended_sessions
FROM court_system.participants p
JOIN court_system.enrollments e ON p.id = e.participant_id
JOIN court_system.programs pr ON e.program_id = pr.id
LEFT JOIN court_system.attendance a ON e.id = a.enrollment_id
GROUP BY p.id, pr.id, e.id;

-- Apply RLS to the view
SELECT public.c77_rbac_apply_policy(
    'court_system.participant_progress_report',
    'view_court_participants',
    'court',
    'assigned_court_id'
);

Laravel Integration for Court System

<?php
// app/Http/Controllers/CourtSystem/ParticipantController.php

namespace App\Http\Controllers\CourtSystem;

use App\Http\Controllers\Controller;
use App\Services\RbacService;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class ParticipantController extends Controller
{
    protected RbacService $rbac;
    
    public function __construct(RbacService $rbac)
    {
        $this->middleware('rbac.context');
        $this->rbac = $rbac;
    }
    
    public function index(Request $request)
    {
        // Participants automatically filtered by RLS
        $participants = DB::table('court_system.participants as p')
            ->join('court_system.courts as c', 'p.assigned_court_id', '=', 'c.id')
            ->select('p.*', 'c.name as court_name')
            ->orderBy('p.created_at', 'desc')
            ->paginate(20);
            
        return view('court-system.participants.index', compact('participants'));
    }
    
    public function enroll(Request $request)
    {
        // Check if user can enroll participants
        if (!$this->rbac->can('enroll_participants', 'court', $request->court_id)) {
            abort(403, 'Cannot enroll participants in this court');
        }
        
        $validated = $request->validate([
            'participants' => 'required|array',
            'participants.*' => 'required|string',
            'program_id' => 'required|integer|exists:court_system.programs,id',
            'court_id' => 'required|integer|exists:court_system.courts,id'
        ]);
        
        // Bulk enroll participants
        $results = $this->rbac->bulkAssignRoles(
            $validated['participants'],
            'participant',
            'program',
            $validated['program_id']
        );
        
        $successful = collect($results)->where('success', true)->count();
        $failed = collect($results)->where('success', false)->count();
        
        return response()->json([
            'message' => "Enrolled {$successful} participants successfully, {$failed} failed",
            'results' => $results
        ]);
    }
    
    public function progress($participantId)
    {
        // Check permission to view this participant
        $participant = DB::table('court_system.participants')
            ->where('id', $participantId)
            ->first();
            
        if (!$participant) {
            abort(404);
        }
        
        // RLS will automatically filter this query
        $progress = DB::table('court_system.participant_progress_report')
            ->where('case_number', $participant->case_number)
            ->get();
            
        return view('court-system.participants.progress', compact('participant', 'progress'));
    }
}

Real-World Example: Multi-Tenant SaaS Platform

Complete SaaS Implementation

-- Step 1: Create SaaS application schema
CREATE SCHEMA saas_app;

CREATE TABLE saas_app.tenants (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    subdomain TEXT UNIQUE NOT NULL,
    plan TEXT NOT NULL DEFAULT 'basic', -- 'basic', 'pro', 'enterprise'
    max_users INTEGER DEFAULT 10,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE saas_app.projects (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT,
    tenant_id INTEGER REFERENCES saas_app.tenants(id),
    owner_id TEXT NOT NULL, -- External user ID
    status TEXT DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE saas_app.tasks (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    description TEXT,
    project_id INTEGER REFERENCES saas_app.projects(id),
    assigned_to TEXT, -- External user ID
    status TEXT DEFAULT 'todo', -- 'todo', 'in_progress', 'done'
    priority TEXT DEFAULT 'medium', -- 'low', 'medium', 'high'
    due_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE saas_app.comments (
    id SERIAL PRIMARY KEY,
    task_id INTEGER REFERENCES saas_app.tasks(id),
    author_id TEXT NOT NULL, -- External user ID
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Define SaaS RBAC structure
-- Tenant-level roles
SELECT public.c77_rbac_grant_feature('tenant_owner', 'manage_tenant_settings');
SELECT public.c77_rbac_grant_feature('tenant_owner', 'manage_tenant_users');
SELECT public.c77_rbac_grant_feature('tenant_owner', 'view_tenant_billing');
SELECT public.c77_rbac_grant_feature('tenant_owner', 'export_tenant_data');

SELECT public.c77_rbac_grant_feature('tenant_admin', 'manage_tenant_users');
SELECT public.c77_rbac_grant_feature('tenant_admin', 'create_projects');
SELECT public.c77_rbac_grant_feature('tenant_admin', 'view_all_projects');

-- Project-level roles
SELECT public.c77_rbac_grant_feature('project_manager', 'manage_project');
SELECT public.c77_rbac_grant_feature('project_manager', 'assign_tasks');
SELECT public.c77_rbac_grant_feature('project_manager', 'view_project_reports');

SELECT public.c77_rbac_grant_feature('project_member', 'view_project');
SELECT public.c77_rbac_grant_feature('project_member', 'create_tasks');
SELECT public.c77_rbac_grant_feature('project_member', 'update_own_tasks');
SELECT public.c77_rbac_grant_feature('project_member', 'comment_on_tasks');

-- Step 3: Apply tenant isolation
SELECT public.c77_rbac_apply_policy(
    'saas_app.projects',
    'view_tenant_data',
    'tenant',
    'tenant_id'
);

SELECT public.c77_rbac_apply_policy(
    'saas_app.tasks',
    'view_project',
    'project',
    'project_id'
);

SELECT public.c77_rbac_apply_policy(
    'saas_app.comments',
    'comment_on_tasks',
    'project', 
    'task_id' -- This would need a join to get project_id
);

-- Step 4: Sample tenant setup
INSERT INTO saas_app.tenants (name, subdomain, plan, max_users) VALUES
('Acme Corp', 'acme', 'enterprise', 100),
('Startup Inc', 'startup', 'pro', 25),
('Small Biz', 'smallbiz', 'basic', 10);

-- Assign tenant roles
SELECT public.c77_rbac_assign_subject('1001', 'tenant_owner', 'tenant', '1'); -- Acme Corp owner
SELECT public.c77_rbac_assign_subject('1002', 'tenant_admin', 'tenant', '1'); -- Acme Corp admin
SELECT public.c77_rbac_assign_subject('2001', 'tenant_owner', 'tenant', '2'); -- Startup Inc owner

-- Project assignments
SELECT public.c77_rbac_assign_subject('1003', 'project_manager', 'project', '101');
SELECT public.c77_rbac_assign_subject('1004', 'project_member', 'project', '101');
SELECT public.c77_rbac_assign_subject('1005', 'project_member', 'project', '101');

-- Step 5: Complex access patterns
-- User 1004 (project member) can only see/edit their own tasks
CREATE POLICY tasks_own_tasks ON saas_app.tasks
    FOR UPDATE
    USING (
        assigned_to = current_setting('c77_rbac.external_id', true) OR
        public.c77_rbac_can_access('assign_tasks', 
            current_setting('c77_rbac.external_id', true), 
            'project', 
            project_id::text)
    );

ALTER TABLE saas_app.tasks ENABLE ROW LEVEL SECURITY;

Node.js SaaS Implementation

// src/services/tenant.service.ts

import { RBACService } from './rbac.service';
import { Pool } from 'pg';

export interface Tenant {
  id: number;
  name: string;
  subdomain: string;
  plan: string;
  max_users: number;
}

export interface Project {
  id: number;
  name: string;
  tenant_id: number;
  owner_id: string;
  status: string;
}

export class TenantService {
  constructor(
    private db: Pool,
    private rbacService: RBACService
  ) {}

  async createTenant(tenantData: Omit<Tenant, 'id'>, ownerId: string): Promise<Tenant> {
    const client = await this.db.connect();
    
    try {
      await client.query('BEGIN');
      
      // Create tenant
      const tenantResult = await client.query(`
        INSERT INTO saas_app.tenants (name, subdomain, plan, max_users)
        VALUES ($1, $2, $3, $4)
        RETURNING *
      `, [tenantData.name, tenantData.subdomain, tenantData.plan, tenantData.max_users]);
      
      const tenant = tenantResult.rows[0];
      
      // Assign owner role
      await this.rbacService.assignRole(
        ownerId,
        'tenant_owner',
        'tenant',
        tenant.id.toString()
      );
      
      await client.query('COMMIT');
      return tenant;
      
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async addUserToTenant(
    tenantId: number,
    userId: string,
    role: string = 'project_member'
  ): Promise<boolean> {
    try {
      // Check if current user can manage tenant users
      const canManage = await this.rbacService.canAccess(
        'manage_tenant_users',
        userId,
        'tenant',
        tenantId.toString()
      );
      
      if (!canManage) {
        throw new Error('Permission denied: cannot manage tenant users');
      }
      
      // Check tenant user limits
      const userCount = await this.db.query(`
        SELECT COUNT(DISTINCT s.external_id) as count
        FROM c77_rbac_subjects s
        JOIN c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
        WHERE sr.scope_type = 'tenant' AND sr.scope_id = $1
      `, [tenantId.toString()]);
      
      const tenant = await this.db.query(
        'SELECT max_users FROM saas_app.tenants WHERE id = $1',
        [tenantId]
      );
      
      if (userCount.rows[0].count >= tenant.rows[0].max_users) {
        throw new Error('Tenant user limit reached');
      }
      
      // Add user to tenant
      return await this.rbacService.assignRole(
        userId,
        role,
        'tenant',
        tenantId.toString()
      );
      
    } catch (error) {
      console.error('Failed to add user to tenant:', error);
      return false;
    }
  }

  async createProject(
    tenantId: number,
    projectData: Omit<Project, 'id' | 'tenant_id'>,
    userId: string
  ): Promise<Project> {
    // Check if user can create projects in this tenant
    const canCreate = await this.rbacService.canAccess(
      'create_projects',
      userId,
      'tenant',
      tenantId.toString()
    );
    
    if (!canCreate) {
      throw new Error('Permission denied: cannot create projects in this tenant');
    }
    
    const client = await this.db.connect();
    
    try {
      await client.query('BEGIN');
      
      // Create project
      const projectResult = await client.query(`
        INSERT INTO saas_app.projects (name, description, tenant_id, owner_id, status)
        VALUES ($1, $2, $3, $4, $5)
        RETURNING *
      `, [projectData.name, projectData.description, tenantId, userId, projectData.status]);
      
      const project = projectResult.rows[0];
      
      // Assign project manager role to creator
      await this.rbacService.assignRole(
        userId,
        'project_manager',
        'project',
        project.id.toString()
      );
      
      await client.query('COMMIT');
      return project;
      
    } catch (error) {
      await client.query('ROLLBACK');
      throw error;
    } finally {
      client.release();
    }
  }

  async getUserTenants(userId: string): Promise<Tenant[]> {
    const result = await this.db.query(`
      SELECT DISTINCT t.*
      FROM saas_app.tenants t
      JOIN c77_rbac_subject_roles sr ON sr.scope_id = t.id::text
      JOIN c77_rbac_subjects s ON s.subject_id = sr.subject_id
      WHERE s.external_id = $1 AND sr.scope_type = 'tenant'
      ORDER BY t.name
    `, [userId]);
    
    return result.rows;
  }

  async getTenantProjects(tenantId: number, userId: string): Promise<Project[]> {
    // Set user context for RLS
    await this.db.query('SET "c77_rbac.external_id" TO $1', [userId]);
    
    const result = await this.db.query(`
      SELECT * FROM saas_app.projects
      WHERE tenant_id = $1
      ORDER BY created_at DESC
    `, [tenantId]);
    
    return result.rows;
  }
}

Real-World Example: Enterprise Document Management

Document Management System

-- Step 1: Create document management schema
CREATE SCHEMA document_mgmt;

CREATE TABLE document_mgmt.categories (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    department_id TEXT NOT NULL,
    security_level TEXT DEFAULT 'internal', -- 'public', 'internal', 'confidential', 'secret'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE document_mgmt.documents (
    id SERIAL PRIMARY KEY,
    title TEXT NOT NULL,
    filename TEXT NOT NULL,
    file_path TEXT NOT NULL,
    category_id INTEGER REFERENCES document_mgmt.categories(id),
    author_id TEXT NOT NULL,
    department_id TEXT NOT NULL,
    security_level TEXT DEFAULT 'internal',
    status TEXT DEFAULT 'draft', -- 'draft', 'review', 'approved', 'archived'
    version INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE document_mgmt.document_access_log (
    id SERIAL PRIMARY KEY,
    document_id INTEGER REFERENCES document_mgmt.documents(id),
    user_id TEXT NOT NULL,
    action TEXT NOT NULL, -- 'view', 'download', 'edit', 'delete'
    ip_address INET,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Step 2: Security-level based RBAC
SELECT public.c77_rbac_grant_feature('security_officer', 'access_secret_documents');
SELECT public.c77_rbac_grant_feature('security_officer', 'manage_document_security');
SELECT public.c77_rbac_grant_feature('security_officer', 'view_access_logs');

SELECT public.c77_rbac_grant_feature('dept_head', 'access_confidential_documents');
SELECT public.c77_rbac_grant_feature('dept_head', 'approve_dept_documents');
SELECT public.c77_rbac_grant_feature('dept_head', 'view_dept_access_logs');

SELECT public.c77_rbac_grant_feature('manager', 'access_internal_documents');
SELECT public.c77_rbac_grant_feature('manager', 'create_documents');
SELECT public.c77_rbac_grant_feature('manager', 'edit_dept_documents');

SELECT public.c77_rbac_grant_feature('employee', 'access_public_documents');
SELECT public.c77_rbac_grant_feature('employee', 'view_own_documents');

-- Step 3: Complex RLS policy for security levels
CREATE OR REPLACE FUNCTION document_mgmt.can_access_document(
    p_user_id TEXT,
    p_document_id INTEGER
) RETURNS BOOLEAN AS $
DECLARE
    doc_security_level TEXT;
    doc_department TEXT;
    doc_author TEXT;
BEGIN
    -- Get document details
    SELECT security_level, department_id, author_id 
    INTO doc_security_level, doc_department, doc_author
    FROM document_mgmt.documents 
    WHERE id = p_document_id;
    
    -- Authors can always access their own documents
    IF doc_author = p_user_id THEN
        RETURN TRUE;
    END IF;
    
    -- Check security level permissions
    CASE doc_security_level
        WHEN 'public' THEN
            RETURN public.c77_rbac_can_access('access_public_documents', p_user_id, 'global', 'all');
        WHEN 'internal' THEN
            RETURN public.c77_rbac_can_access('access_internal_documents', p_user_id, 'department', doc_department);
        WHEN 'confidential' THEN
            RETURN public.c77_rbac_can_access('access_confidential_documents', p_user_id, 'department', doc_department);
        WHEN 'secret' THEN
            RETURN public.c77_rbac_can_access('access_secret_documents', p_user_id, 'global', 'all');
        ELSE
            RETURN FALSE;
    END CASE;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- Apply the complex policy
CREATE POLICY document_security_policy ON document_mgmt.documents
    FOR ALL
    USING (document_mgmt.can_access_document(
        current_setting('c77_rbac.external_id', true)::TEXT, 
        id
    ));

ALTER TABLE document_mgmt.documents ENABLE ROW LEVEL SECURITY;

-- Step 4: Audit logging with RBAC
CREATE OR REPLACE FUNCTION document_mgmt.log_document_access()
RETURNS TRIGGER AS $
BEGIN
    INSERT INTO document_mgmt.document_access_log (
        document_id, 
        user_id, 
        action, 
        ip_address
    ) VALUES (
        NEW.id,
        current_setting('c77_rbac.external_id', true),
        'view',
        inet_client_addr()
    );
    RETURN NEW;
END;
$ LANGUAGE plpgsql;

CREATE TRIGGER document_access_audit
    AFTER SELECT ON document_mgmt.documents
    FOR EACH ROW EXECUTE FUNCTION document_mgmt.log_document_access();

Performance Optimization

Query Performance Best Practices

-- 1. Analyze slow queries
EXPLAIN (ANALYZE, BUFFERS) 
SELECT * FROM posts WHERE department_id = 'engineering';

-- 2. Create appropriate indexes for RLS columns
CREATE INDEX CONCURRENTLY idx_posts_department_id ON posts(department_id);
CREATE INDEX CONCURRENTLY idx_users_tenant_id ON users(tenant_id);

-- 3. Use partial indexes for common query patterns
CREATE INDEX CONCURRENTLY idx_active_participants 
ON participants(assigned_court_id) 
WHERE status = 'active';

-- 4. Composite indexes for multi-column RLS
CREATE INDEX CONCURRENTLY idx_tasks_project_assigned 
ON tasks(project_id, assigned_to);

-- 5. Function-based indexes for computed values
CREATE INDEX CONCURRENTLY idx_enrollments_completion_status 
ON enrollments((CASE WHEN completion_date IS NOT NULL THEN 'completed' ELSE 'active' END));

Caching Strategies

-- 1. Create materialized view for expensive permission calculations
CREATE MATERIALIZED VIEW user_effective_permissions AS
SELECT 
    s.external_id,
    f.name as feature,
    sr.scope_type,
    sr.scope_id,
    'direct' as permission_source
FROM c77_rbac_subjects s
JOIN c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
JOIN c77_rbac_role_features rf ON sr.role_id = rf.role_id
JOIN c77_rbac_features f ON rf.feature_id = f.feature_id

UNION ALL

-- Include inherited permissions for hierarchical structures
SELECT 
    s.external_id,
    f.name as feature,
    'global' as scope_type,
    'all' as scope_id,
    'inherited' as permission_source
FROM c77_rbac_subjects s
JOIN c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
JOIN c77_rbac_role_features rf ON sr.role_id = rf.role_id
JOIN c77_rbac_features f ON rf.feature_id = f.feature_id
WHERE sr.scope_type = 'global' AND sr.scope_id = 'all';

-- Index the materialized view
CREATE UNIQUE INDEX idx_user_permissions_unique 
ON user_effective_permissions(external_id, feature, scope_type, scope_id);

-- Refresh strategy (run via cron or trigger)
CREATE OR REPLACE FUNCTION refresh_user_permissions() 
RETURNS void AS $
BEGIN
    REFRESH MATERIALIZED VIEW CONCURRENTLY user_effective_permissions;
    PERFORM pg_notify('permissions_refreshed', 'materialized view updated');
END;
$ LANGUAGE plpgsql;

-- 2. Optimized permission check using materialized view
CREATE OR REPLACE FUNCTION public.c77_rbac_can_access_fast(
    p_feature_name TEXT,
    p_external_id TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS BOOLEAN AS $
BEGIN
    -- Fast lookup in materialized view
    RETURN EXISTS (
        SELECT 1 FROM user_effective_permissions
        WHERE external_id = p_external_id
        AND feature = p_feature_name
        AND (
            (scope_type = 'global' AND scope_id = 'all') OR
            (scope_type = p_scope_type AND scope_id = p_scope_id)
        )
    );
END;
$ LANGUAGE plpgsql STABLE;

Bulk Operations Performance

-- 1. Efficient bulk role assignment with batch processing
CREATE OR REPLACE FUNCTION public.c77_rbac_bulk_assign_subjects_batched(
    p_external_ids TEXT[],
    p_role_name TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT,
    p_batch_size INTEGER DEFAULT 1000
) RETURNS TABLE(external_id TEXT, success BOOLEAN, error_message TEXT) AS $
DECLARE
    v_batch_start INTEGER := 1;
    v_batch_end INTEGER;
    v_current_batch TEXT[];
    v_batch_result RECORD;
BEGIN
    WHILE v_batch_start <= array_length(p_external_ids, 1) LOOP
        v_batch_end := LEAST(v_batch_start + p_batch_size - 1, array_length(p_external_ids, 1));
        v_current_batch := p_external_ids[v_batch_start:v_batch_end];
        
        -- Process current batch
        FOR v_batch_result IN 
            SELECT * FROM public.c77_rbac_bulk_assign_subjects(
                v_current_batch, p_role_name, p_scope_type, p_scope_id
            )
        LOOP
            external_id := v_batch_result.external_id;
            success := v_batch_result.success;
            error_message := v_batch_result.error_message;
            RETURN NEXT;
        END LOOP;
        
        v_batch_start := v_batch_end + 1;
        
        -- Optional: Add small delay between batches to reduce load
        PERFORM pg_sleep(0.1);
    END LOOP;
    
    RAISE NOTICE 'Completed batched assignment for % users in batches of %', 
        array_length(p_external_ids, 1), p_batch_size;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Optimized bulk removal
CREATE OR REPLACE FUNCTION public.c77_rbac_bulk_revoke_subject_roles(
    p_external_ids TEXT[],
    p_role_name TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS INTEGER AS $
DECLARE
    v_removed_count INTEGER;
BEGIN
    -- Single DELETE operation for better performance
    DELETE FROM public.c77_rbac_subject_roles sr
    WHERE sr.subject_id IN (
        SELECT subject_id FROM public.c77_rbac_subjects 
        WHERE external_id = ANY(p_external_ids)
    )
    AND sr.role_id = (
        SELECT role_id FROM public.c77_rbac_roles 
        WHERE name = p_role_name
    )
    AND sr.scope_type = p_scope_type
    AND sr.scope_id = p_scope_id;
    
    GET DIAGNOSTICS v_removed_count = ROW_COUNT;
    
    RAISE NOTICE 'Bulk revoked % role assignments', v_removed_count;
    RETURN v_removed_count;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Application-Level Caching

// Laravel Redis caching example
class OptimizedRbacService extends RbacService
{
    protected $redis;
    protected $cachePrefix = 'rbac:';
    protected $cacheTtl = 300; // 5 minutes
    
    public function __construct()
    {
        parent::__construct();
        $this->redis = Redis::connection();
    }
    
    public function can(string $feature, string $scopeType = 'global', string $scopeId = 'all'): bool
    {
        if (!Auth::check()) {
            return false;
        }
        
        $userId = Auth::id();
        $cacheKey = $this->cachePrefix . "can:{$userId}:{$feature}:{$scopeType}:{$scopeId}";
        
        // Try cache first
        $cached = $this->redis->get($cacheKey);
        if ($cached !== null) {
            return (bool) $cached;
        }
        
        // Fallback to parent implementation
        $result = parent::can($feature, $scopeType, $scopeId);
        
        // Cache the result
        $this->redis->setex($cacheKey, $this->cacheTtl, (int) $result);
        
        return $result;
    }
    
    protected function clearUserCache(int $userId): void
    {
        parent::clearUserCache($userId);
        
        // Clear Redis cache patterns
        $pattern = $this->cachePrefix . "*:{$userId}:*";
        $keys = $this->redis->keys($pattern);
        
        if (!empty($keys)) {
            $this->redis->del($keys);
        }
    }
    
    public function warmupUserCache(int $userId, array $features = []): void
    {
        // Pre-populate common permission checks
        $commonScopes = [
            ['global', 'all'],
            ['department', $this->getUserDepartment($userId)],
            ['tenant', $this->getUserTenant($userId)]
        ];
        
        foreach ($features as $feature) {
            foreach ($commonScopes as [$scopeType, $scopeId]) {
                if ($scopeId) {
                    $this->can($feature, $scopeType, $scopeId);
                }
            }
        }
    }
}

Continue to Part 5: Security Best Practices and Troubleshooting for security guidelines, debugging techniques, and production considerations.

32 KiB Raw Permalink Blame History