Error Handling¶

The PostgreSQL Permissions Model implements a structured error handling system with specific error codes for different types of security and operational events. Understanding these error codes is essential for proper application integration and troubleshooting.

Error Code System¶

The system uses PostgreSQL's structured error handling with specific error codes in the range 50001-52999:

50001-50999: Informational events (tenant created, user updated, etc.)
52001-52999: Security errors (user not found, permission denied, etc.)

Error Code Categories¶

Informational Events (50001-50999)¶

These codes indicate successful operations or informational events:

Code	Name	Description
50001	TENANT_CREATED	New tenant successfully created
50002	USER_CREATED	New user account created
50003	USER_UPDATED	User information updated
50004	GROUP_CREATED	New group created
50005	PERMISSION_ASSIGNED	Permission successfully assigned
50006	LOGIN_SUCCESS	Successful authentication
50007	LOGOUT_SUCCESS	User logged out
50008	PASSWORD_CHANGED	Password successfully changed

Security Errors (52001-52999)¶

These codes indicate security-related errors and unauthorized access attempts:

Code	Name	Description
52001	USER_NOT_FOUND	User does not exist
52002	PERMISSION_DENIED	User lacks required permission
52003	INVALID_CREDENTIALS	Authentication failed
52004	ACCOUNT_DISABLED	User account is disabled
52005	ACCOUNT_LOCKED	User account is locked
52006	TENANT_ACCESS_DENIED	User not authorized for tenant
52007	API_KEY_INVALID	Invalid or expired API key
52008	TOKEN_EXPIRED	Authentication token expired
52009	INSUFFICIENT_PRIVILEGES	Operation requires higher privileges

Error Functions¶

Throwing Errors¶

-- Throw permission denied error
SELECT auth.throw_no_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _perm_code := 'admin.system_settings'
);

-- Throw custom error
SELECT auth.throw_error(
    _error_code := '52005',
    _message := 'Account locked due to multiple failed login attempts'
);

Error Information Functions¶

-- Get error details
SELECT auth.get_error_info('52002') as error_details;
-- Returns: {code: "52002", name: "PERMISSION_DENIED", description: "..."}

-- Check if error is security-related
SELECT auth.is_security_error('52002'); -- Returns: true
SELECT auth.is_security_error('50001'); -- Returns: false

Permission Check Error Handling¶

Automatic Error Throwing¶

-- Default behavior - throws error if permission denied
SELECT auth.has_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _perm_code := 'sensitive.operation'
);
-- Throws 52002 PERMISSION_DENIED if user lacks permission

Silent Permission Checks¶

-- Silent check - returns boolean without throwing error
IF auth.has_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _perm_code := 'optional.feature',
    _throw_err := false
) THEN
    -- User has permission, proceed
    PERFORM enable_optional_feature();
ELSE
    -- User lacks permission, continue with limited functionality
    PERFORM show_limited_interface();
END IF;

Multiple Permission Checks¶

-- Check multiple permissions with custom error handling
BEGIN
    -- This will throw on first failed permission
    PERFORM auth.has_permissions(
        _tenant_id := 'tenant-uuid',
        _user_id := 'user-uuid',
        _perm_codes := ARRAY['users.read', 'users.update', 'users.delete']
    );

    -- All permissions verified, proceed with operation
    PERFORM execute_user_management_operation();

EXCEPTION
    WHEN SQLSTATE '52002' THEN
        -- Handle permission denied
        RAISE NOTICE 'User lacks required permissions for user management';
        RETURN 'INSUFFICIENT_PERMISSIONS';
END;

Application Integration Patterns¶

Stored Procedure Error Handling¶

CREATE OR REPLACE FUNCTION business.process_order(
    _tenant_id uuid,
    _user_id uuid,
    _order_id uuid
) RETURNS text AS $$
DECLARE
    v_result text;
BEGIN
    -- Check permissions first
    IF NOT auth.has_permission(_tenant_id, _user_id, 'orders.process', false) THEN
        -- Log unauthorized attempt
        PERFORM auth.create_auth_event(
            _tenant_id,
            _user_id,
            '52002',
            'Unauthorized order processing attempt',
            jsonb_build_object('order_id', _order_id)
        );
        RETURN 'PERMISSION_DENIED';
    END IF;

    -- Proceed with business logic
    -- ... order processing code ...

    -- Log successful operation
    PERFORM auth.create_auth_event(
        _tenant_id,
        _user_id,
        '50010',
        'Order processed successfully',
        jsonb_build_object('order_id', _order_id)
    );

    RETURN 'SUCCESS';

