permission problem snowflake reading from S3

Question

I am trying to read a simple csv file from S3 (encrypted) but keep running into various problems...

I created an IAM User (programmatic access only), put aside the access key id and secret access key.

I gave that user the policy below which I understand should give it read/write access to everything in my bucket

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
              "s3:PutObject",
              "s3:GetObject",
              "s3:GetObjectVersion",
              "s3:DeleteObject",
              "s3:DeleteObjectVersion"
            ],
            "Resource": [
              "arn:aws:s3:::my_bucket",
              "arn:aws:s3:::my_bucket/*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": "s3:ListBucket",
            "Resource": [
              "arn:aws:s3:::my_bucket"
            ],
            "Condition": {
            "StringLike": {
                "s3:prefix": [
                    "*"
                ]
            }
          }
        }
    ]
}

Created a stage

create or replace stage my_s3_stage
  url='s3://my_bucket/'
  credentials=(aws_key_id='...' aws_secret_key='...')
  encryption=(type='AWS_SSE_KMS' kms_key_id = 'f03...aee');

At that stage, I can list objects in the bucket/stage

list @my_s3_stage;

So far so good.

I then created a simple table

create or replace TABLE mytable (col1 String null, col2 string null, col3 string null);

But then I get stuck with an error message about permissions...

copy into mytable from @my_s3_stage pattern='.*.csv';

Failed to access remote file: access denied. Please check your credentials

Doesn't the fact that I could list the files show that my credentials were right?

Any idea what the real problem might be? Am I getting something wrong with encryption?

nicor88 nicor88 · Accepted Answer · 2020-05-14T13:37:58

As far as I can see there are missing permissions. Your IAM user needs to have permission to decrypt the Objects in S3, that's why when you list the content in the bucket works, but as far as you try to copy you get an error. Try with this policy for your user (I narrow down what you need to read from S3, that is all the List* and Get* permissions):

    {
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
              "kms:Decrypt",
              "kms:List*" 
            ],
            "Resource": [
              "*"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
              "s3:Get*",
              "s3:List*",
            ],
            "Resource": [
              "arn:aws:s3:::my_bucket",
              "arn:aws:s3:::my_bucket/*"
            ]
        }
    ]
 }

If this works, be sure to only add the kms key that you use to your policy.

Also, a best practice is to use an IAM role, instead of IAM user, to avoid to copy-paste keys around.

permission problem snowflake reading from S3

4 Answers