Admin Configuration

After installing the Query Runner application, administrators must configure SQL Studio for each database service. This configuration determines which authentication methods are available to users and what connection settings they can override.

Configuration Overview

Prerequisites

Before configuring SQL Studio for any database service:

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

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

Select authentication type for SQL Studio

Common Configuration Settings

All services share these configuration options:

BigQuery Configuration

BigQuery supports three authentication methods: Collate SSO, OAuth, and Basic Authentication.

Authentication Method 1: Collate SSO

Collate SSO leverages your existing Google SSO configuration in Collate to authenticate with database services. Instead of configuring separate OAuth credentials, SQL Studio automatically uses already configured fields from Collate login.

Configuration Steps:

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)

Collate SSO authentication configuration with auto-populated fields

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

Query Runner tab after successful configuration

Prerequisites:

• 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

OAuth allows users to authenticate directly with Google OAuth 2.0, separate from their Collate login.

Configuration Steps:

Step 1: Create Google OAuth App (if not already created)

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)

Step 2: Configure in Collate

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)

Configure OAuth authentication with client credentials

3. Toggle Enabled to ON to activate

4. Click Save Configuration

Authentication Method 3: Basic Authentication (Service Account)

Basic Authentication allows users to provide their own GCP Service Account credentials.

Setup Steps:

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

Configure Basic Authentication for SQL Studio

2. Toggle Enabled to ON to activate

3. Click Save Configuration

User Requirements:

• 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

Snowflake supports OAuth and Basic Authentication.

Authentication Method 1: OAuth

Prerequisites:

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

Setup Steps:

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

2. Retrieve OAuth Credentials (run in Snowflake):

This returns JSON with OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET.

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

Setup Steps:

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

User Requirements:

• Users must have Snowflake credentials (username/password or key-pair)
• For key-pair auth, users must upload their public key to Snowflake

Trino Configuration

Trino (Starburst) currently supports OAuth only.

Authentication Method: OAuth

Prerequisites:

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

Setup Steps:

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

Configuration Best Practices

Choosing Authentication Methods

User Configurable Fields

Enable user-configurable fields to give users flexibility while maintaining security:

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

Users should use LIMIT clauses in their queries to control result size further.

Updating Configuration

To update an existing Query Runner 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

After configuring authentication, you must enable SQL Studio for users:

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

Check Enabled to activate SQL Studio for this service

Once enabled, the service will appear in SQL Studio for all users with appropriate permissions.

Disabling SQL Studio

To temporarily disable SQL Studio for a service:

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

This prevents new user connections without deleting existing configurations. Users will see "Service not available" in SQL Studio.

Connection Status Indicators

Understanding connection status helps you troubleshoot user issues:

Connection Flow

Connection Testing:

• 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?

Users can access SQL Studio if they have:

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

Use Collate's role-based access control (RBAC):

• 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

To completely remove Query Runner 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

Problem: "Redirect URI mismatch" error during OAuth setup

Solution:

• 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

Problem: Collate SSO option doesn't show OAuth credentials

Solution:

• 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

Problem: Configuration saved successfully but users see "Service not available"

Solution:

• 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

After configuring SQL Studio:

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

Ready for users to connect? Guide them through User Authentication to establish their first connection.