Menu
Amazon Redshift
Management Guide (API Version 2012-12-01)

Database Audit Logging

Overview

Amazon Redshift logs information about connections and user activities in your database. These logs help you to monitor the database for security and troubleshooting purposes, which is a process often referred to as database auditing. The logs are stored in the Amazon Simple Storage Service (Amazon S3) buckets for convenient access with data security features for users who are responsible for monitoring activities in the database.

Amazon Redshift Logs

Amazon Redshift logs information in the following log files:

  • Connection log — logs authentication attempts, and connections and disconnections.

  • User log — logs information about changes to database user definitions.

  • User activity log — logs each query before it is run on the database.

The connection and user logs are useful primarily for security purposes. You can use the connection log to monitor information about the users who are connecting to the database and the related connection information, such as their IP address, when they made the request, what type of authentication they used, and so on. You can use the user log to monitor changes to the definitions of database users.

The user activity log is useful primarily for troubleshooting purposes. It tracks information about the types of queries that both the users and the system perform in the database.

The connection log and user log both correspond to information that is stored in the system tables in your database. You can use the system tables to obtain the same information, but the log files provide an easier mechanism for retrieval and review. The log files rely on Amazon S3 permissions rather than database permissions to perform queries against the tables. Additionally, by viewing the information in log files rather than querying the system tables, you reduce any impact of interacting with the database.

Note

Log files are not as current as the base system log tables, STL_USERLOG and STL_CONNECTION_LOG. Records that are older than, but not including, the latest record are copied to log files.

Connection Log

Logs authentication attempts, and connections and disconnections. The following table describes the information in the connection log.

Column name Description
event Connection or authentication event.
recordtime Time the event occurred.
remotehost Name or IP address of remote host.
remoteport Port number for remote host.
pid Process ID associated with the statement.
dbname Database name.
username User name.
authmethod Authentication method.
duration Duration of connection in microseconds.
sslversion Secure Sockets Layer (SSL) version.
sslcipher SSL cipher.
mtu Maximum transmission unit (MTU).
sslcompression SSL compression type.
sslexpansion SSL expansion type.

User Log

Records details for the following changes to a database user:

  • Create user

  • Drop user

  • Alter user (rename)

  • Alter user (alter properties)

Column name Description
userid ID of user affected by the change.
username User name of the user affected by the change.
oldusername For a rename action, the original user name. For any other action, this field is empty.
action Action that occurred. Valid values:
  • Alter

  • Create

  • Drop

  • Rename

usecreatedb If true (1), indicates that the user has create database privileges.
usesuper If true (1), indicates that the user is a superuser.
usecatupd If true (1), indicates that the user can update system catalogs.
valuntil Password expiration date.
pid Process ID.
xid Transaction ID.
recordtime Time in UTC that the query started.

User Activity Log

Logs each query before it is run on the database.

Column name Description
recordtime Time the event occurred.
db Database name.
user User name.
pid Process ID associated with the statement.
userid User ID.
xid Transaction ID.
query A prefix of LOG: followed by the text of the query, including newlines.

Enabling Logging

Audit logging is not enabled by default in Amazon Redshift. When you enable logging on your cluster, Amazon Redshift creates and uploads logs to Amazon S3 that capture data from the creation of the cluster to the present time. Each logging update is a continuation of the information that was already logged.

Note

Audit logging to Amazon S3 is an optional, manual process. When you enable logging on your cluster, you are enabling logging to Amazon S3 only. Logging to system tables is not optional and happens automatically for the cluster. For more information about logging to system tables, see System Tables Reference in the Amazon Redshift Database Developer Guide.

The connection log, user log, and user activity log are enabled together by using the AWS Management Console, the Amazon Redshift API Reference, or the AWS Command Line Interface (AWS CLI). For the user activity log, you must also enable the enable_user_activity_logging database parameter. If you enable only the audit logging feature, but not the associated parameter, the database audit logs will log information for only the connection log and user log, but not for the user activity log. The enable_user_activity_logging parameter is disabled (false) by default, but you can set it to true to enable the user activity log. For more information, see Amazon Redshift Parameter Groups.

Managing Log Files

The number and size of Amazon Redshift log files in Amazon S3 will depend heavily on the activity in your cluster. If you have an active cluster that is generating a large number of logs, Amazon Redshift might generate the log files more frequently. You might have a series of log files for the same type of activity, such as having multiple connection logs within the same hour.

Because Amazon Redshift uses Amazon S3 to store logs, you will incur charges for the storage that you use in Amazon S3. Before you configure logging, you should have a plan for how long you need to store the log files, and determine when they can either be deleted or archived based on your auditing needs. The plan that you create depends heavily on the type of data that you store, such as data subject to compliance or regulatory requirements. For more information about Amazon S3 pricing, go to Amazon Simple Storage Service (S3) Pricing.

Bucket Permissions for Amazon Redshift Audit Logging

When you enable logging, Amazon Redshift collects logging information and uploads it to log files stored in Amazon S3. You can use an existing bucket or a new bucket. Amazon Redshift requires the following IAM permissions to the bucket:

  • s3:GetBucketAcl The service requires read permissions to the Amazon S3 bucket so it can identify the bucket owner.

  • s3:PutObject The service requires put object permissions to upload the logs. Each time logs are uploaded, the service determines whether the current bucket owner matches the bucket owner at the time logging was enabled. If these owners do not match, logging is still enabled but no log files can be uploaded until you select a different bucket.

If you want to use a new bucket, and have Amazon Redshift create it for you as part of the configuration process, the correct permissions will be applied to the bucket. However, if you create your own bucket in Amazon S3 or use an existing bucket, you need to add a bucket policy that includes the bucket name, and the Amazon Redshift Account ID that corresponds to your region from the following table:

