Configuring Secure Access to AWS S3

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

An AWS administrator in your organization can limit access to your S3 bucket (and the objects contained in the bucket) to Snowflake. This security restriction grants access to your S3 bucket to traffic from your Snowflake virtual private cloud (VPC) while blocking requests that originate from outside the VPC. The process involves creating a bucket policy that restricts access to a specific VPC; in this case, the Snowflake VPC.

This security feature requires that your S3 bucket is located in the same AWS region as your Snowflake account.

To whitelist the Snowflake VPC ID:

  1. Contact Snowflake Support to obtain the Snowflake VPC ID for the AWS region in which your account is deployed.

  2. Whitelist the Snowflake VPC ID by creating an Amazon S3 policy for a specific VPC.

  3. Provide an AWS IAM role to Snowflake to access the whitelisted Amazon S3 bucket instead of the AWS key and secret.

For additional help regarding this configuration process or any of the other AWS configuration steps, please contact your organization’s AWS administrator.

The following options for configuring secure access to a private/protected S3 bucket are supported:

Option 1

Configure a storage integration object to delegate authentication responsibility for external cloud storage to a Snowflake identity and access management (IAM) entity.

Note

We highly recommend this option, which avoids the need to supply AWS IAM credentials when creating stages or loading data.

Option 2

Configure an AWS 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.

Note that implementing this feature requires a named external stage. Support for accessing an S3 bucket URL directly in a COPY statement is not supported.

Option 3

Configure an AWS 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 a Snowflake Storage Integration

This topic describes how to use storage integrations to allow Snowflake to read data from and write data to a AWS S3 bucket referenced in an external (i.e. S3) stage. Integrations are named, first-class Snowflake objects that avoid the need for passing explicit cloud provider credentials such as secret keys or access tokens. Integration objects store an AWS identity and access management (IAM) user ID. An administrator in your organization grants the integration IAM user permissions in the AWS account.

An integration can also list buckets (and optional paths) that limit the locations users can specify when creating external stages that use the integration.

Note

Completing the instructions in this topic requires permissions in AWS to create and manage IAM policies and roles. If you are not an AWS administrator, ask your AWS administrator to perform these tasks.

The following diagram shows the integration flow for a S3 stage:

AWS S3 Stage Integration Flow
  1. An external (i.e. S3) stage references a storage integration object in its definition.

  2. Snowflake automatically associates the storage integration with a S3 IAM user created for your account. Snowflake creates a single IAM user that is referenced by all S3 storage integrations in your Snowflake account.

  3. An AWS administrator in your organization grants permissions to the IAM user to access the bucket referenced in the stage definition. Note that many external stage objects can reference different buckets and paths and use the same storage integration for authentication.

When a user loads or unloads data from or to a stage, Snowflake verifies the permissions granted to the IAM user on the bucket before allowing or denying access.

In this Section:

Step 1: Configure Access Permissions for the S3 Bucket

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 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 region where your account is located, and choose Activate if the status is Inactive.

  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, e.g.:

    "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. Click Create policy.

    Create Policy button in Review Policy page

Step 2: Create the AWS IAM Role

In the AWS Management Console, create an AWS IAM role to grant 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 temporarily. Later, you will modify the trusted relationship and grant access to Snowflake.

  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. An external ID is required to grant access to your AWS resources (i.e. S3) to a third party (i.e. Snowflake).

  8. Click the Next button.

  9. Locate the policy you created in Step 1: Configure Access Permissions for the S3 Bucket (in this topic), 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 integration that references this role.

    IAM Role

Step 3: Create a Cloud Storage Integration in Snowflake

Create a storage integration using the CREATE STORAGE INTEGRATION command. A storage integration is a Snowflake object that stores a generated identity and access management (IAM) user for your S3 cloud storage, along with an optional set of allowed or blocked storage locations (i.e. buckets). Cloud provider administrators in your organization grant permissions on the storage locations to the generated user. This option allows users to avoid supplying credentials when creating stages or loading data.

Note

Only account administrators (users with the ACCOUNTADMIN role) or a role with the global CREATE INTEGRATION privilege can execute this SQL command.

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = '<iam_role>'
  STORAGE_ALLOWED_LOCATIONS = ('s3://<bucket>/<path>/', 's3://<bucket>/<path>/')
  [ STORAGE_BLOCKED_LOCATIONS = ('s3://<bucket>/<path>/', 's3://<bucket>/<path>/') ]

Where:

  • integration_name is the name of the new integration.

  • iam_role is the Amazon Resource Name (ARN) of the role you created in Step 2: Create the AWS IAM Role (in this topic).

  • bucket is the name of a S3 bucket that stores your data files (e.g. mybucket). The STORAGE_ALLOWED_LOCATIONS and STORAGE_BLOCKED_LOCATIONS parameters restrict or block access to these buckets, respectively, when stages that reference this integration are created or modified.

  • path is an optional path that can be used to provide granular control over objects in the bucket.

The following example creates an integration that explicitly limits external stages that use the integration to reference either of two buckets and paths. In a later step, we will create an external stage that references one of these buckets and paths.

Additional external stages that also use this integration can reference the allowed buckets and paths:

CREATE STORAGE INTEGRATION s3_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = S3
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::001234567890:role/myrole'
  STORAGE_ALLOWED_LOCATIONS = ('s3://mybucket1/mypath1/', 's3://mybucket2/mypath2/')
  STORAGE_BLOCKED_LOCATIONS = ('s3://mybucket1/mypath1/sensitivedata/', 's3://mybucket2/mypath2/sensitivedata/');

