Integrate Workbench with Snowflake

Workbench | Enhanced Advanced

There are two ways that Posit Workbench can integrate with Snowflake.

The instructions on this page describe how to configure the Snowflake integration in Posit Workbench outside of the Snowflake Native App. If you are using the Snowflake Native App, instead follow the Snowflake Native App documentation.

Advantages of Workbench’s integration for Snowflake

Workbench has a native integration for Snowflake that includes support for managed Snowflake OAuth credentials.

The Snowflake integration allows end users to sign into a Snowflake account from the home page and, using their existing identity, be immediately granted access to data and warehouses.

Workbench-managed credentials have several security and usability advantages over user-managed credentials, and we recommend them whenever possible:

  • Users arrive in a session to find that most official Snowflake SDKs and database drivers for Python and R work without needing a separate step to configure default credentials.
  • Users do not need to use their personal Snowflake password to have individually-scoped permissions.
  • Administrators can grant or revoke granular permissions for individuals directly through their Snowflake account.

Requirements

Important

Currently, this feature is only supported for RStudio Pro and VS Code sessions.

Before you begin, you must:

  • Ensure that end users are using HTTPS to access Workbench. For security reasons, web browsers do not allow the required OAuth flows to take place without HTTPS in place. For additional information, see the Mozilla Developer - Secure contexts documentation.
  • Enable the Job Launcher with the launcher-session-callback-address setting configured correctly. See the Job Launcher section for more information.
  • Have a Snowflake administrator available to create a custom OAuth integration for Workbench.

Snowflake configuration

Workbench requires a dedicated OAuth client in your Snowflake account, which you can create with the CREATE SECURITY INTEGRATION command. Both commands must be run in sequence.

Snowflake Console
-- Create OAuth Security Integration
-- See: https://docs.snowflake.com/en/sql-reference/sql/create-security-integration-oauth-snowflake
CREATE OR REPLACE SECURITY INTEGRATION my_posit_workbench
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'PUBLIC'
  OAUTH_ENFORCE_PKCE = TRUE
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  -- Be sure to replace this with your actual Workbench URL.
  OAUTH_REDIRECT_URI = 'https://<my-workbench-url>/oauth_redirect_callback'
  COMMENT = 'Posit Workbench';

-- Retrieve Client ID and Secret
SELECT system$show_oauth_client_secrets('MY_POSIT_WORKBENCH');

The output should be something like:

{"OAUTH_CLIENT_SECRET_2":"<client_secret>","OAUTH_CLIENT_SECRET":"<client_secret>","OAUTH_CLIENT_ID":"<client_id>"}

Record the client ID and either of the two secrets.

Refer to Snowflake’s own Configure Snowflake OAuth for custom clients documentation for additional details.

Workbench configuration

Configure available Snowflake accounts using one of the methods described below.

Once configured, verify that the Snowflake configuration has been picked up correctly by logging into the homepage and clicking New Session > RStudio Pro and confirming the Snowflake credential is visible in the credential selection widget.

Snowflake accounts in the New Session dialog’s credential selection widget

Workbench-managed credentials are refreshed automatically when sessions are actively using them.

Configure using a configuration file

Configure available Snowflake accounts in the /etc/rstudio/snowflake.conf file. Below is an example of what this file could contain:

/etc/rstudio/snowflake.conf"
[dev-partner-account]
name=Dev Partner Account
account=zfsccal-dev_partner_account
client-id=UF9wv/BhnkZBZtInBclix+tugSg=
client-secret=PxeYuXNL9d1yGshicrkpUOUxxSKjzxtC=

[prod]
account=zfsccal-production_account
client-id=bCoqbnU3YiYiIiNsIkA=
client-secret=Zk0qaDc0XjxhajpEMCVzeyA2ViVsZGM=

Each [header] names a section with the following properties:

Key Value Description Required
account string The account ID of a Snowflake account within your organization. Yes
client-id string The client ID of the OAuth integration created for Workbench. See Snowflake configuration above. Yes
client-secret string The client secret of the OAuth integration created for Workbench. See Snowflake configuration above. Yes
name string An optional user-friendly name for this Snowflake account. If not specified, Workbench will use a formatted version of the account property. No

  • Reload the Workbench service for the changes to take effect:

    Terminal
    $ sudo rstudio-server reload

Configure using envionment variables

Set the following environment variables to your Snowflake account ID and the client ID and secret recorded above. For example:

SNOWFLAKE_ACCOUNT=zfsccal-my_organization
SNOWFLAKE_CLIENT_ID=bCoqbnU3YiYiIiNsIkA=
SNOWFLAKE_CLIENT_SECRET=Zk0qaDc0XjxhajpEMCVzeyA2ViVsZGM=

If you are using systemd, you can run systemctl edit rstudio-server and add the following line:

Environment=SNOWFLAKE_ACCOUNT=zfsccal-my_organization SNOWFLAKE_CLIENT_ID=bCoqbnU3YiYiIiNsIkA= SNOWFLAKE_CLIENT_SECRET=Zk0qaDc0XjxhajpEMCVzeyA2ViVsZGM=

Restart the Workbench service for the changes to take effect:

Terminal
$ sudo rstudio-server restart

Automatic discovery of Snowflake roles

Workbench is able to automatically discover Snowflake roles assigned to users once they have successfully logged in with at least one role. Upon initial authentication with a role, Workbench automatically sends a request to the Snowflake SQL API and retrieves all of the roles the user has been assigned for that account. See Snowflake’s Snowflake SQL REST API documentation for more information.

The discovered roles are added to Workbench and available for users to select from the drop-down lists in the Session Credentials widget and the Edit Credentials dialog:

Snowflake roles in the Session Credentials widget

Snowflake roles in the Edit Credentials dialog

Users can also trigger a manual refresh of their roles by clicking the refresh control in the Edit Credentials dialog:

Refresh Snowflake roles control in the Edit Credentials dialog

Automatic discovery of Snowflake roles is enabled by default. To disable this feature, set allow-refresh-snowflake-roles=0:

/etc/rstudio/rserver.conf"
allow-refresh-snowflake-roles=0

This prevents Workbench from querying the Snowflake SQL API for users’ roles, and also disables the refresh control in the Edit Credentials dialog.

Troubleshooting

  • This integration relies on a properly-configured OAuth client to work. When encountering permissions errors during the Snowflake Sign-in flow from the home page, verify that the redirect URL is correct (and includes the schema) on the Snowflake side and that the client ID and client secret have been correctly copied from the Snowflake console. See the Snowflake configuration section above for more information.

  • End users who configure Snowflake-related environment variables or configuration files themselves for a session may override or interfere with Workbench-managed credentials. We suggest that users who need to manage their own credentials select the None option for the Snowflake account when launching sessions from the home page, which disables Workbench-managed credentials for that session.

Back to top