Question 175847 in Amazon Web Sevices Question posted in
The official Amazon Web Services documentation can be found here.

Can credentials be checked to load data from AWS S3 bucket file w/SSE S3 encryption into Snowflake? #accessdenied

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 !

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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.


  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search