How can I perform database auditing on my Amazon Redshift cluster?

Amazon Redshift provides three logging options:

Audit logs and STL tables record database-level activities, such as which users logged in and when, as well as which SQL activities these users performed and at what time. CloudTrail tracks activities performed at the service level.  

Audit logs and STL tables

The following table compares audit logs and STL tables. Choose the logging option that's appropriate for your use case.

Audit logs STL tables
Must be enabled. To enable audit logging, follow the steps for Configuring Auditing Using the Console or Configuring Logging by Using the Amazon Redshift CLI and API. Automatically available on every node in the data warehouse cluster.
Audit log files are stored indefinitely unless you define Amazon S3 lifecycle rules to archive or delete files automatically. For more information, see Object Lifecycle Management. Log history is stored for two to five days, depending on log usage and available disk space. To extend the retention period, use the Amazon Redshift System Object Persistence Utility from AWS Labs on GitHub.
Access to audit log files does not require access to the Amazon Redshift database. Access to STL tables requires access to the Amazon Redshift database.
Reviewing logs stored in Amazon S3 does not require database computing resources. Running queries against STL tables requires database computing resources, just like when you run other queries.
Using time stamps, you can correlate process IDs with database activities. Cluster restarts don't affect audit logs in Amazon S3. It's not always possible to correlate process IDs with database activities, because process IDs might be recycled when the cluster restarts.

Stores information in the following log files:

Stores information in multiple tables. Use the following tables to review information similar to what is stored in the S3 audit logs:

  • SVL_STATEMENTTEXT: Provides a complete record of SQL commands that have been run on the system. Combines all of the rows in the STL_DDLTEXT, STL_QUERYTEXT, and STL_UTILITYTEXT tables.
  • STL_CONNECTION_LOG: Logs authentication attempts, connections, or disconnections.
Records all SQL statements in the user activity logs. Executed queries are logged in STL_QUERY. DDL statements are logged in STL_DDLTEXT. The text of non-SELECT SQL commands are logged in STL_UTILITYTEXT.
Statements are logged as soon as Amazon Redshift receives them, but files on Amazon S3 are updated in batch, and can take a few hours to appear. Logs are generated after each SQL statement executes.
Records who performed what action and when that action happened, but not how long it took to perform the action. Use the STARTTIME and ENDTIME columns to determine how long an activity took to complete. To determine which user performed an action, combine SVL_STATEMENTTEXT (userid) with PG_USER (usesysid).
You are charged for the storage that your logs use in Amazon S3. There are no additional charges for STL table storage.
Leader-node only queries are recorded. Leader-node only queries are not recorded.

CloudTrail

Using information collected by CloudTrail, you can determine what requests were successfully made to AWS services, who made the request, and when it was made. For more information, see Logging Amazon Redshift API Calls with AWS CloudTrail.

CloudTrail log files are stored indefinitely in Amazon S3, unless you define lifecycle rules to archive or delete files automatically. For more information, see Object Lifecycle Management.


Did this page help you? Yes | No

Back to the AWS Support Knowledge Center

Need help? Visit the AWS Support Center

Published: 2018-08-22