Configuring IAM Credentials to Access an External Stage

To read data from and write to an S3 bucket, the security and access management policies on the bucket must allow Snowflake to access the bucket.

Important

Snowflake Security Best Practices requires Customers to whitelist the Snowflake VPC ID to allow for external stage access. After whitelisting the Snowflake VPC ID, Snowflake can copy data directly from the Customer AWS S3 bucket to Snowflake.

After obtaining the Snowflake VPC ID from Snowflake Support, whitelist the Snowflake VPC ID using the procedure found in the AWS Documentation. Scroll down and use this procedure: To create an interface endpoint to an endpoint service.

The following options for configuring AWS Identity & Access Management (IAM) credentials are supported:

  • Option 1. Configure an IAM role with the required policies and permissions to access your external S3 bucket. This approach allows individual users to avoid providing and managing security credentials and access keys. Option 1 is preferable to Option 2.

    Note

    • Support for accessing external stages using IAM AWS roles is currently in open preview. Also, the feature is not available to Snowflake accounts on the Microsoft Azure cloud platform.
    • Implementing this feature requires a named external stage. Support for accessing an S3 bucket URL directly in a COPY statement is not supported.
  • Option 2. Configure an IAM user with the required permissions to access your S3 bucket. This one-time setup involves establishing access permissions on a bucket and associating the required permissions with an IAM user. You can then access an external (i.e. S3) stage that points to the bucket with the AWS key and secret key.

This topic describes how to perform the required tasks in S3.

Note

Completing the instructions in this topic requires administrative access to AWS. If you are not an AWS administrator, ask your AWS administrator to perform these tasks.

In this Topic:

Option 1: Configuring an AWS IAM Role

This section describes how to configure an S3 bucket, IAM role, and policies for Snowflake to access an external stage in a secure manner on behalf of one or more individual users in your Snowflake account.

As a best practice, limit S3 bucket access to a specific IAM role with the minimum required permissions. The IAM role is created in your AWS account along with the permissions to access your S3 bucket and the trust policy to allow Snowflake to assume the IAM role.

Trust policies allowing IAM user access to S3 bucket

1: An AWS IAM user created for your Snowflake account is associated with an IAM role you configure via a trust relationship.

2: The role is granted limited access to an S3 bucket through IAM policies you configure.

Note

Completing the instructions in this topic requires administrative access to AWS. If you are not an AWS administrator, ask your AWS administrator to perform these tasks.

Step 1: Configuring S3 Bucket Access Permissions

AWS Access Control Requirements

Snowflake requires the following permissions on an S3 bucket and folder to be able to access files in the folder (and any sub-folders):

  • s3:GetObject
  • s3:GetObjectVersion
  • s3:ListBucket

Note

The additional s3:PutObject and s3:DeleteObject permissions are required only if you plan to unload files to the bucket or automatically purge the files after loading them into a table.

As a best practice, Snowflake recommends creating an IAM policy for Snowflake access to the S3 bucket. You can then attach the policy to the role and use the security credentials generated by AWS for the role to access files in the bucket.

Creating an IAM Policy

