Snowflake connection patterns on Connect using viewer OAuth authentication

These patterns enable you to access Snowflake data in Posit Connect using Connect Viewer-level OAuth 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

Administrator configuration required

Your Connect administrator must configure a Snowflake viewer OAuth integration in Connect. Your Snowflake administrator must also grant viewers appropriate permissions to access Snowflake resources based on their individual credentials.

For detailed setup instructions, see the Snowflake OAuth Integration page in the Connect Admin Guide.

Viewer integrations allow published content to use the content viewer’s credentials when accessing Snowflake, providing each viewer with a personalized view of the data based on their individual permissions. Viewer integrations only support interactive content types. They do not support statically rendered content, content with access type Anyone - no login required, or Share links.

Required steps when publishing

You must enable the OAuth integration for your specified content using the Access tab in the Content Settings pane in Connect, or by selecting the integration upon deployment.

For more details, refer to the Adding Integrations section of the Connect User Guide.

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. This example uses pandas.

Install the required Python packages:

uv add snowflake-connector-python posit-sdk pandas shiny

pip install snowflake-connector-python posit-sdk pandas shiny

The connection patterns in this guide use odbc and the odbc::snowflake() function to connect to Snowflake via an ODBC driver. Use the connectcreds and snowflakeauth packages to authenticate with viewer OAuth credentials.

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 odbc, connectcreds, and snowflakeauth.

install.packages(c("odbc", "connectcreds", "snowflakeauth"))

The example below also uses shiny, DT, dplyr, and dbplyr.

install.packages(c("shiny", "DT", "dplyr", "dbplyr"))

Connection patterns

Build the connection per session, not at global scope

The viewer’s session token only exists at request time, so the connection must be created inside per-request code (for example, within server() in a Shiny app), not at global scope. A connection created at global scope either fails at startup (no token available) or is shared across all viewers, in contradiction to the per-viewer access that viewer OAuth is meant to provide.

To authenticate with Snowflake using viewer credentials, your code must retrieve the viewer’s session token from the Posit-Connect-User-Session-Token HTTP header and exchange it for a Snowflake OAuth access token. The posit.connect.external.snowflake module from the posit-sdk package simplifies this token exchange process, with PositAuthenticator handling the exchange when deployed to Connect.

This code example uses different authentication approaches depending on the environment:

PositAuthenticator retrieves and exchanges the viewer’s session token when deployed to Connect
connection_name="workbench" uses Workbench managed credentials when running on Workbench
EXTERNALBROWSER provides browser-based authentication for local development

This approach allows the same code to run across environments.

import os

import pandas as pd
import snowflake.connector
from posit.connect.external.snowflake import PositAuthenticator
from shiny.express import render, session, ui


def create_connection() -> snowflake.connector.SnowflakeConnection:
    """Create a Snowflake connection object."""
    # Environment variables
    database = os.getenv("SNOWFLAKE_DATABASE", "LENDING_CLUB")
    schema = os.getenv("SNOWFLAKE_SCHEMA", "PUBLIC")

    # Determine if the app is running on Connect or Workbench
    on_connect = os.getenv("POSIT_PRODUCT") == "CONNECT"
    on_workbench = os.getenv("POSIT_PRODUCT") == "WORKBENCH"

    if on_connect:
        # Retrieve the viewer's session token.
        # The token is stored on the http header for the interactive user session.
        # Depending on what framework you are using (e.g., Shiny, Streamlit, etc.)
        # the framework will have a different retrieval method for the header.
        # This example shows retrieval of the token when using Shiny.
        session_token = session.http_conn.headers.get(
            "Posit-Connect-User-Session-Token"
        )
        # SNOWFLAKE_ACCOUNT is automatically set by the Posit Connect Viewer
        # integration.
        account = os.environ["SNOWFLAKE_ACCOUNT"]
        auth = PositAuthenticator(user_session_token=session_token)

        return snowflake.connector.connect(
            account=account,
            database=database,
            schema=schema,
            authenticator=auth.authenticator,
            token=auth.token,
        )
    elif on_workbench:
        return snowflake.connector.connect(
            connection_name="workbench",
            database=database,
            schema=schema,
        )
    else:
        # Local development - browser-based authentication
        # USER is only required when running locally with external browser auth
        user = os.environ["SNOWFLAKE_USER"]
        account = os.environ["SNOWFLAKE_ACCOUNT"]
        return snowflake.connector.connect(
            user=user,
            account=account,
            database=database,
            schema=schema,
            authenticator="externalbrowser",
        )


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 odbc::snowflake() function is the recommended approach for connecting to Snowflake from R content developed on Workbench and published to Connect. It handles both Workbench managed credentials and Connect viewer OAuth credentials without requiring code changes.

The only difference between this code and what is shown in the Posit Workbench Managed Credentials section is the addition of library(connectcreds), which automatically detects the environment and retrieves the appropriate credentials at runtime.

See the snowflake() helper function in the odbc package documentation for more information.

library(dbplyr)
library(dplyr)
library(shiny)
# When deployed to Posit Connect connectcreds and snowflakeauth need
# to be loaded even though they are never called.
library(connectcreds)
library(snowflakeauth)


create_connection <- function() {
    on_connect <- Sys.getenv("POSIT_PRODUCT") == "CONNECT"
    on_workbench <- Sys.getenv("POSIT_PRODUCT") == "WORKBENCH"

    database <- "LENDING_CLUB"
    schema <- "PUBLIC"

    if (on_workbench) {
        # When running on Workbench, the connection name "workbench" will be used to
        # retrieve the viewer's session token and authenticate with Snowflake.
        con <- DBI::dbConnect(
            odbc::snowflake(),
            database = database,
            schema = schema
        )
        return(con)
    } else if (on_connect) {
        # The connectcreds package will automatically retrieve the viewer's session token
        # and use it to authenticate with Snowflake when a connection is created.
        con <- DBI::dbConnect(
            odbc::snowflake(),
            database = database,
            schema = schema
        )
        return(con)
    } else {
        # Local development - use browser-based authentication
        con <- DBI::dbConnect(
            odbc::snowflake(),
            user = Sys.getenv("SNOWFLAKE_USER"),
            account = Sys.getenv("SNOWFLAKE_ACCOUNT"),
            database = database,
            schema = schema,
            authenticator = "externalbrowser"
        )
        return(con)
    }
}

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)