Snowflake

Creating a connection to Snowflake requires to set up key-pair authentication.

Steps

1. Service account and permissions setup

2. Configure key-pair authentication

3. Create and format a credentials JSON file

For more information on Snowflake key-pair authentication please refer to Snowflake documentation https://docs.snowflake.com/en/user-guide/key-pair-auth

1. Service account and permissions setup

For the initial set up, start with the SQL script template below. The script will :

• Create a dedicated service account, role, and warehouse for mediarithmics to use

• Give the service account read only access on a database of your choice and all its content

• Create a working schema inside the database and give full access (read/write) to the service account on the schema

You can edit this template to match your needs for instance :

• If you want to set up access to multiple databases

• If you wish to restrict access to specific tables or views the minimum permissions required are :

  • USAGE on DATABASE

  • USAGE on SCHEMA

  • SELECT on TABLES (or VIEWS)

• If you don't need the working schema you can omit the last part of the script

-- =============================================================
-- Setup variables - update these values for your environment
-- =============================================================
SET mics_username          = 'MICS_USER';
SET mics_default_warehouse = '<warehouse>';
SET mics_database          = '<database>';
SET mics_default_namespace = '<database.schema>';
SET mics_default_role      = 'MICS_ROLE';

-- =============================================================
-- Switch to admin role
-- =============================================================
USE ROLE ACCOUNTADMIN;

-- =============================================================
-- Provision resources: warehouse, role and service user
-- =============================================================
CREATE WAREHOUSE IF NOT EXISTS identifier($mics_default_warehouse);
CREATE ROLE IF NOT EXISTS identifier($mics_default_role);

CREATE USER IF NOT EXISTS identifier($mics_username)
    TYPE              = SERVICE
    DEFAULT_WAREHOUSE = $mics_default_warehouse
    DEFAULT_NAMESPACE = $mics_default_namespace
    DEFAULT_ROLE      = $mics_default_role;

-- =============================================================
-- Role assignments
-- =============================================================
GRANT ROLE identifier($mics_default_role) TO ROLE SYSADMIN;
GRANT ROLE identifier($mics_default_role) TO USER identifier($mics_username);

-- =============================================================
-- Warehouse access
-- =============================================================
GRANT USAGE ON WAREHOUSE identifier($mics_default_warehouse) TO ROLE identifier($mics_default_role);

-- =============================================================
-- Read-only access on the database, schemas, tables and views
-- (includes future objects to cover tables/views created later)
-- =============================================================
GRANT USAGE  ON DATABASE identifier($mics_database)                       TO ROLE identifier($mics_default_role);
GRANT USAGE  ON ALL SCHEMAS       IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON ALL TABLES        IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON ALL VIEWS         IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON FUTURE TABLES     IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);
GRANT SELECT ON FUTURE VIEWS      IN DATABASE identifier($mics_database)  TO ROLE identifier($mics_default_role);

-- =============================================================
-- Dedicated schema with full ownership
-- =============================================================
USE identifier($mics_database);
CREATE SCHEMA IF NOT EXISTS mediarithmics_schema;
GRANT OWNERSHIP ON SCHEMA mediarithmics_schema TO ROLE identifier($mics_default_role);

2. Configure key-pair authentication

Use the following command to generate a private key (called rsa_key.p8)

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

Then use the following command to generate a public key (called rsa_key.pub linked to the private key called rsa_key.pub stored in the current directory)

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Then, from the Snowflake interface or the Snowflake CLI run the following SQL query to assign the public key generated at step 1 (rsa_key.pub) to the service account created for mediarithmics to use :

ALTER USER <MICS_USER> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Nota bene :

• Exclude the public key delimiters in the SQL statement

• To run this query you will need one of the following role/privilege

  • The MODIFY PROGRAMMATIC AUTHENTICATION METHODS or OWNERSHIP privilege on the user

  • The SECURITYADMIN role or higher

3. Edit a credentials file

Now you need to generate a JSON file with the following format :

{
"user" : "MICS_USER",
"account" : "ORGANIZATION_NAME-ACCOUNT_NAME",
"database" : "DATABASE",
"private_key" : "-----BEGIN PRIVATE KEY-----\nMII..."
}

• "user" : the user you associated the public key with

• "account" : the "account" property is what is called "Account identifier" in Snowflake. It's usually the organization name and the account name hyphenated

• "database" : the database containing the data you want to make available to mediarithmics

• "private key" : the private key generated in step one and associated to the user public key. Be ware of the formatting of the private key : the key must be in on a single line, this means you should use a "\n" separator at each return to line of the private key.