snowflake_storage_integration.ps1

$accountid="<your AWS account ID>"
$bucketname="<your stage bucket>"
$bucketarn="<your stage bucket arn>"
$kmskeyarn="<you kms key arn if applicable>"
$prefix="" # can be used to add a prefix for more granular access e.g. "<prefix>/"
$snowflake_policy_doc=@"
{
 "Version": "2012-10-17",
 "Statement": [
  {
   "Effect": "Allow",
   "Action": [
    "s3:GetObject",
    "s3:GetObjectVersion"
   ],
   "Resource": [
    "${bucketarn}/${prefix}*"
   ]
  },
  {
   "Effect": "Allow",
   "Action": [
    "s3:ListBucket",
    "s3:GetBucketLocation"
   ],
   "Resource": [
    "${bucketarn}"
   ],
   "Condition": {
    "StringLike": {
     "s3:prefix": [
      "${prefix}*"
     ]
    }
   }   
  },  
  {
   "Effect": "Allow",
   "Action": [
    "kms:Decrypt"
   ],
   "Resource": [
    "${kmskeyarn}"
   ]
  }  
 ]
}
"@
$snowflake_policy_doc | Out-File -Encoding "ascii" snowflake_policy_doc.json

$assume_role_policy_doc=@"
{
 "Version": "2012-10-17",
 "Statement": [
  {
   "Effect": "Allow",
   "Principal": {
    "AWS": "arn:aws:iam::${accountid}:root"
   },
   "Action": "sts:AssumeRole",
   "Condition": {
    "StringEquals": {
     "sts:ExternalId": "0000"
    }
   }
  }
 ]
}
"@
$assume_role_policy_doc | Out-File -Encoding "ascii" assume_role_policy_doc.json

# create snowflake access policy
$policyarn=$(aws iam create-policy `
 --policy-name snowflake-access-policy `
 --policy-document file://snowflake_policy_doc.json `
 --query 'Policy.Arn')

# create snowflake iam role - get arn
$rolearn=$(aws iam create-role `
 --role-name snowflake-access-role `
 --assume-role-policy-document file://assume_role_policy_doc.json `
 --query 'Role.Arn')

# attach policy to role
aws iam attach-role-policy `
--role-name snowflake-access-role `
--policy-arn $policyarn

# create storage integration
snowsql --query @"
CREATE STORAGE INTEGRATION staging_int 
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '${rolearn}'
  STORAGE_ALLOWED_LOCATIONS = ('${bucketname}/')
"@

# get external id
$descintresp=$(snowsql `
--query "DESC INTEGRATION staging_int" `
-o output_format=json -o timing=false -o friendly=false)

$descintobj = $descintresp | ConvertFrom-Json

$storage_aws_iam_user_arn = $descintobj | Where-Object {$_.property -in ('STORAGE_AWS_IAM_USER_ARN')} | Select-Object -ExpandProperty property_value

$storage_aws_external_id = $descintobj | Where-Object {$_.property -in ('STORAGE_AWS_EXTERNAL_ID')} | Select-Object -ExpandProperty property_value

# update snowflake access policy
$updated_assume_role_policy_doc=@"
{
 "Version": "2012-10-17",
 "Statement": [
  {
   "Effect": "Allow",
   "Principal": {
    "AWS": "${storage_aws_iam_user_arn}"
   },
   "Action": "sts:AssumeRole",
   "Condition": {
    "StringEquals": {
     "sts:ExternalId": "${storage_aws_external_id}"
    }
   }
  }
 ]
}
"@
$updated_assume_role_policy_doc | Out-File -Encoding "ascii" updated_assume_role_policy_doc.json

aws iam update-assume-role-policy `
--role-name snowflake-access-role `
--policy-document file://updated_assume_role_policy_doc.json