英文:
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;
copy into emp_raw from @my_ext_stage_2/emp/emp.csv
on_error='ABORT_STATEMENT';
desc stage my_ext_stage_2;
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 "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.
答案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.
{
"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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论