CMD Solutions’ Todd Hunter describes a simplified process for configuring secure access between two different components.

Problem statement

The documentation provided by Snowflake to enable external stage creation and authentication with AWS requires going back and forth between Snowflake and the IAM console (see Configuring Secure Access to AWS S3).

To minimise the amount of moving parts and copying and pasting when configuring secure access between two different components, the process is able to be reduced to creating the storage integration and then using CloudFormation to create the IAM roles granting access.

In order to do this I have removed the clunky back and forwards between IAM and Snowflake and instead reduced it to creating the integration with a single CREATE INTEGRATION step for Snowflake and a parameterised CloudFormation template to create the roles.

The CloudFormation creates an IAM role which restricted to the minimum actions required for Snowflake to read files and optionally add or delete files from specific buckets.

By using a consistent naming scheme between the buckets and roles the process is simplified and able to be made consistent and repeatable via resource creation using CloudFormation.

Within this example we will assume that the s3 bucket name storing the data to be loaded into s3 will be named snowflake-integration-demo

The following uses Snowflake’s recommended best practices of using a storage integration, as a way to avoid supplying IAM credentials when creating stages or loading data.

Prerequisites

  1. snowflake-integration-demo encrypted with AWS-KMS encryption with a key alias, for this example we are using snowflake-s3-key
  2. Permissions within AWS to create an IAM role
  3. Permissions within Snowflake to create STORAGE INTEGRATIONS and STAGEs

Step 1: Create snowflake external stage

The key pattern is to keep names consistent between steps.

The STORAGE_ROLE_ARN and STORAGE_ALLOWED_LOCATIONS are both set with values which will be created in the future via CloudFormation. They do not need to be created prior to creating the integration state.

Within Snowflake, the first step is to create the Integration stage. Snowflake Integrations are objects that avoid adding AWS credentials through each stage location added.

As part of this the STORAGE_AWS_ROLE_ARN needs to be created, this is created by updating the account id for the account containing the bucket and the bucket name.

The form of this is arn:aws:iam::${account_id}:role/snowflake_s3_access_role_${bucket_name}

Naming the role this way will make it consistent with the role created via CloudFormation.

The SQL command to do so is:

SQL USE SCHEMA DEMO.PUBLIC; CREATE STORAGE INTEGRATION S3_INTEGRATION TYPE = EXTERNAL_STAGE STORAGE_PROVIDER = S3 ENABLED = TRUE STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake_s3_access_role_snowflake-integration-demo' STORAGE_ALLOWED_LOCATIONS = ('s3://snowflake-integration-demo/');

Once the storage integration has been created then you need to retrieve the AWS_EXTERNAL_ID and SNOWFLAKE_IAM_USER from the bucket.

This can be retrieved from:

SQL
DESCRIBE INTEGRATION S3_INTEGRATION;

|--------------------|--------------------------------|---------------|---------------------------------------------------------------------------------------------|--------------------|
| parent_property    | property                       | property_type | property_value                                                                              | property_default   |
|--------------------|--------------------------------|---------------|---------------------------------------------------------------------------------------------|--------------------|
| ...                |                                |               |                                                                                             |                    |
| STAGE_CREDENTIALS  | AWS_ROLE                       | String        | arn:aws:iam::123456789012:role/snowflake_s3_access_role_snowflake-integration-demo          |                    |
| STAGE_CREDENTIALS  | AWS_EXTERNAL_ID                | String        | EV17150_SFCRole=4_Fw462aerYwordn9Q2dHfVs6qHFo=                                              |                    |
| STAGE_CREDENTIALS  | SNOWFLAKE_IAM_USER             | String        | arn:aws:iam::567890123456:user/zmz4-s-auss1160                                              |                    |
|--------------------|--------------------------------|---------------|-------------------------------------------------------------------------------------------s--|--------------------|

 

These values are required to be added to the IAM role in order to allow Snowflake access to the S3 bucket.

Once the stage integration has been created, this can then be used to create an external stage referring to the s3 bucket from which files will be loaded.

This contains the alias of the KMS key that is required to decrypt the files stored within S3.

SQL
CREATE OR REPLACE STAGE s3_stage_integration
  STORAGE_INTEGRATION=S3_INTEGRATION
  URL='s3://snowflake-integration-demo/'
  FILE_FORMAT = STAFF_FORMAT
  ENCRYPTION = ( TYPE= 'AWS_SSE_KMS'
                 KMS_KEY_ID = 'snowflake-s3-key' );

Once this has been created, the appropriate role will need to be created within IAM for Snowflake to assume so that it can copy the data from S3.

The following CloudFormation will need to be run to create the appropriate roles and policies for Snowflake to be able to
access the files within S3 and to decrypt them.

This CloudFormation requires 4 parameters in order to complete.

BucketName The name of the S3 bucket to load files from

