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

Note

You may encounter an assumeRole error when using the deprecated authentication method.

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.

In this Topic:

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:GetBucketLocation

  • s3:GetObject

  • s3:GetObjectVersion

  • s3:ListBucket

Note

The following additional permissions are required to perform additional SQL actions:

Permission

SQL Action

s3:PutObject

Unload files to the bucket.

s3:DeleteObject

Either automatically purge files from the stage after a successful load or execute REMOVE statements to manually remove files.

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",
                    "s3:GetBucketLocation"
                ],
                "Resource": "arn:aws:s3:::<bucket>",
                "Condition": {
                    "StringLike": {
                        "s3:prefix": [
                            "<prefix>/*"
                        ]
                    }
                }
            }
        ]
    }
    
    Copy

    Note

    Setting the "s3:prefix": condition to either ["*"] or ["<path>/*"] grants access to all prefixes in the specified bucket or path in the bucket, respectively.

    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:GetBucketLocation, 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",
                    "s3:GetBucketLocation"
                ],
                "Resource": "arn:aws:s3:::<bucket>",
                "Condition": {
                    "StringLike": {
                        "s3:prefix": [
                            "<prefix>/*"
                        ]
                    }
                }
            }
        ]
    }
    
    Copy
  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

Note

Snowflake caches the temporary credentials for a period that cannot exceed the 60 minute expiration time. If you revoke access from Snowflake, users might be able to list files and load data from the cloud storage location until the cache expires.

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