EXCEPTION
    WHEN OTHERS THEN
        -- Log unexpected error
        PERFORM auth.create_auth_event(
            _tenant_id,
            _user_id,
            '52999',
            'Unexpected error in order processing: ' || SQLERRM,
            jsonb_build_object('order_id', _order_id, 'error', SQLSTATE)
        );
        RETURN 'SYSTEM_ERROR';
END;
$$ LANGUAGE plpgsql;

Application-Level Error Handling¶

// Example Node.js error handling
async function processUserRequest(tenantId, userId, operation) {
    try {
        const result = await db.query(
            'SELECT auth.has_permission($1, $2, $3)',
            [tenantId, userId, operation]
        );

        // Permission granted, proceed
        return await executeOperation(operation);

    } catch (error) {
        // Check for specific error codes
        switch (error.code) {
            case '52001': // USER_NOT_FOUND
                return { error: 'User not found', code: 404 };
            case '52002': // PERMISSION_DENIED
                return { error: 'Permission denied', code: 403 };
            case '52006': // TENANT_ACCESS_DENIED
                return { error: 'Tenant access denied', code: 403 };
            default:
                return { error: 'Internal server error', code: 500 };
        }
    }
}

Audit Event Integration¶

Automatic Audit Logging¶

-- Permission checks automatically create audit events
CREATE OR REPLACE FUNCTION auth.has_permission_with_audit(
    _tenant_id uuid,
    _user_id uuid,
    _perm_code text,
    _throw_err boolean DEFAULT true
) RETURNS boolean AS $$
DECLARE
    v_has_permission boolean;
    v_event_code text;
    v_message text;
BEGIN
    -- Check permission
    v_has_permission := auth.has_permission(_tenant_id, _user_id, _perm_code, false);

    IF v_has_permission THEN
        v_event_code := '50020';
        v_message := 'Permission check successful';
    ELSE
        v_event_code := '52002';
        v_message := 'Permission denied';

        IF _throw_err THEN
            PERFORM auth.throw_no_permission(_tenant_id, _user_id, _perm_code);
        END IF;
    END IF;

    -- Create audit event
    PERFORM auth.create_auth_event(
        _tenant_id,
        _user_id,
        v_event_code,
        v_message,
        jsonb_build_object('permission', _perm_code)
    );

    RETURN v_has_permission;
END;
$$ LANGUAGE plpgsql;

Custom Event Logging¶

-- Log custom business events with error codes
PERFORM auth.create_auth_event(
    _tenant_id := 'tenant-uuid',
    _user_id := 'user-uuid',
    _event_code := '50100', -- Custom business event
    _event_description := 'Report generated successfully',
    _event_data := jsonb_build_object(
        'report_type', 'financial',
        'date_range', '2024-01-01 to 2024-01-31',
        'records_processed', 1500
    )
);

Error Recovery Strategies¶

Graceful Degradation¶

CREATE OR REPLACE FUNCTION get_user_dashboard_data(
    _tenant_id uuid,
    _user_id uuid
) RETURNS jsonb AS $$
DECLARE
    v_result jsonb := '{}'::jsonb;
    v_section jsonb;
BEGIN
    -- Try to get each dashboard section
    -- Financial data (may require special permission)
    BEGIN
        IF auth.has_permission(_tenant_id, _user_id, 'dashboard.financial', false) THEN
            SELECT get_financial_data(_tenant_id) INTO v_section;
            v_result := v_result || jsonb_build_object('financial', v_section);
        END IF;
    EXCEPTION WHEN OTHERS THEN
        -- Log error but continue
        PERFORM auth.create_auth_event(_tenant_id, _user_id, '52010',
                                     'Dashboard financial section error: ' || SQLERRM);
    END;

    -- User data (basic permission)
    BEGIN
        IF auth.has_permission(_tenant_id, _user_id, 'dashboard.basic', false) THEN
            SELECT get_user_summary(_tenant_id, _user_id) INTO v_section;
            v_result := v_result || jsonb_build_object('user', v_section);
        END IF;
    EXCEPTION WHEN OTHERS THEN
        PERFORM auth.create_auth_event(_tenant_id, _user_id, '52011',
                                     'Dashboard user section error: ' || SQLERRM);
    END;

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Retry Logic for Transient Errors¶

