Enable AWS Redshift Audit logging to S3

In addition to querying Redshift system tables for user activities, you also have an option to write audit logs to S3. This option is especially helpful if you are looking to keep history of user activities for more than just few days. As Redshift default system tables will only keep data for last 3 -5 days in rolling manner.

To enable audit logging to S3 Buckets, you need complete following steps.

Step: 1 Enable Audit logging from Console.  

Step 2 : Provide S3 bucket information

Note :- S3 Prefix is optional

Step 2:  Change Bucket Policy

Bucket Policy for Redshift Audit Logs: –

{

    “Version”: “2012-10-17”,

    “Statement”: [

        {

            “Sid”: “Put bucket policy needed for audit logging”,

            “Effect”: “Allow”,

            “Principal”: “*”,

            “Action”: “s3:PutObject”,

            “Resource”: “arn:aws:s3:::meta7/*”

        },

        {

            “Sid”: “Get bucket policy needed for audit logging “,

            “Effect”: “Allow”,

            “Principal”: “*”,

            “Action”: “s3:GetBucketAcl”,

            “Resource”: “arn:aws:s3:::meta7”

        }

    ]

}

Note : – This is just for POC , Please use more restrictive access for you production systems.

Step 3: Review S3 buckets folder log files.

Note : – Statements are logged as soon as Amazon Redshift receives them. Files on Amazon S3 are updated in batch, and can take a few hours to appear.