User is is not authorized to assume IAM Role while copy from DynamoDB Table cross account.

Question

Hi AWS,

I am trying to copy data from DynamDB table in account A to redshift cluster in account B. The dynamodb table is encrypted with customer managed kms key and it is standard table with On-demand Capacity Mode.

These are the CloudFormation Templates:

DynamoDB Table Account:

```
# version: 1.0
AWSTemplateFormatVersion: "2010-09-09"
Resources: 
  RootRole: 
    Type: "AWS::IAM::Role"
    Properties: 
      AssumeRolePolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - Effect: "Allow"
            Principal: 
              AWS: 
                - arn:aws:iam:::root
                - arn:aws:iam:::root
            Action: 
              - "sts:AssumeRole"
      Path: "/"
      RoleName: "terraform_iam_role"
      
  IAMPolicy: 
    Type: "AWS::IAM::Policy"
    Properties: 
      PolicyName: drdc_iam_policy
      PolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - Effect: "Allow"
            Action: 
              - "ec2:*"
              - "ecs:*"
              - "redshift-serverless:*"
              - "redshift:*"
              - "iam:*"
              - "ec2:*"
              - "cloudwatch:*"
              - "s3:*"
              - "logs:*"
              - "cloudtrail:*"
              - "sns:*"
              - "lambda:*"
              - "kms:*"
              - "route53:*"
              - "dynamodb:*"
            Resource: "*"
          - Effect: Allow
            Action:
              - iam:PassRole
            Resource: arn:aws:iam:::role/aws-service-role/dynamodb.application-autoscaling.amazonaws.com/AWSServiceRoleForApplicationAutoScaling_DynamoDBTable
      Roles: 
        - Ref: RootRole
```

==========================
Redshift Cluster Account:

```
# version: 1.0
AWSTemplateFormatVersion: "2010-09-09"
Resources: 
  RootRole: 
    Type: "AWS::IAM::Role"
    Properties: 
      AssumeRolePolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - Effect: "Allow"
            Principal: 
              Service:
                - redshift.amazonaws.com
                - redshift-serverless.amazonaws.com
                - scheduler.redshift.amazonaws.com
                - dynamodb.amazonaws.com
              AWS: 
                - arn:aws:iam:::root
                - arn:aws:iam:::root
            Action: 
              - "sts:AssumeRole"
      Path: "/"
      RoleName: "terraform_iam_role"
      
  IAMPolicy: 
    Type: "AWS::IAM::Policy"
    Properties: 
      PolicyName: drdc_iam_policy
      PolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - Effect: "Allow"
            Action: 
              - "ec2:*"
              - "ecs:*"
              - "redshift-serverless:*"
              - "redshift:*"
              - "iam:*"
              - "ec2:*"
              - "cloudwatch:*"
              - "s3:*"
              - "logs:*"
              - "cloudtrail:*"
              - "sns:*"
              - "lambda:*"
              - "kms:*"
              - "route53:*"
            Resource: "*"
          - Effect: Allow
            Action:
              - iam:PassRole
            Resource:
              - !Sub arn:aws:iam::${AWS::AccountId}:role/aws-service-role/redshift.amazonaws.com/AWSServiceRoleForRedshift
              - !Sub arn:aws:iam::${AWS::AccountId}:role/drdc_lambda_execution_redshift_role
              - !Sub arn:aws:iam::${AWS::AccountId}:role/terraform_iam_role
      Roles: 
        - Ref: RootRole
```

When I am running the command from redshift query editor in account B:

```
COPY sales FROM 'dynamodb://sales'
iam_role 'arn:aws:iam:::role/terraform_iam_role,arn:aws:iam:::role/terraform_iam_role'
readratio 50;
```
I am experiencing the following error:

