Snowflake programmatic access token authentication

When creating an environment for a Snowflake data warehouse, you can choose to use Snowflake programmatic access tokens (PAT) for authentication. To use this authentication method, the PAT must first be generated within Snowflake, as described below. For more information, read the Snowflake documentation.

---

Generating the Snowflake PAT

To use PAT authentication, you must first configure a Snowflake user with the appropriate role and security policies. You will also need to generate a PAT for this user. The following SQL scripts provide examples of how to do this. You will need to modify the scripts to suit your own requirements, such as replacing the IP addresses in the network policy with the static IP addresses of your Matillion ETL instance.

Create user, role, and schema

USE ROLE SECURITYADMIN;

-- Create a dedicated role for Matillion.
CREATE ROLE MATILLION_SERVICE_ROLE;

-- Create the service user (no password needed for PATs).
CREATE USER MATILLION_SERVICE_USER
  TYPE = SERVICE
  DEFAULT_ROLE = MATILLION_SERVICE_ROLE;

-- Grant the role to the user.
GRANT ROLE MATILLION_SERVICE_ROLE TO USER MATILLION_SERVICE_USER;

-- Create a schema and grant role privileges.
USE ROLE SYSADMIN;
CREATE SCHEMA MATILLION_DATABASE.MATILLION_SCHEMA;
GRANT USAGE ON WAREHOUSE MATILLION_COMPUTE_WH TO ROLE MATILLION_SERVICE_ROLE;
GRANT USAGE ON DATABASE MATILLION_DATABASE TO ROLE MATILLION_SERVICE_ROLE;
GRANT USAGE ON SCHEMA MATILLION_DATABASE.MATILLION_SCHEMA TO ROLE MATILLION_SERVICE_ROLE;
GRANT CREATE TABLE ON SCHEMA MATILLION_DATABASE.MATILLION_SCHEMA TO ROLE MATILLION_SERVICE_ROLE;

The GRANT statements above are examples only, to illustrate giving the new user account permissions to the needed warehouses, databases, schemas, etc. You should modify these grants to reflect your needs.

Configure security policies and apply to user

USE ROLE ACCOUNTADMIN;

-- Create mandatory network policy to restrict access by IP address.
CREATE NETWORK POLICY MATILLION_AGENT_NETWORK_POLICY
  ALLOWED_IP_LIST = ('<IP_ADDRESS>', '<IP_ADDRESS>'); -- Replace with your agent's static IPs

-- Apply network policy to the user.
ALTER USER MATILLION_SERVICE_USER
  SET NETWORK_POLICY = MATILLION_AGENT_NETWORK_POLICY;

-- Create an authentication policy to allow PATs.
CREATE AUTHENTICATION POLICY MATILLION_PAT_AUTHENTICATION_POLICY
  AUTHENTICATION_METHODS = ('PROGRAMMATIC_ACCESS_TOKEN');

-- Apply authentication policy to the user.
ALTER USER MATILLION_SERVICE_USER
  SET AUTHENTICATION POLICY MATILLION_PAT_AUTHENTICATION_POLICY;

Generate the access token

Token expiration is set to 90 days in the example below, but you can adjust this as needed. You should plan token rotation and perform the rotation before the token expires, to avoid any disruption of service.

USE ROLE ACCOUNTADMIN;

-- IMPORTANT: Copy the token immediately. It will not be shown again.
ALTER USER MATILLION_SERVICE_USER
  ADD PROGRAMMATIC ACCESS TOKEN MATILLION_AGENT_ACCESS_TOKEN
  ROLE_RESTRICTION = 'MATILLION_SERVICE_ROLE' -- Scopes token to this role's privileges.
  DAYS_TO_EXPIRY = 90;                        -- Sets the token's lifespan.