How do I use logs to track activity in my Amazon Redshift database cluster?

Last updated: 2019-04-16

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

Short Description

Amazon Redshift provides three logging options:

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

Note: To view logs using external tables, use Amazon Redshift Spectrum. For more information, see Analyze Database Audit Logs for Security and Compliance Using Amazon Redshift Spectrum.

Resolution

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 doesn't require access to the Amazon Redshift database. Access to STL tables requires access to the Amazon Redshift database.
Reviewing logs stored in Amazon S3 doesn't require database computing resources. Running queries against STL tables requires database computing resources, just as 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. 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 aren't recorded.

CloudTrail

Using information collected by CloudTrail, you can determine what requests were successfully made to AWS services, who made the request, and when the request 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 article help you?

Anything we could improve?


Need more help?