Step 4: Retrieve the AWS IAM User for your Snowflake Account

  1. Execute the DESCRIBE INTEGRATION command to retrieve the ARN for the AWS IAM user that was created automatically for your Snowflake account:

    DESC INTEGRATION <integration_name>;
    

    Where:

    For example:

    DESC INTEGRATION s3_int;
    
    +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
    | property                  | property_type | property_value                                                                 | property_default |
    +---------------------------+---------------+--------------------------------------------------------------------------------+------------------|
    | ENABLED                   | Boolean       | true                                                                           | false            |
    | STORAGE_ALLOWED_LOCATIONS | List          | s3://mybucket1/mypath1/,s3://mybucket2/mypath2/                                | []               |
    | STORAGE_BLOCKED_LOCATIONS | List          | s3://mybucket1/mypath1/sensitivedata/,s3://mybucket2/mypath2/sensitivedata/    | []               |
    | STORAGE_AWS_IAM_USER_ARN  | String        | arn:aws:iam::123456789001:user/abc1-b-self1234                                 |                  |
    | STORAGE_AWS_ROLE_ARN      | String        | arn:aws:iam::001234567890:role/myrole                                          |                  |
    | STORAGE_AWS_EXTERNAL_ID   | String        | MYACCOUNT_SFCRole=2_a123456/s0aBCDEfGHIJklmNoPq=                               |                  |
    +---------------------------+---------------+--------------------------------------------------------------------------------+------------------+
    
  2. Record the following values:

    Value

    Description

    STORAGE_AWS_IAM_USER_ARN

    The AWS IAM user created for your Snowflake account, arn:aws:iam::123456789001:user/abc1-b-self1234 in this example. We provision a single IAM user for your entire Snowflake account. All S3 storage integrations use that IAM user.

    STORAGE_AWS_EXTERNAL_ID

    The external ID that is needed to establish a trust relationship.

    You will provide these values in the next section.

Step 5: Grant the IAM User Permissions to Access Bucket Objects

The following step-by-step instructions describe how to configure IAM access permissions for Snowflake in your AWS Management Console so that you can use a 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 Roles from the left-hand navigation pane.

  4. Click on the role you created in Step 2: Create the AWS IAM Role (in this topic).

  5. Click on the Trust relationships tab.

  6. Click the Edit trust relationship button.

  7. Modify the policy document with the DESC STORAGE INTEGRATION output values you recorded in Step 4: Retrieve the AWS IAM User for your Snowflake Account (in this topic):

    Policy document for IAM role

    {
      "Version": "2012-10-17",
      "Statement": [
        {
          "Sid": "",
          "Effect": "Allow",
          "Principal": {
            "AWS": "<snowflake_user_arn>"
          },
          "Action": "sts:AssumeRole",
          "Condition": {
            "StringEquals": {
              "sts:ExternalId": "<snowflake_external_id>"
            }
          }
        }
      ]
    }
    

    Where:

    • snowflake_external_id is the STORAGE_AWS_EXTERNAL_ID value you recorded.

    • snowflake_user_arn is the STORAGE_AWS_IAM_USER_ARN value you recorded.

  8. Click the Update Trust Policy button. The changes are saved.

Step 6: Create an External Stage

Create an external (i.e. S3) stage that references the storage integration you created in Step 3: Create a Cloud Storage Integration in Snowflake (in this topic).

Note

Creating a stage that uses a storage integration requires a role that has the CREATE STAGE privilege for the schema as well as the USAGE privilege on the integration. For example:

GRANT CREATE STAGE ON SCHEMA public TO ROLE myrole;

GRANT USAGE ON INTEGRATION s3_int TO ROLE myrole;

Create the stage using the CREATE STAGE command.

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 mybucket1/path1, which are supported by the integration. The stage also references a named file format object called my_csv_format:

USE SCHEMA mydb.public;

CREATE STAGE my_s3_stage
  STORAGE_INTEGRATION = s3_int
  URL = 's3://bucket1/path1'
  FILE_FORMAT = my_csv_format;

Note

  • To load or unload data from or to a stage that uses an integration, a role must have the USAGE privilege on the stage. It is not necessary to have the USAGE privilege on the integration.

  • The STORAGE_INTEGRATION parameter is handled separately from other stage parameters, such as FILE_FORMAT. Support for these other parameters is the same regardless of the integration used to access your S3 bucket.

Option 2: 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: Configure 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 region where your account is located, and choose Activate if the status is Inactive.

  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: 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. 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: Configure S3 Bucket Access Permissions (in this topic), 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: 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.

    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');
    
  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: 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, 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: 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="
                    }
                }
            }
          ]
      }
      

      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 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 3: 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: Configure 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 region where your account is located, and choose Activate if the status is Inactive.

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

Step 3: Create an External (i.e S3) Stage

Create an external stage that references the AWS credentials you created.

Create the 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, 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 OR REPLACE STAGE my_S3_stage
  URL='s3://mybucket/load/files/'
  CREDENTIALS=(AWS_KEY_ID='1a2b3c' AWS_SECRET_KEY='4x5y6z')
  ENCRYPTION=(TYPE='AWS_SSE_KMS' KMS_KEY_ID = 'aws/key');

Next: AWS Data File Encryption