Arrange cross-account audit logging on your Amazon Redshift cluster

Amazon Redshift is a totally managed, petabyte-scale information warehouse service within the cloud. With Amazon Redshift, you possibly can analyze all of your information to derive holistic insights about your online business and your prospects. Probably the greatest practices of recent utility design is to have centralized logging. Troubleshooting utility issues is straightforward when you possibly can correlate all of your information collectively.

Whenever you allow audit logging, Amazon Redshift logs info about connections and person actions within the database. These logs show you how to monitor the database for safety and troubleshooting functions, a course of referred to as database auditing. The logs are saved in Amazon Easy Storage Service (Amazon S3) buckets. These present handy entry with information safety features for customers who’re accountable for monitoring actions within the database.

If you wish to set up a central audit logging account to seize audit logs generated by Amazon Redshift clusters positioned in separated AWS accounts, you need to use the answer on this publish to realize cross-account audit logging for Amazon Redshift. As of this writing, the Amazon Redshift console solely lists S3 buckets from the identical account (wherein the Amazon Redshift cluster is positioned) whereas enabling audit logging, so you possibly can’t arrange cross-account audit logging utilizing the Amazon Redshift console. On this publish, we show how you can configure cross-account audit logging utilizing the AWS Command Line Interface (AWS CLI).

Conditions

For this walkthrough, it’s essential to have the next stipulations:

• Two AWS accounts: one for analytics and one for centralized logging
• A provisioned Amazon Redshift cluster within the analytics AWS account
• An S3 bucket within the centralized logging AWS account
• Entry to the AWS CLI

Overview of resolution

As a common safety finest follow, we suggest ensuring that Amazon Redshift audit logs are despatched to the proper S3 buckets. The Amazon Redshift service crew has launched extra safety controls within the occasion that the vacation spot S3 bucket resides in a unique account from the Amazon Redshift cluster proprietor account. For extra info, see Bucket permissions for Amazon Redshift audit logging.

This publish makes use of the AWS CLI to determine cross-account audit logging for Amazon Redshift, as illustrated within the following structure diagram.

For this publish, we established an Amazon Redshift cluster named redshift-analytics-cluster-01 within the analytics account in Area us-east-2.

We additionally arrange an S3 bucket named redshift-cluster-audit-logging-xxxxxxxxxxxx within the centralized logging account for capturing audit logs in Area us-east-1.

Now you’re prepared to finish the next steps to arrange the cross-account audit logging:

1. Create AWS Identification and Entry Administration (IAM) insurance policies within the analytics AWS account.
2. Create an IAM person and connect the insurance policies you created.
3. Create an S3 bucket coverage within the centralized logging account to permit Amazon Redshift to put in writing audit logs to the S3 bucket, and permit the IAM person to allow audit logging for the S3 bucket.
4. Configure the AWS CLI.
5. Allow audit logging within the centralized logging account.

Create IAM insurance policies within the analytics account

Create two IAM insurance policies within the analytics account that has the Amazon Redshift cluster.

The primary coverage is the Amazon Redshift entry coverage (we named the coverage redshift-audit-logging-redshift-policy). This coverage permits the principal to whom it’s connected to allow, disable, or describe Amazon Redshift logs. It additionally permits the principal to explain the Amazon Redshift cluster. See the next code:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "VisualEditor0",
            "Effect": "Allow",
            "Action": [
                "redshift:EnableLogging",
                "redshift:DisableLogging",
                "redshift:DescribeLoggingStatus"
            ],            
"Useful resource": "arn:aws:redshift:us-east-2:xxxxxxxxxxxx:cluster: redshift-analytics-cluster-01"
        },
        {
            "Sid": "VisualEditor1",
            "Impact": "Enable",
            "Motion": "redshift:DescribeClusters",
            "Useful resource": "*"
        }
    ]
}

The second coverage is the Amazon S3 entry coverage (we named the coverage redshift-audit-logging-s3-policy). This coverage permits the principal to whom it’s connected to put in writing to the S3 bucket within the centralized logging account. See the next code:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:PutObjectAcl"
            ],
            "Useful resource": [
                "arn:aws:s3:::redshift-cluster-audit-logging-xxxxxxxxxxxx",
                "arn:aws:s3:::redshift-cluster-audit-logging-xxxxxxxxxxxx/*"
            ]
        }
    ]
}

Create an IAM person and connect the insurance policies

Create an IAM person (we named it redshift-audit-logging-user) with programmatic entry within the analytics account and connect the insurance policies you created to it.

Save the generated AWS secret key and secret entry key credentials for this person securely. We use these credentials within the subsequent step.

