Snowflake Keypair Authenitcation via Posit Connect

snowflake-keypair.R

# Use SSH key if on Connect, otherwise use managed credentials
if (Sys.getenv("RSTUDIO_PRODUCT") == "CONNECT"){
  # grab ssh key from environment variable and cache as tempfile
  cached_key <- tempfile()
  readr::write_file(openssl::base64_decode(Sys.getenv("SNOWFLAKE_SSH_KEY")), file = cached_key)
  
  # The ambient credential feature in odbc::snowflake() causes unexpected overwrites, so we'll use the base Snowflake driver.
  con <- dbConnect(
    odbc::odbc(),
    driver = "Snowflake",
    server = paste0(Sys.getenv("SNOWFLAKE_ACCOUNT"), ".snowflakecomputing.com"),
    uid = "SVC_SOLENG",
    warehouse = "DEFAULT_WH",
    database = "LENDING_CLUB",
    schema = "PUBLIC",
    # Settings to set Snowflake key-pair auth
    authenticator = "SNOWFLAKE_JWT",
    PRIV_KEY_FILE = cached_key
  )