2.3 Cloud Storage Integrations & IAM Trust Policies

2.3 Cloud Storage Integrations & IAM Trust Policies

In early cloud warehouse architectures, loading data from external cloud object storage (Amazon S3, Azure Blob / ADLS Gen2, Google Cloud Storage) required embedding explicit cloud credentials—such as AWS access keys, secret keys, or Azure SAS tokens—directly within SQL CREATE STAGE or COPY INTO commands. This legacy approach represented a severe security risk: credentials were often logged in plaintext in QUERY_HISTORY, lacked automatic rotation, and prevented unified auditing.

To establish enterprise-grade security, Snowflake utilizes Storage Integrations. A Storage Integration is a first-class, account-level Snowflake object that delegates identity verification and authorization directly to cloud hyperscaler Identity & Access Management (IAM) services.

+-----------------------------------------------------------------------------------------+
|                                 Snowflake Cloud Platform                                |
|                                                                                         |
|  +--------------------+         +-----------------------+         +------------------+  |
|  | Virtual Warehouse  | ------> | External Named Stage  | ------> |  STORAGE         |  |
|  | (Compute Engine)   |         | (URL = 's3://...')    |         |  INTEGRATION     |  |
|  +--------------------+         +-----------------------+         +------------------+  |
+-----------------------------------------------------------------------------│-----------+
                                                                              │ (Cloud Trust)
                                                                              ▼
+-----------------------------------------------------------------------------------------+
|                                Cloud Hyperscaler (AWS / Azure / GCP)                    |
|                                                                                         |
|  +------------------------+      Trust Relationship Handshake      +-----------------+  |
|  | Hyperscaler IAM Role / | <====================================> | Secure Cloud    |  |
|  | Service Principal      |       (External ID / Tenant Consent)   | Storage Bucket  |  |
|  +------------------------+                                        +-----------------+  |
+-----------------------------------------------------------------------------------------+

The AWS S3 Integration Architecture: The IAM Trust Handshake

Configuring an external stage pointing to Amazon S3 via a storage integration requires a strict, two-phase cryptographic handshake between Snowflake's underlying AWS account and the customer's AWS account. This prevents the classic Confused Deputy Problem in cloud multi-tenant architectures.

The Complete AWS S3 Configuration Handshake

-- Phase 1: Snowflake Admin creates the Storage Integration
USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE STORAGE INTEGRATION s3_customer_data_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'S3'
  ENABLED = TRUE
  STORAGE_AWS_ROLE_ARN = 'arn:aws:iam::123456789012:role/snowflake_s3_readwrite_role'
  STORAGE_ALLOWED_LOCATIONS = ('s3://mycompany-analytics-prod/raw/', 's3://mycompany-analytics-prod/stages/')
  STORAGE_BLOCKED_LOCATIONS = ('s3://mycompany-analytics-prod/raw/confidential/');

-- Phase 2: Inspect Snowflake-generated identity parameters
DESC INTEGRATION s3_customer_data_int;

Executing DESC INTEGRATION outputs two non-configurable, dynamically generated property values that the AWS administrator must capture:

1. STORAGE_AWS_IAM_USER_ARN: The Amazon Resource Name of Snowflake's internal AWS identity (e.g., arn:aws:iam::987654321098:user/abc1-b-user).
2. STORAGE_AWS_EXTERNAL_ID: A unique, account-and-integration-specific string generated by Snowflake (e.g., AB12345_SFCRole=2_a1b2c3d4...).

// Phase 3: Customer AWS Admin updates the AWS IAM Role Trust Relationship Policy
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "SnowflakeAssumeRolePolicy",
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::987654321098:user/abc1-b-user" 
      },
      "Action": "sts:AssumeRole",
      "Condition": {
        "StringEquals": {
          "sts:ExternalId": "AB12345_SFCRole=2_a1b2c3d4..."
        }
      }
    }
  ]
}

// Phase 4: AWS IAM Permissions Policy attached to snowflake_s3_readwrite_role
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": [
        "s3:GetObject",
        "s3:GetObjectVersion",
        "s3:ListBucket",
        "s3:GetBucketLocation"
      ],
      "Resource": [
        "arn:aws:s3:::mycompany-analytics-prod",
        "arn:aws:s3:::mycompany-analytics-prod/*"
      ]
    },
    {
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:DeleteObject",
        "s3:DeleteObjectVersion"
      ],
      "Resource": "arn:aws:s3:::mycompany-analytics-prod/stages/*"
    }
  ]
}

AWS KMS Encryption Configuration

If the S3 bucket is encrypted using AWS Key Management Service (AWS KMS) (Customer Managed Keys / CMK):

• The IAM role (STORAGE_AWS_ROLE_ARN) must have an attached policy granting kms:Decrypt, kms:GenerateDataKey, and kms:DescribeKey.
• The KMS Key Policy in AWS must explicitly grant permission to the target IAM role to perform cryptographic operations. Without this, queries against external stages return an access denied error during decryption.

Azure Blob & ADLS Gen2 Integration Architecture

