Snowflake

💡 This is a step by step guide to integrate Snowflake and Querio. We use key-pair authentication to create a secure, non-human, persistent connection—no OAuth refresh tokens or session timeouts.

Before you start

• A Snowflake admin role with rights to create users/roles and manage grants (typically SECURITYADMIN and USERADMIN).

• The RSA public key you want to associate with the Snowflake service user (2048-bit or stronger). We’ll send you this key, however, if your policy requires, you may generate your own key pair and keep the private key on your side.

1) Create a dedicated service user

What: A non-human user that Querio will use to connect (type SERVICE).

How (as USERADMIN) execute the following SQL in a worksheet :

CREATE USER QUERIO_SVC
  LOGIN_NAME = QUERIO_SVC
  TYPE = SERVICE
  DEFAULT_ROLE = QUERIO_ROLE
  DEFAULT_WAREHOUSE = QUERIO_WH
  DEFAULT_NAMESPACE = MY_DATABASE.PUBLIC
  COMMENT = 'Service user for Querio integration'

If the user already exists, you can ALTER USER to set the properties.

https://docs.snowflake.com/en/sql-reference/sql/create-user

Note: TYPE = SERVICE clearly identifies this as an application/service identity.

2) Create a least-privilege role and grants

What: A role with only the privileges needed to read your chosen objects.

How (as SECURITYADMIN or ACCOUNTADMIN) execute the following SQL in a worksheet:

-- Role
CREATE ROLE IF NOT EXISTS QUERIO_ROLE;

-- Warehouse access (query execution)
GRANT USAGE ON WAREHOUSE QUERIO_WH TO ROLE QUERIO_ROLE;
-- If you don't know your current warehouse, run SELECT CURRENT_WAREHOUSE();
-- If you want to see all your warehouses, run SHOW WAREHOUSES;

-- Data access (Give us access to all necessary databases and schemas)
GRANT USAGE ON DATABASE MY_DATABASE TO ROLE QUERIO_ROLE;
GRANT USAGE ON SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;

-- Read-only data privileges
GRANT SELECT ON ALL TABLES IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;
GRANT SELECT ON ALL VIEWS  IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;

-- Keep future objects covered
GRANT SELECT ON FUTURE TABLES IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;
GRANT SELECT ON FUTURE VIEWS  IN SCHEMA MY_DATABASE.PUBLIC TO ROLE QUERIO_ROLE;

-- Assign role to the service user
GRANT ROLE QUERIO_ROLE TO USER

Important: Follow least privilege and scope grants only to the databases/schemas you intend Querio to read. Use future grants to avoid drift. 

https://docs.snowflake.com/en/user-guide/security-access-control-configure

Tip: You typically do not need OPERATE on the warehouse if AUTO_RESUME is enabled (default). Queries with USAGE can trigger auto-resume. 

https://docs.snowflake.com/en/sql-reference/sql/alter-warehouse?utm_source=chatgpt.com

3) Associate the RSA public key to the service user

What: Register the public key so Snowflake can verify key-pair logins.

How (as SECURITYADMIN or a role with the proper privilege):

-- Assign the public key (omit PEM header/footer lines)
ALTER USER QUERIO_SVC SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

-- Verify the fingerprint
DESC USER QUERIO_SVC;
-- Check the RSA_PUBLIC_KEY value

Note: Snowflake requires the base64 content of the public key without the -----BEGIN/END PUBLIC KEY----- lines. Use DESCRIBE USER to confirm the fingerprint. 

https://docs.snowflake.com/en/user-guide/key-pair-auth

Optional – you generate the key pair internally

# Encrypted PKCS#8 private key (recommended)
openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
# Public key (PEM)
openssl rsa -in rsa_key.p8 -pubout -out

Store the private key securely per your policy.

4) Share the connection metadata

Provide the following to complete the connection:

{
  "account":   "orgname-account_name",   
  // To find your account run SELECT CURRENT_ORGANIZATION_NAME() || '-' || CURRENT_ACCOUNT_NAME();
  "user":      "QUERIO_SVC",
  "warehouse": "QUERIO_WH",
  "database":  "MY_DATABASE",
  "schema":    "PUBLIC",
  "role":      "QUERIO_ROLE"
}