How to Automatically Generate a Key Pair for Snowflake

Key Pair is one of the four types of authentication supported by Snowflake (basic username-password, key pair, SSO, and OAuth). As described by Snowflake, the process is manual, error-prone, and very repetitive when you have to do it for multiple clients, users or accounts.

I’ve been planning for a while to try automating the process, and I was able to do it today. The whole code is on GitHub, use it as you wish.

Unencrypted Private Key and Public Key

If you can still connect to Snowflake with basic authentication (i.e. username and password), you may quickly generate an unencrypted private key and a public key with the command below:

python key-pair-generator.py
   --a www55555 --u MyUsername --p MyPassword123_

You passed here just your Snowflake account, username and password. What the code did is it generated a .ssh\rsa_key_N.p8 unencrypted private key file and a .ssh\rsa_key_N.pub public key file in your home directory (under \Users\yourname folder in Windows). N is 1, 2, 3 or another number, that just makes sure we always generate new files and overwrite nothing. And you may provide another file prefix than “rsa_key” with the “--f” option.

We also extracted the content from the public key file, skipping the first and the last lines, and used it in this SQL statement:

alter user MyUsername
   set rsa_public_key = 'MIIBIjANBgkqh...';

Then we tried to connect with the new key pair, passing the full path of the private key file as a connection parameter. I just assume you used the ACCOUNTADMIN role by default, if not the code should include the role as a parameter. Also remark that, as long as we did not provide any pass-phrase, we created an unencrypted private key, and the “password” here below will be None.

# Connect to Snowflake with a key pair
def connectKeyPair(account, user, private_key_file, passphrase):
   with open(private_key_file, "rb") as pk_file:
      p_key = serialization.load_pem_private_key(
         pk_file.read(),
         password = passphrase,
         backend = default_backend())
   private_key = p_key.private_bytes(
      serialization.Encoding.DER,
      serialization.PrivateFormat.PKCS8,
      serialization.NoEncryption())
   return snowflake.connector.connect(
      account = account,
      # role = "ACCOUNTADMIN",
      user = user,
      private_key = private_key
   )

Encrypted Private Key and Public Key

While it is always safer to generate an encrypted private key, the only new element here below will be the addition of a pass-phrase in the “--pp” option:

python key-pair-generator.py
   --a www55555 --u MyUsername --p MyPassword123_
   --pp "This is the way"

The same connectKeyPair function described before will be called, but with a byte-encoded passphrase, used to load the content of the private key file.

Key Pair Rotation

But what if you already configured a key pair for the current user, like before, and no longer connect to Snowflake with basic authentication? You can in this case pass in the command line key pair specific parameters.

The “--p” parameter will no longer use a password, but the passphrase we established before. This will be to decrypt the private key file whose path we will pass in the “--pk” option. The “--pp” option will have a passphrase used for the newly generated private key.

python key-pair-generator.py
   --a www55555 --u MyUsername
   --pk C:\Users\username\.ssh\rsa_key_1.p8 --p "This is the way"
   --pp "And this is another way"

You may set max two key pairs per user, in separate user parameters: RSA_PUBLIC_KEY or RSA_PUBLIC_KEY_2. When you connect to Snowflake with a password, RSA_PUBLIC_KEY is always overwritten, with no warning. However, when you connect with a key pair, we use RSA_PUBLIC_KEY_2 if empty, or RSA_PUBLIC_KEY if empty, or we stop if both are used. It will be up to you to issue a manual UNSET user statement.

According to Snowflake, this last use case allows for a key pair rotation: (1) you may connect with one key pair, (2) set another one in RSA_PUBLIC_KEY_2, (3) reconnect with this new one and (4) eventually remove the one that you had in RSA_PUBLIC_KEY.

Conclusions

Configuring a key pair for Snowflake does not have to be a manual process anymore. Use one of the three described use cases to take care of this with one simple command-line call. The process hides all complex technical details and automatically associates the public key with a Snowflake user.