API Key Management

The PostgreSQL Permissions Model provides a unique approach to API key management by creating a "technical user" for each API key. This design ensures that all permission checks work uniformly across both human users and service accounts, maintaining database consistency and simplifying authorization logic.

Key Concepts

Technical User Pattern

When an API key is created, the system automatically: 1. Creates a new entry in auth.user_info with type 'api' 2. Links the API key to this technical user 3. Allows permission assignments like any regular user 4. Maintains audit trails and consistent permission checking

Benefits of This Approach

• Uniform Permission Model: Same functions work for users and API keys
• Audit Consistency: All actions traced to a user (human or technical)
• Permission Management: API keys can be assigned permission sets and individual permissions
• Tenant Isolation: API keys are tenant-scoped like regular users

Database Structure

API Key Table

CREATE TABLE auth.api_key (
    api_key_id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    tenant_id int NOT NULL REFERENCES auth.tenant,
    api_key text UNIQUE NOT NULL,
    title text,
    description text,
    secret_hash text NOT NULL,
    expire_at timestamptz,
    notification_email text,
    created_at timestamptz DEFAULT now(),
    created_by text NOT NULL,
    modified_by text NOT NULL
);

Technical User Integration

-- Technical users in user_info
CREATE TABLE auth.user_info (
    user_id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
    code text UNIQUE NOT NULL, -- Generated: 'api_key_' + api_key
    username text UNIQUE NOT NULL,
    user_type_code text DEFAULT 'human' CHECK (user_type_code IN ('human', 'api')),
    email text,
    display_name text NOT NULL,
    is_active boolean DEFAULT true,
    created_by text NOT NULL,
    modified_by text NOT NULL,
    created_at timestamptz DEFAULT now()
    -- ... other fields
);

Creating API Keys

Basic API Key Creation

-- Create API key (automatically creates API user)
SELECT auth.create_api_key(
    _created_by := 'admin_user',
    _user_id := 1,  -- ID of the user creating the key
    _title := 'Service Integration API',
    _description := 'API key for service integration',
    _api_secret := 'secure-api-key-secret-123',
    _tenant_id := 1
);

-- Returns: {api_key_id, api_key, api_secret}

API Key with Expiration

-- Create temporary API key
SELECT auth.create_api_key(
    _created_by := 'admin_user',
    _user_id := 1,
    _title := 'Temporary Integration Key',
    _description := 'Temporary key for integration testing',
    _api_secret := 'temp-secret-456',
    _expire_at := now() + interval '30 days',
    _tenant_id := 1
);

Complete Service Setup

-- Create API key and assign permissions
DO $$
DECLARE
    v_api_result record;
    v_user_id uuid;
BEGIN
    -- Create API key
    SELECT * FROM auth.create_api_key(
        _created_by := 'admin_user',
        _user_id := 1,
        _title := 'Reporting Service API',
        _description := 'API key for reporting service',
        _api_secret := 'reports-api-secret-789',
        _tenant_id := 1
    ) INTO v_api_result;

    -- Get the API user ID from the API key
    SELECT ui.user_id INTO v_user_id
    FROM auth.api_key ak
    JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
    WHERE ak.api_key_id = v_api_result.api_key_id;

    -- Assign permission set to API user
    PERFORM auth.assign_permission(
        _tenant_id := 1::uuid,
        _user_id := v_user_id,
        _user_group_id := NULL,
        _perm_set_code := 'REPORTING_SERVICE',
        _perm_code := NULL
    );

    RAISE NOTICE 'API Key created for user: %', v_user_id;
END $$;

Managing API Keys

Update API Key Details

-- Update API key information
SELECT auth.update_api_key(
    _updated_by := 'admin_user',
    _user_id := 1,
    _api_key_id := 1,  -- Integer ID, not UUID
    _title := 'Updated Service Name',
    _expire_at := now() + interval '90 days',
    _tenant_id := 1
);

