英文:
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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。





评论