AWS Redshift Serverless: `ERROR: Not authorized to get credentials of role`

Question

I've created a serverless Redshift instance, and I'm trying to import a CSV file from an S3 bucket.

I've made an IAM role with full Redshift + Redshift serverless access and S3 Read access, and added this role as a Default Role under the Permissions settings of the Serverless Configuration. Basically, I've tried to do anything that I thought should be necessary according to the documentation.

However, there docs are only targeted at the normal EC2 hosted Redshift for now, and not for the Serverless edition, so there might be something that I've overlooked.

But when I try running a COPY command (generated by the UI), I get this error:

ERROR: Not authorized to get credentials of role arn:aws:iam::0000000000:role/RedshiftFull Detail: ----------------------------------------------- error: Not authorized to get credentials of role arn:aws:iam::00000000:role/RedshiftFull code: 30000 context: query: 18139 location: xen_aws_credentials_mgr.cpp:402 process: padbmaster [pid=8791] ----------------------------------------------- [ErrorId: 1-61dc479b-570a4e96449b228552f2c911]

Here's the command I'm trying to run:

COPY dev."test-schema"."transactions" FROM 's3://bucket-name/something-1_2021-11-01T00_00_00.000Z_2022-01-03.csv' IAM_ROLE 'arn:aws:iam::0000000:role/RedshiftFull' FORMAT AS CSV DELIMITER ',' QUOTE '"' REGION AS 'eu-central-1'

Here's the Role

{
    "Role": {
        "Path": "/",
        "RoleName": "RedshiftFull",
        "RoleId": "AROA2PAMxxxxxxx",
        "Arn": "arn:aws:iam::000000000:role/RedshiftFull",
        "CreateDate": "2022-01-10T13:55:03+00:00",
        "AssumeRolePolicyDocument": {
            "Version": "2012-10-17",
            "Statement": [
                {
                    "Effect": "Allow",
                    "Principal": {
                        "Service": [
                            "redshift.amazonaws.com",
                            "sagemaker.amazonaws.com"
                        ]
                    },
                    "Action": "sts:AssumeRole"
                }
            ]
        },
        "Description": "Allows Redshift clusters to call AWS services on your behalf.",
        "MaxSessionDuration": 3600,
        "RoleLastUsed": {}
    }
}

{
    "AttachedPolicies": [
        {
            "PolicyName": "redshift-serverless",
            "PolicyArn": "arn:aws:iam::719432241830:policy/redshift-serverless"
        },
        {
            "PolicyName": "AmazonRedshiftFullAccess",
            "PolicyArn": "arn:aws:iam::aws:policy/AmazonRedshiftFullAccess"
        },
        {
            "PolicyName": "AmazonS3ReadOnlyAccess",
            "PolicyArn": "arn:aws:iam::aws:policy/AmazonS3ReadOnlyAccess"
        }
    ]
}

The redshift-serverless policy is here:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "redshift-serverless:*",
            "Resource": "*"
        }
    ]
}

Drastic

I wonder whether you need iam:PassRole permissions to specify the IAM Role to be used? Do you have iam:* permissions? (This is just a guess -- I haven't used Redshift Serverless.) – Jalapa

That didn't make any change, unfortunately :( I also tried adding redshift-serverless.amazonaws.com to the Trusted Entities, with no luck. – Drastic

Do you happen to have an AWS Support subscription? They'd be able to assist. – Jalapa

@EsbenvonBuchwald sorry for unsolicited question, but how were you able to connect to redshift serverless? In my case it complains on the absence of ClusterID when I try to use provided JDBC link – Tyratyrannical

I don't think you need to create a role anymore for serverless right ? – Portend

Facing a similar issue. Were you able to figure out a solution? – Arrhythmia

Answer 1

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:<region>:<account-id>:dbuser:<cluster-name>/<user>",
            "arn:aws:redshift:<region>:<account-id>:dbuser:<workgroup-name>/<user>"
        ]
    }
}

although this Redshift serverless documentation says

* For regular Redshift cluster use the following ARN format: arn:aws:redshift:<region>:<account-id>:dbuser:<cluster-name>/<user-name>
* For serverless Redshift use the following ARN format: arn:aws:redshift:<region>:<account-id>:dbuser:<workgroup-name>/<user-name>

the serverless ARN format is wrong. After countless trial-n-error, I discovered that only this ARN format arn:aws:redshift:<region>:<account-id>: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.

Prog

Answer 2

In my case, what worked is chaining 2 roles:

• the role of the cluster
• the role I created for redshift to access s3

I found it in the following documentation.

Thyratron

In OP's case, they are the same role – Prog