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 to monitor database security and troubleshoot 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. 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, make 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, complete the following steps:

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, then you have the option to choose an existing bucket or Create new bucket for storing database audit logs.
   If you select CloudWatch, then 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 with the AWS Command Line Interface (AWS CLI), see enable-logging.

Note: If you receive errors when you run AWS CLI commands, then see Troubleshooting errors for the AWS CLI. Also, make sure that you're using the most recent AWS CLI version.

The following example shows how to use the enable-logging command to turn on audit logging with a 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 S3 bucket or 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, make sure that the enable_user_activity_logging parameter is set to true in the cluster parameter group attached to the Amazon Redshift cluster.

Note: When an Amazon Redshift cluster is initially created, it's automatically associated with the default parameter group. In this configuration, the enable_user_activity_logging parameter is set to false. The default parameter group itself can't be modified directly.

To turn on the enable_user_activity_logging parameter, complete the following steps:

1. Create a new parameter group.
2. Modify the parameter group to set the parameter enable_user_activity_logging to true.

To associate the new parameter group to the cluster, complete the following steps:

1. In the navigation pane, choose Clusters. Then, select the cluster that you want to modify.
2. Choose Properties.
3. Scroll down to the Parameter group section and choose Edit.
4. Choose the new parameter group from the list.
5. Choose Save changes.

You might need to restart the cluster after you modify the parameter values in a parameter group that is already associated with a cluster. You might also need to associate a different parameter group with a cluster for the updated parameter values to take effect. Changes aren't applied if your cluster is restarted during maintenance.

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

For more information, see Amazon Redshift parameter groups.

Turn on audit logging for Amazon Redshift Serverless

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

1. Open the Amazon Redshift console.
2. In the navigation pane, choose Redshift Serverless, and then choose Serverless dashboard.
3. Select the Namespace that you 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 S3 buckets.

For more information on how to monitor the audit logs, see Log events in CloudWatch.

Related information

Database audit logging

Audit logging for Amazon Redshift Serverless