Logging with CloudTrail - Amazon Redshift

Logging with CloudTrail

Logging calls with AWS CloudTrail

Amazon Redshift, data sharing, Amazon Redshift Serverless, Amazon Redshift Data API, and query editor v2 are all integrated with AWS CloudTrail. CloudTrail is 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. The calls captured include calls from the Redshift console and code calls to the Redshift operations.

If you create a CloudTrail trail, you can have continuous delivery of CloudTrail events to an Amazon S3 bucket, including events for 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 things. These include the request that was made to Redshift, the IP address from which the request was made, who made the request, when it was made, and additional details.

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.

Working with information in CloudTrail

CloudTrail is turned on in your AWS account when you create the account. When activity occurs, 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 in the AWS CloudTrail User Guide.

For an ongoing record of events in your AWS account, including events for Redshift, create a trail. CloudTrail uses trails to deliver log files to an Amazon S3 bucket. By default, when you create a trail in the console, the trail applies to all AWS 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 the following in the AWS CloudTrail User Guide:

All Amazon Redshift, Amazon Redshift Serverless, Data API, data sharing, and query editor v2 actions are logged by CloudTrail. For example, calls to the AuthorizeDatashare, CreateNamespace, ExecuteStatement, and CreateConnection 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 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 CloudTrail userIdentity Element in the AWS CloudTrail User Guide.

Understanding log files entries

A trail is a configuration that allows for 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 aren't an ordered stack trace of the public API calls, so they don't appear in any specific order.

Amazon Redshift Datashare example

The following example shows a CloudTrail log entry that illustrates the AuthorizeDataShare operation.

{ "eventVersion": "1.08", "userIdentity": { "type": "AssumedRole", "principalId": "AKIAIOSFODNN7EXAMPLE:janedoe", "arn": "arn:aws:sts::111122223333:user/janedoe", "accountId": "111122223333", "accessKeyId": "AKIAI44QH8DHBEXAMPLE", "sessionContext": { "sessionIssuer": { "type": "Role", "principalId": "AKIAIOSFODNN7EXAMPLE:janedoe", "arn": "arn:aws:sts::111122223333:user/janedoe", "accountId": "111122223333", "userName": "janedoe" }, "attributes": { "creationDate": "2021-08-02T23:40:45Z", "mfaAuthenticated": "false" } } }, "eventTime": "2021-08-02T23:40:58Z", "eventSource": "redshift.amazonaws.com", "eventName": "AuthorizeDataShare", "awsRegion": "us-east-1", "sourceIPAddress": "3.227.36.75", "userAgent":"aws-cli/1.18.118 Python/3.6.10 Linux/4.9.217-0.1.ac.205.84.332.metal1.x86_64 botocore/1.17.41", "requestParameters": { "dataShareArn": "arn:aws:redshift:us-east-1:111122223333:datashare:4c64c6ec-73d5-42be-869b-b7f7c43c7a53/testshare", "consumerIdentifier": "555555555555" }, "responseElements": { "dataShareArn": "arn:aws:redshift:us-east-1:111122223333:datashare:4c64c6ec-73d5-42be-869b-b7f7c43c7a53/testshare", "producerNamespaceArn": "arn:aws:redshift:us-east-1:123456789012:namespace:4c64c6ec-73d5-42be-869b-b7f7c43c7a53", "producerArn": "arn:aws:redshift:us-east-1:111122223333:namespace:4c64c6ec-73d5-42be-869b-b7f7c43c7a53", "allowPubliclyAccessibleConsumers": true, "dataShareAssociations": [ { "consumerIdentifier": "555555555555", "status": "AUTHORIZED", "createdDate": "Aug 2, 2021 11:40:56 PM", "statusChangeDate": "Aug 2, 2021 11:40:57 PM" } ] }, "requestID": "87ee1c99-9e41-42be-a5c4-00495f928422", "eventID": "03a3d818-37c8-46a6-aad5-0151803bdb09", "readOnly": false, "eventType": "AwsApiCall", "managementEvent": true, "recipientAccountId": "111122223333", "eventCategory": "Management" }

Amazon Redshift Serverless example

Amazon Redshift Serverless is integrated with AWS CloudTrail to provide a record of actions taken in Amazon Redshift Serverless. CloudTrail captures all API calls for Amazon Redshift Serverless as events. For more information about Amazon Redshift Serverless features, see Amazon Redshift Serverless feature overview.

The following example shows a CloudTrail log entry that demonstrates the CreateNamespace action.