Region Name Region Account ID
US East (N. Virginia) Region us-east-1 193672423079
US East (Ohio) Region us-east-2 391106570357
US West (N. California) Region us-west-1 262260360010
US West (Oregon) Region us-west-2 902366379725
Asia Pacific (Mumbai) Region ap-south-1 865932855811
Asia Pacific (Seoul) Region ap-northeast-2 760740231472
Asia Pacific (Singapore) Region ap-southeast-1 361669875840
Asia Pacific (Sydney) Region ap-southeast-2 762762565011
Asia Pacific (Tokyo) Region ap-northeast-1 404641285394
Canada (Central) Region ca-central-1 907379612154
EU (Frankfurt) Region eu-central-1 053454850223
EU (Ireland) Region eu-west-1 210876761215
EU (London) Region eu-west-2 307160386991
South America (São Paulo) Region sa-east-1 075028567923

The bucket policy uses the following format, where BucketName and AccountId are placeholders for your own values:

Copy
{ "Version": "2012-10-17", "Statement": [ { "Sid": "Put bucket policy needed for audit logging", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::AccountId:user/logs" }, "Action": "s3:PutObject", "Resource": "arn:aws:s3:::BucketName/*" }, { "Sid": "Get bucket policy needed for audit logging ", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::AccountID:user/logs" }, "Action": "s3:GetBucketAcl", "Resource": "arn:aws:s3:::BucketName" } ] }

The following example is a bucket policy for the US East (N. Virginia) Region and bucket named AuditLogs.

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

For more information about creating Amazon S3 buckets and adding bucket policies, go to Creating a Bucket and Editing Bucket Permissions in the Amazon Simple Storage Service Console User Guide.

Bucket Structure for Amazon Redshift Audit Logging

By default, Amazon Redshift organizes the log files in the Amazon S3 bucket by using the following bucket and object structure: AWSLogs/AccountID/ServiceName/Region/Year/Month/Day/AccountID_ServiceName_Region_ClusterName_LogType_Timestamp.gz

For example: AWSLogs/123456789012/redshift/us-east-1/2013/10/29/123456789012_redshift_us-east-1_mycluster_userlog_2013-10-29T18:01.gz

If you provide an Amazon S3 key prefix, the prefix is placed at the start of the key.

For example, if you specify a prefix of myprefix: myprefix/AWSLogs/123456789012/redshift/us-east-1/2013/10/29/123456789012_redshift_us-east-1_mycluster_userlog_2013-10-29T18:01.gz

The Amazon S3 key prefix cannot exceed 512 characters. It cannot contain spaces ( ), double quotation marks (“), single quotation marks (‘), a backslash (\). There are also a number of special characters and control characters that are not allowed. The hexadecimal codes for these characters are:

  • x00 to x20

  • x22

  • x27

  • x5c

  • x7f or larger

Troubleshooting Amazon Redshift Audit Logging

Amazon Redshift audit logging can be interrupted for the following reasons:

  • Amazon Redshift does not have permission to upload logs to the Amazon S3 bucket. Verify that the bucket is configured with the correct IAM policy. For more information, see Bucket Permissions for Amazon Redshift Audit Logging.

  • The bucket owner changed. When Amazon Redshift uploads logs, it verifies that the bucket owner is the same as when logging was enabled. If the bucket owner has changed, Amazon Redshift cannot upload logs until you configure another bucket to use for audit logging. For more information, see Modifying the Bucket for Audit Logging.

  • The bucket cannot be found. If the bucket is deleted in Amazon S3, Amazon Redshift cannot upload logs. You either need to recreate the bucket or configure Amazon Redshift to upload logs to a different bucket. For more information, see Modifying the Bucket for Audit Logging.

Using AWS CloudTrail for Amazon Redshift

This service supports AWS CloudTrail, which is a service that records AWS calls for your AWS account and delivers log files to an Amazon S3 bucket. By using information collected by CloudTrail, you can determine what requests were successfully made to AWS services, who made the request, when it was made, and so on. To learn more about CloudTrail, including how to turn it on and find your log files, see the AWS CloudTrail User Guide.

CloudTrail can be used independently from or in addition to Amazon Redshift database audit logging.

Amazon Redshift Account IDs in AWS CloudTrail Logs

When Amazon Redshift calls another AWS service on your behalf, the call is logged with an account ID that belongs to the Amazon Redshift service instead of your own account ID. For example, when Amazon Redshift calls AWS Key Management Service (AWS KMS) actions such as CreateGrant, Decrypt, Encrypt, and RetireGrant to manage encryption on your cluster, the calls are logged by AWS CloudTrail using an Amazon Redshift account ID.

Amazon Redshift uses the account IDs in the following table when calling other AWS services.

Region Region Account ID
US East (N. Virginia) Region us-east-1 368064434614
US East (Ohio) Region us-east-2 790247189693
US West (N. California) Region us-west-1 703715109447
US West (Oregon) Region us-west-2 473191095985
Asia Pacific (Mumbai) Region ap-south-1 408097707231
Asia Pacific (Seoul) Region ap-northeast-2 713597048934
Asia Pacific (Singapore) Region ap-southeast-1 960118270566
Asia Pacific (Sydney) Region ap-southeast-2 485979073181
Asia Pacific (Tokyo) Region ap-northeast-1 615915377779
Canada (Central) Region ca-central-1 764870610256
EU (Frankfurt) Region eu-central-1 434091160558
EU (Ireland) Region eu-west-1 246478207311
EU (London) Region eu-west-2 885798887673
South America (São Paulo) Region sa-east-1 392442076723