Rotate API Key Secret

-- Generate new secret for existing API key
SELECT auth.update_api_key_secret(
    _updated_by := 'admin_user',
    _user_id := 1,
    _api_key_id := 1,  -- Integer ID, not UUID
    _api_secret := 'new-secure-secret-456',
    _tenant_id := 1
);

-- Returns: api_key_id, api_secret

Activate/Deactivate API Keys

-- Deactivate API key
UPDATE auth.api_key
SET is_active = false
WHERE api_key_id = 1;

-- Also deactivate the API user
UPDATE auth.user_info
SET is_active = false
WHERE code = (
    SELECT auth.generate_api_key_username(ak.api_key)
    FROM auth.api_key ak
    WHERE ak.api_key_id = 1
);

Delete API Key

-- Delete API key and API user
SELECT auth.delete_api_key(
    _deleted_by := 'admin_user',
    _user_id := 1,
    _api_key_id := 1,  -- Integer ID, not UUID
    _tenant_id := 1
);
-- This handles cleanup of permissions, user, and key

Permission Management for API Keys

Assign Permission Sets

-- Get API user ID from API key
WITH api_user AS (
    SELECT ui.user_id
    FROM auth.api_key ak
    JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
    WHERE ak.api_key = 'SERVICE_API'
      AND ak.tenant_id = 1
)
-- Assign permission set
SELECT auth.assign_permission(
    _tenant_id := 'tenant-uuid',
    _user_id := (SELECT user_id FROM api_user),
    _user_group_id := NULL,
    _perm_set_code := 'API_INTEGRATION',
    _perm_code := NULL
);

Assign Individual Permissions

-- Assign specific permissions
SELECT auth.assign_permission(
    _created_by := 'admin_user',
    _user_id := 1,  -- The human user doing the assignment
    _target_user_id := (
        SELECT ui.user_id
        FROM auth.api_key ak
        JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
        WHERE ak.api_key = 'REPORTS_API'
    ),
    _perm_code := 'reports.generate',
    _tenant_id := 1
);

Add API Key to Groups

-- Add API user to a group
INSERT INTO auth.user_group_member (user_group_id, user_id)
VALUES (
    1,  -- service group ID
    (SELECT ui.user_id
     FROM auth.api_key ak
     JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
     WHERE ak.api_key = 'SERVICE_API')
);

Authentication with API Keys

Verify API Key

-- Validate API key and get user context
SELECT
    ak.api_key_id,
    ak.tenant_id,
    ui.user_id,
    ui.display_name,
    ak.expire_at,
    ui.is_active
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
WHERE ak.api_key = 'SERVICE_API'
  AND ak.secret_hash = crypt('provided-secret', ak.secret_hash)
  AND ui.is_active = true
  AND (ak.expire_at IS NULL OR ak.expire_at > now());

Check API Key Permissions

-- Check if API key has permission (same as user check)
SELECT auth.has_permission(
    _tenant_id := 1,
    _user_id := (
        SELECT ui.user_id
        FROM auth.api_key ak
        JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
        WHERE ak.api_key = 'SERVICE_API'
    ),
    _perm_code := 'reports.generate'
);

Querying API Keys

List Tenant API Keys

-- Get all API keys for a tenant
SELECT
    ak.api_key,
    ak.title,
    ui.display_name as api_user_name,
    ak.expires_at,
    ak.is_active,
    ak.created_at,
    COUNT(pa.permission_assignment_id) as permission_count
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
LEFT JOIN auth.user_permission pa ON ui.user_id = pa.user_id
WHERE ak.tenant_id = 1
GROUP BY ak.api_key_id, ui.user_id
ORDER BY ak.created_at DESC;

API Key Permission Summary

