How do I turn on audit logging in Amazon Redshift and Amazon Redshift Serverless?

Short description

Amazon Redshift stores system logs in system tables and views with a retention period of up to seven days. These logs help with monitoring database security and troubleshooting database issues.

To store the logs for a longer duration, turn on the audit logging feature of Amazon Redshift. Logs can be stored in Amazon Simple Storage Service (Amazon S3) buckets or Amazon CloudWatch. Amazon CloudWatch has features to visualize audit logging data.

Amazon Redshift logs information in the following types of logs:

• Connection log – Logs authentication attempts, connections, and disconnections.
• User log – Logs information about changes to database user definitions.
• User activity log – Logs each query before it's run on the database.

Note: For user activity logs, be sure that you set up the parameter groups to properly store logs.

Resolution

Turn on audit logging in an Amazon Redshift provisioned cluster

To turn on audit logging in an Amazon Redshift provisioned cluster using the console, do the following:

1. Open the Amazon Redshift console.
2. In the navigation pane, choose Clusters, and then choose the cluster you want to update.
3. Choose the Properties tab.
4. On the Database configurations panel, choose Edit, and then choose Edit audit logging.
5. For Edit audit logging, choose Turn on, and then select S3 bucket or CloudWatch.
   If you select S3 bucket, you have the option to choose an existing bucket or Create new bucket for storing database audit logs.
   If you select CloudWatch, you can select from the following log types: Connection log, User log, and User activity log.
6. Choose Save changes.

To turn on audit logging using the AWS CLI, see enable-logging.
Note: If you receive errors when running AWS CLI commands, make sure that you’re using the most recent version of the AWS CLI.

The following is an example of running the enable-logging command to turn on audit logging with an Amazon S3 bucket as the audit logging destination:

aws redshift enable-logging --cluster-identifier redshift-cluster-1 --log-destination-type s3  --bucket-name mybucket --s3-key-prefix mybucket/test --region us-east-1

The output looks similar to the following:

{    “LoggingEnabled”: true,    “BucketName”: “mybucket”,    “S3KeyPrefix”: “mybucket/test/“,    “LastSuccessfulDeliveryTime”: “2022-09-14T12:04:42.558000+00:00"}

Note: There might be a delay in logs that appear in the Amazon S3 bucket or Amazon CloudWatch. You can check the Last successful delivery date and time in the cluster properties to check when was the log delivery was last performed.

Parameter group for user activity logs

To log the user activity logs, be sure that the enable_user_activity_logging parameter is set to true in the cluster parameter group attached to the Amazon Redshift cluster.

To turn on the enable_user_activity_logging parameter**,** do the following:

1. Create a new parameter group.
2. Modify the parameter group to set the parameter enable_user_activity_logging to true.
3. Modify the cluster to attach the new parameter group to the Amazon Redshift cluster.

Note: The enable_user_activity_logging parameter is set to false by default and you can't modify the default parameter group.

If you turn on audit logging but not the enable_user_activity_logging parameter in the parameter group, then the following happens:

• Database audit logs store information for only the connection log and user log
• The user activity log isn't stored

Turn on audit logging for Redshift Serverless

To turn on audit logging for Amazon Redshift Serverless, do the following:

1. Open the Amazon Redshift console.
2. In the navigation pane, choose Redshift Serverless, and then choose Serverless dashboard.
3. Select the Namespace that want to turn on audit logging for.
4. Choose the Security and Encryption tab.
5. For Security and encryption, choose Edit.
6. In Export these logs, select the logs that you want to save in CloudWatch. You can select from the following log types: User log, Connection log, and User activity log.
7. Choose Save changes.

Note: Amazon Redshift Serverless can't export logs to Amazon S3 buckets.

For more information on monitoring the audit logs, see Monitoring log events in CloudWatch.

---

Related information

Database audit logging

Audit logging for Amazon Redshift Serverless