Aws Iam Policy to Allow Read and Write to S3 Bucket

Option two: Configuring an AWS IAM Role to Access Amazon S3 — Deprecated¶

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

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

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

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

Annotation

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

In this Topic:

• Footstep 1: Configure S3 Saucepan Admission Permissions

• Pace 2: Create an AWS IAM Role

• Footstep 3: Create an External Phase

• Step 4: Configure the AWS IAM Office to Allow Admission to the Phase

Step ane: Configure S3 Saucepan Admission Permissions¶

AWS Admission Command Requirements¶

Snowflake requires the following permissions on an S3 bucket and folder to exist able to admission files in the folder (and whatsoever 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 saucepan.
s3:DeleteObject	Either automatically purge files from the phase after a successful load or execute REMOVE statements to manually remove files.

As a all-time practice, Snowflake recommends creating an IAM policy for Snowflake access to the S3 saucepan. Y'all can and then attach the policy to the role and use the security credentials generated by AWS for the function to access files in the bucket.

Creating an IAM Policy¶

The following step-by-footstep instructions depict how to configure access permissions for Snowflake in your AWS Direction 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):

   

3. Choose Account settings from the left-mitt navigation pane.

4. Expand the Security Token Service Regions listing, observe the AWS region respective 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:

   

7. Click the JSON tab.

8. Add together a policy certificate that will allow Snowflake to access the S3 bucket and binder.

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

   Copy and paste the text into the policy editor:

   Annotation

   Make sure to supplant bucket and prefix with your bodily bucket name and folder path prefix.

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

   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.

   Annotation that AWS policies support a variety of dissimilar security use cases.

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

   Alternative policy: Load from a read-simply S3 saucepan

                                           {                    "Version":                    "2012-10-17",                    "Argument":                    [                    {                    "Consequence":                    "Allow"                    ,                    "Action":                    [                    "s3:GetObject",                    "s3:GetObjectVersion"                    ]                    ,                    "Resource":                    "arn:aws:s3:::                    <bucket>                    /                    <prefix>                    /*"                    }                    ,                    {                    "Effect":                    "Permit"                    ,                    "Action":                    [                    "s3:ListBucket",                    "s3:GetBucketLocation"                    ]                    ,                    "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.

    

Footstep 2: Create an AWS IAM Office¶

In the AWS Direction 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 & Admission Management (IAM):

   

3. Choose Roles from the left-hand navigation pane.

4. Click the Create function push button.

   

5. Select Another AWS account every bit the trusted entity type.

6. In the Account ID field, enter your ain AWS account ID. Later, y'all will modify the trusted human relationship and grant access to Snowflake. An external ID is required to grant access to your AWS resources (i.eastward. 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 on, you volition 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 i: Configure S3 Bucket Access Permissions (in this topic), and select this policy.

10. Click the Side by side button.

    

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

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

12. Record the Part ARN value located on the role summary folio. In the side by side step, y'all will create a Snowflake stage that references this role equally the security credentials.

    

Step 3: Create an External Stage¶

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

1. Create an external stage using the CREATE STAGE control, or you can choose to alter an existing external stage and fix 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.

   • Suspend a forward slash ( / ) to the URL value to filter to the specified folder path. If the forrad 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 call up unstructured data files in the stage.

   For example, gear up 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 saucepan 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                    =(                    Blazon                    =                    'AWS_SSE_KMS'                    KMS_KEY_ID                    =                    'aws/primal'                    );                  

2. Execute the Depict Stage command to view the stage properties:

                                           DESC                    Phase                    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 phase. The ID has the post-obit 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 pace, you lot will configure your AWS IAM role to grant access to the Snowflake IAM user using the generated AWS external ID.

Step iv: Configure the AWS IAM Role to Allow Access to the Stage¶

In the AWS Management Panel, configure the IAM role using the stage properties y'all recorded in Footstep 3: Create an External Stage (in this topic):

1. Log into the AWS Management Console.

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

   

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

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

5. In the Policy Document field, update the policy with the holding values for the phase:

   • 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-x-17",                        "Statement":                        [                        {                        "Upshot":                        "Permit"                        ,                        "Principal":                        {                        "AWS":                        [                        "arn:aws:iam::123456789001:user/vj4g-a-abcd1234"                        ]                        }                        ,                        "Action":                        "sts:AssumeRole",                        "Condition":                        {                        "StringEquals":                        {                        "sts:ExternalId":                        "MYACCOUNT_SFCRole=2_jYfRf+gT0xSH7G2q0RAODp00Cqw="                        }                        }                        }                        ]                        }                      

     Annotation

     The higher up trust policy allows a single external stage in your Snowflake business relationship to presume your IAM role. It is the well-nigh 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 Phase (in this topic).

       In the current example, the snowflake_role_id value is ii . 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 bachelor. The part ID for a given part is simply exposed in the AWS_EXTERNAL_ID value in the DESCRIBE STAGE output. As a all-time exercise, restrict the power 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). Buying of the stage can be transferred to a different part later with no respective 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 presume the IAM role unless the trust policy is modified.

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

     snowflake_account _SFCRole= snowflake_role_id _* , due east.g. MYACCOUNT_SFCRole=2_* in the current case.

     This course of the external ID allows whatsoever external S3 stage created past a user in your account with the aforementioned Snowflake role (i.e. SYSADMIN) to assume the IAM role, and in turn whatever S3 bucket the IAM role has access to. Notation that if you implement this less secure type of trust policy, you must change the Status from StringEquals to StringLike .

6. Click the Update Trust Policy push button.

You have at present completed the one-time setup to access your S3 saucepan using an AWS role.

Next: AWS Data File Encryption

harrisgothys.blogspot.com

Source: https://docs.snowflake.com/en/user-guide/data-load-s3-config-aws-iam-role.html

Share this post