Key Rotation For Connected Applications

Connected Application builders require consumers that want to use their application to provide access to their account. I often get questions about how to manage this credential. This article will go through steps I would take to secure and use a credential given to me as a builder of a connected application.

In order to get credentials secured for a consumer’s account, I would document and create a script the consumer can easily audit and run to get started. It would also be really nice if I could plan for credential rotation to prevent manual processes later. In this article I will provide a basic example but every application will want to customize this based on their needs and use-case.

Here is a workflow diagram of the process:

I will be using key pair authentication for the connected accounts. I would not use usernames and passwords because there is no easy way to automate rotation (each user only has 1 password). Every user in Snowflake has 2 keypairs that can be active at a time which is perfect for rotations without downtime.

First, the builder will create a keypair for authentication. This could be created per customer or be a shared key, that is up to the builder to choose.

# generate a public & private key

#!/bin/bash
openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
PUBLIC_KEY=`cat rsa_key.pub | head -n 8 | tail -n 7 | tr -d '\n'`

The following SQL script could be sent to be run by the application customer which will create a role, user, warehouse, and database I can use. You should replace the PUBLIC_KEY in the script with the key generated above before sending it to the application customer to run.

/* EXAMPLE SETUP SCRIPT FOR CONSUMER TO RUN AS ACOUNTADMIN */
CREATE OR REPLACE ROLE MY_CONNECTED_APP_ROLE;
CREATE OR REPLACE WAREHOUSE MY_CONNECTED_APP_WH;
CREATE OR REPLACE DATABASE MY_CONNECTED_APP_DB;

CREATE USER MY_CONNECTED_APP_USER must_change_password=false
default_role=MY_CONNECTED_APP_ROLE rsa_public_key='PUBLIC_KEY'
default_namespace=MY_CONNECTED_APP_DB.PUBLIC
default_warehouse=MY_CONNECTED_APP_WH;

GRANT ROLE MY_CONNECTED_APP_ROLE TO USER MY_CONNECTED_APP_USER;
GRANT ROLE MY_CONNECTED_APP_ROLE TO ROLE ACCOUNTADMIN;
GRANT OWNERSHIP ON WAREHOUSE MY_CONNECTED_APP_WH TO ROLE MY_CONNECTED_APP_ROLE;
GRANT ALL ON WAREHOUSE MY_CONNECTED_APP_WH TO ROLE ACCOUNTADMIN;
GRANT OWNERSHIP ON DATABASE MY_CONNECTED_APP_DB TO ROLE MY_CONNECTED_APP_ROLE;

USE ROLE MY_CONNECTED_APP_ROLE;
CREATE OR REPLACE SCHEMA MY_CONNECTED_APP_DB.APP;

I would also like to be able to rotate my own credential from my infrastructure to persist the new private and public keypair in my secrets store. In order to do so, I will also create a stored procedure to allow the user to rotate their credential.

/* EXAMPLE SETUP SCRIPT FOR CONSUMER TO RUN AS ACOUNTADMIN */
CREATE OR REPLACE SCHEMA MY_CONNECTED_APP_DB.ADMIN;
USE ROLE ACCOUNTADMIN;

CREATE OR REPLACE PROCEDURE MY_CONNECTED_APP_DB.ADMIN.SET_CREDENTIAL(key1 STRING, key2 STRING)
returns varchar not null
language sql
execute as owner
as
BEGIN
let k1 TEXT := :KEY1;
let k2 TEXT := :KEY2;
let sql TEXT := 'ALTER USER MY_CONNECTED_APP_USER set rsa_public_key=? rsa_public_key_2=?';
execute immediate :sql using (k1, k2);
return 'ok';
END;

GRANT USAGE ON SCHEMA MY_CONNECTED_APP_DB.ADMIN TO ROLE MY_CONNECTED_APP_ROLE;
GRANT USAGE ON PROCEDURE MY_CONNECTED_APP_DB.ADMIN.SET_CREDENTIAL(STRING,STRING) TO ROLE MY_CONNECTED_APP_ROLE;

Now anytime the builder wants to rotate the credential they can create the new public and private key and call the set_credentials with the current public key and the new public key. After that is successful the secrets store can be updated to store the new keypair. The application can then use the new keypair for authentication. After another rotation the previous key is no longer valid and couldn’t be used.

You can see the procedure code is running as owner, this is necessary because users cannot alter their own public keys. This procedure hard coded the connected application user so it is only solving for this one need and not allowing more than is required. This also fits well with what is allowed in Snowflake with owner’s rights.

I only included the Snowflake pieces in this post, mostly because there are many different ways to store secrets and run processes depending on your organization’s technology and cloud choices. It is a Best Practice to store these keys in something backed by an HSM like AWS Parameter Store EncryptedString and for those to be rotated regularly. To do so, I would create and schedule a task that would:

1. Create a new keypair
2. Connect to the consumer’s account using current keypair and call SET_CREDENTIALS with both public keys which should be valid
3. Update the keypair in the secrets store so the application will start using the new credential
4. Have the application read the keypair from the secrets store