Snowflake × Terraform × GitHub Actions で Workload Identity Federation を構築してみた

snowflake-terraform-sample % tree
.
├── .github
│   └── workflows
│       └── dev-snowflake-terraform-cicd.yml
├── cfn
│   └── create_state_gha_resources.yml
├── environments
│   └── dev
│       ├── backend.tf
│       ├── main.tf
│       ├── variable.tf
│       └── versions.tf
└── modules
    └── data_engineer
        ├── main.tf
        ├── outputs.tf
        ├── variables.tf
        └── versions.tf

provider "snowflake" {
  role              = "SYSADMIN"
  organization_name = var.snowflake_organization_name
  account_name      = var.snowflake_account_name
  user              = "TERRAFORM_USER"
  authenticator     = "SNOWFLAKE_JWT"
  private_key       = var.snowflake_private_key  # 秘密鍵が必要
  warehouse         = "TERRAFORM_WH"
}

-- 1. Terraformサービスアカウント作成（WIF対応）
USE ROLE SECURITYADMIN;

CREATE USER TERRAFORM_BLOG_USER
    TYPE = SERVICE
    WORKLOAD_IDENTITY = (
      TYPE = AWS
      ARN = 'arn:aws:iam::<aws_account_id>:role/<your-iam-role-name>'
    )
    COMMENT = 'Terraform automation service account with Workload Identity Federation';

-- 2. 最小限のロール付与（3つのみ）
-- SYSADMIN: Database/Warehouse/Stage作成
-- ACCOUNTADMIN: Storage Integration作成
-- SECURITYADMIN: Grant操作
USE ROLE ACCOUNTADMIN;

GRANT ROLE SYSADMIN TO USER TERRAFORM_BLOG_USER;
GRANT ROLE ACCOUNTADMIN TO USER TERRAFORM_BLOG_USER;
GRANT ROLE SECURITYADMIN TO USER TERRAFORM_BLOG_USER;

-- 3. 設定確認
SHOW USER WORKLOAD IDENTITY AUTHENTICATION METHODS FOR USER TERRAFORM_BLOG_USER;
DESC USER TERRAFORM_BLOG_USER;

-- 4. ウェアハウス作成
USE ROLE SYSADMIN;

CREATE OR REPLACE WAREHOUSE TERRAFORM_BLOG_WH
    WAREHOUSE_SIZE = XSMALL
    AUTO_RESUME = TRUE
    AUTO_SUSPEND = 60
    INITIALLY_SUSPENDED = TRUE
    STATEMENT_TIMEOUT_IN_SECONDS = 300
    COMMENT = 'For terraform automation only';

terraform {
  required_version = ">= 1.5.0"
  required_providers {
    snowflake = {
      source  = "snowflakedb/snowflake"
      version = "~> 2.11.0"
    }
    aws = {
      source  = "hashicorp/aws"
      version = "6.18.0"
    }
  }
}

# Snowflake provider with SYSADMIN role
provider "snowflake" {
  alias                      = "sysadmin"
  role                       = "SYSADMIN"
  organization_name          = var.snowflake_organization_name
  account_name               = var.snowflake_account_name
  user                       = "TERRAFORM_BLOG_USER"
  authenticator              = "WORKLOAD_IDENTITY"
  workload_identity_provider = "AWS"
  warehouse                  = "TERRAFORM_BLOG_WH"
  preview_features_enabled = [
    "snowflake_stage_resource"
  ]
}

# Snowflake provider with ACCOUNTADMIN role (for admin module)
provider "snowflake" {
  alias                      = "accountadmin"
  role                       = "ACCOUNTADMIN"
  organization_name          = var.snowflake_organization_name
  account_name               = var.snowflake_account_name
  user                       = "TERRAFORM_BLOG_USER"
  authenticator              = "WORKLOAD_IDENTITY"
  workload_identity_provider = "AWS"
  warehouse                  = "TERRAFORM_BLOG_WH"
  preview_features_enabled = [
    "snowflake_storage_integration_resource"
  ]
}

provider "aws" {
  region = "ap-northeast-1"
}

variable "snowflake_organization_name" {
  type        = string
  description = "Snowflake organization name"
}

variable "snowflake_account_name" {
  type        = string
  description = "Snowflake account name"
}

name: "Snowflake Terraform CI/CD - DEV"

on:
  push:
    branches:
      - develop
    paths:
      - "environments/dev/**"
      - "modules/**"
      - ".github/workflows/dev-snowflake-terraform-cicd.yml"
  pull_request:
    branches:
      - develop
    paths:
      - "environments/dev/**"
      - "modules/**"
      - ".github/workflows/dev-snowflake-terraform-cicd.yml"

