Unlocking Secrets for Snowflake Native Applications

Snowflake Secrets

Snowflake Secrets enables secure storage and retrieval of sensitive credentials, tokens, or keys, helping developers to securely store secrets and access them within the Snowflake environment without exposing sensitive details to users or logs.

In this article, we dive into a custom procedure written in Python for Snowflake Native Applications. This procedure simplifies and secures the retrieval of secrets created and managed by the Application Consumer in Snowflake, enabling seamless integration with external services and APIs.

What Problem Does This Code Solve?

Native Applications often require integration with external systems such as APIs or authentication providers. These integrations typically necessitate credentials like API tokens, OAuth2 access tokens, or username-password combinations. Snowflake Secrets are only accessible through API, you cannot get them with SQL: https://docs.snowflake.com/en/developer-guide/external-network-access/secret-api-reference
Moreover, Snowflake Secrets are always tight to an External Access Integration.

The custom procedure solves the following problems:

1. Centralized Secrets Management: Provides a unified approach to manage secrets for Native Application. The consumer is in full control, define, grant, revoke access to the secrets.
2. Secure Access: Ensures secrets are securely retrieved only when required, avoiding unnecessary exposure.
3. Support for Multiple Secret Types: Handles various secret types such as OAuth2 tokens, generic strings, and username-password.
4. Ease of Use in Applications: Simplifies secret retrieval for Native Applications running inside Snowflake.

Code Explanation

The custom procedure encapsulates the process of securely retrieving secrets stored in Snowflake. Here's a step-by-step breakdown of its functionality:

1. Procedure Definition

The procedure is defined using Snowflake’s Python Stored Procedure in the Native Application Setup script:

CREATE OR REPLACE PROCEDURE app_public.getsecrets(eai varchar, secname varchar)

• Parameters:
• eai (External Access Integration): Specifies the external access integration for securely accessing external services.
• secname (Secret Name): The fully qualified name of the secret to retrieve.
• Return Type: The procedure returns the secret as a string or a dictionary.

2. Creating an Inner Procedure: _getSecrets

The getsecrets procedure dynamically creates another stored procedure, _getSecrets, to handle the specific logic of retrieving secrets. This allows the dynamic behavior needed, remember the secrets can be retrieved only with an External Access Integration AND through API.

CREATE OR REPLACE PROCEDURE _getSecrets()
RETURNS VARCHAR
LANGUAGE PYTHON
EXTERNAL_ACCESS_INTEGRATIONS = ({eai})
SECRETS = ('secinfo' = {secname})
PACKAGES = ('snowflake-snowpark-python')

• Key Features:
• External Access Integration: Grants _getSecrets access to external systems as defined by the eai parameter.
• Secrets Binding: Binds the secret (secname) to the secinfo alias for secure reference.
• Dynamic Secret Type Handling: Retrieves the secret based on its type.

3. Secret Retrieval Logic

The _getSecrets procedure uses the _snowflake module to fetch secrets:

secrettype = _snowflake.get_secret_type("secinfo")
if secrettype == "OAUTH2":
    return _snowflake.get_oauth_access_token("secinfo")
elif secrettype == "GENERIC_STRING":
    return _snowflake.get_generic_secret_string("secinfo")
elif secrettype == "PASSWORD":
    username_password_object = _snowflake.get_username_password("secinfo")
    username_password_dictionary = dict()
    username_password_dictionary["Username"] = username_password_object.username
    username_password_dictionary["Password"] = username_password_object.password
    return username_password_dictionary
else:
    return "Secret type not supported."

This logic ensures flexibility by supporting three secret types:

1. OAuth2 Tokens: Returns an access token for OAuth2 integrations.
2. Generic Strings: Retrieves any generic string stored as a secret.
3. Passwords: Returns a dictionary containing the username and password.

4. Granting Necessary Permissions

The grants permissions for the Native Application (ran by the consumer):

• Granting External Access Integrations, Database, and Secrets to the application.

GRANT USAGE on DATABASE SECRET_DB to APPLICATION <MY APP>;
GRANT USAGE on SCHEMA SECRET_DB.SECRET_SC to APPLICATION <MY APP>;
GRANT USAGE ON INTEGRATION DUMMY_SECRET_EAI TO APPLICATION <MY APP>;
GRANT READ ON SECRET SECRET_DB.SECRET_SC.MYGENSECRET TO APPLICATION <MY APP>;

5. Invocation Example

The procedure is invoked using:

CALL <MY APP>.<APP SCHEMA>.GETSECRETS('DUMMY_SECRET_EAI', 'SECRET_DB.SECRET_SC.MYGENSECRET');

Benefits of This Approach

1. Secure by Design: Secrets remain encrypted and are accessible only to authorized application(s).
2. Dynamic and Reusable: The procedure supports dynamic binding of external integrations and secrets, enabling reuse across various scenarios.
3. Integration with Snowflake Ecosystem: Leverages Snowflake’s native features for seamless integration with external services.

By centralising secrets retrieval and integrating with Snowflake Native Applications, this approach enhances security, simplifies application development, and adheres to best practices for managing sensitive credentials..

Code

Implementation in the setup script requires escaping large parts of the procedure:

CREATE OR REPLACE PROCEDURE app_public.getsecrets(eai varchar,secname varchar)
RETURNS VARCHAR
LANGUAGE PYTHON
PACKAGES = ('snowflake-snowpark-python')
RUNTIME_VERSION = 3.9
HANDLER = 'main'
AS '
import logging
session_logger = logging.getLogger("snowflake.snowpark.session")
session_logger.setLevel(logging.INFO)
logger = logging.getLogger("python_logger")
def main(session, eai, secname):   
   logger.info("GET SECRET FOR EAI: \'"+eai+"\' ---- SECRET: \'"+secname+"\'")
   session.sql(f"""
CREATE OR REPLACE PROCEDURE _getSecrets()
RETURNS VARCHAR
LANGUAGE PYTHON
RUNTIME_VERSION = 3.9
EXTERNAL_ACCESS_INTEGRATIONS = ({eai})
SECRETS = (\'secinfo\' = {secname}) 
PACKAGES = (\'snowflake-snowpark-python\')
HANDLER = \'get\'
AS 
$$
import _snowflake
def get(session):
   secrettype = _snowflake.get_secret_type("secinfo")
   if secrettype == "OAUTH2":
      return _snowflake.get_oauth_access_token("secinfo")
   elif secrettype == "GENERIC_STRING":
      return _snowflake.get_generic_secret_string("secinfo")
   elif secrettype == "PASSWORD":
      username_password_object = _snowflake.get_username_password("secinfo");
      username_password_dictionary = dict()
      username_password_dictionary["Username"] = username_password_object.username
      username_password_dictionary["Password"] = username_password_object.password
      return username_password_dictionary
   else:
      return "Secret type not supported, only OAUTH2, GENERIC_STRING or PASSWORD are allowed."    
$$    
""").collect()
   return session.sql("CALL _getSecrets()").collect()[0][0]
';   
-- FOR TESTING PURPOSE ONLY ----
GRANT USAGE ON PROCEDURE app_public.getsecrets(varchar,varchar) TO APPLICATION ROLE APP_USERS;

Granting the secrets to the Application:

GRANT USAGE on DATABASE SECRET_DB to APPLICATION <MY APP>;
GRANT USAGE on SCHEMA SECRET_DB.SECRET_SC to APPLICATION <MY APP>;
GRANT USAGE ON INTEGRATION DUMMY_SECRET_EAI TO APPLICATION <MY APP>;
GRANT READ ON SECRET SECRET_DB.SECRET_SC.MYGENSECRET TO APPLICATION <MY APP>;