Database audit logging - Amazon Redshift
OverviewAmazon Redshift logsEnabling loggingManaging log filesTroubleshooting Amazon Redshift audit loggingLogging Amazon Redshift API calls with AWS CloudTrailAmazon Redshift account IDs in AWS CloudTrail logs

Database audit logging

Topics

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 Amazon S3 buckets. These provide 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. This information might be 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.
iamauthguid The IAM authentication ID for the CloudTrail request.
application_name The initial or updated name of the application for a session.
driver_version The version of ODBC or JDBC driver that connects to your Amazon Redshift cluster from your third-party SQL client tools.
os_version The version of the operating system that is on the client machine that connects to your Amazon Redshift cluster.
plugin_name The name of the plugin used to connect to your Amazon Redshift cluster.

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 time audit logging is enabled 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 log information for only the connection log and user log, but not for the user activity log. The enable_user_activity_logging parameter is not enabled (false) by default. You can set it to true to enable the user activity log. For more information, see Amazon Redshift parameter groups.

Note

Currently, you can only use Amazon S3-managed keys (SSE-S3) encryption (AES-256) for audit logging.

Managing log files

The number and size of Amazon Redshift log files in Amazon S3 depends 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 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. As part of this, determine when the log files 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. Also, the IAM user or IAM role that enables logging must have s3:PutObject permission to the Amazon S3 bucket. 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, you receive an error.

If you have Amazon Redshift create a new bucket for you as part of configuration, correct permissions are 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. You also need the Amazon Redshift account ID that corresponds to your AWS 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
Africa (Cape Town) Region af-south-1 365689465814
Asia Pacific (Hong Kong) Region ap-east-1 313564881002
Asia Pacific (Mumbai) Region ap-south-1 865932855811
Asia Pacific (Osaka) Region ap-northeast-3 090321488786
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
Europe (Frankfurt) Region eu-central-1 053454850223
Europe (Ireland) Region eu-west-1 210876761215
Europe (London) Region eu-west-2 307160386991
Europe (Milan) Region eu-south-1 945612479654
Europe (Paris) Region eu-west-3 915173422425
Europe (Stockholm) Region eu-north-1 729911121831
Middle East (Bahrain) Region me-south-1 013126148197
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: 

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

Logging Amazon Redshift API calls with AWS CloudTrail

Amazon Redshift is integrated with AWS CloudTrail, a service that provides a record of actions taken by a user, role, or an AWS service in Amazon Redshift. CloudTrail captures all API calls for Amazon Redshift as events. These include calls from the Amazon Redshift console and from code calls to the Amazon Redshift API operations. If you create a trail, you can enable continuous delivery of CloudTrail events to an Amazon S3 bucket, including events for Amazon Redshift. If you don't configure a trail, you can still view the most recent events in the CloudTrail console in Event history. Using the information collected by CloudTrail, you can determine certain details. These include the request that was made to Amazon Redshift, the IP address it was made from, who made it, when it was made, and other information.

You can use CloudTrail independently from or in addition to Amazon Redshift database audit logging.

To learn more about CloudTrail, see the AWS CloudTrail User Guide.

Amazon Redshift information in CloudTrail

CloudTrail is enabled on your AWS account when you create the account. When activity occurs in Amazon Redshift, that activity is recorded in a CloudTrail event along with other AWS service events in Event history. You can view, search, and download recent events in your AWS account. For more information, see Viewing Events with CloudTrail Event History.

For an ongoing record of events in your AWS account, including events for Amazon Redshift, create a trail. A trail enables CloudTrail to deliver log files to an Amazon S3 bucket. By default, when you create a trail in the console, the trail applies to all regions. The trail logs events from all regions in the AWS partition and delivers the log files to the Amazon S3 bucket that you specify. Additionally, you can configure other AWS services to further analyze and act upon the event data collected in CloudTrail logs. For more information, see:

All Amazon Redshift actions are logged by CloudTrail and are documented in the Amazon Redshift API Reference. For example, calls to the CreateCluster, DeleteCluster, and DescribeCluster actions generate entries in the CloudTrail log files.

