Redshift Cluster Audit Logging

Overview

This check verifies that Amazon Redshift clusters have audit logging enabled. Audit logging captures connection attempts, user queries, and user activity, then exports these events to Amazon S3 or CloudWatch Logs for security monitoring and compliance.

Risk

Without audit logging enabled:

• Undetected malicious activity - Unauthorized access or data exfiltration can go unnoticed
• No forensic evidence - Security investigations lack the data needed to understand what happened
• Compliance failures - Many regulations (PCI DSS, HIPAA, SOC 2) require database activity logging
• Insider threats - Unusual user behavior patterns cannot be identified or tracked

Remediation Steps

Prerequisites

• Access to the AWS Console with permissions to modify Redshift clusters
• An S3 bucket in the same region as your Redshift cluster (for S3 logging)
• The S3 bucket must allow Redshift to write logs (bucket policy required)

S3 bucket policy for Redshift logging

Your S3 bucket needs a policy that allows Redshift to write logs. Replace <your-bucket-name>, <your-aws-account-id>, and <your-cluster-region> with your values:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "Put bucket policy needed for Redshift audit logging",
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": [
        "s3:PutObject",
        "s3:GetBucketAcl"
      ],
      "Resource": [
        "arn:aws:s3:::<your-bucket-name>",
        "arn:aws:s3:::<your-bucket-name>/*"
      ],
      "Condition": {
        "StringEquals": {
          "aws:SourceAccount": "<your-aws-account-id>"
        },
        "ArnLike": {
          "aws:SourceArn": "arn:aws:redshift:<your-cluster-region>:<your-aws-account-id>:cluster:*"
        }
      }
    }
  ]
}

AWS Console Method

1. Open the Amazon Redshift console at https://console.aws.amazon.com/redshift/
2. In the left navigation, click Clusters
3. Click on the name of the cluster you want to configure
4. Select the Properties tab
5. Scroll down to Database audit logging and click Edit
6. Toggle Enable audit logging to On
7. Choose your log destination:
   • S3 - Select an existing S3 bucket and optionally add a key prefix
   • CloudWatch - Select which log types to export (connection log, user log, user activity log)
8. Click Save changes

AWS CLI (optional)

Enable logging to S3:

aws redshift enable-logging \
  --cluster-identifier <your-cluster-identifier> \
  --bucket-name <your-s3-bucket-name> \
  --s3-key-prefix "redshift-logs/" \
  --region us-east-1

Enable logging to CloudWatch:

aws redshift enable-logging \
  --cluster-identifier <your-cluster-identifier> \
  --log-destination-type cloudwatch \
  --log-exports connectionlog userlog useractivitylog \
  --region us-east-1

Parameters:

• --cluster-identifier - The name of your Redshift cluster
• --bucket-name - S3 bucket for logs (must be in the same region as the cluster)
• --s3-key-prefix - Optional prefix for log file names
• --log-destination-type - Either s3 or cloudwatch
• --log-exports - Log types: connectionlog, userlog, useractivitylog

CloudFormation (optional)

Add the LoggingProperties block to your AWS::Redshift::Cluster resource:

AWSTemplateFormatVersion: '2010-09-09'
Description: Redshift cluster with audit logging enabled

Parameters:
  ClusterIdentifier:
    Type: String
    Description: The identifier for the Redshift cluster
  LogBucketName:
    Type: String
    Description: S3 bucket name for storing audit logs
  S3KeyPrefix:
    Type: String
    Default: redshift-logs/
    Description: Prefix for log files in the S3 bucket

Resources:
  RedshiftCluster:
    Type: AWS::Redshift::Cluster
    Properties:
      ClusterIdentifier: !Ref ClusterIdentifier
      LoggingProperties:
        BucketName: !Ref LogBucketName
        S3KeyPrefix: !Ref S3KeyPrefix
      # Additional required properties for your cluster
      NodeType: dc2.large
      MasterUsername: admin
      MasterUserPassword: '{{resolve:secretsmanager:MyRedshiftSecret:SecretString:password}}'
      ClusterType: single-node
      DBName: mydb

Outputs:
  ClusterIdentifier:
    Description: The Redshift cluster identifier
    Value: !Ref RedshiftCluster

Note: For existing clusters, you may need to import the resource into CloudFormation or use AWS::Redshift::ClusterParameterGroup with appropriate parameters.

Terraform (optional)

Use the aws_redshift_logging resource to enable audit logging on an existing cluster:

S3 destination:

resource "aws_redshift_logging" "audit_logging" {
  cluster_identifier = "my-redshift-cluster"
  bucket_name        = "my-redshift-logs-bucket"
  s3_key_prefix      = "redshift-logs/"
}

CloudWatch destination:

resource "aws_redshift_logging" "cloudwatch_logging" {
  cluster_identifier   = "my-redshift-cluster"
  log_destination_type = "cloudwatch"
  log_exports          = ["connectionlog", "userlog", "useractivitylog"]
}

Variables for reusability:

variable "cluster_identifier" {
  description = "The identifier of the Redshift cluster"
  type        = string
}

variable "log_bucket_name" {
  description = "S3 bucket name for storing audit logs"
  type        = string
}

variable "s3_key_prefix" {
  description = "Prefix for log files in the S3 bucket"
  type        = string
  default     = "redshift-logs/"
}

resource "aws_redshift_logging" "audit_logging" {
  cluster_identifier = var.cluster_identifier
  bucket_name        = var.log_bucket_name
  s3_key_prefix      = var.s3_key_prefix
}

Verification

After enabling audit logging, verify the configuration:

1. In the Redshift console, go to your cluster's Properties tab
2. Scroll to Database audit logging - it should show Enabled
3. Check your S3 bucket or CloudWatch Logs for new log files (may take a few minutes)

CLI verification commands

# Check logging status for a specific cluster
aws redshift describe-logging-status \
  --cluster-identifier <your-cluster-identifier> \
  --region us-east-1

# Expected output for S3 logging:
# {
#   "LoggingEnabled": true,
#   "BucketName": "your-bucket-name",
#   "S3KeyPrefix": "redshift-logs/"
# }

# Expected output for CloudWatch logging:
# {
#   "LoggingEnabled": true,
#   "LogDestinationType": "cloudwatch",
#   "LogExports": ["connectionlog", "userlog", "useractivitylog"]
# }

Additional Resources

• Amazon Redshift Database Audit Logging
• Configuring Audit Logging Using the Console
• Amazon Redshift Logging and Monitoring
• AWS Redshift Terraform Resource: aws_redshift_logging

Notes

• S3 bucket location: The S3 bucket must be in the same AWS region as your Redshift cluster
• Log types:
  • Connection log - Authentication attempts, connections, and disconnections
  • User log - Changes to database user definitions
  • User activity log - Each query before it runs (requires enable_user_activity_logging parameter set to true)
• Performance impact: Audit logging has minimal performance impact on your cluster
• Costs: You will incur S3 storage costs or CloudWatch Logs costs depending on your destination
• Retention: Configure S3 lifecycle policies or CloudWatch retention settings to manage log storage costs