# c77_rbac Best Practices Guide This guide provides recommended patterns and practices for implementing c77_rbac in production environments. While the tutorials demonstrate basic usage, this document focuses on enterprise-grade implementations. ## Table of Contents 1. [Database Organization](#database-organization) 2. [Permission Design Patterns](#permission-design-patterns) 3. [Performance Optimization](#performance-optimization) 4. [Security Hardening](#security-hardening) 5. [Multi-Tenant Architectures](#multi-tenant-architectures) 6. [Migration Strategies](#migration-strategies) 7. [Framework-Specific Patterns](#framework-specific-patterns) 8. [Monitoring and Maintenance](#monitoring-and-maintenance) 9. [Common Pitfalls](#common-pitfalls) 10. [Production Checklist](#production-checklist) ## Database Organization ### Schema Architecture **Recommended Production Structure:** ```sql -- Extensions and system objects only public.* -- Core application tables application.* -- Multi-tenant schemas (if applicable) tenant_.* -- Audit and compliance data audit.* -- Read-only views and reports reporting.* -- Temporary processing tables staging.* ``` **Implementation Example:** ```sql -- As database owner or superuser CREATE EXTENSION c77_rbac; -- Goes in public -- As application user CREATE SCHEMA application; CREATE SCHEMA audit; CREATE SCHEMA reporting; -- Create tables in appropriate schemas CREATE TABLE application.users ( id SERIAL PRIMARY KEY, email TEXT UNIQUE NOT NULL, -- ... ); -- Apply RBAC policies with schema qualification SELECT c77_rbac_apply_policy( 'application.users', 'view_users', 'department', 'department_id' ); ``` ### Why Schema Separation Matters 1. **Security Isolation**: Each schema can have different access rules 2. **Clear Boundaries**: Prevents mixing system and application objects 3. **Easier Maintenance**: Can backup/restore schemas independently 4. **Better Performance**: Optimizer works better with organized schemas 5. **Compliance**: Easier to demonstrate data separation for audits ## Permission Design Patterns ### Role Naming Conventions **Pattern**: `__` ```sql -- Good role names SELECT c77_rbac_grant_feature('dept_expense_approver', 'approve_expenses'); SELECT c77_rbac_grant_feature('global_report_viewer', 'view_all_reports'); SELECT c77_rbac_grant_feature('tenant_data_editor', 'edit_tenant_data'); -- Avoid generic names -- Bad: 'admin', 'user', 'role1', 'temp' ``` ### Feature (Permission) Naming **Pattern**: `__` ```sql -- Well-structured permissions SELECT c77_rbac_grant_feature('finance_manager', 'approve_expenses_over_1000'); SELECT c77_rbac_grant_feature('hr_staff', 'view_employee_personal_data'); SELECT c77_rbac_grant_feature('sales_rep', 'edit_own_opportunities'); -- Create feature hierarchies SELECT c77_rbac_grant_feature('viewer', 'view_public_data'); SELECT c77_rbac_grant_feature('editor', 'view_public_data'); -- Inherits viewer permissions SELECT c77_rbac_grant_feature('editor', 'edit_public_data'); ``` ### Scope Design Patterns ```sql -- Hierarchical scopes 'global/all' -- System-wide access 'region/north_america' -- Regional access 'country/usa' -- Country-specific 'state/california' -- State-level 'office/san_francisco' -- Office-level -- Functional scopes 'department/engineering' -- Department-based 'project/apollo' -- Project-based 'team/mobile_dev' -- Team-based 'cost_center/cc_12345' -- Financial scopes -- Multi-tenant scopes 'tenant/customer_abc' -- Tenant isolation 'subscription/enterprise' -- Feature-based access 'api_client/client_xyz' -- API client scoping ``` ## Performance Optimization ### Index Strategy for RLS ```sql -- Always index columns used in RLS policies CREATE INDEX idx_users_department_id ON application.users(department_id); CREATE INDEX idx_documents_tenant_id ON application.documents(tenant_id); -- Composite indexes for complex policies CREATE INDEX idx_projects_dept_status ON application.projects(department_id, status); -- Partial indexes for common filters CREATE INDEX idx_active_users_dept ON application.users(department_id) WHERE is_active = true; -- Hash indexes for exact matches CREATE INDEX idx_users_external_id_hash ON application.users USING hash(external_id); ``` ### Query Optimization Patterns ```sql -- Use CTEs for complex permission checks WITH user_permissions AS ( SELECT * FROM c77_rbac_user_permissions WHERE external_id = current_setting('c77_rbac.external_id', true) ) SELECT d.* FROM application.documents d JOIN user_permissions up ON d.department_id = up.scope_id WHERE up.feature_name = 'view_documents'; -- Materialized views for permission matrices CREATE MATERIALIZED VIEW reporting.user_effective_permissions AS SELECT s.external_id, f.name as feature, sr.scope_type, sr.scope_id, 'direct' as permission_type 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; CREATE UNIQUE INDEX idx_user_perms_unique ON reporting.user_effective_permissions(external_id, feature, scope_type, scope_id); -- Refresh strategy REFRESH MATERIALIZED VIEW CONCURRENTLY reporting.user_effective_permissions; ``` ### Connection Pool Optimization ```sql -- Pre-warm permission cache function CREATE OR REPLACE FUNCTION warmup_user_permissions(p_user_id TEXT) RETURNS void AS $$ BEGIN -- Pre-load common permission checks PERFORM c77_rbac_can_access('view_data', p_user_id, 'global', 'all'); PERFORM c77_rbac_can_access('edit_data', p_user_id, 'department', (SELECT department_id FROM application.users WHERE external_id = p_user_id)); END; $$ LANGUAGE plpgsql; ``` ## Security Hardening ### Connection Security ```python # Python/Django example def get_db_connection(user_id): with connection.cursor() as cursor: # Always reset context first cursor.execute("RESET c77_rbac.external_id") cursor.execute("RESET role") # Reset any SET ROLE commands # Set new context cursor.execute("SET c77_rbac.external_id = %s", [user_id]) # Verify context was set cursor.execute("SELECT current_setting('c77_rbac.external_id', true)") if cursor.fetchone()[0] != str(user_id): raise SecurityError("Failed to set security context") ``` ```php // PHP/Laravel example class SecureConnection { public function setUserContext($userId) { // Use transaction to ensure atomic context setting DB::transaction(function () use ($userId) { DB::statement("RESET c77_rbac.external_id"); DB::statement("SET LOCAL c77_rbac.external_id = ?", [$userId]); // Verify $result = DB::selectOne("SELECT current_setting(?, true) as ctx", ['c77_rbac.external_id']); if ($result->ctx !== (string)$userId) { throw new SecurityException('Context verification failed'); } }); } } ``` ### Audit Logging ```sql -- Comprehensive audit table CREATE TABLE audit.rbac_changes ( id BIGSERIAL PRIMARY KEY, event_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP, event_type TEXT NOT NULL, -- 'role_assigned', 'role_revoked', etc. performed_by TEXT NOT NULL, target_user TEXT, role_name TEXT, scope_type TEXT, scope_id TEXT, success BOOLEAN NOT NULL, error_message TEXT, client_ip INET, session_id TEXT, additional_context JSONB ); -- Audit trigger for role changes CREATE OR REPLACE FUNCTION audit.log_rbac_changes() RETURNS TRIGGER AS $$ BEGIN INSERT INTO audit.rbac_changes ( event_type, performed_by, target_user, role_name, scope_type, scope_id, success, client_ip, additional_context ) VALUES ( TG_OP, current_setting('c77_rbac.external_id', true), CASE WHEN TG_OP = 'DELETE' THEN OLD.external_id ELSE NEW.external_id END, TG_ARGV[0], -- Pass role name as trigger argument NEW.scope_type, NEW.scope_id, true, inet_client_addr(), jsonb_build_object( 'table', TG_TABLE_NAME, 'when', TG_WHEN, 'level', TG_LEVEL ) ); RETURN NEW; END; $$ LANGUAGE plpgsql; ``` ### Preventing Privilege Escalation ```sql -- Function to validate role assignments CREATE OR REPLACE FUNCTION validate_role_assignment() RETURNS TRIGGER AS $$ DECLARE assigner_roles TEXT[]; BEGIN -- Get roles of the user making the assignment SELECT array_agg(r.name) INTO assigner_roles FROM c77_rbac_subjects s JOIN c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id JOIN c77_rbac_roles r ON sr.role_id = r.role_id WHERE s.external_id = current_setting('c77_rbac.external_id', true); -- Prevent non-admins from assigning admin roles IF NEW.role_name = 'admin' AND NOT ('admin' = ANY(assigner_roles)) THEN RAISE EXCEPTION 'Only admins can assign admin role' USING HINT = 'Contact an administrator for this operation'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; ``` ## Multi-Tenant Architectures ### Strict Isolation Pattern ```sql -- Each tenant gets their own schema CREATE OR REPLACE FUNCTION create_tenant_schema(p_tenant_id TEXT) RETURNS void AS $$ DECLARE schema_name TEXT; BEGIN schema_name := 'tenant_' || regexp_replace(p_tenant_id, '[^a-z0-9]', '_', 'g'); -- Create schema EXECUTE format('CREATE SCHEMA %I', schema_name); -- Create standard tables in tenant schema EXECUTE format(' CREATE TABLE %I.users (LIKE application.users INCLUDING ALL)', schema_name ); EXECUTE format(' CREATE TABLE %I.documents (LIKE application.documents INCLUDING ALL)', schema_name ); -- Apply RLS policies PERFORM c77_rbac_apply_policy( format('%I.users', schema_name), 'view_tenant_data', 'tenant', 'tenant_id' ); -- Grant default tenant admin role PERFORM c77_rbac_assign_subject( p_tenant_id || '_admin', 'tenant_admin', 'tenant', p_tenant_id ); END; $$ LANGUAGE plpgsql; ``` ### Shared Tables Pattern ```sql -- Single table with tenant isolation CREATE TABLE application.documents ( id SERIAL PRIMARY KEY, tenant_id TEXT NOT NULL, title TEXT NOT NULL, content TEXT, -- ... other columns created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ); -- Index for tenant isolation CREATE INDEX idx_documents_tenant_id ON application.documents(tenant_id); -- Apply tenant isolation policy SELECT c77_rbac_apply_policy( 'application.documents', 'view_tenant_data', 'tenant', 'tenant_id' ); -- Ensure complete isolation with additional check ALTER TABLE application.documents ENABLE ROW LEVEL SECURITY; CREATE POLICY tenant_isolation_policy ON application.documents FOR ALL USING ( tenant_id = ANY( SELECT scope_id FROM c77_rbac_subject_roles sr JOIN c77_rbac_subjects s ON sr.subject_id = s.subject_id WHERE s.external_id = current_setting('c77_rbac.external_id', true) AND sr.scope_type = 'tenant' ) ); ``` ### Hybrid Pattern (Best of Both) ```sql -- Shared tables for common data application.users -- All users across tenants application.subscriptions -- Tenant subscriptions application.features -- Available features -- Tenant-specific schemas for isolated data tenant_abc.invoices tenant_abc.customers tenant_xyz.invoices tenant_xyz.customers -- Cross-tenant reporting schema reporting.tenant_metrics reporting.usage_summary ``` ## Migration Strategies ### New Project Setup ```sql -- 1. Initial database setup CREATE DATABASE myapp; CREATE USER myapp_user WITH PASSWORD 'secure_password'; \c myapp CREATE EXTENSION c77_rbac; -- 2. Create application schemas CREATE SCHEMA application; CREATE SCHEMA audit; CREATE SCHEMA reporting; -- 3. Grant privileges GRANT ALL PRIVILEGES ON DATABASE myapp TO myapp_user; GRANT ALL ON SCHEMA application, audit, reporting TO myapp_user; -- 4. Set default privileges ALTER DEFAULT PRIVILEGES FOR USER myapp_user GRANT ALL ON TABLES TO myapp_user; ALTER DEFAULT PRIVILEGES FOR USER myapp_user GRANT ALL ON SEQUENCES TO myapp_user; ``` ### Existing Application Migration ```sql -- Phase 1: Parallel implementation -- Keep existing auth while testing c77_rbac -- Add tracking column ALTER TABLE users ADD COLUMN rbac_migrated BOOLEAN DEFAULT false; -- Migrate in batches CREATE OR REPLACE FUNCTION migrate_users_batch(p_limit INTEGER = 1000) RETURNS INTEGER AS $$ DECLARE migrated_count INTEGER := 0; user_record RECORD; BEGIN FOR user_record IN SELECT * FROM users WHERE NOT rbac_migrated ORDER BY id LIMIT p_limit LOOP -- Map existing roles to c77_rbac CASE user_record.role WHEN 'admin' THEN PERFORM c77_rbac_assign_subject( user_record.id::TEXT, 'admin', 'global', 'all' ); WHEN 'manager' THEN PERFORM c77_rbac_assign_subject( user_record.id::TEXT, 'manager', 'department', user_record.department_id::TEXT ); ELSE PERFORM c77_rbac_assign_subject( user_record.id::TEXT, 'employee', 'department', user_record.department_id::TEXT ); END CASE; -- Mark as migrated UPDATE users SET rbac_migrated = true WHERE id = user_record.id; migrated_count := migrated_count + 1; END LOOP; RETURN migrated_count; END; $$ LANGUAGE plpgsql; -- Phase 2: Gradual table migration -- Start with low-risk tables SELECT c77_rbac_apply_policy('public_content', 'view_content', 'global', 'all'); -- Phase 3: Critical tables with testing -- Apply policies but keep old auth active SELECT c77_rbac_apply_policy('users', 'view_users', 'department', 'department_id'); -- Phase 4: Cutover -- Remove old auth code -- Remove rbac_migrated column ALTER TABLE users DROP COLUMN rbac_migrated; ``` ## Framework-Specific Patterns ### Laravel Integration ```php // app/Traits/UsesRbac.php trait UsesRbac { public static function bootUsesRbac() { // Set context on model events static::creating(function ($model) { if (Auth::check()) { DB::statement('SET LOCAL c77_rbac.external_id = ?', [Auth::id()]); } }); } // Scope for RBAC-protected queries public function scopeAuthorized($query) { if (!Auth::check()) { return $query->whereRaw('1=0'); // No results } DB::statement('SET LOCAL c77_rbac.external_id = ?', [Auth::id()]); return $query; } } // Usage in models class Document extends Model { use UsesRbac; // Automatically filtered by RLS public static function allForUser() { return static::authorized()->get(); } } ``` ### Django Integration ```python # rbac/middleware.py class RbacContextMiddleware: def __init__(self, get_response): self.get_response = get_response def __call__(self, request): if request.user.is_authenticated: with connection.cursor() as cursor: cursor.execute( "SET LOCAL c77_rbac.external_id = %s", [str(request.user.id)] ) response = self.get_response(request) # Clean up with connection.cursor() as cursor: cursor.execute("RESET c77_rbac.external_id") return response # rbac/models.py class RbacProtectedModel(models.Model): class Meta: abstract = True @classmethod def apply_rbac_policy(cls, feature, scope_type, scope_column): with connection.cursor() as cursor: cursor.execute( "SELECT c77_rbac_apply_policy(%s, %s, %s, %s)", [cls._meta.db_table, feature, scope_type, scope_column] ) ``` ### Rails Integration ```ruby # app/models/concerns/rbac_protected.rb module RbacProtected extend ActiveSupport::Concern included do # Set user context before queries default_scope -> { if Current.user connection.execute("SET LOCAL c77_rbac.external_id = '#{Current.user.id}'") end all } end class_methods do def apply_rbac_policy(feature, scope_type, scope_column) connection.execute( "SELECT c77_rbac_apply_policy('#{table_name}', '#{feature}', '#{scope_type}', '#{scope_column}')" ) end end end # Usage class Document < ApplicationRecord include RbacProtected # Apply policy after migrations # Document.apply_rbac_policy('view_documents', 'department', 'dept_id') end ``` ## Monitoring and Maintenance ### Health Check Queries ```sql -- Daily health check function CREATE OR REPLACE FUNCTION daily_rbac_health_check() RETURNS TABLE( check_name TEXT, status TEXT, details TEXT, action_required BOOLEAN ) AS $$ BEGIN -- Check for users without roles RETURN QUERY SELECT 'Orphaned Users'::TEXT, CASE WHEN COUNT(*) > 0 THEN 'WARNING' ELSE 'OK' END, format('%s users without any roles', COUNT(*)), COUNT(*) > 0 FROM c77_rbac_subjects s WHERE NOT EXISTS ( SELECT 1 FROM c77_rbac_subject_roles sr WHERE sr.subject_id = s.subject_id ); -- Check for excessive permissions RETURN QUERY SELECT 'Permission Sprawl'::TEXT, CASE WHEN COUNT(*) > 50 THEN 'WARNING' ELSE 'OK' END, format('%s users with global admin access', COUNT(*)), COUNT(*) > 50 FROM c77_rbac_subject_roles WHERE scope_type = 'global' AND scope_id = 'all'; -- Check for stale roles RETURN QUERY SELECT 'Unused Roles'::TEXT, CASE WHEN COUNT(*) > 10 THEN 'WARNING' ELSE 'OK' END, format('%s roles with no assigned users', COUNT(*)), COUNT(*) > 10 FROM c77_rbac_roles r WHERE NOT EXISTS ( SELECT 1 FROM c77_rbac_subject_roles sr WHERE sr.role_id = r.role_id ); END; $$ LANGUAGE plpgsql; -- Schedule via pg_cron or external scheduler -- SELECT cron.schedule('daily-rbac-check', '0 2 * * *', 'SELECT daily_rbac_health_check()'); ``` ### Performance Monitoring ```sql -- Track slow permission checks CREATE TABLE monitoring.slow_permission_checks ( id BIGSERIAL PRIMARY KEY, query_start TIMESTAMP, duration INTERVAL, feature_name TEXT, external_id TEXT, scope_type TEXT, scope_id TEXT ); -- Monitor function with timing CREATE OR REPLACE FUNCTION c77_rbac_can_access_monitored( p_feature_name TEXT, p_external_id TEXT, p_scope_type TEXT, p_scope_id TEXT ) RETURNS BOOLEAN AS $$ DECLARE start_time TIMESTAMP; result BOOLEAN; duration INTERVAL; BEGIN start_time := clock_timestamp(); result := c77_rbac_can_access(p_feature_name, p_external_id, p_scope_type, p_scope_id); duration := clock_timestamp() - start_time; -- Log slow checks (> 10ms) IF duration > interval '10 milliseconds' THEN INSERT INTO monitoring.slow_permission_checks (query_start, duration, feature_name, external_id, scope_type, scope_id) VALUES (start_time, duration, p_feature_name, p_external_id, p_scope_type, p_scope_id); END IF; RETURN result; END; $$ LANGUAGE plpgsql; ``` ## Common Pitfalls ### 1. Forgetting to Set Context **Problem**: Queries return no data because context isn't set. **Solution**: Always verify context is set: ```sql -- Add to your application's base query class CREATE OR REPLACE FUNCTION verify_context_set() RETURNS void AS $$ BEGIN IF current_setting('c77_rbac.external_id', true) IS NULL THEN RAISE EXCEPTION 'Security context not set' USING HINT = 'Call SET c77_rbac.external_id before querying'; END IF; END; $$ LANGUAGE plpgsql; ``` ### 2. Connection Pool Context Leakage **Problem**: User A sees User B's data due to connection reuse. **Solution**: Always reset context: ```python # Python context manager from contextlib import contextmanager @contextmanager def rbac_context(user_id): cursor = connection.cursor() try: cursor.execute("SAVEPOINT rbac_context") cursor.execute("SET LOCAL c77_rbac.external_id = %s", [user_id]) yield cursor finally: cursor.execute("ROLLBACK TO SAVEPOINT rbac_context") ``` ### 3. Over-Granting Permissions **Problem**: Giving users more access than needed. **Solution**: Regular permission audits: ```sql -- Find over-privileged users SELECT s.external_id, COUNT(DISTINCT f.name) as permission_count, array_agg(DISTINCT r.name) as roles FROM c77_rbac_subjects s JOIN c77_rbac_subject_roles sr ON s.subject_id = sr.subject_id JOIN c77_rbac_roles r ON sr.role_id = r.role_id JOIN c77_rbac_role_features rf ON r.role_id = rf.role_id JOIN c77_rbac_features f ON rf.feature_id = f.feature_id GROUP BY s.external_id HAVING COUNT(DISTINCT f.name) > 20 -- Threshold for investigation ORDER BY permission_count DESC; ``` ### 4. Inefficient RLS Policies **Problem**: Complex policies causing slow queries. **Solution**: Optimize policy conditions: ```sql -- Bad: Multiple subqueries CREATE POLICY slow_policy ON documents USING ( author_id IN (SELECT user_id FROM team_members WHERE team_id IN (SELECT team_id FROM user_teams WHERE user_id = current_user_id())) ); -- Good: Single optimized query CREATE POLICY fast_policy ON documents USING ( EXISTS ( SELECT 1 FROM team_members tm JOIN user_teams ut ON tm.team_id = ut.team_id WHERE tm.user_id = author_id AND ut.user_id = current_user_id() ) ); ``` ## Production Checklist ### Pre-Deployment - [ ] All schemas created with proper ownership - [ ] Indexes created on all RLS policy columns - [ ] Connection pool context handling implemented - [ ] Audit logging configured - [ ] Monitoring queries scheduled - [ ] Backup strategy includes c77_rbac tables - [ ] Performance baseline established ### Deployment - [ ] Extension installed by superuser - [ ] Application user has required privileges - [ ] Initial roles and features configured - [ ] RLS policies applied to all sensitive tables - [ ] Context setting added to application layer - [ ] Health check queries return OK ### Post-Deployment - [ ] Monitor slow query logs - [ ] Review audit logs daily for first week - [ ] Check for orphaned users/roles - [ ] Verify no permission escalation attempts - [ ] Document any custom policies - [ ] Train team on RBAC concepts ### Ongoing Maintenance - [ ] Weekly: Run health check queries - [ ] Monthly: Review and clean up unused roles - [ ] Quarterly: Permission audit and optimization - [ ] Annually: Review and update security policies ## Conclusion Following these best practices will help you build a secure, performant, and maintainable authorization system with c77_rbac. Remember that security is an ongoing process - regular monitoring and updates are essential for maintaining a robust system. For specific implementation questions or advanced scenarios not covered here, refer to the main documentation or open an issue in the project repository.