// Example retry logic for database connection errors
async function executeWithRetry(query, params, maxRetries = 3) {
    for (let attempt = 1; attempt <= maxRetries; attempt++) {
        try {
            return await db.query(query, params);
        } catch (error) {
            // Check for transient errors
            if (isTransientError(error) && attempt < maxRetries) {
                await delay(1000 * attempt); // Exponential backoff
                continue;
            }
            throw error;
        }
    }
}

function isTransientError(error) {
    const transientCodes = [
        '08000', // Connection exception
        '08003', // Connection does not exist
        '08006', // Connection failure
        '53300', // Too many connections
    ];
    return transientCodes.includes(error.code);
}

Monitoring and Alerting¶

Error Rate Monitoring¶

-- Get error rate by hour
SELECT
    date_trunc('hour', event_at) as hour,
    COUNT(*) FILTER (WHERE event_code::int >= 52001) as error_count,
    COUNT(*) as total_events,
    ROUND(
        (COUNT(*) FILTER (WHERE event_code::int >= 52001) * 100.0) / COUNT(*),
        2
    ) as error_percentage
FROM auth.user_event
WHERE event_at >= now() - interval '24 hours'
GROUP BY hour
ORDER BY hour;

Security Event Monitoring¶

-- Monitor security events
SELECT
    ue.event_code,
    ue.event_description,
    COUNT(*) as occurrence_count,
    array_agg(DISTINCT u.username) as affected_users
FROM auth.user_event ue
JOIN auth.user_info u ON ue.user_id = u.user_id
WHERE ue.event_at >= now() - interval '1 hour'
  AND ue.event_code::int BETWEEN 52001 AND 52999
GROUP BY ue.event_code, ue.event_description
HAVING COUNT(*) > 5 -- Alert threshold
ORDER BY occurrence_count DESC;

Alerting Rules¶

-- Create alerting function
CREATE OR REPLACE FUNCTION auth.check_security_alerts()
RETURNS TABLE(alert_type text, message text, severity text) AS $$
BEGIN
    -- High error rate alert
    RETURN QUERY
    WITH recent_errors AS (
        SELECT COUNT(*) as error_count
        FROM auth.user_event
        WHERE event_at >= now() - interval '5 minutes'
          AND event_code::int >= 52001
    )
    SELECT
        'HIGH_ERROR_RATE'::text,
        'High error rate detected: ' || error_count || ' errors in 5 minutes'::text,
        'CRITICAL'::text
    FROM recent_errors
    WHERE error_count > 50;

    -- Multiple failed login attempts
    RETURN QUERY
    WITH failed_logins AS (
        SELECT
            u.username,
            COUNT(*) as failure_count
        FROM auth.user_event ue
        JOIN auth.user_info u ON ue.user_id = u.user_id
        WHERE ue.event_at >= now() - interval '15 minutes'
          AND ue.event_code = '52003' -- INVALID_CREDENTIALS
        GROUP BY u.user_id
        HAVING COUNT(*) >= 5
    )
    SELECT
        'BRUTE_FORCE_ATTEMPT'::text,
        'Multiple failed logins for user: ' || username::text,
        'HIGH'::text
    FROM failed_logins;
END;
$$ LANGUAGE plpgsql;

Best Practices¶

Error Handling Guidelines¶

Fail Securely: Default to denying access when errors occur
Log Comprehensively: Capture all security events
Provide Minimal Information: Don't expose sensitive data in error messages
Handle Gracefully: Provide fallback functionality when possible

Performance Considerations¶

Avoid Exception Overhead: Use silent checks when appropriate
Batch Operations: Group permission checks when possible
Cache Results: Cache permission check results when feasible
Monitor Performance: Track error handling performance impact

Security Considerations¶

Rate Limiting: Implement rate limiting for error-prone operations
Anomaly Detection: Monitor for unusual error patterns
Secure Logging: Ensure error logs don't contain sensitive data
Regular Review: Audit error patterns and handling logic

What's Next¶

Explore Audit Logging for comprehensive event tracking
Review Security Best Practices
Learn about Authentication error handling
Understand Authorization error patterns