Audit Logging

-- Enable audit logging
ALTER SYSTEM SET audit_logging = on;
ALTER SYSTEM SET audit_log_format = 'json';
ALTER SYSTEM SET audit_log_destination = 'file';  -- or 'syslog', 'database'
ALTER SYSTEM SET audit_log_directory = '/var/log/entropydb/audit';
ALTER SYSTEM SET audit_log_rotation = '1d';  -- Daily rotation
ALTER SYSTEM SET audit_log_retention = '90d';  -- Keep for 90 days

-- Configure what to audit
ALTER SYSTEM SET audit_log_connections = on;
ALTER SYSTEM SET audit_log_disconnections = on;
ALTER SYSTEM SET audit_log_ddl = on;
ALTER SYSTEM SET audit_log_dml = on;
ALTER SYSTEM SET audit_log_failed_queries = on;

-- Reload configuration
SELECT pg_reload_conf();

-- Audit all access to sensitive table
CREATE AUDIT POLICY audit_users
  ON users
  FOR SELECT, INSERT, UPDATE, DELETE
  AUDIT ALL;

-- Audit specific columns
CREATE AUDIT POLICY audit_salaries
  ON employees
  FOR SELECT (salary, bonus)
  AUDIT ALL;

-- Audit only certain operations
CREATE AUDIT POLICY audit_deletions
  ON orders
  FOR DELETE
  AUDIT ALL;

-- Audit based on user role
CREATE AUDIT POLICY audit_external_access
  ON confidential_data
  FOR ALL
  WHEN (current_user_has_role('external_contractor'))
  AUDIT ALL;

-- Audit failed access attempts
CREATE AUDIT POLICY audit_failures
  ON sensitive_data
  FOR SELECT
  AUDIT FAILED_ONLY;

-- View active audit policies
SELECT * FROM entropy_audit_policies;

-- Disable audit policy
ALTER AUDIT POLICY audit_users DISABLE;

-- Drop audit policy
DROP AUDIT POLICY audit_users;

-- View recent audit events
SELECT 
  timestamp,
  username,
  database_name,
  event_type,
  object_type,
  object_name,
  command,
  success
FROM entropy_audit_log
WHERE timestamp > NOW() - INTERVAL '1 hour'
ORDER BY timestamp DESC;

-- Find failed authentication attempts
SELECT 
  timestamp,
  username,
  client_addr,
  event_type,
  details
FROM entropy_audit_log
WHERE event_type = 'AUTH_FAILED'
  AND timestamp > NOW() - INTERVAL '24 hours'
ORDER BY timestamp DESC;

-- Track data access by user
SELECT 
  username,
  COUNT(*) as access_count,
  COUNT(DISTINCT object_name) as tables_accessed
FROM entropy_audit_log
WHERE event_type IN ('SELECT', 'UPDATE', 'DELETE')
  AND timestamp > NOW() - INTERVAL '7 days'
GROUP BY username
ORDER BY access_count DESC;

-- Find suspicious patterns
SELECT 
  username,
  client_addr,
  COUNT(*) as failed_attempts,
  MAX(timestamp) as last_attempt
FROM entropy_audit_log
WHERE event_type = 'AUTH_FAILED'
  AND timestamp > NOW() - INTERVAL '1 hour'
GROUP BY username, client_addr
HAVING COUNT(*) > 5
ORDER BY failed_attempts DESC;

-- Export audit logs
COPY (
  SELECT * FROM entropy_audit_log
  WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'
) TO '/exports/audit_january_2024.csv' CSV HEADER;

# Send audit logs to Splunk
# entropydb.conf
audit_log_destination = 'syslog'
audit_syslog_facility = 'LOCAL0'
audit_syslog_format = 'json'

# Configure rsyslog to forward to Splunk
# /etc/rsyslog.d/entropydb.conf
local0.* @@splunk.example.com:514

# Or use Splunk Universal Forwarder
# /opt/splunkforwarder/etc/apps/entropydb/inputs.conf
[monitor:///var/log/entropydb/audit/*.log]
disabled = false
index = entropydb_audit
sourcetype = entropydb:audit
host = entropydb-prod-1

# Elasticsearch integration
# Use Filebeat to ship logs
# filebeat.yml
filebeat.inputs:
- type: log
  enabled: true
  paths:
    - /var/log/entropydb/audit/*.log
  json.keys_under_root: true
  json.add_error_key: true
  fields:
    service: entropydb
    environment: production

output.elasticsearch:
  hosts: ["elasticsearch:9200"]
  index: "entropydb-audit-%{+yyyy.MM.dd}"

# Query in Kibana
GET /entropydb-audit-*/_search
{
  "query": {
    "bool": {
      "must": [
        { "match": { "event_type": "AUTH_FAILED" }},
        { "range": { "timestamp": { "gte": "now-24h" }}}
      ]
    }
  }
}

-- Generate compliance report
SELECT 
  DATE(timestamp) as date,
  event_type,
  COUNT(*) as event_count,
  COUNT(DISTINCT username) as unique_users,
  COUNT(DISTINCT client_addr) as unique_ips
FROM entropy_audit_log
WHERE timestamp BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY DATE(timestamp), event_type
ORDER BY date, event_type;

-- User activity report
SELECT 
  username,
  COUNT(*) FILTER (WHERE event_type LIKE 'SELECT%') as reads,
  COUNT(*) FILTER (WHERE event_type IN ('INSERT', 'UPDATE', 'DELETE')) as writes,
  COUNT(*) FILTER (WHERE success = false) as failures,
  MIN(timestamp) as first_activity,
  MAX(timestamp) as last_activity
FROM entropy_audit_log
WHERE timestamp > NOW() - INTERVAL '30 days'
GROUP BY username
ORDER BY (reads + writes) DESC;

-- Privilege escalation detection
SELECT 
  username,
  timestamp,
  command,
  details
FROM entropy_audit_log
WHERE event_type IN ('GRANT', 'REVOKE', 'ALTER_ROLE')
  AND timestamp > NOW() - INTERVAL '7 days'
ORDER BY timestamp DESC;

-- Data export tracking (for GDPR/CCPA)
SELECT 
  username,
  timestamp,
  object_name,
  rows_affected,
  client_addr
FROM entropy_audit_log
WHERE command LIKE 'COPY%TO%'
   OR command LIKE 'SELECT%INTO OUTFILE%'
ORDER BY timestamp DESC;