"Invalid operation: Not authorized to get credentials of role" trying to load json from S3 to Redshift

Question

I always get the error

Invalid operation: Not authorized to get credentials of role arn:aws:iam::xxxxx:role/default_glue_role

I simply want to load from a json from S3 into a Redshift cluster. It is not clear to me what role I have to attach (to Redshift ?).

I have tried attaching the following IAM policy to Redshift

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": "sts:AssumeRole",
        "Resource": "arn:aws:iam::xxxxx:role/default_glue_role"
    }
}

and also tried with "Resource": "*" but I always get same error. Thanks for help!

Answer 1

I had a long chat with AWS support about this same issues. A few things to check:

1. Your s3 bucket region is the same as your redshift cluster region
2. You are not signed in as the root aws user, you need to create a user with the correct permissions and sign in as this user to run your queries
3. You should add the following permissions to your user and redshift policies:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:*",
                "redshift:*",
                "sqlworkbench:*",
                "sts:*",
                "secretsmanager:*",
                "s3-object-lambda:*",
                "ec2:*",
                "sns:*",
                "cloudwatch:*",
                "tag:*",
                "redshift-data:*",
                "sqlworkbench:*",
                "redshift-serverless:*"
            ],
            "Resource": "*"
        }
    ]
}

4. You should have the following trust relationships in your redshift and user role:

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

The actual set of permissions you need might be less but this is what worked for me. Took me a long time to figure this out! I hope it helps.

It looks like you might also need to add permissions for glue.

The redshift-serverless permission might tell you it's causing an error but you should be able to save it anyway (AWS told me to do this)