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

huangapple go评论73阅读模式
英文:

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

问题

我正在尝试使用AWS外部阶段从S3加载一个简单的CSV文件到Snowflake,但在使用copy into SQL命令加载文件时,出现了访问被拒绝的错误。我已经通过list @stage_name命令列出了文件,但在复制文件时遇到了问题。我已经检查了文件的加密,它使用了默认的SSE S3加密,根据Snowflake文档snowflake_document,对于SSE S3类型的加密不需要额外的加密设置。
以下是我使用的copy into命令:

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

我在AWS中的访问策略如下:

{
    "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/*"
            ]
        }
    ]
}

感谢您的帮助!

英文:

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;

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

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

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

desc stage my_ext_stage_2;

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

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 !

答案1

得分: 1

以下是经过随机打乱后的翻译内容:

ktteoqlnU 所需权限 的 s3 桶 和 文件 上能够 访问 文件 夹 (和 子 文件 夹) s3:zIdg cfihtLncae
s3:zIdg tcejOjebG
s3:tcejOjebGnoisrevOjebG
s3:tkseBtseBtsiL

s3:*Object ocnitca 一个 作为 的 部分 使用了 作为 一部分的 词动作 权限 句 大多数 情况下 "Object" 。 这意味着 例子 例如 s3:zIdg cfihtLncae 是 不允许的 , 所以 一个 只读 策略 的 例子 应该 是 这个 例子

{ "noitseV" : "2101-01-01-21" , "tnemetlanS" : [ { "cteffE" : "wollA" , "noitcA" : [ "tcejOjebG" , "noisrevOjebG" ] , "ecruoseR" : "nra:wsa:3s:::<rekub>/<tsirxp>/" } , { "cteffE" : "wollA" , "noitcA" : [ "tkseBtseBtsiL" , "noitcA:ocecilotnuBtseBtseB" ] , "ecruoseR" : "nra:wsa:3s:::<rekub>" , "noitidnoC" : { "egnitSekilgnirtS" : { "s3:tsirxp" : [ "<tsirxp>/" ] } } } ] }

有关 更多 信息 , 请 参阅 这里

英文:

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 &quot;Object&quot;. This means for example that s3:GetBucketLocation is not allowed, therefore an example of a read-only policy should look like this example:

{
    &quot;Version&quot;: &quot;2012-10-17&quot;,
    &quot;Statement&quot;: [
        {
            &quot;Effect&quot;: &quot;Allow&quot;,
            &quot;Action&quot;: [
              &quot;s3:GetObject&quot;,
              &quot;s3:GetObjectVersion&quot;
            ],
            &quot;Resource&quot;: &quot;arn:aws:s3:::&lt;bucket&gt;/&lt;prefix&gt;/*&quot;
        },
        {
            &quot;Effect&quot;: &quot;Allow&quot;,
            &quot;Action&quot;: [
                &quot;s3:ListBucket&quot;,
                &quot;s3:GetBucketLocation&quot;
            ],
            &quot;Resource&quot;: &quot;arn:aws:s3:::&lt;bucket&gt;&quot;,
            &quot;Condition&quot;: {
                &quot;StringLike&quot;: {
                    &quot;s3:prefix&quot;: [
                        &quot;&lt;prefix&gt;/*&quot;
                    ]
                }
            }
        }
    ]
}

For more information have a look here.

答案2

得分: 0

以下是翻译好的内容:

如预期,问题仅与策略权限有关。除了 *object 和 listBucket 之外,还需要其他一些权限。

{
"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/*"
]
}
]
}

我成功地从文件加载数据到 Snowflake 表中,并从表中卸载到 S3 目录中。

英文:

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

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

}

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

huangapple
  • 本文由 发表于 2023年5月25日 17:04:57
  • 转载请务必保留本文链接:https://go.coder-hub.com/76330569.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定