Coalesce supports OAuth authentication with Snowflake. When Snowflake OAuth is enabled, users can authorize their Development credentials using Single Sign-On (SSO) via Snowflake rather than submitting a username and password to Coalesce directly.
Only a Snowflake
ACCOUNTADMINrole or a role with the global
CREATE INTEGRATIONprivilege can create security integrations.
In Snowflake, execute a query to create a security integration. Please see the complete documentation for this in Snowflake's Create Security Integration documentation. You can find a sample
CREATE OR REPLACE security integration query below. Note this will be different if you're using SSO, or an EU account, this is only an example -
CREATE OR REPLACE SECURITY INTEGRATION "COALESCE_OAUTH" ENABLED = TRUE TYPE = OAUTH OAUTH_CLIENT = CUSTOM OAUTH_CLIENT_TYPE = 'CONFIDENTIAL' OAUTH_REDIRECT_URI = 'https://<COALESCE_APP_DOMAIN>/oauthredirect' OAUTH_ISSUE_REFRESH_TOKENS = TRUE OAUTH_REFRESH_TOKEN_VALIDITY = 7776000
<COALESCE_APP_DOMAIN> with your Coalesce app domain. For example: "app.coalescesoftware.io"
Dynamic Example in App
You can also find sample SQL in the app to make creating your OAuth integration easier.
|ENABLED*||Coalesce requires this field to be set to |
Note: Although not required in Snowflake documentation,
|TYPE*||Coalesce requires this field to be set to |
|OAUTH_CLIENT*||Coalesce requires this field to be set to|
|OAUTH_CLIENT_TYPE*||Coalesce requires this field to be set to |
|OAUTH_REDIRECT_URI*||Coalesce requires this field to be set to |
|OAUTH_ISSUE_REFRESH_TOKENS||Boolean that specifies whether to allow the client to exchange a refresh token for an access token when the current access token has expired. If set to |
|OAUTH_REFRESH_TOKEN_VALIDITY||Integer that specifies how long refresh tokens should be valid (in seconds). This can be used to expire the refresh token periodically. Note that|
*Required. Additional configuration options can be found in Snowflake's Create Security Integration documentation.
As a Coalesce Organization Admin, navigate to Build Settings → Environments or Build Settings →Development Workspaces . Select edit on the Environment or Workspace that you wish to configure.
OAuth is Configured per Environment/Workspace
By design, this allows for the flexibility of different Snowflake accounts per environment/workspace. If your organization uses the same Snowflake account and OAuth configuration for multiple environments, you will need to manually copy your configuration to each environment respectively. Workspaces can be easily duplicated including their settings.
Inside Edit Environment/Workspace → User Credentials, select Snowflake OAuth, enter your role and Snowflake compute warehouse, then Save.
Now obtain the Client ID and Client Secret which was generated by creating a Snowflake security integration. To retrieve these values, execute the following SQL query in Snowflake:
WITH INTEGRATION_SECRETS AS ( SELECT parse_json(system$show_oauth_client_secrets('COALESCE_OAUTH')) AS SECRETS ) SELECT SECRETS:"OAUTH_CLIENT_ID"::STRING AS CLIENT_ID, SECRETS:"OAUTH_CLIENT_SECRET"::STRING AS CLIENT_SECRET FROM INTEGRATION_SECRETS;
Now go to OAuth Settings and populate your CLIENT_ID and CLIENT_SECRET into the respective fields and click Save.
Now return to User Credentials to authenticate your integration.
After OAuth has been configured for the desired Coalesce Environment/Workspace, navigate to Build Settings Environments/Development Workspaces. Select edit on the environment/workspace that you wish to connect to Snowflake using OAuth.
Inside Edit Environment/Workspace User Credentials, select Authentication Type as Snowflake OAuth. Enter desired Role then select Authenticate and follow the directions in the Snowflake OAuth popup. Once successfully authenticated, you may input a desired Warehouse, Test Connection, and Save.
ACCOUNTADMIN Role and OAuth
Snowflake does not allow login via OAuth while specifying a role of ACCOUNTADMIN for security reasons.
OAuth and Snowflake Role
The role will be fixed for each OAuth connection established. If you'd like to change role, you'll need to disconnect, change role, and then reconnect.
Once a user has authorized Coalesce with Snowflake via their identity provider, Snowflake will return a Refresh Token to Coalesce. Coalesce is then able to exchange this refresh token for an Access Token which can then be used to open a Snowflake connection and execute queries in Coalesce on behalf of users.
To disable Snowflake OAuth, set the
ENABLED parameter to
FALSE. More details in Snowflake's documentation here.
The OAuth Toggle
Disabling OAuth via the Enable OAuth toggle will only prevent new OAuth connections from being created; existing connections will not be affected.
You can find an example query below.
alter integration COALESCE_OAUTH set enabled = false
To remove the integration you can drop it using the following command. More details in Snowflake's documentation here. Note that you will need to delete the configuration settings on the Coalesce side afterwards.
drop integration if exists COALESCE_OAUTH;
- Invalid consent request - When authenticating with Snowflake OAuth successfully redirects you to the Snowflake login page, but you receive an Invalid consent request error, your Snowflake user may not have access to the Snowflake role configured when authorizing OAuth. Double-check that you have access to that role and if the role name has been correctly entered in as Snowflake is case-sensitive.
Basic Auth is the fastest way to connect your target database platform to Coalesce, and the credential can be stored either in your local browser's storage (not recommended) or in Coalesce's cloud secrets vault.
Navigate to Build Settings Environments/Development Workspaces. Select Edit on the environment/workspace that you wish to connect to Snowflake using Basic Auth.
Inside Edit Environment/Workspace User Credentials, select Authentication Type as Username and Password (Cloud) or Username and Password (Browser Storage). Enter Username and Password into their respective fields and Save.
Updated 6 days ago