title: S3 to Redshift : Copy with Access Denied
body: <p>We previously used to copy files from s3 to Redshift using the COPY command every day, from a bucket with no specific policy.</p>
  
  <pre><code>COPY schema.table_staging     
  FROM 's3://our-bucket/X/YYYY/MM/DD/'     
  CREDENTIALS 'aws_access_key_id=xxxxxx;aws_secret_access_key=xxxxxx'     
  CSV     
  GZIP     
  DELIMITER AS '|'     
  TIMEFORMAT 'YYYY-MM-DD HH24:MI:SS';  
  </code></pre>
  
  <p>As we needed to improve the security of our S3 bucket, we added a policy to authorize connections either from our VPC (the one we use for our Redshift cluster) or specific IP address.</p>
  
  <pre><code>{
  "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"
                  ]
              }
          }
      }
  ]
  }
  </code></pre>
  
  <p>This policy works well for accessing files from EC2, EMR or our specific address using AWS CLI or the boto Python library.</p>
  
  <p>Here is the error we have on Redshift :</p>
  
  <pre><code>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]
  -----------------------------------------------
  </code></pre>
  
  <p>Many thanks in advance if you can help us on this,</p>
  
  <p>Damien</p>
  
  <p>ps : this question is quite similar to this one : <a href="https://stackoverflow.com/questions/36398734/copying-data-from-s3-to-redshift-access-denied/40523899?noredirect=1#comment68288222_40523899">Copying data from S3 to Redshift - Access denied</a></p>