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;

For RSA Key Pair Authentication (Recommended)

# 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, Schema, Tables and Views to the SundialRole.

-- Grant warehouse access
GRANT USAGE ON WAREHOUSE <warehouse_name> TO ROLE SundialRole;

-- Grant database and schema access
GRANT USAGE ON DATABASE <database_name> TO ROLE SundialRole;
GRANT USAGE ON SCHEMA <database_name>.<schema_name> TO ROLE SundialRole;
GRANT CREATE STAGE ON SCHEMA <database_name>.<schema_name> TO ROLE SundialRole;

Table Access

Grant access to specific tables and optionally future tables:

-- Grant access to specific table(s)
GRANT SELECT ON TABLE <database_name>.<schema_name>.<table_name> TO ROLE SundialRole;

-- Grant access to all existing tables in schema
GRANT SELECT ON ALL TABLES IN SCHEMA <database_name>.<schema_name> TO ROLE SundialRole;

-- Grant access to future tables (for new tables created in the schema)
GRANT SELECT ON FUTURE TABLES IN SCHEMA <database_name>.<schema_name> TO ROLE SundialRole;

View Access

Grant access to specific views and optionally future views:

-- Grant access to specific view(s)
GRANT SELECT ON VIEW <database_name>.<schema_name>.<view_name> TO ROLE SundialRole;

-- Grant access to all existing views in schema
GRANT SELECT ON ALL VIEWS IN SCHEMA <database_name>.<schema_name> TO ROLE SundialRole;

-- Grant access to future views (for new views created in the schema)
GRANT SELECT ON FUTURE VIEWS IN SCHEMA <database_name>.<schema_name> TO ROLE SundialRole;

Materialized View Access

Grant access to specific materialized views and optionally future materialized views:

-- Grant access to specific materialized view(s)
GRANT SELECT ON MATERIALIZED VIEW <database_name>.<schema_name>.<materialized_view_name> TO ROLE SundialRole;

-- Grant access to all existing materialized views in schema
GRANT SELECT ON ALL MATERIALIZED VIEWS IN SCHEMA <database_name>.<schema_name> TO ROLE SundialRole;

-- Grant access to future materialized views (for new materialized views created in the schema)
GRANT SELECT ON FUTURE MATERIALIZED VIEWS IN SCHEMA <database_name>.<schema_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