Integrating Snowflake with Microsoft Azure Storage (Blob Storage or Azure Data Lake Storage Gen2) relies on Azure Active Directory (Microsoft Entra ID) Enterprise Applications and Service Principals.

Step-by-Step Azure Integration Workflow

-- Step 1: Create the Azure Storage Integration in Snowflake
USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE STORAGE INTEGRATION azure_adls_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'AZURE'
  ENABLED = TRUE
  AZURE_TENANT_ID = 'c1234567-89ab-cdef-0123-456789abcdef'
  STORAGE_ALLOWED_LOCATIONS = ('azure://myadlsaccount.blob.core.windows.net/telemetry/raw/');

-- Step 2: Retrieve the Consent URL and Application Name
DESC INTEGRATION azure_adls_int;

Executing DESC INTEGRATION on an Azure integration exposes:

• AZURE_CONSENT_URL: A dedicated Microsoft login URL containing a permissions consent request.
• AZURE_MULTI_TENANT_APP_NAME: The Snowflake client application identifier registered within Azure (e.g., SnowflakePACProd...).

Azure Active Directory Handshake Flow:

1. DESC INTEGRATION -> Capture AZURE_CONSENT_URL
2. An Azure administrator navigates to AZURE_CONSENT_URL in a web browser
3. Administrator clicks "Accept" to grant Snowflake's Multi-Tenant App permission in the Azure Tenant
4. Azure Admin opens Azure Portal -> Storage Account / ADLS Container -> Access Control (IAM)
5. Role Assignment: Assign AZURE_MULTI_TENANT_APP_NAME the "Storage Blob Data Contributor" role

Azure RBAC Roles: Reader vs Contributor

• Storage Blob Data Reader: Sufficient if the Snowflake stage is read-only (used exclusively for COPY INTO <table> ingestion).
• Storage Blob Data Contributor: Required when Snowflake must also write to the container (unloading with COPY INTO <location>, or purging loaded files).
• Snowpipe auto-ingest on Azure is configured separately: a notification integration of TYPE = QUEUE with NOTIFICATION_PROVIDER = AZURE_STORAGE_QUEUE reads Event Grid messages from a storage queue, and its Snowflake app needs the Storage Queue Data Contributor role on that queue.
• Exam Trap: Assigning Azure's built-in Reader or Contributor roles at the resource group level does NOT confer data-plane access. Snowflake requires the explicit Storage Blob Data... RBAC roles to read/write blobs.

Google Cloud Storage (GCS) Integration Architecture

Integrating Snowflake with Google Cloud Storage follows a streamlined model using Google Cloud IAM Service Accounts provisioned and managed by Snowflake.

-- Step 1: Create the GCS Storage Integration
USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE STORAGE INTEGRATION gcs_lake_int
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'GCS'
  ENABLED = TRUE
  STORAGE_ALLOWED_LOCATIONS = ('gcs://mygcp-lake-bucket/sensors/');

-- Step 2: Retrieve the Snowflake-generated GCP Service Account
DESC INTEGRATION gcs_lake_int;

Inspecting the integration reveals STORAGE_GCP_SERVICE_ACCOUNT (e.g., v7abcd0000-sf@prod3-us-central1.iam.gserviceaccount.com).

GCP Cloud IAM Configuration:
1. Open GCP Console -> Cloud Storage -> Buckets -> Select 'mygcp-lake-bucket'
2. Navigate to Permissions Tab -> Grant Access
3. New Principal: Paste the value from STORAGE_GCP_SERVICE_ACCOUNT
4. Grant the service account a role with the needed permissions. Snowflake's documentation recommends a custom IAM role:
   - Loading: storage.buckets.get, storage.objects.get, storage.objects.list
   - Unloading and purging: add storage.objects.create and storage.objects.delete

Secure Stage Creation & Location Bounding

Once a storage integration is configured and the cloud IAM trust handshake is established, database administrators create External Named Stages that reference the integration, completely abstracting cloud credentials from end users.

-- Creating an External Stage referencing a Storage Integration
USE ROLE SYSADMIN;

CREATE OR REPLACE STAGE analytics_db.raw.s3_sensor_stage
  STORAGE_INTEGRATION = s3_customer_data_int
  URL = 's3://mycompany-analytics-prod/raw/sensors/'
  FILE_FORMAT = (TYPE = 'PARQUET' COMPRESSION = 'SNAPPY');

-- Granting access to the stage to an Access Role
GRANT USAGE ON STAGE analytics_db.raw.s3_sensor_stage TO ROLE AR_ANALYTICS_INGEST;

Stage Path Validation: Allowed vs Blocked Locations

Snowflake strictly enforces URL containment rules between the stage definition and the parent storage integration:

1. Containment Rule: The URL parameter of any stage linking to the integration must match or be an exact subpath of at least one path defined in STORAGE_ALLOWED_LOCATIONS.
2. Exclusion Rule: If the stage URL matches any path or subpath defined in STORAGE_BLOCKED_LOCATIONS, stage creation or execution fails immediately with an authorization violation.