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.

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.
usernameUser name.
authmethodAuthentication method.
duration Duration of connection in microseconds.
sslversionSecure Sockets Layer (SSL) version.
sslcipherSSL cipher.
mtu Maximum transmission unit (MTU).
sslcompression SSL compression type.
sslexpansionSSL 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
useridID of user affected by the change.
usernameUser name of the user affected by the change.
oldusernameFor a rename action, the original user name. For any other action, this field is empty.
actionAction that occurred. Valid values:
  • Alter

  • Create

  • Drop

  • Rename

usecreatedbIf true (1), indicates that the user has create database privileges.
usesuperIf true (1), indicates that the user is a superuser.
usecatupdIf true (1), indicates that the user can update system catalogs.
valuntilPassword expiration date.
pidProcess ID.
xidTransaction ID.
recordtimeTime in UTC that the query started.

User Activity Log

Logs each query before it is run on the database.

Column name Description
recordtimeTime the event occurred.
dbDatabase name.
userUser name.
pidProcess ID associated with the statement.
useridUser ID.
xidTransaction ID.
queryA 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 NameRegionAccount ID
US East (N. Virginia) Regionus-east-1193672423079
US East (Ohio) Regionus-east-2391106570357
US West (N. California) Regionus-west-1262260360010
US West (Oregon) Regionus-west-2902366379725
Asia Pacific (Mumbai) Regionap-south-1865932855811
Asia Pacific (Seoul) Regionap-northeast-2760740231472
Asia Pacific (Singapore) Regionap-southeast-1361669875840
Asia Pacific (Sydney) Regionap-southeast-2762762565011
Asia Pacific (Tokyo) Regionap-northeast-1404641285394
Canada (Central) Regionca-central-1907379612154
EU (Frankfurt) Regioneu-central-1053454850223
EU (Ireland) Regioneu-west-1210876761215
EU (London) Regioneu-west-2307160386991
South America (São Paulo) Regionsa-east-1075028567923

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

{
	"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.

{
	"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.

RegionRegionAccount ID
US East (N. Virginia) Regionus-east-1368064434614
US East (Ohio) Regionus-east-2790247189693
US West (N. California) Regionus-west-1703715109447
US West (Oregon) Regionus-west-2473191095985
Asia Pacific (Mumbai) Regionap-south-1408097707231
Asia Pacific (Seoul) Regionap-northeast-2713597048934
Asia Pacific (Singapore) Regionap-southeast-1960118270566
Asia Pacific (Sydney) Regionap-southeast-2485979073181
Asia Pacific (Tokyo) Regionap-northeast-1615915377779
Canada (Central) Regionca-central-1764870610256
EU (Frankfurt) Regioneu-central-1434091160558
EU (Ireland) Regioneu-west-1246478207311
EU (London) Regioneu-west-2885798887673
South America (São Paulo) Regionsa-east-1392442076723