CODEWITHSUNDEEP
amazon-s3snowflake-cloud-data-platform
Eric Mamet
Eric Mamet

Reputation: 3691

permission problem snowflake reading from S3

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?

Upvotes: 2

Views: 3034

Answers (4)

Eric Mamet
Eric Mamet

Reputation: 3691

Thank you all for your useful comments.

The last thing that threw me out is that each time one recreates a Snowflake STORAGE INTEGRATION object (easy with "CREATE OR REPLACE" statement), the AWS External ID is re-generated, thus invalidating my AWS role trust relationship with the Snowflake AWS account.

Again, thanks a lot for your help. Helped me tremendously.

Upvotes: 0

nicor88
nicor88

Reputation: 1563

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.

Upvotes: 1

Mike Gohl
Mike Gohl

Reputation: 737

I've used option-2 in the "Configuring Secure Access to Amazon" document for AWS_SSE_KMS. If you don't follow the instructions exactly, it will not work.

https://docs.snowflake.com/en/user-guide/data-load-s3-config.html#option-2-configuring-an-aws-iam-role

The steps should be as follows: 1. Create Role with network policy in place 2. Create IAM User 3. Create KMS Key using user with designated role 4. Create stage command specifying credentials including AWS role and KMS key

Upvotes: 1

Gokhan Atil
Gokhan Atil

Reputation: 10189

Maybe some object permissions are missing. Could you check the following link?

https://docs.aws.amazon.com/IAM/latest/UserGuide/reference_policies_examples_s3_rw-bucket-console.html

Using s3*Object may help to solve the issue:

{
    "Effect": "Allow",
    "Action": "s3:*Object",
    "Resource": ["arn:aws:s3:::my_bucket/*"]
}

Upvotes: 1

Related Questions