trogers1884 10f8637413 c77_rbac v1.1

2025-05-23 23:29:45 -05:00

30 KiB

Raw Permalink Blame History

c77_rbac Usage Guide - Part 5: Security Best Practices and Troubleshooting

This is the final part of the comprehensive c77_rbac usage guide, covering security best practices, troubleshooting, and production guidelines.

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
Part 5: Security Best Practices and Troubleshooting (this document)

Security Best Practices
Troubleshooting and Debugging
Production Guidelines
API Reference

Security Best Practices

Input Validation and Sanitization

-- 1. Enhanced input validation function
CREATE OR REPLACE FUNCTION public.validate_rbac_inputs(
    p_external_id TEXT,
    p_role_name TEXT DEFAULT NULL,
    p_scope_type TEXT DEFAULT NULL,
    p_scope_id TEXT DEFAULT NULL,
    p_feature_name TEXT DEFAULT NULL
) RETURNS void AS $$
BEGIN
    -- External ID validation
    IF p_external_id IS NULL OR length(trim(p_external_id)) = 0 THEN
        RAISE EXCEPTION 'external_id cannot be NULL or empty'
            USING ERRCODE = 'invalid_parameter_value';
    END IF;
    
    IF length(p_external_id) > 255 THEN
        RAISE EXCEPTION 'external_id too long (max 255 characters)'
            USING ERRCODE = 'invalid_parameter_value';
    END IF;
    
    -- Role name validation
    IF p_role_name IS NOT NULL THEN
        IF length(trim(p_role_name)) = 0 THEN
            RAISE EXCEPTION 'role_name cannot be empty'
                USING ERRCODE = 'invalid_parameter_value';
        END IF;
        
        IF p_role_name !~ '^[a-zA-Z0-9_-]+$' THEN
            RAISE EXCEPTION 'role_name contains invalid characters (use only letters, numbers, underscore, hyphen)'
                USING ERRCODE = 'invalid_parameter_value';
        END IF;
    END IF;
    
    -- Scope type validation
    IF p_scope_type IS NOT NULL THEN
        IF length(trim(p_scope_type)) = 0 THEN
            RAISE EXCEPTION 'scope_type cannot be empty'
                USING ERRCODE = 'invalid_parameter_value';
        END IF;
        
        IF p_scope_type !~ '^[a-zA-Z0-9_]+$' THEN
            RAISE EXCEPTION 'scope_type contains invalid characters'
                USING ERRCODE = 'invalid_parameter_value';
        END IF;
    END IF;
    
    -- Feature name validation
    IF p_feature_name IS NOT NULL THEN
        IF length(trim(p_feature_name)) = 0 THEN
            RAISE EXCEPTION 'feature_name cannot be empty'
                USING ERRCODE = 'invalid_parameter_value';
        END IF;
        
        IF p_feature_name !~ '^[a-zA-Z0-9_]+$' THEN
            RAISE EXCEPTION 'feature_name contains invalid characters'
                USING ERRCODE = 'invalid_parameter_value';
        END IF;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- 2. SQL injection prevention in dynamic policies
CREATE OR REPLACE FUNCTION public.c77_rbac_apply_policy_secure(
    p_table_name TEXT,
    p_feature_name TEXT,
    p_scope_type TEXT,
    p_scope_column TEXT
) RETURNS VOID AS $$
DECLARE
    v_schema_name TEXT;
    v_table_name TEXT;
    v_quoted_table TEXT;
    v_quoted_column TEXT;
