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

4 minute read
0

I want to audit the database on my Amazon Redshift cluster.

Short description

Amazon Redshift provides three logging options:

Audit logging and STL/SYS tables record database-level activities, such as the users that are logged in and when they're logged in. The tables also record the SQL activities that the users performed and when they performed them. CloudTrail tracks user activity at the service level.

Note: To use external tables to view logs, use Amazon Redshift Spectrum. For more information, see Analyze database audit logs for security and compliance using Amazon Redshift Spectrum.

Resolution

Audit logging and STL/ SYS tables

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

Audit loggingSTL/SYS tables
Use either the Amazon Redshift console to turn on audit logging, or the AWS Command Line Interface (AWS CLI) and Amazon Redshift API.STL/SYS tables are automatically available on every node in the data warehouse cluster.
Audit log files are stored indefinitely, unless you define Amazon S3 lifecycle rules to automatically archive or delete files. For more information, see Managing your storage lifecycle.Log history is stored for 2-5 days, depending on log usage and available disk space. To extend the retention period, use the Amazon Redshift system object persistence utility on the GitHub website.
Access to audit log files doesn't require access to the Amazon Redshift database.Access to STL/SYS tables requires access to the Amazon Redshift database.
Database computing resources aren't required to review logs stored in Amazon S3.Similar to when you run other queries, database computing resources are required to run queries against STL/SYS tables.
You can use timestamps to correlate process IDs with database activities. Cluster restarts don't affect audit logging in Amazon S3.You can't always correlate process IDs with database activities because process IDs might be recycled when the cluster restarts.
Audit logging stores information in the following log files: Connection log, User log, and User activity log. Note: You must turn on the enable_user_activity_logging database parameter for the user activity log. For more information, see Turning on logging.You can use SVL_STATEMENTTEXT, STL_CONNECTION_LOG, and SYS_CONNECTION_LOG to view detailed information.
Audit logging records all SQL statements in the user activity logs.Queries that you run are logged in STL_QUERY. DDL statements are logged in STL_DDLTEXT. The text of non-SELECT SQL commands are logged in STL_UTILITYTEXT. On Amazon Redshift Serverless, use SYS_QUERY_HISTORY and SYS_QUERY_DETAIL to check all SQL commands.
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 you run each SQL statement.
Audit logging 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) on provisioned clusters. Use SYS_QUERY_HISTORY and SYS_QUERY_DETAIL to determine how long an activity took to complete on Amazon Redshift Serverless.
You are charged for the storage that your logs use in Amazon S3.There are no additional charges for STL/SYS table storage.
Queries that you run only on the leader node are recorded.Queries that you run only on the leader node aren't recorded.

CloudTrail

Use information that CloudTrail collects to 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 automatically archive or delete files. For more information, see Managing your storage lifecycle.

Related information

Tuning query performance

AWS OFFICIAL
AWS OFFICIALUpdated a month ago