SnowflakeIAMUser The IAM username supplied by Snowflake which is used to assume the role within the account.

This is the value given by SNOWFLAKE_IAM_USER from the DESCRIBE INTEGRATION command

SnowflakeExternalId External ID supplied by Snowflake, this is the value give by AWS_EXTERNAL_ID
from the DESCRIBE INTEGRATION command

KMSKeyARN This is the full ARN of the KMS key used to encrypt the files on S3 (note, this is not the alias of the key)

yaml AWSTemplateFormatVersion: '2010-09-09' Description: AWS CloudFormation Template for creating snowflake roles and access policy for Snowflake Stages. Parameters: BucketName: Type: String Description: Comma seperated list of bucket names to grant Snowflake access too SnowflakeIAMUser: Type: String Description: Stage credential SNOWFLAKE_IAM_USER Default: '' SnowflakeExternalId: Type: String Description: Stage credential AWS_EXTERNAL_ID Default: 123abc456 KMSKeyARN: Type: String Description: KMS Key Alias for decription of S3 files Conditions: HasSnowflakeIamUser: !Not - !Equals - !Ref 'SnowflakeIAMUser' - '' Resources: # Create a role to allow Snowflake to access S3. SnowflakeUserRole: Type: AWS::IAM::Role Properties: RoleName: !Join - '' - - snowflake_s3_access_role_ - !Ref 'BucketName' AssumeRolePolicyDocument: # Policy Document to allow snowflake to assume the role Version: '2012-10-17' Statement: - Effect: Allow Principal: AWS: - !If - HasSnowflakeIamUser - !Ref 'SnowflakeIAMUser' - !Ref 'AWS::AccountId' Action: sts:AssumeRole Condition: StringLike: sts:ExternalId: !Ref 'SnowflakeExternalId' Policies: # Policy to grant Snowflake permissions. - PolicyName: !Join - '' - - snowflake_policy_ - !Ref 'BucketName' PolicyDocument: Version: '2012-10-17' Statement: - Effect: Allow Action: # PutObject required if exporting data from Snowflake to S3 - s3:PutObject # GetObject required to copy files form S3 into Snowflake - s3:GetObject - s3:GetObjectVersion # if PURGE=TRUE within COPY command, Snowflake will delete the files after the data has been successfully loaded. - s3:DeleteObject - s3:DeleteObjectVersion Resource: !Join - '' - - 'arn:aws:s3:::' - !Ref 'BucketName' - /* - Effect: Allow Action: s3:ListBucket Resource: !Join - '' - - 'arn:aws:s3:::' - !Ref 'BucketName' - Effect: Allow # kms:Decrypt is required to allow snowflake to decrypt encrypted files from S3. Action: kms:Decrypt Resource: !Ref KMSKeyARN Outputs: SnowflakeARN: Value: !GetAtt 'SnowflakeUserRole.Arn' Description: ARN of user role

This CloudFormation includes the s3:PutObject and s3:DeleteObject permissions. These are required if you want to allow Snowflake to create files within the bucket when it exports data or to delete files once they have been successfully loaded into Snowflake.

If you do not plan to unload files from snowflake or purge files once they have been loaded, these permissions can be removed.

Once the CloudFormation role has been created the permissions should be in place in order to load data into snowflake.

sql COPY INTO "STAFF" from @"S3_STAGE_INTEGRATION"\

At the moment the CloudFormation supports only a single s3 bucket source, while the Snowflake storage integration can be passed a list of buckets, this is difficult to automate with CloudFormation. In this case they list of buckets within the Policy will need to be created. The above process simplifies the creation of Snowflake integrations into AWS by applying consistent names and managing IAM rules via CloudFormation.

Once the storage integration has been created then you need to retrieve the AWS_EXTERNAL_ID and SNOWFLAKE_IAM_USER from the bucket.

This can be retrieved from:

SQL
DESCRIBE INTEGRATION S3_INTEGRATION;

|--------------------|--------------------------------|---------------|---------------------------------------------------------------------------------------------|--------------------|
| parent_property    | property                       | property_type | property_value                                                                              | property_default   |
|--------------------|--------------------------------|---------------|---------------------------------------------------------------------------------------------|--------------------|
| ...                |                                |               |                                                                                             |                    |
| STAGE_CREDENTIALS  | AWS_ROLE                       | String        | arn:aws:iam::123456789012:role/snowflake_s3_access_role_snowflake-integration-demo          |                    |
| STAGE_CREDENTIALS  | AWS_EXTERNAL_ID                | String        | EV17150_SFCRole=4_Fw462aerYwordn9Q2dHfVs6qHFo=                                              |                    |
| STAGE_CREDENTIALS  | SNOWFLAKE_IAM_USER             | String        | arn:aws:iam::567890123456:user/zmz4-s-auss1160                                              |                    |
|--------------------|--------------------------------|---------------|-------------------------------------------------------------------------------------------s--|--------------------|

 