-- View API key permissions
SELECT
    ak.api_key as api_key_code,
    ak.title as api_key_name,
    array_agg(DISTINCT ps.code) FILTER (WHERE ps.code IS NOT NULL) as permission_sets,
    array_agg(DISTINCT p.code) FILTER (WHERE p.code IS NOT NULL) as individual_permissions
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
LEFT JOIN auth.user_permission pa ON ui.user_id = pa.user_id
LEFT JOIN auth.perm_set ps ON pa.perm_set_id = ps.perm_set_id
LEFT JOIN auth.permission p ON pa.permission_id = p.permission_id
WHERE ak.tenant_id = 1
  AND ak.api_key = 'SERVICE_API'
GROUP BY ak.api_key_id;

Expiring API Keys

-- Find API keys expiring soon
SELECT
    ak.api_key,
    ak.title,
    ak.expire_at,
    ak.expire_at - now() as time_until_expiry
FROM auth.api_key ak
WHERE ak.expire_at IS NOT NULL
  AND ak.expire_at <= now() + interval '30 days'
ORDER BY ak.expire_at;

Common API Key Patterns

Service Integration Keys

-- Create service-specific API keys
CREATE OR REPLACE FUNCTION create_service_api_key(
    _tenant_id int,
    _service_name text,
    _permissions text[]
) RETURNS int AS $$
DECLARE
    v_result record;
    v_user_id uuid;
    v_perm text;
BEGIN
    -- Create API key
    SELECT * FROM auth.create_api_key(
        _created_by := 'system',
        _user_id := 1,
        _title := _service_name || ' Service API',
        _description := 'Auto-generated API key for ' || _service_name,
        _api_secret := encode(gen_random_bytes(32), 'base64'),
        _tenant_id := _tenant_id
    ) INTO v_result;

    -- Get the API user ID
    SELECT ui.user_id INTO v_user_id
    FROM auth.api_key ak
    JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
    WHERE ak.api_key_id = v_result.api_key_id;

    -- Assign permissions
    FOREACH v_perm IN ARRAY _permissions
    LOOP
        PERFORM auth.assign_permission(
            _tenant_id::uuid,
            v_user_id,
            NULL,
            v_perm,
            NULL
        );
    END LOOP;

    RETURN v_result.api_key_id;
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT create_service_api_key(
    1,  -- tenant_id as integer
    'Payment Processing',
    ARRAY['PAYMENT_PROCESSOR', 'ORDER_READER']
);

Temporary Access Keys

-- Create temporary API key for data migration
SELECT auth.create_api_key(
    _created_by := 'admin_user',
    _user_id := 1,
    _title := 'Data Migration Tool',
    _description := 'Temporary key for data migration process',
    _api_secret := 'migration-secret-' || extract(epoch from now())::text,
    _expire_at := now() + interval '7 days',
    _tenant_id := 1
);

Read-Only API Keys

-- Create read-only API key for reporting
DO $$
DECLARE
    v_api_result record;
    v_user_id uuid;
BEGIN
    SELECT * FROM auth.create_api_key(
        _created_by := 'admin_user',
        _user_id := 1,
        _title := 'Read-Only Reporting API',
        _description := 'Read-only access for reporting system',
        _api_secret := 'readonly-reports-secret-' || gen_random_uuid()::text,
        _tenant_id := 1
    ) INTO v_api_result;

    -- Get the API user ID
    SELECT ui.user_id INTO v_user_id
    FROM auth.api_key ak
    JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
    WHERE ak.api_key_id = v_api_result.api_key_id;

    -- Assign read-only permission set
    PERFORM auth.assign_permission(
        1::uuid,
        v_user_id,
        NULL,
        'READ_ONLY_ACCESS',
        NULL
    );
END $$;

Security Best Practices

Secret Management

1. Generate Strong Secrets: Use cryptographically secure random generators
2. Hash Storage: Store hashed secrets, never plaintext
3. Regular Rotation: Implement secret rotation schedules
4. Environment Variables: Store secrets in environment variables, not code

