Skip to main content

Snowflake

For onboarding a new customer who has snowflake we need set of credentials and grants:

You can give Sundial Read-Only access to your existing Snowflake Schemas and Tables.

Alternatively you can create a separate Snowflake Database to store the tables which you would like to share with Sundial.

Few Steps to be followed before providing us with the credentials:

Create User, Role and attach permissions to the Tables shared with Sundial.

User and Role Setup​

Create a dedicated role and user for Sundial with appropriate permissions.

# Create Role for Sundial
CREATE ROLE SundialRole;
# Create User for Sundial with RSA key pair authentication
CREATE USER IF NOT EXISTS SundialUser
RSA_PUBLIC_KEY = '<rsa_public_key_content>'
DEFAULT_ROLE = SundialRole;

GRANT ROLE SundialRole to USER SundialUser;

To generate RSA key pair for authentication:

# Generate private key
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

# Generate public key
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

# Extract public key content (remove headers and format as single line)
cat rsa_key.pub | grep -v "BEGIN\|END" | tr -d '\n'

For Password Authentication (Deprecated)​

⚠️ Deprecation Notice: Password-based authentication is deprecated and will be removed in a future version. Please use RSA Key Pair Authentication for enhanced security. Snowflake is also blocking single-factor password authentication for improved security.

# Create User for Sundial with password authentication
CREATE USER IF NOT EXISTS SundialUser
PASSWORD = '<set_password_for_SundialUser>'
DEFAULT_ROLE = SundialRole;

GRANT ROLE SundialRole to USER SundialUser;

Read-Only Permissions​

Give Read-Only access to your Snowflake Database or Tables to the SundialRole.

GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE SundialRole;
GRANT USAGE ON DATABASE <database_name> TO ROLE SundialRole;
GRANT USAGE ON SCHEMA <schema_name> TO ROLE SundialRole;
GRANT CREATE STAGE ON SCHEMA <schema_name> TO ROLE SundialRole;
GRANT SELECT ON TABLE <table_name> TO ROLE SundialRole;

Write Permissions​

Sundial can store generated Standard Product Metrics into your Snowflake. If this is required, please provide the SundialRole access to write to a Snowflake Database.

# use role accountadmin;
# Create a new database for Sundial.
CREATE OR REPLACE DATABASE sundial_state_db;
CREATE SCHEMA IF NOT EXISTS sundial_state_db.Sundial_Scratch;

# Give read-write permissions to the SundialRole to the created Database.
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE SundialRole;
GRANT USAGE ON DATABASE sundial_state_db TO ROLE SundialRole;
GRANT USAGE ON SCHEMA sundial_state_db.Sundial_Scratch TO ROLE SundialRole;
GRANT CREATE STAGE ON SCHEMA sundial_state_db.Sundial_Scratch TO ROLE SundialRole;

GRANT ALL ON ALL schemas in database sundial_state_db TO ROLE SundialRole;
GRANT ALL ON ALL TABLES IN SCHEMA sundial_state_db.Sundial_Scratch TO ROLE SundialRole;
GRANT ALL ON future schemas in database sundial_state_db TO ROLE SundialRole;
GRANT ALL ON future tables in schema sundial_state_db.Sundial_Scratch to ROLE SundialRole;

These grants can be extended to views and other objects in DB using the above commands by making necessary keyword changes.

Credentials to be shared with Sundial​

Please share the following credentials and details with Sundial over a secure Vault or Secrets store.

Common Variables​

SNOWFLAKE_URL : URL to the snowflake account
SNOWFLAKE_ACCOUNT : Account number (also found within the link)
SNOWFLAKE_USER : User id for the user created for Sundial
SNOWFLAKE_DATABASE : Database Names for Read-Only and Read-Write database
SNOWFLAKE_SCHEMA : Schema of the table
SNOWFLAKE_WAREHOUSE : Warehouse name provided to Sundial
SNOWFLAKE_ROLE : Role provided to Sundial

RSA Key Pair Authentication​

For RSA key pair authentication, additionally provide:

SNOWFLAKE_PRIVATE_KEY : Private key content (PEM format)
SNOWFLAKE_PRIVATE_KEY_PASSPHRASE : Passphrase for the private key (if encrypted)

Note: The private key should be provided in PKCS#8 PEM format. If using an encrypted private key, also provide the passphrase.

Password Authentication (Deprecated)​

For password-based authentication, additionally provide:

SNOWFLAKE_PASSWORD : Password for the Sundial User