{ "eventVersion": "1.08", "userIdentity": { "type": "AssumedRole", "principalId": "AAKEOFPINEXAMPLE:admin", "arn": "arn:aws:sts::111111111111:assumed-role/admin/admin", "accountId": "111111111111", "accessKeyId": "AAKEOFPINEXAMPLE", "sessionContext": { "sessionIssuer": { "type": "Role", "principalId": "AAKEOFPINEXAMPLE", "arn": "arn:aws:iam::111111111111:role/admin", "accountId": "111111111111", "userName": "admin" }, "webIdFederationData": {}, "attributes": { "creationDate": "2022-03-21T20:51:58Z", "mfaAuthenticated": "false" } } }, "eventTime": "2022-03-21T23:15:40Z", "eventSource": "redshift-serverless.amazonaws.com", "eventName": "CreateNamespace", "awsRegion": "us-east-1", "sourceIPAddress": "56.23.155.33", "userAgent": "aws-cli/2.4.14 Python/3.8.8 Linux/5.4.181-109.354.amzn2int.x86_64 exe/x86_64.amzn.2 prompt/off command/redshift-serverless.create-namespace", "requestParameters": { "adminUserPassword": "HIDDEN_DUE_TO_SECURITY_REASONS", "adminUsername": "HIDDEN_DUE_TO_SECURITY_REASONS", "dbName": "dev", "namespaceName": "testnamespace" }, "responseElements": { "namespace": { "adminUsername": "HIDDEN_DUE_TO_SECURITY_REASONS", "creationDate": "Mar 21, 2022 11:15:40 PM", "defaultIamRoleArn": "", "iamRoles": [], "logExports": [], "namespaceArn": "arn:aws:redshift-serverless:us-east-1:111111111111:namespace/befa5123-16c2-4449-afca-1d27cb40fc99", "namespaceId": "8b726a0c-16ca-4799-acca-1d27cb403599", "namespaceName": "testnamespace", "status": "AVAILABLE" } }, "requestID": "ed4bb777-8127-4dae-aea3-bac009999163", "eventID": "1dbee944-f889-4beb-b228-7ad0f312464", "readOnly": false, "eventType": "AwsApiCall", "managementEvent": true, "recipientAccountId": "111111111111", "eventCategory": "Management", }

Amazon Redshift Data API examples

The following example shows a CloudTrail log entry that demonstrates the ExecuteStatement action.

{ "eventVersion":"1.05", "userIdentity":{ "type":"IAMUser", "principalId":"AKIAIOSFODNN7EXAMPLE:janedoe", "arn":"arn:aws:sts::123456789012:user/janedoe", "accountId":"123456789012", "accessKeyId":"AKIAI44QH8DHBEXAMPLE", "userName": "janedoe" }, "eventTime":"2020-08-19T17:55:59Z", "eventSource":"redshift-data.amazonaws.com", "eventName":"ExecuteStatement", "awsRegion":"us-east-1", "sourceIPAddress":"192.0.2.0", "userAgent":"aws-cli/1.18.118 Python/3.6.10 Linux/4.9.217-0.1.ac.205.84.332.metal1.x86_64 botocore/1.17.41", "requestParameters":{ "clusterIdentifier":"example-cluster-identifier", "database":"example-database-name", "dbUser":"example_db_user_name", "sql":"***OMITTED***" }, "responseElements":{ "clusterIdentifier":"example-cluster-identifier", "createdAt":"Aug 19, 2020 5:55:58 PM", "database":"example-database-name", "dbUser":"example_db_user_name", "id":"5c52b37b-9e07-40c1-98de-12ccd1419be7" }, "requestID":"00c924d3-652e-4939-8a7a-cd0612eeb8ac", "eventID":"c1fb7076-102f-43e5-9ec9-40820bcc1175", "readOnly":false, "eventType":"AwsApiCall", "recipientAccountId":"123456789012" }

The following example shows a CloudTrail log entry that demonstrates the ExecuteStatement action showing the clientToken used for idempotency.