ERROR: User arn:aws:redshift:ca-central-1::dbuser:redshift-postgres-cluster/awsuser is not authorized to assume IAM Role 
arn:aws:iam:::role/terraform_iam_role,arn:aws:iam:::role/terraform_iam_role. Detail: -----------------------------------------------
error: User arn:aws:redshift:ca-central-1::dbuser:redshift-postgres-cluster/awsuser is not authorized to assume IAM Role
arn:aws:iam:::role/terraform_iam_role,arn:aws:iam:::role/terraform_iam_role. code: 8001 context: 
IAM Role=arn:aws:iam:::role/terraform_iam_role,arn:aws:iam::203188538396:role/terraform_iam_role query: 201398 location: xen_aws_credentials_mgr.cpp:498 process: query0_125_201398 [pid=14950] ---------------------

Can you please confirm whether cross account is possible in this case or am I missing something from IAM permissions point of view or there is something wrong in the COPY command I ran.

Thanks

Answer

The answer to the above issue is you need to attach IAM policy with sts:AssumeRole access for the dynamodb IAM role in account A to redshift cluster role in account B. Also make sure the DynamoDB table and Redshift Cluster in both accounts exist in the same region.
Apart from that  hashkey or rangekey in the dynamodb table should have corresponding columns in the redshift table. Make sure they exactly match in name and have suitable types else you will get error something like this:

**Error: invalid end key specified detail: ----------------------------------------------- error: invalid end key specified code: 9005 context: table name = sales query: 202639 location: copy_dynamodb_scanner.cpp:203 process: query0_126_202639 [pid=19265] -----------------------------------------------**

for more info refer this stackoverflow link: https://superuser.com/questions/590632/what-does-it-mean-when-redshift-gives-you-invalid-end-key-specified-on-a-dynam

The updated redshift cluster account template:

```
# version: 1.0
AWSTemplateFormatVersion: "2010-09-09"
Resources: 
  RootRole: 
    Type: "AWS::IAM::Role"
    Properties: 
      AssumeRolePolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - Effect: "Allow"
            Principal: 
              Service:
                - redshift.amazonaws.com
                - redshift-serverless.amazonaws.com
                - scheduler.redshift.amazonaws.com
                - dynamodb.amazonaws.com
              AWS: 
                - arn:aws:iam:::root
            Action: 
              - "sts:AssumeRole"
      Path: "/"
      RoleName: "terraform_iam_role"
      
  IAMPolicy: 
    Type: "AWS::IAM::Policy"
    Properties: 
      PolicyName: drdc_iam_policy
      PolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - Effect: "Allow"
            Action: 
              - "ec2:*"
              - "ecs:*"
              - "redshift-serverless:*"
              - "redshift:*"
              - "iam:*"
              - "ec2:*"
              - "cloudwatch:*"
              - "s3:*"
              - "logs:*"
              - "cloudtrail:*"
              - "sns:*"
              - "lambda:*"
              - "kms:*"
              - "route53:*"
            Resource: "*"
          - Effect: Allow
            Action:
              - iam:PassRole
            Resource:
              - !Sub arn:aws:iam::${AWS::AccountId}:role/aws-service-role/redshift.amazonaws.com/AWSServiceRoleForRedshift
              - !Sub arn:aws:iam::${AWS::AccountId}:role/drdc_lambda_execution_redshift_role
              - !Sub arn:aws:iam::${AWS::AccountId}:role/terraform_iam_role
  CrossAccountAssumeRolePolicy: 
    Type: "AWS::IAM::Policy"
    Properties: 
      PolicyName: drdc_cross_account_assume_role_policy
      PolicyDocument: 
        Version: "2012-10-17"
        Statement: 
          - Effect: "Allow"
            Action: 
              - sts:AssumeRole
            Resource: arn:aws:iam:::role/terraform_iam_role
      Roles: 
        - Ref: RootRole
```

Cheers!!

Answer

Apparently my Redshift IAM role was missing a policy to STS:AssumeRole on the source IAM role.

User <awsuser> is is not authorized to assume IAM Role while copy from DynamoDB Table cross account.

Relevant content