0

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 !

Pawan Rawat
  • 495
  • 1
  • 7
  • 25
  • was this csv file uploaded to s3 by the same aws account who owns the S3 bucket or it was uploaded from somewhere else? – aek May 25 '23 at 09:16
  • yes , uploaded the fille manually using the same user.... I am accessing the bucket with aws key id and secret of a role which has access to s3 bucket via above policy @aek – Pawan Rawat May 25 '23 at 09:20
  • Can you update your question with the full COPY INTO command you are running (obviously obfuscate any sensitive information). I assume you are running the COPY INTO using the same user/role you are using to run the LIST command? – NickW May 25 '23 at 10:25
  • @NickW i have updated the details you asked. I am using the same role for listing the stage and loading the file. – Pawan Rawat May 26 '23 at 04:50
  • Can you query the data in the stage i.e. run something like: SELECT t.$1 FROM @my_ext_stage_2 t; ? Also, you access policy doesn't match either of the examples in the Snowflake policy (https://docs.snowflake.com/en/user-guide/data-load-s3-config-storage-integration) so that may be the issue – NickW May 26 '23 at 09:24

2 Answers2

1

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.

Sergiu
  • 4,039
  • 1
  • 13
  • 21
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.

Pawan Rawat
  • 495
  • 1
  • 7
  • 25