Encrypted Cross Account Zero-ETL integration between DynamoDB and Redshift

When attempting to integrate from DynamoDB to Redshift, the initial action is launched from Redshift and without some of the below permissions the action could result in a silent failure. The goal here is to provide permissions required for this complex actions so it's not confusing why the integration fails.

The necessary KMS policies and permissions for both the Redshift (source) and DynamoDB (target) accounts for zero-ETL integration are listed below.

Replace the following placeholders:

• REDSHIFT_ACCOUNT_ID: The AWS account ID where Redshift is hosted
• DYNAMODB_ACCOUNT_ID: The AWS account ID where DynamoDB is hosted
• REDSHIFT_ROLE_NAME: The IAM role name used by Redshift
• REGION: The AWS region where your resources are located
• TABLE_NAME: The name of your DynamoDB table
• KMS_KEY_ID: The ID of your KMS key

1. First, the KMS Key Policy in the DynamoDB account:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "Enable IAM User Permissions",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::DYNAMODB_ACCOUNT_ID:root"
            },
            "Action": "kms:*",
            "Resource": "*"
        },
        {
            "Sid": "Allow Redshift to use the key",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::REDSHIFT_ACCOUNT_ID:role/REDSHIFT_ROLE_NAME"
            },
            "Action": [
                "kms:Decrypt",
                "kms:DescribeKey",
                "kms:GenerateDataKey",
                "kms:GenerateDataKeyWithoutPlaintext"
            ],
            "Resource": "*"
        }
    ]
}

2. IAM Policy for the Redshift role (in Redshift account):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowDynamoDBAccess",
            "Effect": "Allow",
            "Action": [
                "dynamodb:DescribeTable",
                "dynamodb:ParitalBatchGetItem",
                "dynamodb:Scan",
                "dynamodb:Query",
                "dynamodb:BatchGetItem",
                "dynamodb:GetItem",
                "dynamodb:GetRecords",
                "dynamodb:GetShardIterator",
                "dynamodb:DescribeStream",
                "dynamodb:ListStreams"
            ],
            "Resource": [
                "arn:aws:dynamodb:REGION:DYNAMODB_ACCOUNT_ID:table/TABLE_NAME",
                "arn:aws:dynamodb:REGION:DYNAMODB_ACCOUNT_ID:table/TABLE_NAME/stream/*"
            ]
        },
        {
            "Sid": "AllowKMSAccess",
            "Effect": "Allow",
            "Action": [
                "kms:Decrypt",
                "kms:DescribeKey",
                "kms:GenerateDataKey",
                "kms:GenerateDataKeyWithoutPlaintext"
            ],
            "Resource": "arn:aws:kms:REGION:DYNAMODB_ACCOUNT_ID:key/KMS_KEY_ID"
        }
    ]
}

1. Trust Relationship for the Redshift role:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "redshift.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

2. DynamoDB Table Policy (if using resource-based policies):

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowRedshiftAccess",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::REDSHIFT_ACCOUNT_ID:role/REDSHIFT_ROLE_NAME"
            },
            "Action": [
                "dynamodb:DescribeTable",
                "dynamodb:ParitalBatchGetItem",
                "dynamodb:Scan",
                "dynamodb:Query",
                "dynamodb:BatchGetItem",
                "dynamodb:GetItem",
                "dynamodb:GetRecords",
                "dynamodb:GetShardIterator",
                "dynamodb:DescribeStream",
                "dynamodb:ListStreams"
            ],
            "Resource": [
                "arn:aws:dynamodb:REGION:DYNAMODB_ACCOUNT_ID:table/TABLE_NAME",
                "arn:aws:dynamodb:REGION:DYNAMODB_ACCOUNT_ID:table/TABLE_NAME/stream/*"
            ]
        }
    ]
}

Important considerations:

1. Ensure the KMS key is in the same region as your DynamoDB table
2. The KMS key must be a customer-managed key (CMK), not an AWS-managed key
3. If using DynamoDB global tables, you'll need to configure permissions for all relevant regions
4. Consider adding condition statements to restrict access based on VPC endpoints or IP ranges
5. For enhanced security, consider using aws:PrincipalOrgID condition to restrict access to your organization
6. Monitor KMS key usage through CloudTrail and CloudWatch metrics