Connect Snowflake to LiftOps

Before you begin

You need a Snowflake administrator who can create roles, warehouses, security integrations, and grants. Decide which databases, schemas, and tasks LiftOps users may access. LiftOps only sees and operates objects granted to the LIFTOPS_APP role.

1. Open a Snowflake worksheet

In Snowsight, open Projects → Worksheets, create a SQL worksheet, and select the ACCOUNTADMIN role.

2. Customize and run the script

Replace YOUR_DATABASE, YOUR_SCHEMA, and YOUR_SNOWFLAKE_USER. The script grants access to all current tasks in that schema and automatically covers future tasks. Repeat the database and schema grants for any additional schemas LiftOps should manage.

USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS LIFTOPS_APP;

CREATE WAREHOUSE IF NOT EXISTS LIFTOPS_WH
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60
  AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

GRANT USAGE ON WAREHOUSE LIFTOPS_WH TO ROLE LIFTOPS_APP;

CREATE SECURITY INTEGRATION IF NOT EXISTS LIFTOPS_OAUTH
  TYPE = OAUTH
  ENABLED = TRUE
  OAUTH_CLIENT = CUSTOM
  OAUTH_CLIENT_TYPE = 'PUBLIC'
  OAUTH_REDIRECT_URI = 'https://liftops-api-gnwedt7uya-uc.a.run.app/auth/snowflake/callback'
  OAUTH_ISSUE_REFRESH_TOKENS = TRUE
  OAUTH_ENFORCE_PKCE = TRUE
  PRE_AUTHORIZED_ROLES_LIST = ('LIFTOPS_APP');

-- Replace the example database and schema with the area LiftOps may manage.
GRANT USAGE ON DATABASE YOUR_DATABASE TO ROLE LIFTOPS_APP;
GRANT USAGE ON SCHEMA YOUR_DATABASE.YOUR_SCHEMA TO ROLE LIFTOPS_APP;

-- Allow LiftOps to operate every task already in this schema.
GRANT OPERATE ON ALL TASKS IN SCHEMA YOUR_DATABASE.YOUR_SCHEMA
  TO ROLE LIFTOPS_APP;

-- Automatically allow LiftOps to operate tasks created here later.
GRANT OPERATE ON FUTURE TASKS IN SCHEMA YOUR_DATABASE.YOUR_SCHEMA
  TO ROLE LIFTOPS_APP;

-- Grant the role to each Snowflake user who will connect through LiftOps.
GRANT ROLE LIFTOPS_APP TO USER YOUR_SNOWFLAKE_USER;

-- Return the OAuth credentials needed for the secure LiftOps registration step.
SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('LIFTOPS_OAUTH');

Security note: OPERATE allows LiftOps to run, suspend, and resume tasks in the selected schema without granting LiftOps ownership or account-administrator access. The task owner must separately retain the privileges required for each task to run.

3. Copy the OAuth client ID

The final query returns an OAuth client ID and may also display a client secret. Copy only the client ID into LiftOps. The public-client PKCE flow does not use the secret, so LiftOps never receives or stores it.

4. Connect LiftOps

1. 1
   Find the account identifier

   In Snowflake, open the account menu and copy the identifier in ORGANIZATION-ACCOUNT format.

2. 2
   Enter both identifiers

   Return to LiftOps and enter the Snowflake account identifier plus the OAuth client ID. Existing LiftOps organizations can leave the client ID blank.

3. 3
   Sign in and approve access

   Use your own Snowflake username and complete any MFA required by your organization.

Troubleshooting