The following step-by-step instructions describe how to configure access permissions for Snowflake in your AWS Management Console so that you can use an S3 bucket to load and unload data:

  1. Log into the AWS Management Console.

  2. From the home dashboard, choose Identity & Access Management (IAM):

    Identity & Access Management in AWS Management Console
  3. Choose Account settings from the left-hand navigation pane.

  4. Expand the Security Token Service Regions list, find the AWS region corresponding to the Snowflake Region where your account is located, and choose Activate if the status is Deactive.

  5. Choose Policies from the left-hand navigation pane.

  6. Click Create Policy:

    Create Policy button on Policies page
  7. Click the JSON tab.

  8. Add a policy document that will allow Snowflake to access the S3 bucket and folder.

    The following policy (in JSON format) provides Snowflake with the required permissions to load or unload data using a single bucket and folder path. You can also purge data files using the PURGE copy option.

    Copy and paste the text into the policy editor:

    Note

    Make sure to replace bucket and prefix with your actual bucket name and folder path prefix.

    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                  "s3:PutObject",
                  "s3:GetObject",
                  "s3:GetObjectVersion",
                  "s3:DeleteObject",
                  "s3:DeleteObjectVersion"
                ],
                "Resource": "arn:aws:s3:::<bucket>/<prefix>/*"
            },
            {
                "Effect": "Allow",
                "Action": "s3:ListBucket",
                "Resource": "arn:aws:s3:::<bucket>",
                "Condition": {
                    "StringLike": {
                        "s3:prefix": [
                            "<prefix>/*"
                        ]
                    }
                }
            }
        ]
    }
    

    Important

    Setting the "s3:prefix": condition to ["*"] grants access to all prefixes in the specified bucket. If more than 1000 objects exist in the bucket, you could encounter the following error: Access Denied (Status Code: 403; Error Code: AccessDenied).

    To avoid the error, remove the condition from the IAM policy:

    "Condition": {
          "StringLike": {
              "s3:prefix": [
                  "*"
              ]
          }
      }
    

    The policy still grants access to the files in the bucket, but S3 does not return an error if more than 1000 objects exist in the bucket.

    Note that AWS policies support a variety of different security use cases.

    The following policy provides Snowflake with the required permissions to load data from a single read-only bucket and folder path. The policy includes the s3:GetObject, s3:GetObjectVersion, and s3:ListBucket permissions:

    Alternative policy: Load from a read-only S3 bucket

    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                  "s3:GetObject",
                  "s3:GetObjectVersion"
                ],
                "Resource": "arn:aws:s3:::<bucket>/<prefix>/*"
            },
            {
                "Effect": "Allow",
                "Action": "s3:ListBucket",
                "Resource": "arn:aws:s3:::<bucket>",
                "Condition": {
                    "StringLike": {
                        "s3:prefix": [
                            "<prefix>/*"
                        ]
                    }
                }
            }
        ]
    }
    
  9. Click Review policy.

  10. Enter the policy name (e.g. snowflake_access) and an optional description. Then, click Create policy to create the policy.

    Create Policy button in Review Policy page

Step 2: Creating 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. Log into the AWS Management Console.

  2. From the home dashboard, choose Identity & Access Management (IAM):

    Identity & Access Management in AWS Management Console
  3. Choose Roles from the left-hand navigation pane.

  4. Click the Create role button.

    Select Trusted Entity Page in AWS Management Console
  5. Select Another AWS account as the trusted entity type.

  6. In the Account ID field, enter your own AWS account ID. Later, you will modify the trusted relationship and grant access to Snowflake. An external ID is required to grant access to your AWS resources (i.e. S3) to a third party (i.e. Snowflake in this case) later in these instructions.

  7. 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.

  8. Click the Next button.

  9. Locate the policy you created in Step 1: Configuring S3 Bucket Access Permissions, and select this policy.

  10. Click the Next button.

    Review Page in AWS Management Console
  11. 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.

  12. 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.

    IAM Role

Step 3: Creating 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.

    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, files in the S3 bucket are encrypted with server-side encryption (AWS_SSE_KMS):

    USE SCHEMA mydb.public;
    
    CREATE OR REPLACE 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');
    
  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                 |                  |
    +--------------------+--------------------------------+---------------+----------------------------------------------------------------+------------------+
    
  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: Configuring 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: Creating an External Stage:

  1. Log into the AWS Management Console.

  2. From the home dashboard, choose Identity & Access Management (IAM):

    Identity & Access Management in AWS Management Console
  3. Choose Roles from the left-hand navigation pane, and click on the role you created in Step 2: Creating an AWS IAM Role.

  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="
                    }
                }
            }
          ]
      }
      

      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: Creating an External Stage. 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 stage (or recreate an existing stage using the CREATE OR REPLACE STAGE syntax), the resulting stage will have 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.

Option 2: Configuring AWS IAM User Credentials

This section describes how to configure a security policy for an S3 bucket and access credentials for a specific IAM user to access an external stage in a secure manner.

