Database passthrough authentication for Snowflake

This guide walks you through setting up database passthrough authentication for Snowflake. With this setup, each Holistics user authenticates with their individual Snowflake credentials, and queries run under their identity in Snowflake.

Step-by-step setup

Step 1: create a Snowflake OAuth integration

Holistics connects to Snowflake using a Snowflake OAuth security integration. This integration lets each user authorize Holistics to run queries under their own Snowflake identity.

1. Switch your role to ACCOUNTADMIN, which is required to create an OAuth integration. If you just switched roles, refresh the browser before continuing.

2. Run the following command, replacing <holistics-domain> with your Holistics domain (see the notes below):

   CREATE SECURITY INTEGRATION OAUTH_HOLISTICS_INT
     TYPE = OAUTH
     ENABLED = TRUE
     OAUTH_CLIENT = CUSTOM
     OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
     OAUTH_REDIRECT_URI = 'https://<holistics-domain>/data_sources/oauth_callback'
     OAUTH_ISSUE_REFRESH_TOKENS = TRUE
     OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
     BLOCKED_ROLES_LIST = ('SYSADMIN')
     OAUTH_ENFORCE_PKCE = TRUE
     OAUTH_ALLOW_NON_TLS_REDIRECT_URI = FALSE
   ;

   Notes:

   • OAUTH_REFRESH_TOKEN_VALIDITY is set to 90 days (7776000 seconds).
   • For <holistics-domain>, copy the domain from the Holistics app. For example:
     • secure.holistics.io
     • eu.holistics.io
     • us.holistics.io
     • company.holistics.io (if you're using a custom domain)

3. Retrieve the Client ID. Run the command below and look for the OAUTH_CLIENT_ID value:

   DESC SECURITY INTEGRATION OAUTH_HOLISTICS_INT;

4. Retrieve the Client Secret. Run the command below and look for the CLIENT_SECRET value:

   SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('OAUTH_HOLISTICS_INT');

5. Save both the Client ID and Client Secret - you'll need them in the next step.

Step 2: configure passthrough authentication in Holistics

1. Navigate to the Data Sources page in Holistics (/manage/data_sources)

2. If you don't already have a Snowflake data source, add a new data source using Private Key authentication

3. Enable passthrough authentication by clicking the "Passthrough" button on your data source in the listing page

4. Enter the Client ID and Client Secret from Step 1, then save

Step 3: connect your database credentials

Each user must authenticate with their individual Snowflake account:

1. Go to My Account settings (/users/settings)
2. Locate the Database Authentication section
3. Click to login with your Snowflake credentials

Token expiration behavior

Access tokens expire after 24 hours, and refresh tokens expire after 90 days. Users will be prompted to re-authenticate when their refresh tokens expire.