{ "eventVersion":"1.05", "userIdentity":{ "type":"IAMUser", "principalId":"AKIAIOSFODNN7EXAMPLE:janedoe", "arn":"arn:aws:sts::123456789012:user/janedoe", "accountId":"123456789012", "accessKeyId":"AKIAI44QH8DHBEXAMPLE", "userName": "janedoe" }, "eventTime":"2020-08-19T17:55:59Z", "eventSource":"redshift-data.amazonaws.com", "eventName":"ExecuteStatement", "awsRegion":"us-east-1", "sourceIPAddress":"192.0.2.0", "userAgent":"aws-cli/1.18.118 Python/3.6.10 Linux/4.9.217-0.1.ac.205.84.332.metal1.x86_64 botocore/1.17.41", "requestParameters":{ "clusterIdentifier":"example-cluster-identifier", "database":"example-database-name", "dbUser":"example_db_user_name", "sql":"***OMITTED***", "clientToken":"32db2e10-69ac-4534-b3fc-a191052616ce" }, "responseElements":{ "clusterIdentifier":"example-cluster-identifier", "createdAt":"Aug 19, 2020 5:55:58 PM", "database":"example-database-name", "dbUser":"example_db_user_name", "id":"5c52b37b-9e07-40c1-98de-12ccd1419be7" }, "requestID":"00c924d3-652e-4939-8a7a-cd0612eeb8ac", "eventID":"c1fb7076-102f-43e5-9ec9-40820bcc1175", "readOnly":false, "eventType":"AwsApiCall", "recipientAccountId":"123456789012" }

Amazon Redshift query editor v2 example

The following example shows a CloudTrail log entry that demonstrates the CreateConnection action.

{ "eventVersion": "1.08", "userIdentity": { "type": "AssumedRole", "principalId": "AAKEOFPINEXAMPLE:session", "arn": "arn:aws:sts::123456789012:assumed-role/MyRole/session", "accountId": "123456789012", "accessKeyId": "AKIAI44QH8DHBEXAMPLE", "sessionContext": { "sessionIssuer": { "type": "Role", "principalId": "AAKEOFPINEXAMPLE", "arn": "arn:aws:iam::123456789012:role/MyRole", "accountId": "123456789012", "userName": "MyRole" }, "webIdFederationData": {}, "attributes": { "creationDate": "2022-09-21T17:19:02Z", "mfaAuthenticated": "false" } } }, "eventTime": "2022-09-21T22:22:05Z", "eventSource": "sqlworkbench.amazonaws.com", "eventName": "CreateConnection", "awsRegion": "ca-central-1", "sourceIPAddress": "192.2.0.2", "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10.15; rv:102.0) Gecko/20100101 Firefox/102.0", "requestParameters": { "password": "***", "databaseName": "***", "isServerless": false, "name": "***", "host": "redshift-cluster-2.c8robpbxvbf9.ca-central-1.redshift.amazonaws.com", "authenticationType": "***", "clusterId": "redshift-cluster-2", "username": "***", "tags": { "sqlworkbench-resource-owner": "AAKEOFPINEXAMPLE:session" } }, "responseElements": { "result": true, "code": "", "data": { "id": "arn:aws:sqlworkbench:ca-central-1:123456789012:connection/ce56b1be-dd65-4bfb-8b17-12345123456", "name": "***", "authenticationType": "***", "databaseName": "***", "secretArn": "arn:aws:secretsmanager:ca-central-1:123456789012:secret:sqlworkbench!7da333b4-9a07-4917-b1dc-12345123456-qTCoFm", "clusterId": "redshift-cluster-2", "dbUser": "***", "userSettings": "***", "recordDate": "2022-09-21 22:22:05", "updatedDate": "2022-09-21 22:22:05", "accountId": "123456789012", "tags": { "sqlworkbench-resource-owner": "AAKEOFPINEXAMPLE:session" }, "isServerless": false } }, "requestID": "9b82f483-9c03-4cdd-bb49-a7009e7da714", "eventID": "a7cdd442-e92f-46a2-bc82-2325588d41c3", "readOnly": false, "eventType": "AwsApiCall", "managementEvent": true, "recipientAccountId": "123456789012", "eventCategory": "Management" }

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) operations 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 (Hyderabad) Region ap-south-2 297058826802
Asia Pacific (Jakarta) Region ap-southeast-3 623197973179
Asia Pacific (Melbourne) Region ap-southeast-4 945512339897
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
Canada West (Calgary) Region ca-west-1 830903446466
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 (Spain) Region eu-south-2 028811157404
Europe (Stockholm) Region eu-north-1 553461782468
Europe (Zurich) Region eu-central-2 668912161003
Israel (Tel Aviv) Region il-central-1 901883065212
Middle East (Bahrain) Region me-south-1 051362938876
Middle East (UAE) Region me-central-1 595013617770
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" }