Securing Snowflake to AWS S3 data pipelines via integration grants

Question

Pulumi · Accepted Answer

To secure data pipelines between Snowflake and AWS S3, we'll create a Snowflake integration with AWS that allows secure access to S3 buckets from within Snowflake. This process typically involves creating a storage integration in Snowflake that defines the AWS IAM role Snowflake will assume when accessing S3. It also requires creating the necessary AWS IAM policies that grant this role access to the appropriate S3 resources.

In Pulumi, we accomplish this by defining the following resources:
- A Snowflake `StorageIntegration` to establish a relationship between Snowflake and the AWS services.
- A `Role` in Snowflake to which the integration grants will be given.
- An AWS `Role` which Snowflake can assume.
- An AWS `Policy` that grants the necessary permissions to Snowflake for S3 access.
- Granting the integration privileges using `IntegrationGrant`.

Below is a Pulumi program written in TypeScript to create these resources and set up the secure pipeline. As you are a novice, I will guide you through the program step by step.

```typescript
import * as pulumi from '@pulumi/pulumi';
import * as aws from '@pulumi/aws';
import * as snowflake from '@pulumi/snowflake';

// Create an AWS IAM role for Snowflake to assume
const snowflakeRole = new aws.iam.Role("SnowflakeS3AccessRole", {
    assumeRolePolicy: {
        Version: "2012-10-17",
        Statement: [{
            Effect: "Allow",
            Principal: { Service: "snowflake.amazonaws.com" },
            Action: "sts:AssumeRole",
        }],
    },
});

// Attach a policy to the role that allows access to the S3 bucket
const policy = new aws.iam.Policy("SnowflakeS3BucketAccessPolicy", {
    policy: {
        Version: "2012-10-17",
        Statement: [{
            Effect: "Allow",
            Action: [
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListBucket",
            ],
            Resource: [
                pulumi.interpolate`${bucket.arn}/*`, // ARN of the S3 bucket
                bucket.arn,
            ],
        }],
    },
});

// Attach the policy to the Snowflake AWS IAM role
const rolePolicyAttachment = new aws.iam.RolePolicyAttachment("SnowflakeS3AccessPolicyAttachment", {
    role: snowflakeRole.name,
    policyArn: policy.arn,
});

// Create a Snowflake Role that will utilize the Storage Integration
const snowflakeRoleResource = new snowflake.Role("MyDataPipelineRole", {
    name: "MY_DATA_PIPELINE_ROLE",
});

// Define the Snowflake Storage Integration
const myStorageIntegration = new snowflake.StorageIntegration("MyStorageIntegration", {
    name: "MY_S3_INTEGRATION",
    type: "EXTERNAL_STAGE", // Specify 'EXTERNAL_STAGE' for S3 integrations
    storageProvider: "S3",
    storageAwsRoleArn: snowflakeRole.arn,
    enabled: true,
});

// Grant integration privileges to the Snowflake role
const integrationGrant = new snowflake.IntegrationGrant("MyIntegrationGrant", {
    privilege: "USAGE",
    integrationName: myStorageIntegration.name,
    roles: [snowflakeRoleResource.name],
});

export const snowflakeIntegrationName = myStorageIntegration.name;
export const integrationGrantName = integrationGrant.name;
export const awsRoleName = snowflakeRole.name;
export const awsRoleArn = snowflakeRole.arn;
export const s3AccessPolicyArn = policy.arn;
```

In the above program, we begin by creating an AWS IAM Role that Snowflake will assume (`SnowflakeS3AccessRole`). We define an IAM policy (`SnowflakeS3BucketAccessPolicy`) that grants this role permission to access the S3 bucket. The policy's actions include `GetObject`, `PutObject`, and `ListBucket`, which are basic operations Snowflake might perform on an S3 bucket. We attach this policy to the IAM role.

Next, we create a Snowflake Role (`MyDataPipelineRole`) and a Snowflake Storage Integration (`MyStorageIntegration`) that specifies the AWS IAM Role ARN. This integration allows Snowflake to interface with S3 under the permissions granted by our AWS IAM Role.

Finally, we assign the integration grant (`MyIntegrationGrant`) USAGE privilege to the Snowflake role for the integration, enabling it to use the integration to interact with S3.

The `export` declarations at the end of the program make some of the important resource names and ARNs available to the Pulumi CLI once the stack has been deployed.

This program must be run within a Pulumi project where AWS and Snowflake providers are configured. Before running the Pulumi `up` command to deploy your infrastructure, you should replace the placeholder for your S3 bucket ARN with the ARN of the bucket you want Snowflake to access.

Remember to manage sensitive information such as AWS credentials securely. Pulumi's secret management system encrypts secret configuration values stored in the state so that they are secure and encrypted in the backend and in transit.