Connecting to Snowflake using Key Pair Authentication
Why do this?
It is often a practice of embedding one’s credentials within code for authentication during development and testing phases. However, such practice is avoided for production grade code as it exposes some vulnerabilities.
While development teams are adept to the practice of embedding credentials in the bash profile of production servers; Data Scientists however do not have that luxury when they operate on Jupyter notebooks and other AWS services like sagemaker.
It would be my best guess that snowflake tried to overcome this issue with their key-pair authentication offering.
In a nutshell, key-pair authentication involves the use of a public and a private key. The User often creates this key pair and provides the public key to the server with which he wants to communicate with. During login, the User then proves his authenticity by presenting the associated private key and gains access to the Server. Refer to the link for more details.
If enabled, Snowflake expects to see the associated private key that matches the public key stored against the specific user. This avoids the exposure of your credentials and makes hacking much more difficult as the key pair values are difficult to re-generate.
This article will be a guiding light for all those who have set out on this transformational journey with security in mind.
The following technologies/platforms have been used for this demonstration:
- EC2 Linux
- python 3.6
- snowflake-connector-python package
- snowflake-sqlalchemy package
Step 1: Use the Appropriate Role
Modifying user data in Snowflake requires the highest of privileges; which ACCOUNTADMIN role can provide.
Thus switch over to ACCOUNTADMIN role:
USE ROLE ACCOUNTADMIN;
One way to verify the role used it to check the user setting bar:
Step 2: Creating a Service Account on Snowflake
Any Users who wish to login and run queries will use this service account and present the private key that we will generate.
Let us thus create a new user in Snowflake:
Syntax:
CREATE OR REPLACE USER <user_name>DEFAULT_ROLE = <role_name>PASSWORD=<password>;
We will initially provide a password to this user and then switch over to using key pair.
The other way to identify if a user is using a key-pair or username password is to look at the RSA_PUBLIC_KEY_FP attribute for the user.
Note: Since we haven’t yet set up the Key Pair authentication for this user; his RSA_PUBLIC_KEY_FP will be NULL.
Optionally you can also test if the credentials provided work or not:
Step 3: Generate the keys
Key Generation is usually done with the help of openssl opensource package. With a few parameters it is easy to generate the keys and not worry about the nitty and gritty details.
Command line:
The instructions provided in this link are useful to generate the keys quickly.
However, this needs to be performed every time you are asked to rotate the keys.
Programmatically:
With the help of Python’s cryptography package the above steps can be automated quite easily.
We start by creating a few environment variables in the bash profile:
Once executed the code will create 2 files:
- Private key file (.p8 extension)
- Public Key file (.pub extension)
Step 4: Update Service Account in Snowflake
Armed with the public key that was generated (by either the CLI or programmatically) head over to snowflake and update the user’s RSA_PUBLIC_KEY attribute.
ALTER USER <user_name>SET RSA_PUBLIC_KEY=’public_ssh_key’;
Running the Describe method will show the public key associate to the user:
Step 5: Connect and Execute queries
This approach will demand a slight change to the usual way of connecting to Snowflake via the python connector.
Turns out adding private_key attribute to the connect() method is all that is needed for the key pair authentication.
We used the default warehouse that Snowflake provides to query the TPCH demo Database.
You can now perform your development and analysis without exposing your credentials and have this published to prod with minimal code changes.
Future Reading:
Snowflake also provides some insight into automating the rotation of keys in the link.
Rotating SSH keys can also be automated.
