Snowflake

Covers Snowflake on any cloud and region. The gateway signs in as a service user with a key pair, so there is no password to store.

Before you start

• A Snowflake administrator who can use the ACCOUNTADMIN role.
• A warehouse for Portward’s queries. A small warehouse that suspends itself is enough.
• openssl on your computer, to make the key pair.
• Your account’s host on the gateway’s allowed hosts, such as orgname-accountname.snowflakecomputing.com (Allow the host).

Network access

The gateway connects to <account>.snowflakecomputing.com over HTTPS. If your account has a network policy, add the gateway’s outbound address to it (Network access says which address that is).

Create a read-only login

Make the key pair on your computer. The gateway can’t read a private key that has a passphrase, so make it without one. Once you paste it in setup, the gateway stores it encrypted.

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out portward_reader.p8 -nocrypt
openssl rsa -in portward_reader.p8 -pubout -out portward_reader.pub

Then run this in a Snowflake worksheet. Replace portward_wh, sales and sales.public with your warehouse, database and schema, and <public key> with the text of portward_reader.pub on one line, without its BEGIN and END lines.

USE ROLE ACCOUNTADMIN;
CREATE ROLE IF NOT EXISTS portward_reader;
GRANT USAGE ON WAREHOUSE portward_wh TO ROLE portward_reader;
GRANT USAGE ON DATABASE sales TO ROLE portward_reader;
GRANT USAGE ON SCHEMA sales.public TO ROLE portward_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA sales.public TO ROLE portward_reader;
GRANT SELECT ON FUTURE TABLES IN SCHEMA sales.public TO ROLE portward_reader;
CREATE USER IF NOT EXISTS portward_reader TYPE = SERVICE DEFAULT_ROLE = portward_reader
  DEFAULT_WAREHOUSE = portward_wh RSA_PUBLIC_KEY = '<public key>';
GRANT ROLE portward_reader TO USER portward_reader;

Grant this role nothing else. The check looks at every role it inherits, and PUBLIC too, so a write or CREATE privilege on that database or schema granted to PUBLIC refuses the connection as well.

Connect it in setup

Choose Snowflake and fill in Name (the name people and the AI see, such as sales), Account (ORGNAME-ACCOUNTNAME, from your Snowflake URL), User (portward_reader), Private key (the whole of portward_reader.p8), Warehouse, Database, Schema and Role (portward_reader; empty means the user’s default role).

What the setup check tests

Before it saves, setup connects as the service user and checks, in this order:

1. The account is written like ORGNAME-ACCOUNTNAME (“account: like ORGNAME-ACCOUNTNAME, from your Snowflake URL”), and its host is on the allowed hosts.
2. The private key can be read (“the private key can’t be read: paste the unencrypted PKCS#8 key (BEGIN PRIVATE KEY)”).
3. It can sign in, and the database and schema exist and the role can use them (“database or schema not found (or not granted)”).
4. The role isn’t an administrator, and neither is any role it inherits or PUBLIC (“connect with a read-only role, not an administrator”). ACCOUNTADMIN, SYSADMIN, SECURITYADMIN, USERADMIN and ORGADMIN are refused, and so are MANAGE GRANTS, CREATE ROLE, CREATE USER and CREATE DATABASE on the account.
5. None of those roles owns, modifies or can create objects in the database or schema (“use a read-only role”). The gateway switches off secondary roles in its sessions, so they can’t add privileges.
6. A token computed inside Snowflake matches the gateway’s own (“in-database tokens don’t match Python”).
7. It lists the schema’s tables and views. A table the roles could write to is listed as one it could change, and can’t be exposed.

If the key or the user stops working later, queries say “Snowflake refused the saved sign-in”, and an admin reconnects the source in setup.