PostgreSQL Connector

# Create a custom parameter group for PostgreSQL with pgAudit
aws rds create-db-parameter-group \
  --db-parameter-group-name postgres-audit-params \
  --db-parameter-group-family postgres15 \
  --description "PostgreSQL parameter group with pgAudit enabled"

# Enable pgAudit shared library and configure logging
aws rds modify-db-parameter-group \
  --db-parameter-group-name postgres-audit-params \
  --parameters \
    "ParameterName=shared_preload_libraries,ParameterValue=pgaudit,ApplyMethod=pending-reboot" \
    "ParameterName=pgaudit.log,ParameterValue=all,ApplyMethod=immediate" \
    "ParameterName=log_statement,ParameterValue=all,ApplyMethod=immediate" \
    "ParameterName=log_connections,ParameterValue=1,ApplyMethod=immediate" \
    "ParameterName=log_disconnections,ParameterValue=1,ApplyMethod=immediate"

# Apply the parameter group to your instance
aws rds modify-db-instance \
  --db-instance-identifier my-postgres-instance \
  --db-parameter-group-name postgres-audit-params \
  --apply-immediately

# Enable CloudWatch Logs export for PostgreSQL logs
aws rds modify-db-instance \
  --db-instance-identifier my-postgres-instance \
  --cloudwatch-logs-export-configuration '{"EnableLogTypes":["postgresql","upgrade"]}'

# Create a new instance with CloudWatch logging enabled
aws rds create-db-instance \
  --db-instance-identifier my-postgres-instance \
  --db-instance-class db.m5.large \
  --engine postgres \
  --engine-version 15.4 \
  --master-username admin \
  --manage-master-user-password \
  --allocated-storage 100 \
  --db-parameter-group-name postgres-audit-params \
  --cloudwatch-logs-export-configuration '["postgresql","upgrade"]'

# Verify CloudWatch Logs export is enabled
aws rds describe-db-instances \
  --db-instance-identifier my-postgres-instance \
  --query 'DBInstances[0].EnabledCloudwatchLogsExports'

-- Connect to your RDS PostgreSQL instance and enable pgAudit
-- Note: shared_preload_libraries must be set in parameter group first

-- Create the pgAudit extension
CREATE EXTENSION IF NOT EXISTS pgaudit;

-- Configure pgAudit for comprehensive logging
-- Log all statement types
ALTER SYSTEM SET pgaudit.log = 'all';

-- Log catalog access for role and privilege queries
ALTER SYSTEM SET pgaudit.log_catalog = on;

-- Include parameter values in log entries
ALTER SYSTEM SET pgaudit.log_parameter = on;

-- Include the fully-qualified object name
ALTER SYSTEM SET pgaudit.log_relation = on;

-- Configure session-based audit logging
ALTER SYSTEM SET pgaudit.log_statement_once = off;

-- Reload configuration
SELECT pg_reload_conf();

-- Verify pgAudit is active
SHOW shared_preload_libraries;
SELECT * FROM pg_extension WHERE extname = 'pgaudit';

-- Create role-based audit policy for specific users
CREATE ROLE auditor NOLOGIN;
ALTER ROLE dbaudit_reader SET pgaudit.role = 'auditor';
GRANT SELECT ON ALL TABLES IN SCHEMA public TO auditor;

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "logs:DescribeLogGroups",
        "logs:DescribeLogStreams",
        "logs:GetLogEvents",
        "logs:FilterLogEvents"
      ],
      "Resource": [
        "arn:aws:logs:*:*:log-group:/aws/rds/instance/*/postgresql:*"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "rds:DescribeDBInstances",
        "rds:DescribeDBLogFiles",
        "rds:DownloadDBLogFilePortion"
      ],
      "Resource": "*"
    }
  ]
}

# DB Audit configuration for RDS PostgreSQL via CloudWatch
databases:
  - name: production-rds-postgres
    type: postgresql

    # RDS endpoint
    host: my-postgres-instance.xxxx.us-east-1.rds.amazonaws.com
    port: 5432
    database: mydb
    username: dbaudit_reader
    password: ${RDS_POSTGRES_PASSWORD}

    # SSL is enabled by default for RDS
    ssl: true
    ssl_mode: require

    options:
      # Use CloudWatch as the log source
      log_source: cloudwatch

      # CloudWatch configuration
      cloudwatch:
        region: us-east-1
        log_group: /aws/rds/instance/my-postgres-instance/postgresql

        # AWS credentials (use IAM role if running on EC2/ECS)
        # access_key_id: ${AWS_ACCESS_KEY_ID}
        # secret_access_key: ${AWS_SECRET_ACCESS_KEY}

        # Or use IAM role (recommended)
        use_iam_role: true

      # Polling interval for CloudWatch logs
      polling_interval: 30

      # Also query pg_stat_statements directly for real-time metrics
      query_pg_stat_statements: true
      track_queries: true
      track_connections: true

# View PostgreSQL logs in CloudWatch Logs Insights
# Log group: /aws/rds/instance/<instance-id>/postgresql

# Query for failed login attempts
fields @timestamp, @message
| filter @message like /FATAL|authentication failed/
| sort @timestamp desc
| limit 100

# Query for DDL operations
fields @timestamp, @message
| filter @message like /CREATE|DROP|ALTER|TRUNCATE/
| sort @timestamp desc
| limit 100

# Query for specific user activity
fields @timestamp, @message
| filter @message like /user=admin/
| sort @timestamp desc
| limit 50

# Query pgAudit logs specifically
fields @timestamp, @message
| filter @message like /AUDIT:/
| parse @message "AUDIT: *,*,*,*,*,*,*" as session_id, statement_id, substatement_id, class, command, object_type, object_name
| sort @timestamp desc
| limit 100

# Query for slow queries (log_min_duration_statement)
fields @timestamp, @message
| filter @message like /duration:/
| parse @message "duration: * ms" as duration_ms
| filter duration_ms > 1000
| sort @timestamp desc
| limit 50