BEGIN
    -- Validate all inputs
    PERFORM public.validate_rbac_inputs(
        'dummy', NULL, p_scope_type, NULL, p_feature_name
    );
    
    -- Validate table name format
    IF p_table_name !~ '^[a-zA-Z_][a-zA-Z0-9_]*(\.[a-zA-Z_][a-zA-Z0-9_]*)?$' THEN
        RAISE EXCEPTION 'Invalid table name format'
            USING ERRCODE = 'invalid_parameter_value';
    END IF;
    
    -- Validate column name
    IF p_scope_column !~ '^[a-zA-Z_][a-zA-Z0-9_]*$' THEN
        RAISE EXCEPTION 'Invalid column name format'
            USING ERRCODE = 'invalid_parameter_value';
    END IF;
    
    -- Parse and quote identifiers safely
    IF position('.' IN p_table_name) > 0 THEN
        v_schema_name := split_part(p_table_name, '.', 1);
        v_table_name := split_part(p_table_name, '.', 2);
        v_quoted_table := quote_ident(v_schema_name) || '.' || quote_ident(v_table_name);
    ELSE
        v_quoted_table := quote_ident(p_table_name);
    END IF;
    
    v_quoted_column := quote_ident(p_scope_column);
    
    -- Verify table and column exist
    PERFORM public.c77_rbac_verify_table_column(p_table_name, p_scope_column);
    
    -- Create policy with proper quoting
    EXECUTE format(
        'CREATE POLICY c77_rbac_policy ON %s FOR ALL TO PUBLIC USING (
            public.c77_rbac_can_access(%L, current_setting(%L, true), %L, %s::text)
        )',
        v_quoted_table,
        p_feature_name,
        'c77_rbac.external_id',
        p_scope_type,
        v_quoted_column
    );
    
    -- Enable RLS
    EXECUTE format('ALTER TABLE %s ENABLE ROW LEVEL SECURITY', v_quoted_table);
    EXECUTE format('ALTER TABLE %s FORCE ROW LEVEL SECURITY', v_quoted_table);
    
    RAISE NOTICE 'Applied secure RLS policy to %', v_quoted_table;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

Audit and Monitoring

