amazon web services - S3 to Redshift : Copy with Access Denied -

we used copy files s3 redshift using copy command every day, bucket no specific policy.

copy schema.table_staging      's3://our-bucket/x/yyyy/mm/dd/'      credentials 'aws_access_key_id=xxxxxx;aws_secret_access_key=xxxxxx'      csv      gzip      delimiter '|'      timeformat 'yyyy-mm-dd hh24:mi:ss';   

as needed improve security of our s3 bucket, added policy authorize connections either our vpc (the 1 use our redshift cluster) or specific ip address.

{ "version": "2012-10-17", "id": "s3policyid1", "statement": [     {         "sid": "denyallexcept",         "effect": "deny",         "principal": "*",         "action": "s3:*",         "resource": [             "arn:aws:s3:::our-bucket/*",             "arn:aws:s3:::our-bucket"         ],         "condition": {             "stringnotequalsifexists": {                 "aws:sourcevpc": "vpc-123456789"             },             "notipaddressifexists": {                 "aws:sourceip": [                     "12.35.56.78/32"                 ]             }         }     } ] } 

this policy works accessing files ec2, emr or our specific address using aws cli or boto python library.

here error have on redshift :

error: s3serviceexception:access denied,status 403,error accessdenied,rid xxxxxx,canretry 1 détail :  ----------------------------------------------- error:  s3serviceexception:access denied,status 403,error accessdenied,rid xxxxxx,canretry 1 code:      8001 context:   listing bucket=our-bucket prefix=x/yyyy/mm/dd/ query:     1587954 location:  s3_utility.cpp:552 process:   padbmaster [pid=21214] ----------------------------------------------- 

many in advance if can on this,

damien

ps : question quite similar 1 : copying data s3 redshift - access denied

you need use 'enhanced vpc routing' feature of redshift. documentation here:

1. when use amazon redshift enhanced vpc routing, amazon redshift forces copy , unload traffic between cluster , data repositories through amazon vpc.

2. if enhanced vpc routing not enabled, amazon redshift routes traffic through internet, including traffic other services within aws network.

3. for traffic amazon s3 bucket in same region cluster, can create vpc endpoint direct traffic directly bucket.