​

Admin Configuration

​

Configuration Overview

​

Prerequisites

1. Ensure the database service is already configured in Collate
2. Service connection should be working (metadata ingestion successful)
3. For Collate SSO: Google OIDC SSO must be configured in Collate (Settings → Security → SSO)
4. For OAuth: You must have OAuth credentials (Client ID and Client Secret) from your OAuth provider

​

Accessing Service Configuration

1. Navigate to Settings → Services → Database Services

2. Select the database service you want to configure (e.g., my-bigquery, production-snowflake)
3. Click on the Query Runner tab
4. Click Configure Connection to begin setup

​

Common Configuration Settings

​

BigQuery Configuration

​

Authentication Method 1: Collate SSO

1. In the Query Runner configuration form, select Authentication Type → Collate SSO
2. OAuth Credentials (auto-populated):
   • Client ID: (automatically fetched from Google OIDC SSO configuration)
   • Client Secret: (automatically fetched from Google OIDC SSO configuration)
   • Scope: (automatically set to include BigQuery scope: https://www.googleapis.com/auth/bigquery)
   • Redirect URL: (auto-generated based on your Collate domain)

3. Click Save Configuration
4. OAuth credentials are automatically prefetched from your Collate’s Google SSO settings
5. After saving, you can view the auto-enriched values in the configuration
6. Toggle Enabled to ON to activate SQL Studio for this service
7. Click Save again to apply changes

• Google OIDC SSO must be configured in Collate (Settings → Security → SSO)
• SSO OAuth app must have BigQuery API scope enabled
• Users must have BigQuery permissions in their Google Cloud projects

​

Authentication Method 2: OAuth

1. Go to Google Cloud Console
2. Navigate to APIs & Services → Credentials
3. Click Create Credentials → OAuth 2.0 Client ID
4. Application type: Web application
5. Name: Collate SQL Studio - BigQuery
6. Authorized redirect URIs: Add https://your-collate-domain.com/api/v1/queryRunner/oauth/callback
   • Replace your-collate-domain.com with your actual Collate domain
   • Ensure the URL is exactly https:// (not http://) for production
7. Click Create
8. Copy the Client ID and Client Secret (you’ll need these in Step 2)

1. In the Query Runner configuration form, select Authentication Type → OAuth
2. Enter OAuth Client Credentials:
   • Client ID: Paste from Google Cloud Console
   • Client Secret: Paste from Google Cloud Console
   • Redirect URL: https://your-collate-domain.com/api/v1/queryRunner/oauth/callback (verify exact match)
   • Scope: https://www.googleapis.com/auth/bigquery (default)

3. Toggle Enabled to ON to activate
4. Click Save Configuration

​

Authentication Method 3: Basic Authentication (Service Account)

1. In the Query Runner configuration form, select Authentication Type → Basic

2. Toggle Enabled to ON to activate
3. Click Save Configuration

• Each user must have a GCP Service Account with BigQuery permissions
• Service Account must have bigquery.jobs.create permission
• Users will upload their Service Account JSON file or enter credentials manually

​

Snowflake Configuration

​

Authentication Method 1: OAuth

• Snowflake OAuth Security Integration must be created (see setup below)

1. Create Snowflake OAuth Security Integration (run in Snowflake):

CREATE OR REPLACE SECURITY INTEGRATION COLLATE_SQL_STUDIO_OAUTH
  TYPE = OAUTH
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
  OAUTH_REDIRECT_URI = 'https://your-collate-domain.com/api/v1/queryRunner/oauth/callback'
  OAUTH_ALLOW_NON_TLS_REDIRECT_URI = FALSE  -- Set TRUE for localhost/dev only
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_REFRESH_TOKEN_VALIDITY = 86400  -- 24 hours
  OAUTH_USE_SECONDARY_ROLES = 'IMPLICIT'
  ENABLED = TRUE;

2. Retrieve OAuth Credentials (run in Snowflake):

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('COLLATE_SQL_STUDIO_OAUTH');

3. Configure in Collate:
   • In the Query Runner configuration form, select Authentication Type → OAuth
   • Enter OAuth Client Credentials:
     • Client ID: From step 2
     • Client Secret: From step 2
     • Redirect URL: https://your-collate-domain.com/api/v1/queryRunner/oauth/callback
     • Scope: session:role-any (or specific role scope like session:role:ANALYST)
   • (Optional) Enable User Configurable Fields:
     • ☑️ Warehouse: Allow users to select compute warehouse
     • ☑️ Database: Allow users to select default database
     • ☑️ Schema: Allow users to select default schema
     • ☑️ Role: Allow users to select their Snowflake role
   • Toggle Enabled to ON to activate
   • Click Save Configuration

​

Authentication Method 2: Basic Authentication

1. In the Query Runner configuration form, select Authentication Type → Basic
2. (Optional) Enable User Configurable Fields:
   • ☑️ Warehouse: Allow users to override warehouse
   • ☑️ Database: Allow users to override database
   • ☑️ Schema: Allow users to override schema
   • ☑️ Role: Allow users to override role
3. Toggle Enabled to ON to activate
4. Click Save Configuration

• Users must have Snowflake credentials (Username + Password OR Username + Key Pair)
• For Key Pair auth, users must upload their public key to Snowflake

​

Trino Configuration

​

Authentication Method 1: OAuth

• Trino/Starburst cluster must have OAuth authentication enabled
• OAuth provider (e.g., Azure AD, Okta) must be configured

1. Create OAuth App in Azure AD (example for Starburst):
   • Go to Azure Portal → Azure Active Directory → App registrations
   • Click New registration
   • Name: Collate SQL Studio - Trino
   • Redirect URI: https://your-collate-domain.com/api/v1/queryRunner/oauth/callback
   • Click Register
   • Go to Certificates & secrets → New client secret
   • Copy the Application (client) ID and Client secret value
2. Configure in Collate:
   • In the Query Runner configuration form, select Authentication Type → OAuth
   • Host Port: Auto-populated from database service connection (e.g., ometa.galaxy.starburst.io:443)
   • Enter OAuth Client Credentials:
     • Client ID: From Azure AD
     • Client Secret: From Azure AD
     • Redirect URL: https://your-collate-domain.com/api/v1/queryRunner/oauth/callback
     • Scope: openid profile email (or custom scope)
   • Toggle Enabled to ON to activate
   • Click Save Configuration

​

Authentication Method 2: Basic Authentication

1. In the Query Runner configuration form, select Authentication Type → Basic
2. Toggle Enabled to ON to activate
3. Click Save Configuration

• Users must have Trino credentials (Username + Password)

​

Redshift / PostgreSQL / MSSQL Configuration

​

Authentication Method: Basic Authentication

1. In the Query Runner configuration form, select Authentication Type → Basic
2. Toggle Enabled to ON to activate
3. Click Save Configuration

• Users must have valid credentials (Username + Password) for the respective database service
• Users must have appropriate permissions on the database

​

Configuration Best Practices

​

Choosing Authentication Methods

​

User Configurable Fields

​

Security Recommendations

1. Use OAuth when possible: OAuth provides better security with token-based authentication
2. Rotate OAuth credentials: Regularly rotate Client Secrets for OAuth apps
3. Limit Max Result Size: Set to 100 or lower to prevent excessive data retrieval
4. Audit configuration changes: Monitor who changes Query Runner configurations
5. Test connections: Always test connections after configuration changes

​

Max Result Size Guidelines

​

Updating Configuration

1. Navigate to the service’s Query Runner tab
2. Click Edit Configuration
3. Modify settings as needed
4. Click Save

​

Enabling/Disabling SQL Studio

​

Enabling SQL Studio

1. In the Query Runner configuration, toggle Enabled to ON
2. Click Save Configuration

​

Disabling SQL Studio

1. Navigate to the service’s Query Runner tab
2. Toggle Enabled to OFF
3. Click Save

​

Connection Status Indicators

​

Connection Flow

Not Connected
     ↓
User Clicks Service
     ↓
Authentication (OAuth popup or credentials modal)
     ↓
Pending (Testing connection)
     ↓
Connection Test (SELECT 1)
     ↓
Connected (Ready to query)

• After initial authentication, SQL Studio runs a test query (SELECT 1)
• This verifies credentials, permissions, and network connectivity
• If successful, status changes to Connected (green)
• If failed, error message displayed with details

​

Managing User Access

​

Who Can Use SQL Studio?

1. View Basic permission on the database service in Collate
2. SQL Studio is enabled by administrator for that service
3. They have successfully authenticated their connection

​

Controlling Access

• Assign users to teams with appropriate policies
• Grant View Basic permission on database services
• Users without permission won’t see the service in SQL Studio

​

Deleting Configuration

1. Navigate to the service’s Query Runner tab
2. Click Delete Configuration
3. Warning: This will permanently delete:
   • Admin configuration
   • All user connections for this service
   • All saved queries for this service
4. Type the service name to confirm
5. Click Confirm Delete

​

Troubleshooting Configuration

​

OAuth Callback URL Mismatch

• Verify the redirect URL in Collate matches exactly: https://your-collate-domain.com/api/v1/queryRunner/oauth/callback
• Check for trailing slashes (shouldn’t have any)
• Ensure protocol is https (not http in production)
• Update the OAuth app configuration in the provider

​

Collate SSO Credentials Not Auto-Populating

• Verify Google OIDC SSO is configured correctly (Settings → Security → SSO)
• Collate SSO currently only supports Google OIDC
• Check that the SSO provider is Google:
  • Google OIDC → BigQuery
• Ensure Google SSO OAuth app has required scopes (e.g., BigQuery API scope)

​

Users Cannot Connect After Configuration

• Verify Enabled is checked
• Check that users are accessing the correct service in SQL Studio
• Review backend logs for configuration errors
• Test the connection using test credentials

​

Next Steps

1. Inform Users: Let users know SQL Studio is available for the configured services
2. Share Authentication Guide: Direct users to User Authentication
3. Monitor Usage: Review query logs and audit trails in Settings → Audit Logs
4. Optimize Settings: Adjust Max Result Size and user-configurable fields based on usage patterns