-- Generate secure API secret
SELECT encode(gen_random_bytes(32), 'base64') as secure_secret;

Access Control

1. Principle of Least Privilege: Only assign necessary permissions
2. Scope Appropriately: Ensure tenant isolation
3. Monitor Usage: Track API key usage and access patterns
4. Expire Unused Keys: Set expiration dates for temporary access

Audit and Monitoring

-- Track API key usage
SELECT
    ak.api_key,
    COUNT(ue.user_event_id) as usage_count,
    MAX(ue.event_at) as last_used
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
LEFT JOIN auth.user_event ue ON ui.user_id = ue.user_id
WHERE ak.tenant_id = 1
GROUP BY ak.api_key_id
ORDER BY usage_count DESC;

Troubleshooting

API Key Authentication Issues

-- Debug API key authentication
SELECT
    ak.api_key,
    ui.is_active as user_active,
    ak.expire_at,
    CASE
        WHEN ak.expire_at IS NULL THEN 'Never expires'
        WHEN ak.expire_at > now() THEN 'Valid'
        ELSE 'Expired'
    END as expiry_status
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
WHERE ak.api_key = 'problematic-key';

Permission Issues

-- Check API key effective permissions
SELECT
    ak.api_key,
    'Direct' as assignment_type,
    ps.code as permission_set,
    p.code as permission
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
JOIN auth.user_permission pa ON ui.user_id = pa.user_id
LEFT JOIN auth.perm_set ps ON pa.perm_set_id = ps.perm_set_id
LEFT JOIN auth.permission p ON pa.permission_id = p.permission_id
WHERE ak.api_key = 'SERVICE_API'

UNION ALL

SELECT
    ak.api_key,
    'Via Group' as assignment_type,
    ps.code,
    p.code
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
JOIN auth.user_group_member ugm ON ui.user_id = ugm.user_id
JOIN auth.user_permission pa ON ugm.user_group_id = pa.user_group_id
LEFT JOIN auth.perm_set ps ON pa.perm_set_id = ps.perm_set_id
LEFT JOIN auth.permission p ON pa.permission_id = p.permission_id
WHERE ak.api_key = 'SERVICE_API';

API Key Validation

The system provides comprehensive API key validation functions that handle authentication, authorization, and security checks. The main validation function is auth.validate_api_key() which performs complete validation including secret verification, expiration checks, and user status validation.

Core Validation Function

-- Complete API key validation
SELECT auth.validate_api_key(
    _api_key := 'SERVICE_API_KEY_123',
    _api_secret := 'provided-secret-from-request',
    _tenant_id := 1  -- Optional: validate against specific tenant
) as validation_result;

-- Returns record with:
-- - api_key_id: int
-- - user_id: uuid
-- - tenant_id: int
-- - is_valid: boolean
-- - error_message: text (if validation fails)

Validation Process Flow

The validation function performs the following checks in order:

flowchart TD
    A[API Key Validation Request] --> B[Find API Key Record]
    B -->|Not Found| C[Return: Invalid Key]
    B -->|Found| D[Verify Secret Hash]
    D -->|Mismatch| E[Return: Invalid Secret]
    D -->|Match| F[Check Expiration]
    F -->|Expired| G[Return: Key Expired]
    F -->|Valid| H[Check API User Status]
    H -->|Inactive| I[Return: User Disabled]
    H -->|Active| J[Check Tenant Access]
    J -->|No Access| K[Return: Tenant Access Denied]
    J -->|Has Access| L[Log Success Event]
    L --> M[Return: Validation Success]

    C --> N[Log Failed Attempt]
    E --> N
    G --> N
    I --> N
    K --> N

Basic Validation Examples

Simple API Key Validation

-- Basic validation without tenant restriction
DO $$
DECLARE
    v_validation record;
