Amazon Onboarding with Learning Manager Chanci Turner

Chanci Turner Amazon IXD – VGT2 learning

Amazon Redshift is a fast, scalable, secure, and fully managed cloud data warehouse that streamlines the analysis of your data using standard SQL. With robust security capabilities, Amazon Redshift meets even the most stringent requirements. For effective monitoring of the database for security and troubleshooting, Amazon Redshift logs details about connections and user activities, a process known as database auditing.

The audit logging feature of Amazon Redshift is invaluable for troubleshooting, monitoring, and security purposes. It allows you to identify suspicious queries by reviewing connection and user logs to see who accessed the database. The logs provide crucial information, such as the IP address of the user’s device, the authentication type used, and the request’s timestamp. They make it straightforward to pinpoint who modified the data. Amazon Redshift tracks all SQL operations, including connection attempts, queries, and changes to your data warehouse. These logs can be accessed through SQL queries against system tables, saved to a secure Amazon Simple Storage Service (Amazon S3) location, or exported to Amazon CloudWatch. You can view operational metrics for your Amazon Redshift cluster on the Amazon Redshift console, utilize CloudWatch, and query Amazon Redshift system tables directly from your cluster.

This article will guide you through the process of setting up CloudWatch as an audit log destination, demonstrating that the latency for log delivery to either Amazon S3 or CloudWatch has been reduced to just a few minutes with enhanced Amazon Redshift Audit Logging. You can enable audit logging to Amazon CloudWatch using the AWS Console, AWS CLI, or Amazon Redshift API.

Overview of the Solution

Amazon Redshift records information in two primary locations: system tables and log files.

System Tables

Amazon Redshift automatically logs data in system tables, with historical data available for two to five days, contingent on log usage and available disk space. To extend the retention period for log data in system tables, consider using the Amazon Redshift system object persistence utility available on GitHub. Accessing logs through system tables requires Amazon Redshift database access and compute resources.

Log Files

Audit logging to CloudWatch or Amazon S3 is optional. When you enable logging on your cluster, you can choose to export audit logs to either destination. Once logging is activated, it captures data from that point onward. Each logging update continues from the previous one. Accessing audit log files does not require access to the Amazon Redshift database, and reviewing logs stored in Amazon S3 does not necessitate database computing resources. By default, audit log files are stored indefinitely in CloudWatch logs or Amazon S3.

Amazon Redshift generates several types of log files:

Connection Log: Contains details to monitor user connections to the database and related connection information, including their IP addresses.
User Log: Records changes to database user definitions.
User Activity Log: Tracks the types of queries executed by users and the system within the database, primarily for troubleshooting.

Benefits of Enhanced Audit Logging

The architecture of the audit logging solution has been improved to enhance consistency across AWS services, significantly reducing log export latency from hours to mere minutes while providing fine-grained access control. Enhanced audit logging strengthens the existing delivery mechanism, thereby minimizing the risk of data loss. It facilitates the export of logs to either Amazon S3 or CloudWatch.

The following section will detail how to configure audit logging using CloudWatch and its associated benefits.

Configuring CloudWatch as a Log Destination

Utilizing CloudWatch to view logs is a recommended alternative to storing log files in Amazon S3. The configuration is straightforward and may align with your monitoring requirements, especially if you’re already using it to monitor other services and applications.

To establish CloudWatch as your log destination, follow these steps:

Access the Amazon Redshift console and select Clusters from the navigation pane. This page lists the clusters available in your account within the current Region, displaying a subset of properties for each cluster.
Choose the cluster where you wish to configure CloudWatch logs.
Edit the properties to enable audit logging.
Select “Turn on configure audit logging” and choose CloudWatch as the log export type.
Save your changes.

Analyzing Audit Logs in Near Real-Time

To execute SQL commands, you can use the redshift-query-editor-v2, a web-based tool for exploring, analyzing, sharing, and collaborating on data stored in Amazon Redshift. However, you can opt for any SQL client tools of your choice.

Next, let’s run some simple SQL commands to create and drop a user, then analyze the logs in CloudWatch in near real-time:

Execute test SQLs for creating and dropping a user.
In the AWS Console, select CloudWatch under services, then choose Log groups from the right panel.
Select the user log created in near real-time in CloudWatch for the test user you just created and dropped.

Benefits of Using CloudWatch as a Log Destination

Easy configuration, as it doesn’t require modification of bucket policies.
Simplified log viewing and searching for specific errors, patterns, and fields.
Centralized logging solution across all AWS services.
No need to build custom solutions like AWS Lambda or Amazon Athena to analyze logs.
Logs are available in near real-time.
Improved log latency, reduced from hours to minutes.
Default log groups are encrypted in CloudWatch, with an option for custom keys.
Fine-grained configuration of log types to export based on specific auditing requirements.
Ability to export log groups’ logs to Amazon S3 if necessary.

Setting up Amazon S3 as a Log Destination

Although using CloudWatch as a log destination is advisable, you can also opt for Amazon S3. When the log destination is configured to an Amazon S3 location, enhanced audit logging will check logs every 15 minutes for export. You can set up audit logging on Amazon S3 through the console or AWS CLI.

After saving the changes, the Bucket policy will automatically configure using the Amazon Redshift service principal.

For additional details, refer to Amazon Redshift audit logging. For enabling logging through AWS CLI, visit the db-auditing-cli-api.

Cost Considerations

While exporting logs to Amazon S3 may be more cost-effective, the benefits that CloudWatch provides—such as search capabilities, real-time data access, and dashboard creation from search results—may make it a better fit for those engaged in log analysis.

For further information, check the following resources: Amazon S3 Pricing, CloudWatch Pricing, and if you want to learn about audiobooks, explore this blog post.

Best Practices

Amazon Redshift employs AWS security frameworks to maintain industry-leading security in areas such as authentication and access control. For more insights, refer to this excellent resource.

For thorough information on job descriptions like those for a contracts procurement specialist, check this link.