9

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": "*"
        }
    ]
}

enter image description here

John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
Esben von Buchwald
  • 2,772
  • 1
  • 29
  • 37
  • 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.) – John Rotenstein Jan 10 '22 at 23:32
  • That didn't make any change, unfortunately :( I also tried adding `redshift-serverless.amazonaws.com` to the Trusted Entities, with no luck. – Esben von Buchwald Jan 11 '22 at 22:44
  • Do you happen to have an AWS Support subscription? They'd be able to assist. – John Rotenstein Jan 11 '22 at 22:53
  • @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 – Alleo Feb 09 '22 at 17:46
  • I don't think you need to create a role anymore for serverless right ? – Tomas G. May 29 '22 at 11:47
  • Facing a similar issue. Were you able to figure out a solution? – m01010011 Jul 07 '23 at 09:51

1 Answers1

0

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.

Karim
  • 1