Audit Logging

The PostgreSQL Permissions Model provides comprehensive audit logging through two distinct but complementary systems: User Events for security-focused auditing and Journal for general-purpose operational logging.

Audit Architecture Overview

graph TB
    subgraph "Application Layer"
        A[User Actions]
        B[System Operations]
        C[Authentication Attempts]
        D[Permission Checks]
    end

    subgraph "Audit Logging Systems"
        E[User Event System]
        F[Journal System]
    end

    subgraph "Storage Layer"
        G[auth.user_event<br/>Security Events]
        H[public.journal<br/>Business Operations]
    end

    subgraph "Analysis & Monitoring"
        I[Security Event Queries]
        J[Journal Search Function]
        K[Audit Reports]
    end

    A --> E
    C --> E
    D --> E
    B --> F
    A --> F

    E --> G
    F --> H

    G --> I
    H --> J
    I --> K
    J --> K

User Event System (Security Auditing)

The User Event system (auth.user_event) provides comprehensive security-focused audit trails for authentication, authorization, and user management operations.

Core Functions

auth.create_user_event() - Security Event Logging

-- Log a security event
SELECT * FROM auth.create_user_event(
    _created_by := 'system',
    _user_id := 1,  -- requesting user ID
    _event_type_code := 'user_logged_in',
    _target_user_id := 123,  -- user being acted upon
    _ip_address := '192.168.1.100',
    _user_agent := 'Mozilla/5.0...',
    _origin := 'https://app.example.com',
    _event_data := '{"login_method": "oauth", "provider": "azure"}'::jsonb,
    _target_user_oid := 'azure-guid-here',
    _target_username := 'john.doe'
);

User Event Types

The system defines the following predefined security event types:

User Management Events

• create_user_info - User account created
• update_user_info - User account updated
• delete_user_info - User account deleted

Identity Management Events

• create_user_identity - New identity provider added to user
• update_user_identity - Identity provider information updated
• delete_user_identity - Identity provider removed from user

Authentication Events

• user_logged_in - Successful login
• user_logged_out - User logout
• api_key_validating - API key authentication attempt

Password & Security Events

• password_reset_requested - Password reset initiated
• password_change - Password change attempt
• password_changed - Password successfully changed

Multi-Factor Authentication Events

• create_mfa_email - Email MFA method added
• update_mfa_email - Email MFA method updated
• delete_mfa_email - Email MFA method removed
• create_mfa_phone - Phone MFA method added
• update_mfa_phone - Phone MFA method updated
• delete_mfa_phone - Phone MFA method removed

Invitation & Verification Events

• user_invitation_sent - User invitation sent
• user_invitation_accepted - User accepted invitation
• user_invitation_rejected - User rejected invitation
• email_verification - Email verification process
• phone_verification - Phone verification process

External Data Events

• external_data_update - External provider data about to change
• external_data_updated - External provider data changed

User Event Table Structure

-- auth.user_event table contains:
user_event_id      -- bigint primary key
event_type_code    -- text (references const.user_event_type)
requester_user_id  -- bigint (who performed the action)
requester_username -- text (username of requester)
target_user_id     -- bigint (who was acted upon)
target_user_oid    -- text (external provider ID)
target_username    -- text (username of target)
ip_address         -- text (client IP address)
user_agent         -- text (browser/client info)
origin             -- text (originating domain)
event_data         -- jsonb (structured event details)
created_at         -- timestamptz (when event occurred)
created_by         -- text (system/username)

Querying Security Events

-- Recent login attempts
SELECT
    ue.created_at,
    ue.requester_username,
    ue.target_username,
    ue.ip_address,
    ue.event_data->>'login_method' as login_method,
    ue.event_data->>'success' as success
FROM auth.user_event ue
WHERE ue.event_type_code = 'user_logged_in'
  AND ue.created_at > now() - interval '24 hours'
ORDER BY ue.created_at DESC;

-- Failed API key validations
SELECT
    ue.created_at,
    ue.target_username,
    ue.ip_address,
    ue.event_data
FROM auth.user_event ue
WHERE ue.event_type_code = 'api_key_validating'
  AND ue.event_data->>'is_successful' = 'false'
  AND ue.created_at > now() - interval '1 hour'
ORDER BY ue.created_at DESC;

-- User management operations by admin
SELECT
    ue.created_at,
    ue.event_type_code,
    ue.requester_username as admin_user,
    ue.target_username as affected_user,
    ue.event_data
FROM auth.user_event ue
WHERE ue.requester_username = 'admin_user'
  AND ue.event_type_code IN ('create_user_info', 'update_user_info', 'delete_user_info')
  AND ue.created_at > now() - interval '7 days'
ORDER BY ue.created_at DESC;

Journal System (Operational Logging)

The Journal system (public.journal) provides general-purpose logging for business operations, data changes, and system events.

Core Functions

add_journal_msg() - Text-based Logging

-- Log a simple text message
SELECT * FROM add_journal_msg(
    _created_by := 'admin_user',
    _user_id := 1,
    _msg := 'User assigned to Administrators group',
    _data_group := 'user_group',
    _data_object_id := 5,  -- group ID
    _payload := ARRAY['user_id', '123', 'group_name', 'Administrators'],
    _event_id := 50131,  -- User added to group
    _data_object_code := 'administrators',
    _tenant_id := 1
);