BEGIN
    SELECT * FROM auth.validate_api_key(
        'SERVICE_API_KEY',
        'provided-secret-123',
        NULL  -- Allow any tenant
    ) INTO v_validation;

    IF v_validation.is_valid THEN
        RAISE NOTICE 'API key valid for user: %', v_validation.user_id;
        -- Proceed with authenticated request
    ELSE
        RAISE NOTICE 'API key validation failed: %', v_validation.error_message;
        -- Return authentication error
    END IF;
END $$;

Tenant-Specific Validation

-- Validate API key for specific tenant
CREATE OR REPLACE FUNCTION validate_tenant_api_request(
    _api_key text,
    _api_secret text,
    _required_tenant_id int
) RETURNS jsonb AS $$
DECLARE
    v_validation record;
    v_result jsonb;
BEGIN
    -- Validate API key
    SELECT * FROM auth.validate_api_key(
        _api_key,
        _api_secret,
        _required_tenant_id
    ) INTO v_validation;

    IF NOT v_validation.is_valid THEN
        -- Return error response
        v_result := jsonb_build_object(
            'success', false,
            'error', 'authentication_failed',
            'message', v_validation.error_message
        );

        -- Log failed attempt
        PERFORM auth.create_auth_event(
            _required_tenant_id,
            NULL,  -- No user ID for failed auth
            '52001',  -- AUTH_FAILED
            'API key validation failed',
            jsonb_build_object('api_key', _api_key, 'reason', v_validation.error_message)
        );

        RETURN v_result;
    END IF;

    -- Return success with user context
    v_result := jsonb_build_object(
        'success', true,
        'user_id', v_validation.user_id,
        'tenant_id', v_validation.tenant_id,
        'api_key_id', v_validation.api_key_id
    );

    RETURN v_result;
END;
$$ LANGUAGE plpgsql;

Manual Validation Steps

For applications that need custom validation logic:

Step 1: Find and Verify API Key

-- Find API key and verify secret
SELECT
    ak.api_key_id,
    ak.tenant_id,
    ak.expire_at,
    ak.secret_hash,
    ui.user_id,
    ui.is_active as user_active
FROM auth.api_key ak
JOIN auth.user_info ui ON ui.code = auth.generate_api_key_username(ak.api_key)
WHERE ak.api_key = 'provided-api-key'
  AND ak.secret_hash = crypt('provided-secret', ak.secret_hash)
  AND ui.user_type_code = 'api';

Step 2: Check Expiration

-- Verify key hasn't expired
SELECT
    CASE
        WHEN expire_at IS NULL THEN true  -- Never expires
        WHEN expire_at > now() THEN true  -- Still valid
        ELSE false  -- Expired
    END as is_not_expired
FROM auth.api_key
WHERE api_key_id = found_api_key_id;

Step 3: Validate Tenant Access

-- Check if API user has access to requested tenant
SELECT EXISTS (
    SELECT 1 FROM auth.tenant_user tu
    WHERE tu.user_id = api_user_id
      AND tu.tenant_id = requested_tenant_id
      AND tu.is_active = true
) as has_tenant_access;

Permission Validation After Authentication

Once the API key is validated, check specific permissions:

-- Complete request validation example
CREATE OR REPLACE FUNCTION process_api_request(
    _api_key text,
    _api_secret text,
    _tenant_id int,
    _required_permission text,
    _operation_data jsonb DEFAULT '{}'::jsonb
) RETURNS jsonb AS $$
DECLARE
    v_validation record;
    v_result jsonb;
