Setup Redshift Logging

AWS Redshift offers a feature to enable logging for different kinds of activity on the cluster. This allows customers to get logs for all connection attempts made to Redshift, logs on users and on user activity. To set this up, follow the steps below.

Prepare S3 bucket for receiving Redshift logs

First, create a new bucket in which to save the S3 Access Logs. Ideally, this S3 Bucket will have been configured to not allow any deletes and should be in a separate AWS account. Next run the following command to update the bucket policy for this S3 bucket to enable Redshift logging.

aws s3api put-bucket-policy \
--bucket <redshift-logs-bucket> \
--policy file://redshift_logs_bucket_policy.json

Here the file redshift_logs_bucket_policy.json contains the following (you'll need to fill in the S3 bucket name and the Amazon Redshift Account ID):

{
"Version": "2012-10-17",
"Statement": [
{
"Sid": "Put bucket policy needed for audit logging",
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::<amazon-redshift-account-id>:user/logs"
},
"Action": "s3:PutObject",
"Resource": "arn:aws:s3:::<redshift-logs-bucket>/*"
},
{
"Sid": "Get bucket policy needed for audit logging ",
"Effect": "Allow",
"Principal": {
"AWS": "arn:aws:iam::<amazon-redshift-account-id>:user/logs"
},
"Action": "s3:GetBucketAcl",
"Resource": "arn:aws:s3:::<redshift-logs-bucket>"
}
]
}

Note: The Amazon Redshift Account ID depends on your AWS region and can be found at the link here.

Enable Redshift Logging

Run the following command:

aws redshift enable-logging \
--cluster-identifier <redshift-cluster-identifier> \
--bucket-name <redshift-logs-bucket>