Snowflake

Truto connects to Snowflake in one of two ways:

• Key pair (service user): Truto creates a key pair for the connection and shows you its public key. A Snowflake admin adds that public key to a Snowflake service user by running SQL that the connect screen writes for you. Nobody signs in, and there is no OAuth app or password to look after. Choose this when your Snowflake policies do not allow OAuth, or when the connection should not depend on one person's account.
• OAuth: a person signs in to Snowflake and allows access, through an OAuth security integration you create in Snowflake.

Install Snowflake integration

The Snowflake integration can be found here. Please install it in your environments before proceeding with the steps below.

Connecting a Snowflake account

1. Click on Connect Account
2. Type in a value for the Tenant ID. This can be an ID of your choosing. And then click on Get connection link
3. A new link will be generated. Clicking on the link will open a new tab with the list of integrations. You can share this link with your customers too and they will be able to connect their Snowflake account.
4. Select Snowflake
5. Choose how to connect: Key pair (service user) or OAuth 2.0. Then follow the matching section below.

Connect with a key pair (service user)

Before you start

• A Snowflake admin with ACCOUNTADMIN runs the SQL. The SQL grants a database role from Snowflake's own SNOWFLAKE database, and only ACCOUNTADMIN can grant those.
• Does your Snowflake account restrict IP addresses? If it has a network policy, Truto's requests must come from fixed IP addresses. Use Truto's Static Gate and allow its IPs for the service user; see Optional: allow the user only from Truto's Static Gate. Without a static gate, Truto's requests come from addresses that change.
• Use a new service user for Truto. Let the SQL create a dedicated user. Truto signs in with the user's login name (LOGIN_NAME). For a user the SQL creates, the login name and the user name are the same. Using an existing user whose login name differs from its name is not covered yet.

Steps

1. On the connect screen, choose Key pair (service user).

2. Fill in the fields:

   • Snowflake Account URL, for example https://myorg-myaccount.snowflakecomputing.com. Snowsight URLs (app.snowflake.com), PrivateLink URLs and .global connection URLs are not supported.
   • Service user login name (default TRUTO_SVC): the user Truto signs in as.
   • Warehouse (default COMPUTE_WH): the warehouse Truto runs its queries on.
   • Role (default TRUTO_ROLE): the role Truto uses.

   The SQL on the screen fills in these values as you type.

3. Copy the SQL with the Copy button and run all of it in a Snowsight SQL worksheet as ACCOUNTADMIN.

4. Click Connect. Connecting does not call Snowflake, so it succeeds even if the key or a grant is wrong. Check the connection with one request through Truto, for example listing warehouses. A problem with the key, the grants or a network policy shows up there.

What the SQL does

The connect screen shows this SQL with your values and the connection's own key filled in. Here it is with the default values:

USE ROLE ACCOUNTADMIN;  -- SNOWFLAKE database roles can only be granted by their owner
 
-- Role Truto uses
CREATE ROLE IF NOT EXISTS TRUTO_ROLE;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE TRUTO_ROLE;
GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE TRUTO_ROLE;
 
-- Service user (service users cannot use passwords or SSO)
CREATE USER IF NOT EXISTS TRUTO_SVC TYPE = SERVICE DEFAULT_ROLE = TRUTO_ROLE;
GRANT ROLE TRUTO_ROLE TO USER TRUTO_SVC;
 
-- This user may only sign in with a key pair (policies live in a schema)
CREATE DATABASE IF NOT EXISTS TRUTO_ADMIN;
CREATE SCHEMA IF NOT EXISTS TRUTO_ADMIN.POLICIES;
CREATE AUTHENTICATION POLICY IF NOT EXISTS TRUTO_ADMIN.POLICIES.TRUTO_KEYPAIR_ONLY AUTHENTICATION_METHODS = ('KEYPAIR');
ALTER USER TRUTO_SVC SET AUTHENTICATION POLICY TRUTO_ADMIN.POLICIES.TRUTO_KEYPAIR_ONLY FORCE;
 
