Redshift Connector

-- Create a dedicated audit user with minimal privileges
-- Connect as admin/superuser

CREATE USER dbaudit_reader PASSWORD 'your_secure_password';

-- Grant access to system tables for audit data
GRANT SELECT ON ALL TABLES IN SCHEMA pg_catalog TO dbaudit_reader;

-- Grant access to STL tables (query history, connections)
GRANT SELECT ON stl_query TO dbaudit_reader;
GRANT SELECT ON stl_querytext TO dbaudit_reader;
GRANT SELECT ON stl_connection_log TO dbaudit_reader;
GRANT SELECT ON stl_ddltext TO dbaudit_reader;
GRANT SELECT ON stl_userlog TO dbaudit_reader;
GRANT SELECT ON stl_wlm_query TO dbaudit_reader;

-- Grant access to SYS views (newer Redshift)
GRANT SELECT ON sys_query_history TO dbaudit_reader;
GRANT SELECT ON sys_query_detail TO dbaudit_reader;
GRANT SELECT ON sys_connection_log TO dbaudit_reader;
GRANT SELECT ON sys_user_history TO dbaudit_reader;

-- Grant access to SVL views
GRANT SELECT ON svl_user_info TO dbaudit_reader;
GRANT SELECT ON svl_statementtext TO dbaudit_reader;

-- Enable Redshift audit logging to S3 (optional but recommended)
-- This enables logging to S3 and CloudWatch

-- Via AWS Console:
-- 1. Go to Redshift Console > Clusters > Your Cluster
-- 2. Properties > Audit logging > Modify
-- 3. Enable audit logging and specify S3 bucket

-- Via AWS CLI:
aws redshift modify-cluster \
  --cluster-identifier your-cluster-id \
  --logging-properties \
    "BucketName=your-audit-bucket,S3KeyPrefix=redshift-audit/" \
  --region us-east-1

-- Enable user activity logging (captures full query text)
-- This requires a parameter group change
aws redshift modify-cluster-parameter-group \
  --parameter-group-name your-parameter-group \
  --parameters ParameterName=enable_user_activity_logging,ParameterValue=true

-- Enable connection logging
aws redshift modify-cluster-parameter-group \
  --parameter-group-name your-parameter-group \
  --parameters ParameterName=log_connections,ParameterValue=true

databases:
  - name: production-redshift
    type: redshift
    host: your-cluster.xxxx.us-east-1.redshift.amazonaws.com
    port: 5439
    database: your_database
    username: dbaudit_reader
    password: ${REDSHIFT_PASSWORD}

    # SSL Configuration (required for Redshift)
    ssl:
      mode: require
      # ca_cert: /etc/dbaudit/certs/redshift-ca.pem  # For verify-ca/verify-full

    # Connection settings
    connect_timeout: 30
    query_timeout: 300

    # Monitoring options
    options:
      track_queries: true
      track_connections: true
      track_ddl: true
      track_user_changes: true
      history_days: 7

# Test connection with psql
psql -h your-cluster.xxxx.us-east-1.redshift.amazonaws.com \
  -p 5439 -U dbaudit_reader -d your_database \
  -c "SELECT COUNT(*) FROM stl_query WHERE starttime > DATEADD(day, -1, GETDATE());"

# Test with SSL
psql "host=your-cluster.xxxx.us-east-1.redshift.amazonaws.com \
  port=5439 user=dbaudit_reader dbname=your_database sslmode=require" \
  -c "SELECT version();"

# Check audit logging status
aws redshift describe-logging-status \
  --cluster-identifier your-cluster-id \
  --region us-east-1

# Verify DB Audit collector can connect
dbaudit-collector test-connections --config /etc/dbaudit/config.yaml