CODEWITHSUNDEEP
amazon-web-servicesamazon-s3encryptionsnowflake-cloud-data-platformsnowpipe
Pawan Rawat
Pawan Rawat

Reputation: 525

Failed to access remote file: access denied. Please check your credentials in loading data into snowflake from aws s3 bucket file - SSE S3 encrypted

I am trying to load a simple CSV file from s3 to snowflake using aws external stage. I am able to list the files using list @stage_name command but get the access denied error while loading the file via copy into sql. I checked the encryption which is default SSE S3 for the file and as per snowflake documentation snowflake_document no additional encryption setting is required for SSE S3 type of encryption. Below is the command copy command i am using

list @my_ext_stage_2;

enter image description here

copy into emp_raw from @my_ext_stage_2/emp/emp.csv
on_error='ABORT_STATEMENT';

enter image description here

desc stage my_ext_stage_2;

enter image description here

my access policy in aws is below

{
"Version": "2012-10-17",
"Statement": [
    {
        "Sid": "ListObjectsInBucket",
        "Effect": "Allow",
        "Action": [
            "s3:ListBucket"
        ],
        "Resource": [
            "arn:aws:s3:::my-bucket-name"
        ]
    },
    {
        "Sid": "AllObjectActions",
        "Effect": "Allow",
        "Action": "s3:*Object",
        "Resource": [
            "arn:aws:s3:::my-bucket-name/*"
        ]
    }
]
}

Thanks for helping !

Upvotes: 1

Views: 2529

Answers (2)

Pawan Rawat
Pawan Rawat

Reputation: 525

as expected the issues was with the policy permissions only. Some other permissions than *object and listBucket were required.

{
"Version": "2012-10-17",
"Statement": [
    {
        "Sid": "VisualEditor0",
        "Effect": "Allow",
        "Action": "s3:ListBucket",
        "Resource": "arn:aws:s3:::sf-inbounds"
    },
    {
        "Sid": "VisualEditor1",
        "Effect": "Allow",
        "Action": [
            "s3:PutObject",
            "s3:GetObjectAcl",
            "s3:GetObject",
            "s3:DeleteObject",
            "s3:GetBucketAcl",
            "s3:GetBucketLocation",
            "s3:GetBucketPolicy",
            "s3:GetObjectVersion"
        ],
        "Resource": [
            "arn:aws:s3:::sf-inbounds",
            "arn:aws:s3:::sf-inbounds/*"
        ]
    }
]

}

I was able to load data from a file into snowflake table and unload into s3 directory from table as well.

Upvotes: 1

Sergiu
Sergiu

Reputation: 4578

Snowflake requires the following permissions on an S3 bucket and folder to be able to access files in the folder (and sub-folders):

s3:GetBucketLocation
s3:GetObject
s3:GetObjectVersion
s3:ListBucket

As part of your policy the s3:*Object action uses a wildcard as part of the action name. The AllObjectActions statement allows the GetObject, DeleteObject, PutObject, and any other Amazon S3 action that ends with the word "Object". This means for example that s3:GetBucketLocation is not allowed, therefore an example of a read-only policy should look like this example:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
              "s3:GetObject",
              "s3:GetObjectVersion"
            ],
            "Resource": "arn:aws:s3:::<bucket>/<prefix>/*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::<bucket>",
            "Condition": {
                "StringLike": {
                    "s3:prefix": [
                        "<prefix>/*"
                    ]
                }
            }
        }
    ]
}

For more information have a look here.

Upvotes: 1

Related Questions