BEGIN
    -- Step 1: Validate API key
    SELECT * FROM auth.validate_api_key(_api_key, _api_secret, _tenant_id)
    INTO v_validation;

    IF NOT v_validation.is_valid THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'authentication_failed',
            'message', v_validation.error_message
        );
    END IF;

    -- Step 2: Check permission
    IF NOT auth.has_permission(_tenant_id::uuid, v_validation.user_id, _required_permission, false) THEN
        -- Log permission denied
        PERFORM auth.create_auth_event(
            _tenant_id,
            v_validation.user_id,
            '52002',  -- PERMISSION_DENIED
            'API request permission denied',
            jsonb_build_object(
                'required_permission', _required_permission,
                'api_key', _api_key
            )
        );

        RETURN jsonb_build_object(
            'success', false,
            'error', 'permission_denied',
            'message', 'Insufficient permissions for this operation'
        );
    END IF;

    -- Step 3: Process the request (business logic here)
    -- ... your application logic ...

    -- Step 4: Log successful operation
    PERFORM auth.create_auth_event(
        _tenant_id,
        v_validation.user_id,
        '50100',  -- OPERATION_SUCCESS
        'API operation completed successfully',
        _operation_data
    );

    RETURN jsonb_build_object(
        'success', true,
        'message', 'Operation completed successfully'
    );
END;
$$ LANGUAGE plpgsql;

-- Usage
SELECT process_api_request(
    'SERVICE_API_KEY',
    'secret-123',
    1,
    'reports.generate',
    '{"report_type": "user_activity"}'::jsonb
);

Validation Error Handling

The validation system provides specific error messages for different failure scenarios:

-- Handle different validation failure types
CREATE OR REPLACE FUNCTION handle_api_validation_error(
    _error_message text
) RETURNS jsonb AS $$
BEGIN
    CASE _error_message
        WHEN 'api_key_not_found' THEN
            RETURN jsonb_build_object(
                'http_status', 401,
                'error_code', 'INVALID_API_KEY',
                'message', 'Invalid API key provided'
            );

        WHEN 'invalid_secret' THEN
            RETURN jsonb_build_object(
                'http_status', 401,
                'error_code', 'INVALID_SECRET',
                'message', 'Invalid API secret provided'
            );

        WHEN 'api_key_expired' THEN
            RETURN jsonb_build_object(
                'http_status', 401,
                'error_code', 'API_KEY_EXPIRED',
                'message', 'API key has expired'
            );

        WHEN 'user_inactive' THEN
            RETURN jsonb_build_object(
                'http_status', 403,
                'error_code', 'USER_DISABLED',
                'message', 'API user account is disabled'
            );

        WHEN 'no_tenant_access' THEN
            RETURN jsonb_build_object(
                'http_status', 403,
                'error_code', 'TENANT_ACCESS_DENIED',
                'message', 'API key does not have access to this tenant'
            );

        ELSE
            RETURN jsonb_build_object(
                'http_status', 500,
                'error_code', 'VALIDATION_ERROR',
                'message', 'API key validation failed'
            );
    END CASE;
END;
$$ LANGUAGE plpgsql;

Rate Limiting and Security

Implement rate limiting and security measures:

-- Track API key usage for rate limiting
CREATE TABLE IF NOT EXISTS auth.api_key_usage (
    api_key_id int REFERENCES auth.api_key(api_key_id),
    request_timestamp timestamptz DEFAULT now(),
    request_ip inet,
    request_path text,
    response_status int,
    tenant_id int
);

-- Check rate limits before validation
CREATE OR REPLACE FUNCTION check_api_rate_limit(
    _api_key text,
    _time_window interval DEFAULT '1 hour',
    _max_requests int DEFAULT 1000
) RETURNS boolean AS $$
DECLARE
    v_request_count int;
    v_api_key_id int;
BEGIN
    -- Get API key ID
    SELECT api_key_id INTO v_api_key_id
    FROM auth.api_key
    WHERE api_key = _api_key;

    IF v_api_key_id IS NULL THEN
        RETURN false;  -- Invalid key
    END IF;

    -- Count recent requests
    SELECT COUNT(*) INTO v_request_count
    FROM auth.api_key_usage
    WHERE api_key_id = v_api_key_id
      AND request_timestamp > now() - _time_window;

    RETURN v_request_count < _max_requests;
