Amazon Redshift COPY always return S3ServiceException:Access Denied,Status 403

Question

I'm really struggling with how to do data transfer from Amazon S3 bucket to Redshift with COPY command.

So far, I created an IAM User and 'AmazonS3ReadOnlyAccess' policy is assigned. But when I call COPY command likes following, Access Denied Error is always returned.

copy my_table from 's3://s3.ap-northeast-2.amazonaws.com/mybucket/myobject' credentials 'aws_access_key_id=<...>;aws_secret_access_key=<...>' REGION'ap-northeast-2' delimiter '|';

Error:

Amazon Invalid operation: S3ServiceException:Access Denied,Status 403,Error AccessDenied,Rid EB18FDE35E1E0CAB,ExtRid ,CanRetry 1
Details: -----------------------------------------------
error: S3ServiceException:Access Denied,Status 403,Error AccessDenied,Rid EB18FDE35E1E0CAB,ExtRid ,CanRetry 1 code: 8001 context: Listing bucket=s3.ap-northeast-2.amazonaws.com prefix=mybucket/myobject query: 1311463 location: s3_utility.cpp:542 process: padbmaster [pid=4527]
-----------------------------------------------;

Is there anyone can give me some clues or advice?

Thanks a lot!

moertel moertel · Accepted Answer · 2016-08-03T10:50:42

Remove the endpoint s3.ap-northeast-2.amazonaws.com from the S3 path:

COPY my_table
FROM 's3://mybucket/myobject'
CREDENTIALS ''
REGION 'ap-northeast-2'
DELIMITER '|'
;

(See the examples in the documentation.) While the Access Denied error is definitely misleading, the returned message gives some hint as to what went wrong:

bucket=s3.ap-northeast-2.amazonaws.com
prefix=mybucket/myobject

We'd expect to see bucket=mybucket and prefix=myobject, though.

Amazon Redshift COPY always return S3ServiceException:Access Denied,Status 403

3 Answers