Option 2: Configuring an AWS IAM role to access Amazon S3 — Deprecated¶

Step 1: Configure S3 bucket access permissions¶

Step 2: Create an AWS IAM role¶

In the AWS Management Console, create an AWS IAM role that grants privileges on the S3 bucket containing your data files.

1. From the left-hand navigation pane in the Identity and Access Management (IAM) Dashboard, select Roles.

2. Select Create role.

3. Select AWS account as the trusted entity type.

4. Under An AWS account, select This account. In a later step, you modify the trusted relationship and grant access to Snowflake.

5. Select the Require external ID option. Enter a dummy ID such as 0000. Later, you will modify the trusted relationship and specify the external ID for your Snowflake stage.

6. Click the Next button.

7. Locate the policy you created in Step 1: Configure S3 Bucket Access Permissions (in this topic), and select this policy.

8. Click the Next button.

   

9. Enter a name and description for the role, and click the Create role button.

   You have now created an IAM policy for a bucket, created an IAM role, and attached the policy to the role.

10. Record the Role ARN value located on the role summary page. In the next step, you will create a Snowflake stage that references this role as the security credentials.

Step 3: Create an external stage¶

Create an external (i.e. S3) stage that references the AWS role you created.

1. Create an external stage using the CREATE STAGE command, or you can choose to alter an existing external stage and set the CREDENTIALS option.

   Note

   • Credentials are handled separately from other stage parameters such as ENCRYPTION and FILE_FORMAT. Support for these other parameters is the same regardless of the credentials used to access your external S3 bucket.

   • Append a forward slash (/) to the URL value to filter to the specified folder path. If the forward slash is omitted, all files and folders starting with the prefix for the specified path are included.

     Note that the forward slash is required to access and retrieve unstructured data files in the stage.

   For example, set mydb.public as the current database and schema for the user session, and then create a stage named my_S3_stage. In this example, the stage references the S3 bucket and path mybucket/load/files. Files in the S3 bucket are encrypted with server-side encryption (AWS_SSE_KMS):

   USE SCHEMA mydb.public;
   
   CREATE STAGE my_s3_stage
     URL='s3://mybucket/load/files'
     CREDENTIALS = (AWS_ROLE = 'arn:aws:iam::001234567890:role/mysnowflakerole')
     ENCRYPTION=(TYPE='AWS_SSE_KMS' KMS_KEY_ID = 'aws/key');
   

   Copy

2. Execute the DESCRIBE STAGE command to view the stage properties:

   DESC STAGE my_S3_stage;
   
   +--------------------+--------------------------------+---------------+----------------------------------------------------------------+------------------+
   | parent_property    | property                       | property_type | property_value                                                 | property_default |
   |--------------------+--------------------------------+---------------+----------------------------------------------------------------+------------------|
   ...
   | STAGE_CREDENTIALS  | AWS_ROLE                       | String        | arn:aws:iam::001234567890:role/mysnowflakerole                 |                  |
   | STAGE_CREDENTIALS  | AWS_EXTERNAL_ID                | String        | MYACCOUNT_SFCRole=2_jYfRf+gT0xSH7G2q0RAODp00Cqw=               |                  |
   | STAGE_CREDENTIALS  | SNOWFLAKE_IAM_USER             | String        | arn:aws:iam::123456789001:user/vj4g-a-abcd1234                 |                  |
   +--------------------+--------------------------------+---------------+----------------------------------------------------------------+------------------+
   

   Copy

3. Record the values for the SNOWFLAKE_IAM_USER and AWS_EXTERNAL_ID properties, where:

   SNOWFLAKE_IAM_USER:

   An AWS IAM user created for your Snowflake account. This user is the same for every external S3 stage created in your account.

   AWS_EXTERNAL_ID:

   A unique ID assigned to the specific stage. The ID has the following format:

   snowflakeAccount_SFCRole=snowflakeRoleId_randomId

   Note that the AWS_ROLE, AWS_EXTERNAL_ID, and SNOWFLAKE_IAM_USER values used in this example are for illustration purposes only.

   In the next step, you will configure your AWS IAM role to grant access to the Snowflake IAM user using the generated AWS external ID.

Step 4: Configure the AWS IAM role to allow access to the stage¶

In the AWS Management Console, configure the IAM role using the stage properties you recorded in Step 3: Create an External Stage (in this topic):

1. Log into the AWS Management Console.

2. From the home dashboard, search for and select IAM.

3. Choose Roles from the left-hand navigation pane, and click on the role you created in Step 2: Create an AWS IAM Role (in this topic).

4. Click the Trust relationships tab, and click the Edit trust relationship button.

5. In the Policy Document field, update the policy with the property values for the stage:

   • AWS: Enter the ARN for the SNOWFLAKE_IAM_USER stage property, i.e. arn:aws:iam::123456789001:user/vj4g-a-abcd1234 in this example.

   • sts:ExternalId: Enter the generated external ID, i.e. MYACCOUNT_SFCRole=2_jYfRf+gT0xSH7G2q0RAODp00Cqw= in this example.

     {
         "Version": "2012-10-17",
         "Statement": [
           {
               "Effect": "Allow",
               "Principal": {
                   "AWS": [
                       "arn:aws:iam::123456789001:user/vj4g-a-abcd1234"
                   ]
               },
               "Action": "sts:AssumeRole",
               "Condition": {
                   "StringEquals": {
                       "sts:ExternalId": "MYACCOUNT_SFCRole=2_jYfRf+gT0xSH7G2q0RAODp00Cqw="
                   }
               }
           }
         ]
     }
     

     Copy

     Note

     The above trust policy allows a single external stage in your Snowflake account to assume your IAM role. It is the most restrictive trust policy and is therefore the most secure.

     The permission to assume the IAM role is associated with the external ID. An external ID has the following format:

     snowflake_account_SFCRole=snowflake_role_id_random_id

     Where:

     • snowflake_account is the name assigned to your Snowflake account.

     • snowflake_role_id is an ID assigned to the Snowflake role that created the stage in Step 3: Create an External Stage (in this topic).

       In the current example, the snowflake_role_id value is 2. This ID is associated with a single role in your Snowflake account. The purpose of this ID is limited to the trust policies for external stages; as such, a mapping of Snowflake roles to IDs is not available. The role ID for a given role is only exposed in the AWS_EXTERNAL_ID value in the DESCRIBE STAGE output. As a best practice, restrict the ability to create external S3 stages to a single Snowflake role.

       Note that the role that creates a stage is not necessarily the same as the stage owner (i.e. the role that has the OWNERSHIP privilege on the stage). Ownership of the stage can be transferred to a different role later with no corresponding change required to the trust policy.

     For security reasons, if you create a new storage integration (or recreate an existing storage integration using the CREATE OR REPLACE STORAGE INTEGRATION syntax), the resulting integration has a different external ID and so it cannot assume the IAM role unless the trust policy is modified.

     If you require a trust policy with a less secure set of restrictions (i.e. a policy that supports all external stages in your account), replace random_id in the external ID with a wildcard character (*):

     snowflake_account_SFCRole=snowflake_role_id_*, e.g. MYACCOUNT_SFCRole=2_* in the current example.

     This form of the external ID allows any external S3 stage created by a user in your account with the same Snowflake role (i.e. SYSADMIN) to assume the IAM role, and in turn any S3 bucket the IAM role has access to. Note that if you implement this less secure type of trust policy, you must change the Condition from StringEquals to StringLike.

6. Click the Update Trust Policy button.

You have now completed the one-time setup to access your S3 bucket using an AWS role.

Next: AWS data file encryption