Every event or log entry contains information about who generated the request. The identity information helps you determine the following:

  • Whether the request was made with root or IAM user credentials.

  • Whether the request was made with temporary security credentials for a role or federated user.

  • Whether the request was made by another AWS service.

For more information, see the CloudTrail userIdentity Element.

Understanding Amazon Redshift log file entries

A trail is a configuration that enables delivery of events as log files to an Amazon S3 bucket that you specify. CloudTrail log files contain one or more log entries. An event represents a single request from any source and includes information about the requested action, the date and time of the action, request parameters, and so on. CloudTrail log files are not an ordered stack trace of the public API calls, so they do not appear in any specific order.

The following example shows a CloudTrail log entry for a sample CreateCluster call. 

{
    "eventVersion": "1.04",
    "userIdentity": {
        "type": "IAMUser",
        "principalId": "AIDAMVNPBQA3EXAMPLE",
        "arn": "arn:aws:iam::123456789012:user/Admin",
        "accountId": "123456789012",
        "accessKeyId": "AKIAIOSFODNN7EXAMPLE",
        "userName": "Admin",
        "sessionContext": {
            "attributes": {
                "mfaAuthenticated": "false",
                "creationDate": "2017-03-03T16:51:56Z"
            }
        },
        "invokedBy": "signin.amazonaws.com"
    },
    "eventTime": "2017-03-03T16:56:09Z",
    "eventSource": "redshift.amazonaws.com",
    "eventName": "CreateCluster",
    "awsRegion": "us-east-2",
    "sourceIPAddress": "52.95.4.13",
    "userAgent": "signin.amazonaws.com",
    "requestParameters": {
        "clusterIdentifier": "my-dw-instance",
        "allowVersionUpgrade": true,
        "enhancedVpcRouting": false,
        "encrypted": false,
        "clusterVersion": "1.0",
        "masterUsername": "awsuser",
        "masterUserPassword": "****",
        "automatedSnapshotRetentionPeriod": 1,
        "port": 5439,
        "dBName": "mydbtest",
        "clusterType": "single-node",
        "nodeType": "dc1.large",
        "publiclyAccessible": true,
        "vpcSecurityGroupIds": [
            "sg-95f606fc"
        ]
    },
    "responseElements": {
        "nodeType": "dc1.large",
        "preferredMaintenanceWindow": "sat:05:30-sat:06:00",
        "clusterStatus": "creating",
        "vpcId": "vpc-84c22aed",
        "enhancedVpcRouting": false,
        "masterUsername": "awsuser",
        "clusterSecurityGroups": [],
        "pendingModifiedValues": {
            "masterUserPassword": "****"
        },
        "dBName": "mydbtest",
        "clusterVersion": "1.0",
        "encrypted": false,
        "publiclyAccessible": true,
        "tags": [],
        "clusterParameterGroups": [
            {
                "parameterGroupName": "default.redshift-1.0",
                "parameterApplyStatus": "in-sync"
            }
        ],
        "allowVersionUpgrade": true,
        "automatedSnapshotRetentionPeriod": 1,
        "numberOfNodes": 1,
        "vpcSecurityGroups": [
            {
                "status": "active",
                "vpcSecurityGroupId": "sg-95f606fc"
            }
        ],
        "iamRoles": [],
        "clusterIdentifier": "my-dw-instance",
        "clusterSubnetGroupName": "default"
    },
    "requestID": "4c506036-0032-11e7-b8bf-d7aa466e9920",
    "eventID": "13ba5550-56ac-405b-900a-8a42b0f43c45",
    "eventType": "AwsApiCall",
    "recipientAccountId": "123456789012"
}

The following example shows a CloudTrail log entry for a sample DeleteCluster call.