jobs:
  plan-dev:
    runs-on: ubuntu-latest
    timeout-minutes: 30
    environment: dev

    env:
      # Terraform
      TF_VERSION: "1.11.0"
      # Snowflake - TF_VAR_プレフィックスでTerraform変数にマッピング
      TF_VAR_snowflake_organization_name: ${{ secrets.SNOWFLAKE_ORG_NAME }}
      TF_VAR_snowflake_account_name: ${{ secrets.SNOWFLAKE_ACCOUNT_NAME }}

    permissions:
      id-token: write # OIDCを利用する際に必須
      contents: read # actions/checkout のために必要
      pull-requests: write # PRコメント投稿用

    steps:
      - uses: actions/checkout@08c6903cd8c0fde910a37f88322edcfb5dd907a8 # v5.0.0

      - uses: hashicorp/setup-terraform@b9cd54a3c349d3f38e8881555d616ced269862dd # v3.1.2
        with:
          terraform_version: ${{ env.TF_VERSION }}

      - name: Set up AWS credentials
        uses: aws-actions/configure-aws-credentials@a03048d87541d1d9fcf2ecf528a4a65ba9bd7838 # v5.0.0
        with:
          role-to-assume: ${{ secrets.AWS_ROLE_ARN }}
          aws-region: ap-northeast-1
          audience: sts.amazonaws.com

      - name: Terraform format
        run: terraform fmt -check -recursive
        working-directory: environments/dev

      - name: Terraform Init
        run: terraform init -upgrade -no-color
        working-directory: environments/dev

      - name: Terraform validate
        run: terraform validate -no-color
        working-directory: environments/dev

      - name: Setup TFLint
        uses: terraform-linters/setup-tflint@acd1575d3c037258ce5b2dd01379dc49ce24c6b7 # v6.2.0
        with:
          tflint_version: v0.58.0

      - name: Run TFLint
        run: tflint --init && tflint -f compact --minimum-failure-severity=error
        working-directory: environments/dev

      - name: Run Trivy Security Scan
        uses: aquasecurity/trivy-action@b6643a29fecd7f34b3597bc6acb0a98b03d33ff8 # 0.33.1
        with:
          scan-type: "config"
          scan-ref: "environments/dev"
          format: "table"
          exit-code: 1
          severity: CRITICAL,HIGH
          ignore-unfixed: true

      - name: Terraform Plan
        id: plan
        if: github.event_name == 'pull_request'
        run: |
          terraform plan -no-color -out=tfplan.binary
          terraform show -no-color tfplan.binary > tfplan.txt
        working-directory: environments/dev

      - name: Post Plan to PR
        if: github.event_name == 'pull_request' && steps.plan.outcome == 'success'
        uses: actions/github-script@ed597411d8f924073f98dfc5c65a23a2325f34cd
        with:
          script: |
            const fs = require('fs');
            const plan = fs.readFileSync('environments/dev/tfplan.txt', 'utf8');

            github.rest.issues.createComment({
              issue_number: context.issue.number,
              owner: context.repo.owner,
              repo: context.repo.repo,
              body: `### Terraform Plan - Dev

            <details>
            <summary>Show Plan</summary>

            \`\`\`terraform
            ${plan.substring(0, 65000)}
            \`\`\`

            </details>`
            });

      - name: Terraform Apply
        if: github.ref == 'refs/heads/develop' && github.event_name == 'push'
        run: terraform apply -auto-approve
        working-directory: environments/dev

// 誤った実装例
resource "aws_iam_role" "snowflake_access" {
  assume_role_policy = jsonencode({
    Statement = [{
      Principal = {
        AWS = snowflake_storage_integration.s3_integ.storage_aws_iam_user_arn
      }
      Condition = {
        StringEquals = {
          # 直接アクセス（Provider 2.5.0以降ではエラー）
          "sts:ExternalId" = snowflake_storage_integration.s3_integ.storage_aws_external_id
        }
      }
    }]
  })
}

// 正しい実装例
resource "aws_iam_role" "snowflake_access" {
  name = var.aws_iam_role_name
  path = "/"

  # storage_aws_iam_user_arn: 直接アクセス可能
  # storage_aws_external_id: describe_output経由でアクセス（Provider 2.5.0以降の仕様）
  assume_role_policy = jsonencode({
    Version = "2012-10-17"
    Statement = [
      {
        Sid    = "TrustIamUserOfSnowflakePolicy"
        Effect = "Allow"
        Principal = {
          AWS = snowflake_storage_integration.s3_integ.storage_aws_iam_user_arn
        }
        Action = "sts:AssumeRole"
        Condition = {
          StringEquals = {
            # describe_output経由でアクセス
            "sts:ExternalId" = snowflake_storage_integration.s3_integ.describe_output[0].storage_aws_external_id[0].value
          }
        }
      }
    ]
  })

  tags = var.aws_tags
}

git add .
git commit -m "feat(dev): Add Workload Identity Federation authentication"
git push

USE ROLE ACCOUNTADMIN;

-- ログイン履歴でWIF認証を確認
SELECT
  event_timestamp,
  user_name,
  first_authentication_factor,  -- "WORKLOAD_IDENTITY"であることを確認
  is_success
FROM snowflake.account_usage.login_history
WHERE user_name = 'TERRAFORM_BLOG_USER'
  AND event_timestamp > DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY event_timestamp DESC
LIMIT 5;

USE ROLE ACCOUNTADMIN;

-- Terraform実行履歴を確認
SELECT
  start_time,
  user_name,
  role_name,
  query_text,
  execution_status
FROM snowflake.account_usage.query_history
WHERE user_name = 'TERRAFORM_BLOG_USER'
  AND start_time > DATEADD(hour, -24, CURRENT_TIMESTAMP())
ORDER BY start_time DESC
LIMIT 10;