Create an S3 bucket coverage for the S3 bucket within the centralized logging AWS account

Add the next bucket coverage to the audit logging S3 bucket redshift-cluster-audit-logging-xxxxxxxxxxxx within the centralized logging account. This coverage serves two functions: it permits Amazon Redshift to put in writing audit logs to the S3 bucket, and it permits the IAM person to allow audit logging for the S3 bucket. See the next code:

{
    "Model": "2012-10-17",
    "Assertion": [
        {
            "Sid": "Put bucket policy needed for audit logging",
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
            },
            "Action": [
                "s3:PutObject",
                "s3:GetBucketAcl"
            ],
            "Useful resource": [
                "arn:aws:s3:::redshift-cluster-audit-logging-xxxxxxxxxxxx",
                "arn:aws:s3:::redshift-cluster-audit-logging-xxxxxxxxxxxx/*"
            ]
        },
        {
            "Sid": "Put IAM Consumer bucket coverage wanted for audit logging",
            "Impact": "Enable",
            "Principal": {
                "AWS": "arn:aws:iam::xxxxxxxxxxxx:person/redshift-audit-logging-user"
            },
            "Motion": "s3:PutObject",
            "Useful resource": [
                "arn:aws:s3:::redshift-cluster-audit-logging-xxxxxxxxxxxx",
                "arn:aws:s3:::redshift-cluster-audit-logging-xxxxxxxxxxxx/*"
            ]
        }
    ]
}

Observe that it’s a must to modify the service title redshift.amazonaws.com to appear like redshift.area.amazonaws.com if the cluster is in one of many opt-in Areas.

Configure the AWS CLI

As a part of this step, it’s good to set up and configure the AWS CLI. After you put in the AWS CLI, configure it to make use of the IAM person credentials that we generated earlier. We carry out the following steps primarily based on the permissions connected to the IAM person we created.

Allow audit logging within the centralized logging account

Run the AWS CLI command to allow audit logging for the Amazon Redshift cluster in an S3 bucket within the centralized logging AWS account. Within the following code, present the Amazon Redshift cluster ID, S3 bucket title, and the prefix utilized to the log file names:

aws redshift enable-logging --cluster-identifier <ClusterName> --bucket-name <BucketName> --s3-key-prefix <worth>

The next screenshot reveals that the cross-account Amazon Redshift audit logging is efficiently arrange.

A take a look at file can be created by AWS to make sure that the log recordsdata could be efficiently written into the S3 bucket. The next screenshot reveals the take a look at file was created efficiently within the S3 bucket beneath the rsauditlog1 prefix.

After a while, we began seeing the audit logs created within the S3 bucket. By default, Amazon Redshift organizes the log recordsdata within the S3 bucket utilizing the next bucket and object construction:

AWSLogs/AccountID/ServiceName/Area/Yr/Month/Day/AccountID_ServiceName_Region_ClusterName_LogType_Timestamp.gz

Amazon Redshift logs info within the following log recordsdata:

• Connection log – Logs authentication makes an attempt, connections, and disconnections
• Consumer log – Logs details about modifications to database person definitions
• Consumer exercise log – Logs every question earlier than it’s run on the database

The next screenshot reveals that log recordsdata, similar to connection logs and person exercise logs, are actually being created within the centralized logging account in us-east-1 from the Amazon Redshift cluster within the analytics account in us-east-2.

For extra particulars on analyzing Amazon Redshift audit logs, consult with beneath talked about blogs

1. Visualize Amazon Redshift audit logs utilizing Amazon Athena and Amazon QuickSight
2. How do I analyze my audit logs utilizing Amazon Redshift Spectrum?

Clear up

To keep away from incurring future costs, you possibly can delete all of the assets you created whereas following the steps on this publish.

Conclusion

On this publish, we demonstrated how you can accomplish cross-account audit logging for an Amazon Redshift cluster in a single account to an Amazon S3 bucket in one other account. Utilizing this resolution, you possibly can set up a central audit logging account to seize audit logs generated by Amazon Redshift clusters positioned in separated AWS accounts.

Do this resolution to realize cross-account audit logging for Amazon Redshift and depart a remark.

In regards to the Authors

Milind Oke is a Knowledge Warehouse Specialist Options Architect primarily based out of New York. He has been constructing information warehouse options for over 15 years and focuses on Amazon Redshift.

Dipankar Kushari is a Sr. Analytics Options Architect with AWS.

Pankaj Pattewar is a Cloud Utility Architect at Amazon Internet Companies. He focuses on architecting and constructing cloud-native functions and allows prospects with finest practices of their cloud journey.

Sudharshan Veerabatheran is a Cloud Help Engineer primarily based out of Portland.