Redshift database user is not authorized to assume IAM Role

Question

I am trying to run a copy command to copy some data from a s3 bucket in account A to redshift in account B. I have created two different IAM roles, one for s3 in account A and one for redshift in account B. I have attached the IAM role to redshift cluster and also added the same as a trust entity for the s3 role.

Here is the trust policy for s3 IAM role :

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::<redshiftAccountId>:root"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}

Below is the inline policy for redshift IAM role :

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Action": [
                "rds-db:connect",
                "redshift:GetClusterCredentials",
                "redshift:JoinGroup",
                "redshift:CreateClusterUser"
            ],
            "Resource": "*",
            "Effect": "Allow"
        },
        {
            "Effect": "Allow",
            "Action": "sts:AssumeRole",
            "Resource": "arn:aws:iam::<s3AccountId>:role/<s3Role>"
        }
    ]
}

Everytime i run the copy command, i get the following error : ERROR: User arn:aws:redshift:<redshiftAccountRegion>:<redshiftAccountId>:dbuser:<redshift-cluster>/<database-user> is not authorized to assume IAM Role arn:aws:iam::<redshiftAccountid>:role/<redshift-role>,arn:aws:iam::<s3AccountId>:role/<s3Role>

Rohit Pandey Rohit Pandey · Accepted Answer · 2021-03-17T09:55:19

I resolved this issue. I have added a bucket policy to my s3 bucket :

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowS3",
            "Effect": "Allow",
            "Principal": {
                "AWS": "arn:aws:iam::<redshiftAccountId>:root"
            },
            "Action": [
                "s3:PutObject",
                "s3:ListBucket",
                "s3:GetObject",
                "s3:DeleteObject"
            ],
            "Resource": [
                "arn:aws:s3:::<bucket-name>/*",
                "arn:aws:s3:::<bucket-name>"
            ]
        }
}

I have also attached a new policy to my redshift IAM role :

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AllowS3",
            "Effect": "Allow",
            "Action": "s3:*",
            "Resource": [
                "arn:aws:s3:::<bucket-name>/*",
                "arn:aws:s3:::<bucket-name>"
            ]
        }
    ]
}

And below is my redshift query :

COPY users
FROM 's3://<bucket-name>/<file-name>'
iam_role 'arn:aws:iam::<redshiftAccountId>:role/<redshiftRole>'
DELIMITER '|'
REGION '<aws-region>'

Refer to the follwing : https://www.pmg.com/blog/cross-account-redshift-unload-copy/

Redshift database user is not authorized to assume IAM Role

1 Answers