[Redshift] When unloading to an S3 bucket where the default encryption is SSE-KMS, note that you need to consider specifying parameters

I'm the Data Business Department's uehara.

In this article, I'd like to discuss the importance of considering parameter specifications when unloading from Redshift to S3 buckets that have SSE-KMS as their default encryption.

Introduction

Particularly when using AWS managed keys (alias/aws/s3) for SSE-KMS bucket encryption as the default encryption setting, many people don't consciously think about encryption during Put operations since the IAM role on the object-putting side only needs S3 access permissions without having to specify KMS key policies.

However, when unloading from Redshift to a bucket with SSE-KMS default encryption settings, if you don't explicitly specify certain parameters, the encryption will be performed using SSE-S3 instead.

Confirming the Issue

First, let's prepare an S3 bucket with SSE-KMS encryption using the AWS managed key (alias/aws/s3).

Let's try uploading a file to this bucket using the AWS CLI.

			
			$ aws s3 cp test.txt s3://uehara-kms-test/test.txt 

		

When we check the uploaded file, we can see it's encrypted with KMS.

This behavior doesn't contradict our intuition.

Next, let's try unloading some data from Redshift to this bucket without specifying any encryption parameters.

			
			UNLOAD('
    SELECT
        1 AS "test"
')
TO 's3://uehara-kms-test/test_from_redshift'
IAM_ROLE '<IAM role ARN>'
FORMAT AS CSV
DELIMITER ','
HEADER
PARALLEL OFF
EXTENSION 'csv';

		

The unload operation completed successfully.

When we check the unloaded file, as mentioned earlier, it's encrypted with SSE-S3.

Intuitively, I expected this to be encrypted with SSE-KMS, but it seems that's not the case.

The Cause

This is the intended behavior for Redshift's Unload operation.

The official documentation states:

If you don't specify the ENCRYPTED parameter, UNLOAD automatically creates encrypted files using Amazon S3 server-side encryption (SSE-S3) with AWS-managed encryption keys.

Therefore, if you don't specify the ENCRYPTED parameter, the output will be encrypted with SSE-S3, even if the S3 bucket's default encryption setting is SSE-KMS.## Response

Today I'll try to use ENCRYPTED to unload (you can also explicitly specify KMS_KEY_ID, but I'll set AUTO to use the default AWS KMS encryption key in the S3 bucket properties).

			
			UNLOAD('
    SELECT
        1 AS "test"
')
TO 's3://uehara-kms-test/test_from_redshift2'
IAM_ROLE '<IAM role ARN>'
FORMAT AS CSV
DELIMITER ','
HEADER
ENCRYPTED AUTO
PARALLEL OFF
EXTENSION 'csv';

		

When checking the unloaded file, we can confirm it was properly encrypted with SSE-KMS.

If you want to prevent files from being output encrypted with SSE-S3 due to missing parameters, you can set a bucket policy that denies s3:PutObject if the x-amz-server-side-encryption-aws-kms-key-id header is not included in the request.

			
			{
    "Version": "2012-10-17",
    "Id": "PutObjectPolicy",
    "Statement": [
        {
            "Sid": "DenyObjectsThatAreNotSSEKMS",
            "Effect": "Deny",
            "Principal": "*",
            "Action": "s3:PutObject",
            "Resource": "arn:aws:s3:::uehara-kms-test/*",
            "Condition": {
                "Null": {
                    "s3:x-amz-server-side-encryption-aws-kms-key-id": "true"
                }
            }
        }
    ]
}

		

With the above bucket policy set, attempting to unload without specifying the ENCRYPTED parameter will fail.

Conclusion

In this article, I discussed the importance of considering parameter specifications when unloading from Redshift to an S3 bucket where the default encryption is SSE-KMS.

I hope you found this helpful.

References

• UNLOAD
• Using Server-Side Encryption with AWS KMS keys (SSE-KMS)