# 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

{% hint style="info" %}
For more information on Snowflake key-pair authentication please refer to Snowflake documentation <https://docs.snowflake.com/en/user-guide/key-pair-auth>
{% endhint %}

### 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](https://developer.mediarithmics.io/data-streams/data-warehouse/preliminary-setup/..#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 :&#x20;

* If you wish to restrict access to specific tables or views the minimum permissions required are :&#x20;
  * `USAGE on DATABASE`
  * `USAGE on SCHEMA`
  * `SELECT on TABLES` (or VIEWS)&#x20;
* If you don't need the working schema you can omit the last part of the script

```sql
-- =============================================================
-- 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&#x20;

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

```bash
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)

```bash
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 :&#x20;

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

{% hint style="info" %}
Nota bene :&#x20;

* 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
    {% endhint %}

### 3. Edit a credentials file

Now you need to generate a JSON file with the following format :&#x20;

```json
{
"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.