These values are required to be added to the IAM role in order to allow Snowflake access to the S3 bucket.

Once the stage integration has been created, this can then be used to create an external stage referring to the s3 bucket from which files will be loaded.

This contains the alias of the KMS key that is required to decrypt the files stored within S3.

SQL
CREATE OR REPLACE STAGE s3_stage_integration
  STORAGE_INTEGRATION=S3_INTEGRATION
  URL='s3://snowflake-integration-demo/'
  FILE_FORMAT = STAFF_FORMAT
  ENCRYPTION = ( TYPE= 'AWS_SSE_KMS'
                 KMS_KEY_ID = 'snowflake-s3-key' );

Once this has been created, the appropriate role will need to be created within IAM for Snowflake to assume so that it can copy the data from S3.

The following CloudFormation will need to be run to create the appropriate roles and policies for Snowflake to be able to
access the files within S3 and to decrypt them.

This CloudFormation requires 4 parameters in order to complete.

BucketName The name of the S3 bucket to load files from

SnowflakeIAMUser The IAM username supplied by Snowflake which is used to assume the role within the account.

This is the value given by SNOWFLAKE_IAM_USER from the DESCRIBE INTEGRATION command

SnowflakeExternalId External ID supplied by Snowflake, this is the value give by AWS_EXTERNAL_ID
from the DESCRIBE INTEGRATION command

KMSKeyARN This is the full ARN of the KMS key used to encrypt the files on S3 (note, this is not the alias of the key)

yaml AWSTemplateFormatVersion: '2010-09-09' Description: AWS CloudFormation Template for creating snowflake roles and access policy for Snowflake Stages. Parameters: BucketName: Type: String Description: Comma seperated list of bucket names to grant Snowflake access too SnowflakeIAMUser: Type: String Description: Stage credential SNOWFLAKE_IAM_USER Default: '' SnowflakeExternalId: Type: String Description: Stage credential AWS_EXTERNAL_ID Default: 123abc456 KMSKeyARN: Type: String Description: KMS Key Alias for decription of S3 files Conditions: HasSnowflakeIamUser: !Not - !Equals - !Ref 'SnowflakeIAMUser' - '' Resources: # Create a role to allow Snowflake to access S3. SnowflakeUserRole: Type: AWS::IAM::Role Properties: RoleName: !Join - '' - - snowflake_s3_access_role_ - !Ref 'BucketName' AssumeRolePolicyDocument: # Policy Document to allow snowflake to assume the role Version: '2012-10-17' Statement: - Effect: Allow Principal: AWS: - !If - HasSnowflakeIamUser - !Ref 'SnowflakeIAMUser' - !Ref 'AWS::AccountId' Action: sts:AssumeRole Condition: StringLike: sts:ExternalId: !Ref 'SnowflakeExternalId' Policies: # Policy to grant Snowflake permissions. - PolicyName: !Join - '' - - snowflake_policy_ - !Ref 'BucketName' PolicyDocument: Version: '2012-10-17' Statement: - Effect: Allow Action: # PutObject required if exporting data from Snowflake to S3 - s3:PutObject # GetObject required to copy files form S3 into Snowflake - s3:GetObject - s3:GetObjectVersion # if PURGE=TRUE within COPY command, Snowflake will delete the files after the data has been successfully loaded. - s3:DeleteObject - s3:DeleteObjectVersion Resource: !Join - '' - - 'arn:aws:s3:::' - !Ref 'BucketName' - /* - Effect: Allow Action: s3:ListBucket Resource: !Join - '' - - 'arn:aws:s3:::' - !Ref 'BucketName' - Effect: Allow # kms:Decrypt is required to allow snowflake to decrypt encrypted files from S3. Action: kms:Decrypt Resource: !Ref KMSKeyARN Outputs: SnowflakeARN: Value: !GetAtt 'SnowflakeUserRole.Arn' Description: ARN of user role

This CloudFormation includes the s3:PutObject and s3:DeleteObject permissions. These are required if you want to allow Snowflake to create files within the bucket when it exports data or to delete files once they have been successfully loaded into Snowflake.

If you do not plan to unload files from snowflake or purge files once they have been loaded, these permissions can be removed.

Once the CloudFormation role has been created the permissions should be in place in order to load data into snowflake.

sql COPY INTO "STAFF" from @"S3_STAGE_INTEGRATION"\

At the moment the CloudFormation supports only a single s3 bucket source, while the Snowflake storage integration can be passed a list of buckets, this is difficult to automate with CloudFormation. In this case they list of buckets within the Policy will need to be created. The above process simplifies the creation of Snowflake integrations into AWS by applying consistent names and managing IAM rules via CloudFormation.