-- Truto's key for this connection (last: fails if already added, which is safe)
ALTER USER TRUTO_SVC ADD KEY PAIR TRUTO_<key_id_short> PUBLIC_KEY = '<public key>';

To check the result afterwards, for example when asking Truto support for help, run this separately. It lists the user's key pairs and shows the account identifier:

SHOW USER KEY PAIRS FOR USER TRUTO_SVC;
SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME() AS account_identifier, CURRENT_ACCOUNT() AS locator;

In plain words:

• Role. TRUTO_ROLE can use the warehouse and read Snowflake's account usage data through the SNOWFLAKE.SECURITY_VIEWER database role. It gets nothing else.
• Service user. A SERVICE user cannot sign in with a password or with SSO, so it can only be used by software that holds its key.
• Authentication policy. The user can sign in only with a key pair. FORCE replaces a policy the user may already have.
• The key. The public key is added under a name of its own: TRUTO_ and the key's key_id_short, the first 8 characters of its ID in upper case. Each connection's key has a different name, so running the SQL again adds the new key instead of clashing with an old one. The private key never leaves Truto.

The SQL is safe to run more than once: every statement that creates something uses IF NOT EXISTS, and the key statement comes last.

A Snowflake user can hold at most 10 key pairs. To remove keys you no longer use, list them and remove them by name:

SHOW USER KEY PAIRS FOR USER TRUTO_SVC;
ALTER USER TRUTO_SVC REMOVE KEY PAIR TRUTO_<key_id_short>;

A connection's key_id_short is in its context, under context.snowflake_key.key_id_short, and it is the end of the key's name in the SQL the connect screen showed.

Optional: allow the user only from Truto's Static Gate

Do this if your Snowflake account restricts IP addresses, or if you want the service user usable only from Truto. Truto's Static Gate sends the connection's requests from two fixed IP addresses: 134.199.136.185 and 143.244.208.28.

1. Make the connection link with a static gate: under Advanced settings → Static gate in the dashboard's Get connection link dialog, or with truto_static_gate_id when you create the link token through the API. If your environment has no static gate yet, ask Truto to set one up for it.
2. As well as the SQL from the connect screen, run this as ACCOUNTADMIN:

CREATE NETWORK POLICY IF NOT EXISTS TRUTO_NETWORK_POLICY ALLOWED_IP_LIST = ('134.199.136.185', '143.244.208.28');
ALTER USER TRUTO_SVC SET NETWORK_POLICY = TRUTO_NETWORK_POLICY;

A network policy set on a user takes the place of the account's own policy for that user, so this works even if your account allows only your own networks. These IPs are shared by every Truto customer's static gate: the policy keeps the internet at large out, and the key pair is what ties the user to your connection.

Set the user's network policy only on a connection that goes through a static gate. Without one, Snowflake refuses every request Truto makes.

Replace the key

Truto does not rotate a connection's key yet, and a reconnect keeps the key the connection already has. To move to a new key, connect a new account with a new connection link and run its SQL, then delete the old account in Truto and remove its key from the service user. The new account has a new ID, and the old account's synced data goes with it when it is deleted.

Connecting through the API

If you create the integrated account with POST /integrated-account instead of the connect screen:

1. If you allow the user only from Truto's Static Gate, put your static gate's truto_static_gate_id in the account's context.
2. Truto creates the key when it creates the account. The public key is in the response, under context.snowflake_key.public_key, and the key's name suffix under context.snowflake_key.key_id_short.
3. Run the SQL above with that key. Truto signs nothing with the key until it needs to, so creating the account does not need the key in Snowflake yet, but requests fail until it is there.
4. Call POST /integrated-account/run-post-install-actions with the account's id. It finishes the connection the way the connect screen does. Truto waits for this call, because those steps need the key to be in Snowflake.

Troubleshooting

• The key is not accepted. Snowflake does not have the key Truto signs with. Run the SQL from the connect screen again, for the same user, and check that the user is the one you entered.
• "Role … is not granted to this user". The role in Truto is not granted to the service user. Run GRANT ROLE TRUTO_ROLE TO USER TRUTO_SVC; with your role and user.
• "You must specify the warehouse…". Snowflake gives this when the warehouse does not exist, or when the role has no USAGE on it. Check the name, then run GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE TRUTO_ROLE; with your warehouse and role.
• The IP address is not allowed. Your account or the service user has a network policy, and the request did not come from an IP it allows. Connect through a static gate and allow both Static Gate IPs for the user; see Optional: allow the user only from Truto's Static Gate.