-- 1. Comprehensive audit logging
CREATE TABLE public.c77_rbac_audit_log (
    id BIGSERIAL PRIMARY KEY,
    action TEXT NOT NULL, -- 'role_assigned', 'role_revoked', 'feature_granted', etc.
    subject_external_id TEXT,
    role_name TEXT,
    feature_name TEXT,
    scope_type TEXT,
    scope_id TEXT,
    performed_by TEXT,
    ip_address INET,
    user_agent TEXT,
    session_id TEXT,
    success BOOLEAN NOT NULL,
    error_message TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_rbac_audit_log_created_at ON public.c77_rbac_audit_log(created_at);
CREATE INDEX idx_rbac_audit_log_subject ON public.c77_rbac_audit_log(subject_external_id);
CREATE INDEX idx_rbac_audit_log_action ON public.c77_rbac_audit_log(action);

-- 2. Audit logging function
CREATE OR REPLACE FUNCTION public.c77_rbac_log_action(
    p_action TEXT,
    p_subject_external_id TEXT DEFAULT NULL,
    p_role_name TEXT DEFAULT NULL,
    p_feature_name TEXT DEFAULT NULL,
    p_scope_type TEXT DEFAULT NULL,
    p_scope_id TEXT DEFAULT NULL,
    p_success BOOLEAN DEFAULT TRUE,
    p_error_message TEXT DEFAULT NULL
) RETURNS void AS $
BEGIN
    INSERT INTO public.c77_rbac_audit_log (
        action,
        subject_external_id,
        role_name,
        feature_name,
        scope_type,
        scope_id,
        performed_by,
        ip_address,
        success,
        error_message
    ) VALUES (
        p_action,
        p_subject_external_id,
        p_role_name,
        p_feature_name,
        p_scope_type,
        p_scope_id,
        current_setting('c77_rbac.external_id', true),
        inet_client_addr(),
        p_success,
        p_error_message
    );
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3. Enhanced functions with audit logging
CREATE OR REPLACE FUNCTION public.c77_rbac_assign_subject_audited(
    p_external_id TEXT,
    p_role_name TEXT,
    p_scope_type TEXT,
    p_scope_id TEXT
) RETURNS VOID AS $
BEGIN
    -- Perform the assignment
    PERFORM public.c77_rbac_assign_subject(p_external_id, p_role_name, p_scope_type, p_scope_id);
    
    -- Log success
    PERFORM public.c77_rbac_log_action(
        'role_assigned',
        p_external_id,
        p_role_name,
        NULL,
        p_scope_type,
        p_scope_id,
        TRUE
    );
    
EXCEPTION WHEN OTHERS THEN
    -- Log failure
    PERFORM public.c77_rbac_log_action(
        'role_assigned',
        p_external_id,
        p_role_name,
        NULL,
        p_scope_type,
        p_scope_id,
        FALSE,
        SQLERRM
    );
    
    -- Re-raise the exception
    RAISE;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Session Management

-- 1. Session context validation
CREATE OR REPLACE FUNCTION public.c77_rbac_validate_session_context()
RETURNS BOOLEAN AS $
DECLARE
    v_external_id TEXT;
    v_session_start TIMESTAMP;
BEGIN
    -- Get current external_id
    v_external_id := current_setting('c77_rbac.external_id', true);
    
    -- Check if external_id is set
    IF v_external_id IS NULL OR v_external_id = '' THEN
        RETURN FALSE;
    END IF;
    
    -- Verify the user exists in our system
    IF NOT EXISTS (SELECT 1 FROM public.c77_rbac_subjects WHERE external_id = v_external_id) THEN
        RAISE WARNING 'RBAC context set for non-existent user: %', v_external_id;
        RETURN FALSE;
    END IF;
    
    RETURN TRUE;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Automatic session cleanup
CREATE OR REPLACE FUNCTION public.c77_rbac_cleanup_stale_sessions()
RETURNS INTEGER AS $
DECLARE
    v_cleanup_count INTEGER := 0;
BEGIN
    -- In a real implementation, you might track active sessions
    -- and clean up orphaned session variables
    
    -- For now, just log current sessions
    RAISE NOTICE 'Current RBAC context: %', 
        current_setting('c77_rbac.external_id', true);
    
    RETURN v_cleanup_count;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Application-Level Security

// Laravel security middleware example
<?php

namespace App\Http\Middleware;

use Closure;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\Auth;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;
use Illuminate\Support\Facades\RateLimiter;

class SecureRbacContext
{
    public function handle(Request $request, Closure $next)
    {
        if (!Auth::check()) {
            return $next($request);
        }
        
        $userId = Auth::id();
        
        // Rate limiting for permission checks
        $key = 'rbac-context:' . $userId;
        if (RateLimiter::tooManyAttempts($key, 100)) {
            Log::warning('RBAC context rate limit exceeded', ['user_id' => $userId]);
            abort(429, 'Too many requests');
        }
        
        RateLimiter::hit($key, 60); // 100 requests per minute
        
        try {
            // Validate user exists and is active
            $user = Auth::user();
            if (!$user->is_active) {
                Log::warning('Inactive user attempted RBAC context', ['user_id' => $userId]);
                Auth::logout();
                abort(403, 'Account deactivated');
            }
            
            // Set RBAC context with validation
            DB::statement('SET "c77_rbac.external_id" TO ?', [$userId]);
            
            // Verify context was set correctly
            $setContext = DB::selectOne('SELECT current_setting(?, true) as context', 
                ['c77_rbac.external_id'])->context;
                
            if ($setContext !== (string)$userId) {
                Log::error('RBAC context validation failed', [
                    'user_id' => $userId,
                    'set_context' => $setContext
                ]);
                abort(500, 'Security context error');
            }
            
        } catch (\Exception $e) {
            Log::error('Failed to set secure RBAC context', [
                'user_id' => $userId,
                'error' => $e->getMessage(),
                'trace' => $e->getTraceAsString()
            ]);
            abort(500, 'Security initialization failed');
        }
        
        $response = $next($request);
        
        // Clean up context
        try {
            DB::statement('RESET "c77_rbac.external_id"');
        } catch (\Exception $e) {
            Log::debug('Failed to cleanup RBAC context', ['error' => $e->getMessage()]);
        }
        
        return $response;
    }
}

Troubleshooting and Debugging

Common Issues and Solutions

Issue 1: RLS Policy Not Working

-- Diagnostic queries for RLS issues

-- 1. Check if RLS is enabled on table
SELECT schemaname, tablename, rowsecurity 
FROM pg_tables 
WHERE tablename = 'your_table_name';

-- 2. Check existing policies
SELECT schemaname, tablename, policyname, permissive, roles, cmd, qual
FROM pg_policies 
WHERE tablename = 'your_table_name';

-- 3. Verify current session context
SELECT current_setting('c77_rbac.external_id', true) as current_user_id;

-- 4. Test permission directly
SELECT public.c77_rbac_can_access('your_feature', 'your_user_id', 'scope_type', 'scope_id');

-- 5. Check user roles
SELECT * FROM public.c77_rbac_get_user_roles('your_user_id');

-- 6. Debug query execution plan
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM your_table;

Issue 2: Performance Problems

-- Performance diagnostic queries

-- 1. Identify slow RBAC queries
SELECT 
    query,
    mean_exec_time,
    calls,
    total_exec_time
FROM pg_stat_statements 
WHERE query ILIKE '%c77_rbac%'
ORDER BY mean_exec_time DESC;

-- 2. Check index usage
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes 
WHERE tablename LIKE 'c77_rbac_%'
ORDER BY idx_scan DESC;

-- 3. Analyze table statistics
SELECT 
    schemaname,
    tablename,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_live_tup,
    n_dead_tup
FROM pg_stat_user_tables 
WHERE tablename LIKE 'c77_rbac_%';

-- 4. Check for missing indexes
SELECT 
    t.tablename,
    c.column_name
FROM information_schema.tables t
JOIN information_schema.columns c ON t.table_name = c.table_name
WHERE t.table_schema = 'public' 
AND t.table_name LIKE 'c77_rbac_%'
AND c.column_name IN ('external_id', 'role_id', 'subject_id', 'scope_type', 'scope_id')
AND NOT EXISTS (
    SELECT 1 FROM pg_indexes i 
    WHERE i.tablename = t.table_name 
    AND i.indexdef ILIKE '%' || c.column_name || '%'
);

Issue 3: Permission Denied Errors

-- Debug permission issues

-- 1. Check function permissions
SELECT 
    routine_name,
    routine_type,
    security_type,
    definer_privileges
FROM information_schema.routines 
WHERE routine_name LIKE 'c77_rbac_%';

-- 2. Verify user grants
SELECT 
    grantee,
    privilege_type,
    is_grantable
FROM information_schema.routine_privileges 
WHERE routine_name LIKE 'c77_rbac_%'
AND grantee = 'your_app_user';

-- 3. Check table permissions
SELECT 
    table_name,
    privilege_type,
    is_grantable
FROM information_schema.table_privileges 
WHERE table_name LIKE 'c77_rbac_%'
AND grantee = 'your_app_user';

-- 4. Comprehensive permission check
CREATE OR REPLACE FUNCTION debug_user_permissions(p_user_id TEXT)
RETURNS TABLE(
    check_type TEXT,
    check_result TEXT,
    details TEXT
) AS $
BEGIN
    -- Check if user exists in RBAC
    RETURN QUERY
    SELECT 
        'User Exists'::TEXT,
        CASE WHEN EXISTS (SELECT 1 FROM public.c77_rbac_subjects WHERE external_id = p_user_id)
            THEN 'YES' ELSE 'NO' END,
        'User record in c77_rbac_subjects'::TEXT;
    
    -- Check user roles
    RETURN QUERY
    SELECT 
        'Role Count'::TEXT,
        count(*)::TEXT,
        'Total roles assigned to user'::TEXT
    FROM public.c77_rbac_subjects s
    JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
    WHERE s.external_id = p_user_id;
    
    -- Check features available
    RETURN QUERY
    SELECT 
        'Feature Count'::TEXT,
        count(DISTINCT f.name)::TEXT,
        'Total features available to user'::TEXT
    FROM public.c77_rbac_subjects s
    JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
    JOIN public.c77_rbac_role_features rf ON sr.role_id = rf.role_id
    JOIN public.c77_rbac_features f ON rf.feature_id = f.feature_id
    WHERE s.external_id = p_user_id;
    
    -- Check for admin access
    RETURN QUERY
    SELECT 
        'Admin Access'::TEXT,
        CASE WHEN EXISTS (
            SELECT 1 FROM public.c77_rbac_subjects s
            JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
            WHERE s.external_id = p_user_id
            AND sr.scope_type = 'global' 
            AND sr.scope_id = 'all'
        ) THEN 'YES' ELSE 'NO' END,
        'User has global admin access'::TEXT;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- Usage: SELECT * FROM debug_user_permissions('your_user_id');

Issue 4: Data Not Visible Despite Permissions

-- Debug data visibility issues

-- 1. Check RLS bypass for debugging (use with caution!)
CREATE OR REPLACE FUNCTION debug_rls_bypass(
    p_table_name TEXT,
    p_user_id TEXT
) RETURNS TABLE(total_rows BIGINT, visible_rows BIGINT) AS $
DECLARE
    v_total BIGINT;
    v_visible BIGINT;
BEGIN
    -- Count total rows (bypassing RLS)
    EXECUTE format('SELECT count(*) FROM %I', p_table_name) INTO v_total;
    
    -- Set user context and count visible rows
    PERFORM set_config('c77_rbac.external_id', p_user_id, true);
    EXECUTE format('SELECT count(*) FROM %I', p_table_name) INTO v_visible;
    
    total_rows := v_total;
    visible_rows := v_visible;
    
    RETURN NEXT;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Analyze specific row access
CREATE OR REPLACE FUNCTION debug_row_access(
    p_table_name TEXT,
    p_row_id INTEGER,
    p_user_id TEXT
) RETURNS TABLE(can_access BOOLEAN, reason TEXT) AS $
DECLARE
    v_policy_condition TEXT;
    v_result BOOLEAN;
BEGIN
    -- Get the policy condition for the table
    SELECT qual INTO v_policy_condition
    FROM pg_policies 
    WHERE tablename = p_table_name 
    AND policyname = 'c77_rbac_policy';
    
    IF v_policy_condition IS NULL THEN
        can_access := FALSE;
        reason := 'No RBAC policy found on table';
        RETURN NEXT;
        RETURN;
    END IF;
    
    -- Set user context
    PERFORM set_config('c77_rbac.external_id', p_user_id, true);
    
    -- Test access to specific row
    EXECUTE format('SELECT EXISTS(SELECT 1 FROM %I WHERE id = %s)', p_table_name, p_row_id) 
    INTO v_result;
    
    can_access := v_result;
    reason := CASE WHEN v_result THEN 'Access granted' ELSE 'Access denied by RLS policy' END;
    
    RETURN NEXT;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Debugging Tools and Utilities

-- 1. RBAC health check function
CREATE OR REPLACE FUNCTION public.c77_rbac_health_check()
RETURNS TABLE(
    check_name TEXT,
    status TEXT,
    message TEXT,
    recommendation TEXT
) AS $
DECLARE
    v_orphaned_subjects INTEGER;
    v_orphaned_roles INTEGER;
    v_unused_features INTEGER;
    v_policy_count INTEGER;
BEGIN
    -- Check for orphaned subjects (users with no roles)
    SELECT count(*) INTO v_orphaned_subjects
    FROM public.c77_rbac_subjects s
    LEFT JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
    WHERE sr.subject_id IS NULL;
    
    RETURN QUERY SELECT 
        'Orphaned Subjects'::TEXT,
        CASE WHEN v_orphaned_subjects = 0 THEN 'OK' ELSE 'WARNING' END,
        format('%s subjects without roles', v_orphaned_subjects),
        CASE WHEN v_orphaned_subjects > 0 THEN 'Clean up unused subjects' ELSE 'None' END;
    
    -- Check for roles without features
    SELECT count(*) INTO v_orphaned_roles
    FROM public.c77_rbac_roles r
    LEFT JOIN public.c77_rbac_role_features rf ON r.role_id = rf.role_id
    WHERE rf.role_id IS NULL;
    
    RETURN QUERY SELECT 
        'Roles Without Features'::TEXT,
        CASE WHEN v_orphaned_roles = 0 THEN 'OK' ELSE 'WARNING' END,
        format('%s roles without features', v_orphaned_roles),
        CASE WHEN v_orphaned_roles > 0 THEN 'Assign features to roles or remove unused roles' ELSE 'None' END;
    
    -- Check for unused features
    SELECT count(*) INTO v_unused_features
    FROM public.c77_rbac_features f
    LEFT JOIN public.c77_rbac_role_features rf ON f.feature_id = rf.feature_id
    WHERE rf.feature_id IS NULL;
    
    RETURN QUERY SELECT 
        'Unused Features'::TEXT,
        CASE WHEN v_unused_features = 0 THEN 'OK' ELSE 'INFO' END,
        format('%s features not assigned to any role', v_unused_features),
        CASE WHEN v_unused_features > 0 THEN 'Consider removing unused features' ELSE 'None' END;
    
    -- Check active policies
    SELECT count(*) INTO v_policy_count
    FROM pg_policies WHERE policyname = 'c77_rbac_policy';
    
    RETURN QUERY SELECT 
        'Active Policies'::TEXT,
        CASE WHEN v_policy_count > 0 THEN 'OK' ELSE 'WARNING' END,
        format('%s tables protected by RBAC policies', v_policy_count),
        CASE WHEN v_policy_count = 0 THEN 'Apply RLS policies to tables' ELSE 'None' END;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- 2. Performance monitoring
CREATE OR REPLACE FUNCTION public.c77_rbac_performance_report()
RETURNS TABLE(
    metric_name TEXT,
    metric_value TEXT,
    status TEXT
) AS $
DECLARE
    v_avg_permission_check_time FLOAT;
    v_total_subjects INTEGER;
    v_total_assignments INTEGER;
BEGIN
    -- Get average permission check time (requires pg_stat_statements)
    SELECT avg(mean_exec_time) INTO v_avg_permission_check_time
    FROM pg_stat_statements 
    WHERE query ILIKE '%c77_rbac_can_access%';
    
    RETURN QUERY SELECT 
        'Avg Permission Check Time'::TEXT,
        coalesce(v_avg_permission_check_time::TEXT || 'ms', 'No data'),
        CASE 
            WHEN v_avg_permission_check_time IS NULL THEN 'UNKNOWN'
            WHEN v_avg_permission_check_time < 10 THEN 'GOOD'
            WHEN v_avg_permission_check_time < 50 THEN 'OK'
            ELSE 'SLOW'
        END;
    
    -- System size metrics
    SELECT count(*) INTO v_total_subjects FROM public.c77_rbac_subjects;
    SELECT count(*) INTO v_total_assignments FROM public.c77_rbac_subject_roles;
    
    RETURN QUERY SELECT 
        'Total Subjects'::TEXT,
        v_total_subjects::TEXT,
        CASE 
            WHEN v_total_subjects < 1000 THEN 'SMALL'
            WHEN v_total_subjects < 10000 THEN 'MEDIUM'
            ELSE 'LARGE'
        END;
    
    RETURN QUERY SELECT 
        'Total Role Assignments'::TEXT,
        v_total_assignments::TEXT,
        CASE 
            WHEN v_total_assignments < 5000 THEN 'SMALL'
            WHEN v_total_assignments < 50000 THEN 'MEDIUM'
            ELSE 'LARGE'
        END;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3. Security audit
CREATE OR REPLACE FUNCTION public.c77_rbac_security_audit()
RETURNS TABLE(
    audit_item TEXT,
    finding TEXT,
    severity TEXT,
    recommendation TEXT
) AS $
DECLARE
    v_global_admins INTEGER;
    v_recent_changes INTEGER;
BEGIN
    -- Check for excessive global admins
    SELECT count(DISTINCT s.external_id) INTO v_global_admins
    FROM public.c77_rbac_subjects s
    JOIN public.c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id
    WHERE sr.scope_type = 'global' AND sr.scope_id = 'all';
    
    RETURN QUERY SELECT 
        'Global Admin Count'::TEXT,
        format('%s users with global admin access', v_global_admins),
        CASE 
            WHEN v_global_admins <= 2 THEN 'LOW'
            WHEN v_global_admins <= 5 THEN 'MEDIUM'
            ELSE 'HIGH'
        END,
        CASE 
            WHEN v_global_admins > 5 THEN 'Review global admin assignments'
            ELSE 'Global admin count is reasonable'
        END;
    
    -- Check for recent permission changes
    SELECT count(*) INTO v_recent_changes
    FROM public.c77_rbac_subject_roles 
    WHERE created_at > CURRENT_TIMESTAMP - INTERVAL '24 hours';
    
    RETURN QUERY SELECT 
        'Recent Permission Changes'::TEXT,
        format('%s role assignments in last 24 hours', v_recent_changes),
        CASE 
            WHEN v_recent_changes = 0 THEN 'LOW'
            WHEN v_recent_changes <= 10 THEN 'MEDIUM'
            ELSE 'HIGH'
        END,
        'Monitor for unusual permission change patterns';
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

Production Guidelines

Deployment Checklist

#!/bin/bash
# production_deployment_checklist.sh

echo "=== C77 RBAC Production Deployment Checklist ==="

# 1. Pre-deployment verification
echo "1. Verifying prerequisites..."
pg_isready -h $DB_HOST -p $DB_PORT -U $DB_USER
if [ $? -ne 0 ]; then
    echo "❌ Database not accessible"
    exit 1
fi

echo "✅ Database accessible"

# 2. Backup verification
echo "2. Creating pre-deployment backup..."
pg_dump -h $DB_HOST -U $DB_USER $DB_NAME > "backup_$(date +%Y%m%d_%H%M%S).sql"
if [ $? -eq 0 ]; then
    echo "✅ Backup created successfully"
else
    echo "❌ Backup failed"
    exit 1
fi

# 3. Extension deployment
echo "3. Deploying RBAC extension..."
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "CREATE EXTENSION IF NOT EXISTS c77_rbac;"
if [ $? -eq 0 ]; then
    echo "✅ Extension deployed"
else
    echo "❌ Extension deployment failed"
    exit 1
fi

# 4. Health check
echo "4. Running health checks..."
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "SELECT * FROM public.c77_rbac_health_check();"

# 5. Performance baseline
echo "5. Establishing performance baseline..."
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "SELECT * FROM public.c77_rbac_performance_report();"

# 6. Security audit
echo "6. Running security audit..."
psql -h $DB_HOST -U $DB_USER -d $DB_NAME -c "SELECT * FROM public.c77_rbac_security_audit();"

echo "=== Deployment checklist complete ==="

Monitoring Setup

-- 1. Create monitoring views
CREATE VIEW public.rbac_monitoring_dashboard AS
SELECT 
    'System Health' as category,
    check_name as metric,
    status as value,
    message as details
FROM public.c77_rbac_health_check()

UNION ALL

SELECT 
    'Performance' as category,
    metric_name as metric,
    status as value,
    metric_value as details
FROM public.c77_rbac_performance_report()

UNION ALL

SELECT 
    'Security' as category,
    audit_item as metric,
    severity as value,
    finding as details
FROM public.c77_rbac_security_audit();

-- 2. Create alerting function
CREATE OR REPLACE FUNCTION public.rbac_alert_check()
RETURNS TABLE(alert_level TEXT, alert_message TEXT) AS $
BEGIN
    -- Critical alerts
    IF EXISTS (SELECT 1 FROM public.c77_rbac_health_check() WHERE status = 'ERROR') THEN
        RETURN QUERY SELECT 'CRITICAL'::TEXT, 'RBAC system health check failed'::TEXT;
    END IF;
    
    -- Warning alerts
    IF EXISTS (SELECT 1 FROM public.c77_rbac_performance_report() WHERE status = 'SLOW') THEN
        RETURN QUERY SELECT 'WARNING'::TEXT, 'RBAC performance degraded'::TEXT;
    END IF;
    
    IF EXISTS (SELECT 1 FROM public.c77_rbac_security_audit() WHERE severity = 'HIGH') THEN
        RETURN QUERY SELECT 'WARNING'::TEXT, 'RBAC security audit found high-risk items'::TEXT;
    END IF;
    
    -- Info alerts
    IF NOT EXISTS (SELECT 1 FROM pg_policies WHERE policyname = 'c77_rbac_policy') THEN
        RETURN QUERY SELECT 'INFO'::TEXT, 'No RBAC policies are currently active'::TEXT;
    END IF;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

-- 3. Scheduled maintenance function
CREATE OR REPLACE FUNCTION public.rbac_maintenance()
RETURNS TEXT AS $
DECLARE
    v_cleanup_count INTEGER;
    v_result TEXT := '';
BEGIN
    -- Refresh materialized views if they exist
    BEGIN
        REFRESH MATERIALIZED VIEW CONCURRENTLY user_effective_permissions;
        v_result := v_result || 'Refreshed user permissions cache. ';
    EXCEPTION WHEN OTHERS THEN
        -- View might not exist, skip silently
    END;
    
    -- Update table statistics
    ANALYZE public.c77_rbac_subjects;
    ANALYZE public.c77_rbac_subject_roles;
    ANALYZE public.c77_rbac_role_features;
    v_result := v_result || 'Updated table statistics. ';
    
    -- Log maintenance completion
    INSERT INTO public.c77_rbac_audit_log (action, success, created_at)
    VALUES ('maintenance_completed', TRUE, CURRENT_TIMESTAMP);
    
    v_result := v_result || 'Maintenance completed successfully.';
    RETURN v_result;
END;
$ LANGUAGE plpgsql SECURITY DEFINER;

API Reference

Core Functions

Function	Parameters	Returns	Description
`c77_rbac_assign_subject`	external_id, role_name, scope_type, scope_id	void	Assign role to user
`c77_rbac_revoke_subject_role`	external_id, role_name, scope_type, scope_id	boolean	Remove role from user
`c77_rbac_grant_feature`	role_name, feature_name	void	Grant feature to role
`c77_rbac_revoke_feature`	role_name, feature_name	boolean	Remove feature from role
`c77_rbac_can_access`	feature_name, external_id, scope_type, scope_id	boolean	Check user permission
`c77_rbac_apply_policy`	table_name, feature_name, scope_type, scope_column	void	Apply RLS policy

Bulk Operations

Function	Parameters	Returns	Description
`c77_rbac_bulk_assign_subjects`	external_ids[], role_name, scope_type, scope_id	table	Bulk assign roles
`c77_rbac_bulk_revoke_subject_roles`	external_ids[], role_name, scope_type, scope_id	integer	Bulk remove roles

Management Functions

Function	Parameters	Returns	Description
`c77_rbac_get_user_roles`	external_id	table	Get user's roles
`c77_rbac_get_role_features`	role_name	table	Get role's features
`c77_rbac_sync_admin_features`	none	integer	Sync features to admin role
`c77_rbac_sync_global_admin_features`	none	integer	Sync features to global admins

Maintenance Functions

Function	Parameters	Returns	Description
`c77_rbac_health_check`	none	table	System health report
`c77_rbac_performance_report`	none	table	Performance metrics
`c77_rbac_security_audit`	none	table	Security audit results
`c77_rbac_show_dependencies`	none	table	Show extension dependencies
`c77_rbac_remove_all_policies`	none	void	Remove all RLS policies
`c77_rbac_cleanup_for_removal`	remove_data	void	Prepare for uninstall

Views

View	Description
`c77_rbac_user_permissions`	Complete user permission matrix
`c77_rbac_summary`	System overview statistics
`rbac_monitoring_dashboard`	Real-time monitoring data

Conclusion

This comprehensive 5-part usage guide covers everything needed to successfully implement and maintain c77_rbac in production environments. From basic concepts to advanced patterns, framework integration to troubleshooting, this guide ensures you can leverage the full power of database-level authorization for your applications.

Key Takeaways:

Database-level authorization provides consistent security across all application layers
Row-Level Security automatically filters data without application code changes
Proper monitoring and maintenance are essential for production deployments
Security best practices must be followed throughout the implementation
Performance optimization techniques ensure scalability for large user bases

For additional support and updates, refer to the project documentation and community resources.

30 KiB Raw Permalink Blame History