AWS Redshift: Masteruser not authorized to assume role

Question

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.

Answer 1

I ran into the same problem but after a good 1 hour of troubleshooting, I realised I had failed to add the Redshift role to the cluster while I was creating it. If you select the cluster from Redshift, choose the drop-down on 'Actions' and select 'Manage IAM roles' from there you will be able to attach the Redshift role you may have created for this cluster.

That solved the problem for me, anyways. Hope this helps.

Answer 2

I resolved this issue !! By default, IAM roles that are available to an Amazon Redshift cluster are available to all users on that cluster. You can choose to restrict IAM roles to specific Amazon Redshift database users on specific clusters or to specific regions.

To permit only specific database users to use an IAM role, take the following steps.

To identify specific database users with access to an IAM role

1. Identify the Amazon Resource Name (ARN) for the database users in your Amazon Redshift cluster. The ARN for a database user is in the format: arn:aws:redshift:region:account-id:dbuser:cluster-name/user-name.

2. Open the IAM Console at url="https://console.aws.amazon.com/.

3. In the navigation pane, choose Roles.

4. Choose the IAM role that you want to restrict to specific Amazon Redshift database users.

5. Choose the Trust Relationships tab, and then choose Edit Trust Relationship. A new IAM role that allows Amazon Redshift to access other AWS services on your behalf has a trust relationship as follows:

{

  "Version": "2012-10-17",

  "Statement": [

    {
      "Effect": "Allow",
      "Principal": {
        "Service": "redshift.amazonaws.com"
      },
      "Action": "sts:AssumeRole"
    }
  ]
}               

6. Add a condition to the sts:AssumeRole action section of the trust relationship that limits the sts:ExternalId field to values that you specify. Include an ARN for each database user that you want to grant access to the role.

For example, the following trust relationship specifies that only database users user1 and user2 on cluster my-cluster in region us-west-2 have permission to use this IAM role.

{

  "Version": "2012-10-17",

  "Statement": [

  {

    "Effect": "Allow",
    "Principal": { 
      "Service": "redshift.amazonaws.com" 
    },
    "Action": "sts:AssumeRole",
    "Condition": {
      "StringEquals": {
        "sts:ExternalId": [
          "arn:aws:redshift:us-west-2:123456789012:dbuser:my-cluster/user1",
          "arn:aws:redshift:us-west-2:123456789012:dbuser:my-cluster/user2"
        ]
      }
    }
  }]
}      

7.Choose Update Trust Policy.

Answer 3

I was trying to access Glue data catalog from Redshift. I created the role with the necessary policies attached (AWSGlueServiceRole, AmazonS3FullAccess), and added it to the cluster. However, I had set the AWS service as Glue but it should've been Redshift since Redshift is the service needing the access. Attaching these policies the Redshift role I have (and adding the role to the cluster, if necessary) solved the problem for me.