Connect with OAuth

Before you start

You'll be asked for these fields in Truto: Snowflake Account URL, Account Locator, Warehouse, Role, Client ID and Client Secret.

Find your Account URL and Account Locator

1. Sign in to your Snowflake Account.
2. In the left navigation bar at bottom, you will find your Account URL and Account Locator as shown below.
3. Copy and enter it in Truto interface.

Create the role Truto uses

1. Go to Worksheets from left navigation bar.
2. Click on the Add button and select SQL Worksheet as shown below.
3. Make sure that the Database is Snowflake and the Schema is Account_Usage.
4. Run the commands below. They create the role, let it use your warehouse, and let it read account usage data.

CREATE ROLE IF NOT EXISTS TRUTO_ROLE;
GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE TRUTO_ROLE;
GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE TRUTO_ROLE;

Create the OAuth application

1. Run the command below in the worksheet to create your OAuth application.

CREATE SECURITY INTEGRATION
         Truto
         TYPE = OAUTH
         OAUTH_CLIENT = CUSTOM
         OAUTH_CLIENT_TYPE = 'CONFIDENTIAL'
         OAUTH_REDIRECT_URI = 'https://api.truto.one/connect/snowflake/callback'
         ENABLED = TRUE
         PRE_AUTHORIZED_ROLES_LIST = ( 'TRUTO_ROLE' )
         OAUTH_ISSUE_REFRESH_TOKENS = TRUE
         OAUTH_REFRESH_TOKEN_VALIDITY = 7776000

If you need to raise the maximum value for Refresh Token's validity upto one year, refer to this link.

2. To view Client ID and Client Secret run the below command in worksheet.

SELECT SYSTEM$SHOW_OAUTH_CLIENT_SECRETS('TRUTO');

3. Copy your OAUTH_CLIENT_ID and OAUTH_CLIENT_SECRET and enter it in Truto interface as Client ID and Client Secret respectively.

Give the role to the person who connects

The person who clicks Connect signs in to Snowflake and allows access. Truto does not ask Snowflake for a particular role, so the connection gets that person's default role. It must be the role you enter in Truto's Role field. Snowflake also blocks OAuth for admin roles such as ACCOUNTADMIN and SECURITYADMIN by default, so the person's default role cannot be one of those.

Grant the role to that person and make it their default, replacing <username> with their Snowflake user name:

GRANT ROLE TRUTO_ROLE TO USER <username>;
ALTER USER <username> SET DEFAULT_ROLE = TRUTO_ROLE;

You can also do this in Snowsight, under Governance & security » Users & roles.

Connect

1. Fill in the fields in Truto and click on Connect. You'll now be asked to log in to your Snowflake account and allow access to Truto. Go through the steps.
2. Voila! You should now see a connection successful message. You can now close this tab.
3. Go to your Truto account and you should see the integration under Integrated Accounts. Click on the Account ID to change or edit the integration to suit your needs.

Optional: full user and role details

Two kinds of data need more than the grants above: user details (from SHOW USERS) and the full list of roles (from SHOW ROLES). Without MANAGE GRANTS ON ACCOUNT, Snowflake leaves most user details empty and lists only the roles TRUTO_ROLE has privileges on.

MANAGE GRANTS lets a role grant and revoke any privilege in the account, so many security teams do not allow it for an outside service. Add it only if you need that data:

GRANT MANAGE GRANTS ON ACCOUNT TO ROLE TRUTO_ROLE;

Uninstalling the Snowflake integration

To uninstall the Snowflake integration from your Truto account, select the Snowflake integration from the Integrations tab.

Click on the Settings tab in Snowflake Integration page

And then click on Uninstall. Uninstalling the integration will also remove all the Snowflake integrated accounts from your Truto account.