Snowflake connection patterns on Connect using key-pair authentication

These patterns enable you to access Snowflake data in Posit Connect using Key-pair Authentication. They include both Python and R code examples.

Choose a different pattern by referring to the table in Choose a connection for Snowflake.

Not sure which pattern to use?

For an overview of credential types and scope in different environments, see Credential types and Managing data access from development to deployment.

Overview

Key-pair authentication uses a public/private key pair associated with a Snowflake service account. This is a one-to-many credential. All viewers of the content use the same service account credentials. Key-pair authentication supports both interactive and rendered content types, as well as content with access type Anyone - no login required and Share links.

Configuration

The private key must be stored as a base64-encoded environment variable in Connect rather than as a file on the filesystem. Set the B64_ENCODED_SNOWFLAKE_PRIVATE_KEY environment variable in the Connect UI under the content’s Advanced settings.

To check if a key pair is already associated with the service account, run the following in Snowflake:

DESC USER "<service-account>";
SELECT *
FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
WHERE "property" LIKE '%RSA%';

If no key pair exists, a Snowflake administrator must create one and associate the public key with the service account. See the Snowflake Key Pair Auth Guide for details.

Prerequisites

The connection patterns in this guide use the snowflake-connector-python package to connect to Snowflake. The connector integrates with data frame libraries like pandas and polars. The example below also uses shiny and pandas.

Install the required Python packages:

uv add snowflake-connector-python shiny pandas

pip install snowflake-connector-python shiny pandas

The connection patterns in this guide use odbc, the odbc::snowflake() function, and snowflakeauth to connect to Snowflake via an ODBC driver. The example below also uses openssl to handle reading the private key. Use dbplyr and dplyr for data manipulation, and shiny for the app framework.

Administrator configuration required

An administrator must install and configure database drivers on the server before you can use them in your code. See the Data sources admin guide for more information.

install.packages(c("odbc", "snowflakeauth", "openssl", "dbplyr", "dplyr", "shiny"))

Connection patterns

When using key-pair authentication on Connect, store the private key as a base64-encoded environment variable rather than as a file on the filesystem. Set the environment variable in the Connect UI under the content’s Advanced settings, or in an .env (Python) or .Renviron (R) file before publishing.

To base64-encode your private key:

# Encode the key
export B64_ENCODED_SNOWFLAKE_PRIVATE_KEY=$(cat ~/rsa_key.p8 | base64 -w 0)

# Add to .Renviron for R
echo "B64_ENCODED_SNOWFLAKE_PRIVATE_KEY=${B64_ENCODED_SNOWFLAKE_PRIVATE_KEY}" >> .Renviron

# Add to .env for Python
echo "B64_ENCODED_SNOWFLAKE_PRIVATE_KEY=${B64_ENCODED_SNOWFLAKE_PRIVATE_KEY}" >> .env

The -w 0 flag ensures the encoded key is a single line.

The code below decodes the base64-encoded private key from the B64_ENCODED_SNOWFLAKE_PRIVATE_KEY environment variable and uses it to authenticate with Snowflake.

This example uses the snowflake-connector-python package with pandas for data retrieval.

import base64
import os

import pandas as pd
import snowflake.connector
from cryptography.hazmat.primitives import serialization
from shiny.express import render, ui


def get_private_key() -> bytes:
    """Decode the base64 PEM key from the environment and return DER bytes."""
    pem = base64.b64decode(os.environ["B64_ENCODED_SNOWFLAKE_PRIVATE_KEY"])
    key = serialization.load_pem_private_key(pem, password=None)
    return key.private_bytes(
        encoding=serialization.Encoding.DER,
        format=serialization.PrivateFormat.PKCS8,
        encryption_algorithm=serialization.NoEncryption(),
    )


def create_connection() -> snowflake.connector.SnowflakeConnection:
    private_key_der = get_private_key()

    con = snowflake.connector.connect(
        account=os.getenv("SNOWFLAKE_ACCOUNT", "<your-account-here>"),
        user=os.getenv("SNOWFLAKE_USER", "<your-username-here>"),
        private_key=private_key_der,
        database="LENDING_CLUB",
        schema="PUBLIC",
    )
    return con


ui.page_opts(
    title="Snowflake Connector with Viewer Authentication Example", fillable=True
)

with ui.card(full_screen=True):

    @render.data_frame
    def snowflake_data():
        # Create the connection object
        con = create_connection()
        # Query data
        query = "SELECT * FROM LOAN_DATA LIMIT 10;"
        df = pd.read_sql(query, con)
        return df

The code below decodes the base64-encoded private key from the B64_ENCODED_SNOWFLAKE_PRIVATE_KEY environment variable and uses it to authenticate with Snowflake via odbc::snowflake().

library(shiny)
library(dbplyr)
library(dplyr)

# When deployed to Posit Connect snowflakeauth needs to be loaded
# even though it is never called.
library(snowflakeauth)

# Convert the base64 PEM key from the environment into the base64-encoded DER
# form that PRIV_KEY_BASE64 expects. This mirrors the Python connector's
# PEM -> DER PKCS8 step and removes the need to write a key file to disk.
get_private_key_base64 <- function() {
    pem <- openssl::base64_decode(Sys.getenv(
        "B64_ENCODED_SNOWFLAKE_PRIVATE_KEY"
    ))
    key <- openssl::read_key(pem, der = FALSE)
    openssl::base64_encode(openssl::write_der(key))
}


create_connection <- function() {
    # AUTHENTICATOR and PRIV_KEY_BASE64 are case-sensitive, so match the docs.
    DBI::dbConnect(
        odbc::snowflake(),
        account = Sys.getenv("SNOWFLAKE_ACCOUNT", "<your-account-here>"),
        user = Sys.getenv("SNOWFLAKE_USER", "<your-username-here>"),
        PRIV_KEY_BASE64 = get_private_key_base64(),
        database = "LENDING_CLUB",
        schema = "PUBLIC"
    )
}

ui <- fluidPage(
    titlePanel("Snowflake Data"),
    DT::dataTableOutput("data")
)


server <- function(input, output, session) {
    con <- create_connection()

    df <- tbl(con, "LOAN_DATA") |>
        select(ID, LOAN_AMNT, TERM, INT_RATE, GRADE) |>
        filter(!is.na(LOAN_AMNT)) |>
        arrange(desc(LOAN_AMNT)) |>
        head(10) |>
        collect()

    output$data <- DT::renderDataTable(df)
}

shinyApp(ui = ui, server = server)