amazon-web-servicesamazon-redshiftamazon-iam

AWS Redshift: Masteruser not authorized to assume role


I created a cloudformation stack with redshift cluster and a masteruser: testuser

"RedshiftCluster" : {
  "IamRoles" : [
      {
        "Fn::GetAtt": [
          "IAMInstanceRole",
          "Arn"
        ]
      }
    ]
  ... other configurations

It uses the below IAM role (IAMInstanceRole) which is in in-sync status and the redshift cluster is up and running:

"IAMInstanceRole": {
  "Properties": {
    "RoleName": "test-iam-role",
    "AssumeRolePolicyDocument": {
      "Statement": [
        {
          "Action": [
            "sts:AssumeRole"
          ],
          "Effect": "Allow",
          "Principal": {
            "Service": [
              "ec2.amazonaws.com",
              "redshift.amazonaws.com",
              "s3.amazonaws.com"
            ]
          }
        }
      ]
    },
    "Path": "/",
    "Policies": [ 
      {
      "PolicyName": "root",
        "PolicyDocument": {
          "Version" : "2012-10-17",
          "Statement": [ 
            {
              "Effect": "Allow",
              "Action": "*",
              "Resource": "*"
            }
          ]
        }
      } 
    ]
  }

I'm trying to load csv file from s3 to redshift using copy command and iam_role as credential. The iam_role has the arn of IAMInstanceRole (declared above). Whenever I execute the below command:

copy test_table from 's3://test-bucket/test.csv' CREDENTIALS 'aws_iam_role=arn:aws:iam::<account-id>:role/test-iam-role' MAXERROR 100000 removequotes TRIMBLANKS emptyasnull blanksasnull delimiter '|';

I get the error:

ERROR:  User arn:aws:redshift:us-west-2:189675173661:dbuser:automated-data-sanity-redshiftcluster-fbp9fgls6lri/sanityuser is not authorized to assume IAM Role arn:aws:iam::189675173661:role/sanity-test-iam-instance-role
DETAIL:




-----------------------------------------------
  error:  User arn:aws:redshift:us-west-2:<account-id>:dbuser:test-redshiftcluster-fbp9fgls6lri/testuser is not authorized to assume IAM Role arn:aws:iam::<account-id>:role/test-iam-role
  code:      8001
  context:   IAM Role=arn:aws:iam::<account-id>:role/test-iam-role
  query:     1139
  location:  xen_aws_credentials_mgr.cpp:236
  process:   padbmaster [pid=29280]
  -----------------------------------------------

Please suggest some resolution.


Solution

  • Got the solution after searching for a while. I created separate IAM role for redshift as John suggested, which is a correct advice but was not the issue in my case. Then followed the thread to resolve the issue: Copy from remote S3 using IAM Role - not authorized to assume IAM Role

    I'd to activate the region where my cluster was in Account Settings