Snowflake’s New TYPE Property: A Step-by-Step Guide to Elevating Security with Key-Pair Authentication

In light of recent data breaches, including a notable incident where customer data from prominent companies was compromised, Snowflake has taken significant steps to bolster security and provide more granular control over user management. One such enhancement is the introduction of the `TYPE` property for users in Snowflake, which offers a new layer of customization and security for managing user accounts.

Quick Overview of the ‘TYPE’ Property

PERSON:

Users do not have restrictions when the ‘TYPE’ property is set to ‘PERSON’, ‘NULL’, or when ‘TYPE’ is unset.

NULL:

Functions the same as ‘PERSON’, with no additional restrictions.

SERVICE:

This type is designed to improve the security posture of non-interactive use cases. Users with the ‘TYPE’ property set to ‘SERVICE’ have the following characteristics:

They cannot log in using a password.
They cannot log in using SAML SSO.
They cannot enroll in MFA.
They are not subject to authentication policy MFA enforcement.
They cannot have certain properties such as ‘FIRST_NAME’, ‘LAST_NAME’, ‘PASSWORD’, etc.
Commands like ‘ALTER USER RESET PASSWORD’ and ‘ALTER USER SET DISABLE_MFA = TRUE’ cannot be used.

Source

In this post, we’ll focus on the ‘SERVICE’ type, which enforces these users to utilize either Key-Pair Authentication or AUTH0, significantly reducing the risk of data breaches. In this blog post, we will focus on Key-Pair Authentication.

Benefits of Using Key-Pair Authentication

Enhanced Security: By eliminating the need for passwords, which are often a target for cyber attacks, Key-Pair Authentication provides a more secure alternative.
Automated Processes: Perfect for non-interactive environments where manual login is unnecessary, ensuring secure automation.
Compliance: Key-Pair Authentication helps meet strict security standards and compliance requirements, making it a preferred choice for enterprise environments.

Step-by-Step Guide: Creating Key-Pair Authentication for a ‘SERVICE’ User

Creating a Service User

To create a ‘SERVICE’ user, use the following command:

CREATE USER python_job_service TYPE = SERVICE;

You can customize this user by adding more parameters such as setting a default warehouse or role. For example:

CREATE USER python_job_service TYPE = SERVICE DEFAULT_ROLE = 'DATA_ENGINEER' DEFAULT_WAREHOUSE = 'COMPUTE_WH';

Refer to the Snowflake documentation for additional options.

Creating a Key-Pair: Public and Private Keys

Snowflake supports key-pair authentication with the following drivers:

Python
JDBC
ODBC
Go
Node.js
.NET
Spark
SnowSQL

Creating a Private Key:

Use the following command to create a private key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out python_job_service_key.p8

Creating a Public Key:

Next, create a public key using this command:

openssl rsa -in python_job_service_key.p8 -pubout -out python_job_service_key.pub

Assigning the Public Key to the User

To assign the public key to your user, first, edit the ‘python_job_service_key.pub’ file. Copy the content between the ‘BEGIN PUBLIC KEY’ and ‘END PUBLIC KEY’ lines.

Then, execute the following command:

ALTER USER python_job_service SET RSA_PUBLIC_KEY='ABCDEFG123...';

Configuring the Python Driver to Use the ‘python_job_service’ User

Create a new Python file and install the Snowflake Python connector:

pip install snowflake-python-connector

Python Code:

import os
import snowflake.connector as sc

# Specify the path to your private key file
private_key_file = 'private_key_filepath’  # Example: '/path/to/private_key.p8'

private_key_file_pwd = '<private_key_password>'

# Connection parameters for Snowflake
conn_params = {

    'Account': '<organization_name>-<account_name>',
    'user': 'python_job_service',
    'private_key_file': private_key_file,
    'private_key_file_pwd': private_key_file_pwd, 
    'warehouse': 'COMPUTE_WH'
}

# Establish a connection to Snowflake
ctx = sc.connect(**conn_params)

cs = ctx.cursor()

print(cs)

# Execute a simple query to test the connection
cs.execute("SELECT current_version()")
version = cs.fetchone()

print("Connected successfully. Snowflake version:", version[0])

Securely Storing the Private Key with AWS Secrets Manager

When deploying the Python file that connects to Snowflake, it’s crucial to store the private key securely. AWS Secrets Manager is a managed service that helps you store and access sensitive information like private keys securely.

Using AWS Secrets Manager

1. Store the Private Key:

aws secretsmanager create-secret --name python_service_key --secret-string file://path_to_your_private_key

2. Retrieve and Use the Private Key in Python:

import boto3

def get_secret(secret_name):
    client = boto3.client('secretsmanager')
    response = client.get_secret_value(SecretId=secret_name)
    return response['SecretString']

private_key = get_secret("python_service_key")

# Pass 'private_key' to the Snowflake connector in the 'conn_params'.

Conclusion

Snowflake’s introduction of the ‘SERVICE’ type with enforced Key-Pair Authentication is a robust step forward in enhancing security, especially in non-interactive use cases.
By following the steps outlined in this post and utilizing AWS Secrets Manager, you can ensure that your private keys are securely managed and your Snowflake environment remains protected against unauthorized access.

Snowflake continues to demonstrate a strong commitment to security, helping organizations safeguard their data in an increasingly complex digital landscape.