{
    "eventVersion": "1.04",
    "userIdentity": {
        "type": "IAMUser",
        "principalId": "AIDAMVNPBQA3EXAMPLE",
        "arn": "arn:aws:iam::123456789012:user/Admin",
        "accountId": "123456789012",
        "accessKeyId": "AKIAIOSFODNN7EXAMPLE",
        "userName": "Admin",
        "sessionContext": {
            "attributes": {
                "mfaAuthenticated": "false",
                "creationDate": "2017-03-03T16:58:23Z"
            }
        },
        "invokedBy": "signin.amazonaws.com"
    },
    "eventTime": "2017-03-03T17:02:34Z",
    "eventSource": "redshift.amazonaws.com",
    "eventName": "DeleteCluster",
    "awsRegion": "us-east-2",
    "sourceIPAddress": "52.95.4.13",
    "userAgent": "signin.amazonaws.com",
    "requestParameters": {
        "clusterIdentifier": "my-dw-instance",
        "skipFinalClusterSnapshot": true
    },
    "responseElements": null,
    "requestID": "324cb76a-0033-11e7-809b-1bbbef7710bf",
    "eventID": "59bcc3ce-e635-4cce-b47f-3419a36b3fa5",
    "eventType": "AwsApiCall",
    "recipientAccountId": "123456789012"
}

Amazon Redshift account IDs in AWS CloudTrail logs

When Amazon Redshift calls another AWS service for you, the call is logged with an account ID that belongs to Amazon Redshift. It isn't logged with your account ID. For example, suppose that Amazon Redshift calls AWS Key Management Service (AWS KMS) actions such as CreateGrant, Decrypt, Encrypt, and RetireGrant to manage encryption on your cluster. In this case, 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
Africa (Cape Town) Region af-south-1 420376844563
Asia Pacific (Hong Kong) Region ap-east-1 651179539253
Asia Pacific (Mumbai) Region ap-south-1 408097707231
Asia Pacific (Osaka) Region ap-northeast-3 398671365691
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
Europe (Frankfurt) Region eu-central-1 434091160558
Europe (Ireland) Region eu-west-1 246478207311
Europe (London) Region eu-west-2 885798887673
Europe (Milan) Region eu-south-1 041313461515
Europe (Paris) Region eu-west-3 694668203235
Europe (Stockholm) Region eu-north-1 553461782468
Middle East (Bahrain) Region me-south-1 051362938876
South America (São Paulo) Region sa-east-1 392442076723

The following example shows a CloudTrail log entry for the AWS KMS Decrypt operation that was called by Amazon Redshift. 

{

    "eventVersion": "1.05",
    "userIdentity": {
        "type": "AssumedRole",
        "principalId": "AROAI5QPCMKLTL4VHFCYY:i-0f53e22dbe5df8a89",
        "arn": "arn:aws:sts::790247189693:assumed-role/prod-23264-role-wp/i-0f53e22dbe5df8a89",
        "accountId": "790247189693",
        "accessKeyId": "AKIAIOSFODNN7EXAMPLE",
        "sessionContext": {
            "attributes": {
                "mfaAuthenticated": "false",
                "creationDate": "2017-03-03T16:24:54Z"
            },
            "sessionIssuer": {
                "type": "Role",
                "principalId": "AROAI5QPCMKLTL4VHFCYY",
                "arn": "arn:aws:iam::790247189693:role/prod-23264-role-wp",
                "accountId": "790247189693",
                "userName": "prod-23264-role-wp"
            }
        }
    },
    "eventTime": "2017-03-03T17:16:51Z",
    "eventSource": "kms.amazonaws.com",
    "eventName": "Decrypt",
    "awsRegion": "us-east-2",
    "sourceIPAddress": "52.14.143.61",
    "userAgent": "aws-internal/3",
    "requestParameters": {
        "encryptionContext": {
            "aws:redshift:createtime": "20170303T1710Z",
            "aws:redshift:arn": "arn:aws:redshift:us-east-2:123456789012:cluster:my-dw-instance-2"
        }
    },
    "responseElements": null,
    "requestID": "30d2fe51-0035-11e7-ab67-17595a8411c8",
    "eventID": "619bad54-1764-4de4-a786-8898b0a7f40c",
    "readOnly": true,
    "resources": [
        {
            "ARN": "arn:aws:kms:us-east-2:123456789012:key/f8f4f94f-e588-4254-b7e8-078b99270be7",
            "accountId": "123456789012",
            "type": "AWS::KMS::Key"
        }
    ],
    "eventType": "AwsApiCall",
    "recipientAccountId": "123456789012",
    "sharedEventID": "c1daefea-a5c2-4fab-b6f4-d8eaa1e522dc"

}