AWS Redshift Serverless COPY Error

Question

I am trying to copy data from S3 into redshift serverless and get the following error.

ERROR: Not authorized to get credentials of role arn:aws:iam::xxx Detail: ----------------------------------------------- error: Not authorized to get credentials of role arn:aws:iam::xxx code: 30000 context: query: 5031 location: xen_aws_credentials_mgr.cpp:402 process: padbxxx [pid=10282] ----------------------------------------------- [ErrorId: 1-6209a3af-30de21be27e8b5e412626606]

The role used has AmazonRedshiftAllCommandsFullAccess and AmazonRedshift-CommandsAccessPolicy-20220213T191838 which was created through the UI as well as a trust relationship as follows

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

Am I missing something?

Davondrick · Answer

I had this same issue, but after a ton of trial and error and looking through `AssumeRole` events in CloudTrail I was able to find that the format of the `dbuser` ARN in Redshift serverless is actually `arn:aws:redshift:::dbuser:serverless--/`. This format isn’t documented by AWS anywhere that I've seen, and the existing [documentation](https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html#authorizing-redshift-service-database-users) says to use the format `arn:aws:redshift:::dbuser:/` for Redshift serverless, but as Zach mentioned that doesn't work.

An example of a IAM role's trust relationship using this format:

```
{
  "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:serverless-123456789012-my-workgroup-id1/user1"
        ]
      }
    }
  }]
}
```

The AWS CLI can be used to get the workgroup id:
```
 aws redshift-serverless get-workgroup --workgroup-name  --query '*.workgroupId | [0]'
```

MilindOke · Answer

is the role set as [Default role](https://docs.aws.amazon.com/redshift/latest/mgmt/default-iam-role.html#managing-iam-role-console)?

and are you providing the [authorization](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-authorization.html) in the copy command?

it might be best to open support ticket if these do not help

Zach · Answer

I have the same issue. Opened up a support case but some AWS support engineers don't really know the ins and outs of Redshift serverless, which is understandable. Serverless == Blackbox. No one knows what's going on inside.

In my case, putting `"redshift-serverless.amazonaws.com"` doesn't work either. The root cause is that I have "Condition" in the trusted entity:

```
"Condition": {
    "StringLike": {
        "sts:ExternalId": [
            "arn:aws:redshift:::dbuser:/",
            "arn:aws:redshift:::dbuser:/"
        ]
    }
}
```

although [this Redshift serverless documentation][1] says

```
* For regular Redshift cluster use the following ARN format: arn:aws:redshift:::dbuser:/
* For serverless Redshift use the following ARN format: arn:aws:redshift:::dbuser:/
```

the serverless ARN format is wrong. After countless trial-n-error, I discovered that only this ARN format `arn:aws:redshift:::dbuser:serverless-*` works for serverless. But I couldn't figure out what the wildcard `*` represents. And of course, removing the "Condition" section entirely works too.

[1]: https://docs.aws.amazon.com/redshift/latest/mgmt/authorizing-redshift-service.html

AWS Redshift Serverless COPY Error

回答を追加する

関連するコンテンツ