add_journal_msg_jsonb() - Structured Logging

-- Log with structured JSON data
SELECT * FROM add_journal_msg_jsonb(
    _created_by := 'system',
    _user_id := 1,
    _msg := 'Permission set updated',
    _data_group := 'perm_set',
    _data_object_id := 10,
    _payload := '{
        "perm_set_code": "ADMIN",
        "permissions_added": ["users.create", "reports.view"],
        "permissions_removed": ["debug.access"]
    }'::jsonb,
    _event_id := 50311,
    _tenant_id := 1
);

Journal Search

search_journal_msgs() - Advanced Search

-- Search journal entries with filters
SELECT * FROM search_journal_msgs(
    _user_id := 1,
    _search_text := 'permission',
    _from := now() - interval '30 days',
    _to := now(),
    _target_user_id := null,  -- any user
    _event_id := null,        -- any event
    _data_group := 'perm_set', -- only permission set operations
    _data_object_id := null,   -- any object
    _data_object_code := null, -- any object code
    _payload_criteria := '{"permissions_added": []}', -- has permissions_added array
    _page := 1,
    _page_size := 50,
    _tenant_id := 1
);

Journal Table Structure

-- public.journal table contains:
journal_id       -- bigint primary key
tenant_id        -- int (multi-tenancy support)
data_group       -- text (categorization: 'user_group', 'perm_set', etc.)
data_object_id   -- bigint (ID of affected object)
data_object_code -- text (code/name of affected object)
event_id         -- int (numeric event classification)
user_id          -- bigint (who performed the action)
message          -- text (human-readable description)
data_payload     -- jsonb (structured data)
created_at       -- timestamptz (when event occurred)
created_by       -- text (system/username)

Event Code System

The system uses structured numeric event codes to categorize different types of operations:

Security Event Codes (50000-59999)

Event Code	Description	Usage
50001	Tenant created	Tenant management
50002	Tenant updated	Tenant management
50003	Permission denied	Authorization failures
50101	User created	User management
50102	User updated	User management
50103	User deleted	User management
50131	User added to group	Group membership
50133	User removed from group	Group membership
50201	Group created	Group management
50301	Permission set created	Permission management
50304	Permission assigned	Permission management
50305	Permission unassigned	Permission management
50501	API key created	API key management
50502	API key updated	API key management

Security Error Codes (52000-52999)

Error Code	Description	Typical Usage
52101	Cannot ensure user for email provider	Email provider issues
52102	User identity already in use	Registration conflicts
52103	User does not exist	User lookup failures
52105	User is not active	Account status issues
52106	User is locked	Security lockouts
52108	User has no access to tenant	Multi-tenancy access
52109	User lacks required permission	Authorization failures

Audit Queries and Reporting

Security Monitoring Queries

-- Monitor failed login attempts
SELECT
    DATE_TRUNC('hour', ue.created_at) as hour_bucket,
    COUNT(*) as failed_attempts,
    COUNT(DISTINCT ue.ip_address) as unique_ips,
    array_agg(DISTINCT ue.target_username) as affected_users
FROM auth.user_event ue
WHERE ue.event_type_code = 'user_logged_in'
  AND ue.event_data->>'success' = 'false'
  AND ue.created_at > now() - interval '24 hours'
GROUP BY hour_bucket
ORDER BY hour_bucket DESC;

-- Detect suspicious IP addresses
SELECT
    ue.ip_address,
    COUNT(*) as total_events,
    COUNT(DISTINCT ue.target_user_id) as unique_users,
    array_agg(DISTINCT ue.event_type_code) as event_types,
    MIN(ue.created_at) as first_seen,
    MAX(ue.created_at) as last_seen
FROM auth.user_event ue
WHERE ue.created_at > now() - interval '7 days'
GROUP BY ue.ip_address
HAVING COUNT(*) > 100 OR COUNT(DISTINCT ue.target_user_id) > 10
ORDER BY total_events DESC;

-- Track permission changes
SELECT
    j.created_at,
    j.created_by,
    j.message,
    j.data_payload->>'perm_set_code' as permission_set,
    j.data_payload->>'target_user' as affected_user
FROM journal j
WHERE j.data_group = 'perm_set'
  AND j.event_id IN (50304, 50305)  -- Permission assigned/unassigned
  AND j.created_at > now() - interval '30 days'
ORDER BY j.created_at DESC;

Compliance and Retention

-- Data retention: Archive old audit events
WITH old_events AS (
    SELECT user_event_id
    FROM auth.user_event
    WHERE created_at < now() - interval '7 years'
)
-- Move to archive table before deletion (implementation dependent)
INSERT INTO auth.user_event_archive
SELECT * FROM auth.user_event
WHERE user_event_id IN (SELECT user_event_id FROM old_events);

-- Clean up old journal entries (keep based on business requirements)
DELETE FROM journal
WHERE created_at < now() - interval '5 years'
  AND data_group NOT IN ('security', 'compliance');  -- Keep security logs longer

What's Next

• Learn about Authentication patterns and security
• Explore Error Handling for proper error logging
• Review Best Practices for comprehensive security
• Understand the Permission Model for context