END;
$$ LANGUAGE plpgsql;

-- Log API usage
CREATE OR REPLACE FUNCTION log_api_usage(
    _api_key text,
    _tenant_id int,
    _request_ip text,
    _request_path text,
    _response_status int
) RETURNS void AS $$
DECLARE
    v_api_key_id int;
BEGIN
    SELECT api_key_id INTO v_api_key_id
    FROM auth.api_key
    WHERE api_key = _api_key;

    INSERT INTO auth.api_key_usage (
        api_key_id,
        request_ip,
        request_path,
        response_status,
        tenant_id
    ) VALUES (
        v_api_key_id,
        _request_ip::inet,
        _request_path,
        _response_status,
        _tenant_id
    );
END;
$$ LANGUAGE plpgsql;

Validation Best Practices

1. Always Validate First: Never process requests without proper API key validation
2. Use Secure Comparisons: Always use cryptographic hash comparison for secrets
3. Log All Attempts: Log both successful and failed validation attempts
4. Rate Limiting: Implement rate limiting to prevent abuse
5. Tenant Isolation: Always validate tenant access when applicable
6. Permission Checking: Validate specific permissions after authentication
7. Error Handling: Provide appropriate error responses without revealing sensitive information

Integration Example

Complete example of integrating API key validation in an application:

-- Application endpoint function
CREATE OR REPLACE FUNCTION api_endpoint_get_users(
    _api_key text,
    _api_secret text,
    _tenant_id int,
    _filters jsonb DEFAULT '{}'::jsonb
) RETURNS jsonb AS $$
DECLARE
    v_validation record;
    v_users jsonb;
BEGIN
    -- Step 1: Rate limiting check
    IF NOT check_api_rate_limit(_api_key, '1 hour', 1000) THEN
        RETURN jsonb_build_object(
            'success', false,
            'error', 'rate_limit_exceeded',
            'message', 'Too many requests. Please try again later.'
        );
    END IF;

    -- Step 2: Validate API key
    SELECT * FROM auth.validate_api_key(_api_key, _api_secret, _tenant_id)
    INTO v_validation;

    IF NOT v_validation.is_valid THEN
        -- Log usage with failure
        PERFORM log_api_usage(_api_key, _tenant_id, 'unknown', '/api/users', 401);

        RETURN handle_api_validation_error(v_validation.error_message);
    END IF;

    -- Step 3: Check permission
    IF NOT auth.has_permission(_tenant_id::uuid, v_validation.user_id, 'users.read', false) THEN
        PERFORM log_api_usage(_api_key, _tenant_id, 'unknown', '/api/users', 403);

        RETURN jsonb_build_object(
            'success', false,
            'error', 'permission_denied',
            'message', 'Insufficient permissions to read users'
        );
    END IF;

    -- Step 4: Execute business logic
    SELECT jsonb_agg(
        jsonb_build_object(
            'user_id', u.user_id,
            'username', u.username,
            'display_name', u.display_name,
            'email', u.email
        )
    ) INTO v_users
    FROM auth.user_info u
    JOIN auth.tenant_user tu ON u.user_id = tu.user_id
    WHERE tu.tenant_id = _tenant_id
      AND tu.is_active = true
      AND u.is_active = true;

    -- Step 5: Log successful request
    PERFORM log_api_usage(_api_key, _tenant_id, 'unknown', '/api/users', 200);
    PERFORM auth.create_auth_event(
        _tenant_id,
        v_validation.user_id,
        '50101',  -- DATA_ACCESS
        'API users list accessed',
        _filters
    );

    -- Step 6: Return results
    RETURN jsonb_build_object(
        'success', true,
        'data', COALESCE(v_users, '[]'::jsonb)
    );
END;
$$ LANGUAGE plpgsql;

What's Next

• Learn about Technical Users in detail
• Explore Identity Providers for human users
• Review Security Best Practices
• Understand Audit Logging for API key usage