Snowflake MFA, KeyPair Auth

Snowflake에서 Multi-Factor Authentication (MFA)을 사용하면서도 snowflake.connector API를 통해 인증 없이 접근하고 싶어서 구축했습니다.

1. Snowflake MultiFactorAuthentication

1) Click My profile

2) Enroll Multi-factor authenticaion

• App Duo Mobile 설치 후 진행

2. KeyPair Authentication

python library에서 MFA 없이도 안전하게 Snowflake에 접근하기 위해 사용했습니다.

1) Create public key and private key

• Local terminal

  openssl genrsa -out rsa_key.pem 2048
  openssl rsa -in rsa_key.pem -pubout -out rsa_key.pub

2) Enroll pub key in Snowflake

• In Snowflake query

  $ ALTER USER your_user_name SET RSA_PUBLIC_KEY='MIIBIjANBg...';
  # '~~' value without 
  # -----BEGIN PUBLIC KEY----- && -----END PUBLIC KEY-----

3) Use private key in Python code

import snowflake.connector
from cryptography.hazmat.primitives import serialization

# Load private key
with open("rsa_key.pem", "rb") as key:
    p_key = serialization.load_pem_private_key(
        key.read(),
        password=None,
    )

# Set Snowflake connection
conn = snowflake.connector.connect(
    user='your_user_name',
    account='your_account',
    private_key=p_key,
    warehouse='your_warehouse',
    database='your_database',
    schema='your_schema'
)

# Confirm Connection
try:
    cursor = conn.cursor()
    cursor.execute("SELECT CURRENT_VERSION()")
    row = cursor.fetchone()
    print(f"Snowflake version: {row[0]}")
finally:
    cursor.close()
    conn.close()