Step 1: Configuring an S3 Bucket Access Policy

AWS Access Control Requirements

Snowflake requires the following permissions on an S3 bucket and folder to be able to access files in the folder (and any sub-folders):

  • s3:GetObject
  • s3:GetObjectVersion
  • s3:ListBucket

Note

The additional s3:PutObject and s3:DeleteObject permissions are required only if you plan to unload files to the bucket or automatically purge the files after loading them into a table.

As a best practice, Snowflake recommends creating an IAM policy and user for Snowflake access to the S3 bucket. You can then attach the policy to the user and use the security credentials generated by AWS for the user to access files in the bucket.

Creating an IAM Policy

The following step-by-step instructions describe how to configure access permissions for Snowflake in your AWS Management Console so that you can use an S3 bucket to load and unload data:

  1. Log into the AWS Management Console.

  2. From the home dashboard, choose Identity & Access Management (IAM):

    Identity & Access Management in AWS Management Console
  3. Choose Account settings from the left-hand navigation pane.

  4. Expand the Security Token Service Regions list, find the AWS region corresponding to the Snowflake Region where your account is located, and choose Activate if the status is Deactive.

  5. Choose Policies from the left-hand navigation pane.

  6. Click Create Policy:

    Create Policy button on Policies page
  7. Click the JSON tab.

  8. Add the policy document that will allow Snowflake to access the S3 bucket and folder.

    The following policy (in JSON format) provides Snowflake with the required access permissions for the specified bucket and folder path. You can copy and paste the text into the policy editor:

    Note

    Make sure to replace bucket_name and prefix with your actual bucket name and folder path prefix.

    {
        "Version": "2012-10-17",
        "Statement": [
            {
                "Effect": "Allow",
                "Action": [
                  "s3:PutObject",
                  "s3:GetObject",
                  "s3:GetObjectVersion",
                  "s3:DeleteObject",
                  "s3:DeleteObjectVersion"
                ],
                "Resource": "arn:aws:s3:::<bucket_name>/<prefix>/*"
            },
            {
                "Effect": "Allow",
                "Action": "s3:ListBucket",
                "Resource": "arn:aws:s3:::<bucket_name>",
                "Condition": {
                    "StringLike": {
                        "s3:prefix": [
                            "<prefix>/*"
                        ]
                    }
                }
            }
        ]
    }
    

Important

Setting the "s3:prefix": condition to ["*"] grants access to all prefixes in the specified bucket. If more than 1000 objects exist in the bucket, you could encounter the following error: Access Denied (Status Code: 403; Error Code: AccessDenied).

To avoid the error, remove the condition from the IAM policy:

"Condition": {
      "StringLike": {
          "s3:prefix": [
              "*"
          ]
      }
  }

The policy still grants access to the files in the bucket, but S3 does not return an error if more than 1000 objects exist in the bucket.

  1. Click Review policy.

  2. Enter the policy name (e.g. snowflake_access) and an optional description. Then, click Create policy to create the policy.

    Create Policy button in Review Policy page

Step 2: Creating an AWS IAM User

  1. Choose Users from the left-hand navigation pane, then click Add user.

  2. On the Add user page, enter a new user name (e.g. snowflake1). Select Programmatic access as the access type, then click Next:

    Add user page
  3. Click Attach existing policies directly, and select the policy you created earlier. Then click Next:

    Set permissions page
  4. Review the user details, then click Create user.

    Review user details page
  5. Record the access credentials. The easiest way to record them is to click Download Credentials to write them to a file (e.g. credentials.csv)

    Attach policy on the user details page

    Attention

    Once you leave this page, the Secret Access Key will no longer be available anywhere in the AWS console. If you lose the key, you must generate a new set of credentials for the user.

You have now:

  • Created an IAM policy for a bucket.
  • Created an IAM user and generated access credentials for the user.
  • Attached the policy to the user.

With the AWS key and secret key for the S3 bucket, you have the credentials necessary to access your S3 bucket in Snowflake using an external stage.

Next: AWS Data File Encryption