Running Terraform and Querying from Snowflake CLI and gosnowflake with Key Pair Authentication

2025-01-28 snowflake terraform

In addition to username/password authentication, Snowflake CLI and various client libraries also support key pair authentication.

The key linked to a user must be an RSA key of at least 2048 bits and ed25519 is currently not supported.

$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
$ mkdir -p ~/.snowflake
$ cp rsa_key.p8 ~/.snowflake/

You can register up to two public keys per user.

ALTER USER ***** SET RSA_PUBLIC_KEY='-----BEGIN PUBLIC KEY-----
MIIBI...'

Run Terraform.

terraform {
  required_providers {
    snowflake = {
      source = "Snowflake-Labs/snowflake"
    }
  }
}

provider "snowflake" {
  organization_name = "*****"
  account_name      = "*****"
  role              = "*****"
  authenticator     = "SNOWFLAKE_JWT"
}

resource "snowflake_warehouse" "warehouse" {
  name                = "WAREHOUSE"
  warehouse_size      = "XSMALL"
  auto_resume         = true
  initially_suspended = true
  auto_suspend        = 1
}

Parameters can also be passed through environment variables.

$ export SNOWFLAKE_USER="****"
$ export SNOWFLAKE_PRIVATE_KEY=$(cat ~/.snowflake/rsa_key.p8)
$ terraform apply
...
Apply complete! Resources: 1 added, 0 changed, 0 destroyed.

Objects are created with the role specified by the provider, but ACCOUNTADMIN is not recommended, except for creating or modifying users or roles, which requires SECURITYADMIN privileges. By setting an alias, you can write multiple providers and use different roles.

Create Snowflake roles with Terraform and grant users table access permissions - sambaiz-net

provider "snowflake" {
  role = "*****"
  ...
}

provider "snowflake" {
  alias = "accountadmin"
  role  = "ACCOUNTADMIN"
  ...
}

resource "snowflake_role" "role" {
  provider = snowflake.accountadmin
  ...
}

Install the Snowflake CLI.

$ brew tap snowflakedb/snowflake-cli
$ brew update
$ brew install snowflake-cli
$ snow --version
Snowflake CLI version: 3.3.0

Add connection information to the configuration file ~/.snowflake/config.toml.

$ snow connection add \
  --connection-name test \
  --account ***** \
  --user ***** \
  --role ***** \
  --warehouse ***** \
  --database ***** \
  --schema ***** \
  --authenticator SNOWFLAKE_JWT \
  --private-key-file ~/.snowflake/rsa_key.p8 \
  --no-interactive
  
$ cat /Users/*****/.snowflake/config.toml
[cli.logs]
save_logs = true
path = "/Users/*****/.snowflake/logs"
level = "info"

[connections.test]
account = "*****"
user = "*****"
database = "*****"
warehouse = "*****"
role = "*****"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/Users/*****/.snowflake/rsa_key.p8"

Now you can execute queries.

$ snow sql --connection test -q "SELECT * FROM test_table"
SELECT * FROM test_table
+-----------+
| ID | DATA |
|----+------|
| 1  | aaaa |
| 2  | bbbb |
| 3  | cccc |
+-----------+

Query from Go’s Snowflake driver gosnowflake too.

package main

import (
	"context"
	"crypto/rsa"
	"crypto/x509"
	"database/sql"
	"encoding/pem"
	"fmt"
	"os"

	"github.com/snowflakedb/gosnowflake"
)

func main() {
	account := os.Getenv("SNOWFLAKE_ACCOUNT") // https://docs.snowflake.com/en/user-guide/admin-account-identifier
	user := os.Getenv("SNOWFLAKE_USER")
	warehouse := os.Getenv("SNOWFLAKE_WAREHOUSE")
	keyPath := os.Getenv("PRIVATE_KEY_PATH")

	ctx := context.Background()
	db, err := newSnowflake(ctx, account, user, warehouse, keyPath)
	if err != nil {
		fmt.Printf("failed to init snowflake: %v\n", err)
		os.Exit(1)
	}
	defer db.Close()

	var version string
	if err := db.QueryRowContext(ctx, "SELECT current_version()").Scan(&version); err != nil {
		fmt.Printf("query error: %v\n", err)
		os.Exit(1)
	}

	fmt.Printf("Snowflake version: %s\n", version)
}

func newSnowflake(ctx context.Context, account, user, warehouse, keyPath string) (*sql.DB, error) {
	pemBytes, err := os.ReadFile(keyPath)
	if err != nil {
		return nil, fmt.Errorf("read key file: %w", err)
	}
	block, _ := pem.Decode(pemBytes)
	if block == nil {
		return nil, fmt.Errorf("failed to decode PEM block")
	}
	pkRaw, err := x509.ParsePKCS8PrivateKey(block.Bytes)
	if err != nil {
		return nil, fmt.Errorf("parse PKCS#8 private key: %w", err)
	}
	rsaPk, ok := pkRaw.(*rsa.PrivateKey)
	if !ok {
		return nil, fmt.Errorf("not an RSA private key: %T", pkRaw)
	}

	dsn, err := gosnowflake.DSN(&gosnowflake.Config{
		Account:       account,
		User:          user,
		Warehouse:     warehouse,
		Authenticator: gosnowflake.AuthTypeJwt,
		PrivateKey:    rsaPk,
	})
	if err != nil {
		return nil, fmt.Errorf("build DSN: %w", err)
	}

	db, err := sql.Open("snowflake", dsn)
	if err != nil {
		return nil, fmt.Errorf("open DB: %w", err)
	}
	if err := db.PingContext(ctx); err != nil {
		db.Close()
		return nil, fmt.Errorf("ping DB: %w", err)
	}
	return db, nil
}

Reference

Snowflake CLIをインストールしてキーペア認証でSnowflakeへ接続してみた | DevelopersIO

TerraformでSnowflakeのリソースを作成してみた | DevelopersIO