skip to Main Content

I have 1 AWS account. I have 1 RedShift cluster in that account. There exists an AWS S3 bucket external to this account. I have an access key and a secret key. I am able to list the files in the bucket in the AWS CLI to verify that the key works. What I do not know how to do is supply the access key and the secret key to RedShift when using the ‘Load Data’ dialogue. I get an access denied error raised.

It seems to me that I will need to create an IAM role that can connect to the bucket using those values and use that role in the dialogue rather than the default role I created. Is this correct, are there alternative approaches?

There are similar questions on this topic, they do not however address my specific scenario.

2

Answers


  1. Chosen as BEST ANSWER

    I can access the file using the COPY command and specifying the keys.

    COPY XXXXX
    FROM 'XXXXX'
    CREDENTIALS 'aws_access_key_id=XXXXX;aws_secret_access_key=XXXXX' 
    DELIMITER ','
    DATEFORMAT 'YYYY-MM-DD'
    REGION AS 'eu-west-1'
    IGNOREHEADER 1
    ;
    

  2. You are correct. The Redshift cluster can ‘assume’ the IAM Role and it will be used to access the S3 bucket.

    Therefore, you should:

    • Create an IAM Role in the same AWS Account as the Redshift cluster
    • Add permissions to the IAM Role to access the S3 bucket in the other account
    • Within the other AWS Account, add a Bucket Policy on the bucket, granting permission for the above IAM Role to access the bucket
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search