[Feature]: Add possibility to connect to Snowflake via Key Pair Authentication

sosdarko commented 6 months ago

Is there an existing issue for this?

[X] I have searched the existing issues

Summary

When creating a Snowflake connection, add possibility to use private key (or just path) and also a passphrase for encrypted private keys.

Why should this be worked on?

This is standard feature across many tools, like dbt, dbeaver and even SnowSQL. This would allow for many users to connect to Snowflake, where their companies require such an authorization. Also, if under the hood you already use SnowSQL or any driver that already supports private keys, it's just a matter of updating the connection Form and passing new parameters to the connector.

csp33 commented 5 months ago

Hi, this feature is very important because it would allow configuring MFA for service users

AnnaHariprasad5123 commented 4 months ago

Hi @rohan-arthur

I have started working on this. Could you please provide your opinion on my approach to solving this?

Approach to Solve :

Step 1: Create a private key and passphrase for Snowflake connection Step 2: Update the Connection Form

Modify the connection form to include the following fields:
Authentication Method: A dropdown with two options:
1. Username and Password
2. Private Key

Private Key File: A text input field to enter the path to the private key file (only visible when Private Key is selected as the authentication method) Private Key Passphrase: A password input field to enter the passphrase for the encrypted private key (only visible when Private Key is selected as the authentication method)

Step 3: Update the Connection Logic

Modify the connection logic to handle the new authentication method. When the user selects Private Key as the authentication method, you'll need to:

Read the private key file from the specified path
Decrypt the private key using the provided passphrase (if encrypted)
Use the decrypted private key to establish a connection to Snowflake.

Step 4: Update the Snowflake Connector

Update the Snowflake connector to accept the private key file and passphrase as optional parameters. When the user selects Private Key as the authentication method, pass the private key file and passphrase to the Snowflake connector.

Step 5: Test the Feature

Test the feature by creating a new Snowflake connection using the private key file and passphrase. Verify that the connection is established successfully and you can query the Snowflake database.

rohan-arthur commented 4 months ago

@AnnaHariprasad5123 thank you for your interest. Our team is already working to solve this.

In future, please check with us before picking up something, as described in the updated code contribution guide here, so that we don't both end up working on the same thing.

(By the way, the requirements description is spot on)

AnnaHariprasad5123 commented 4 months ago

Thank you @rohan-arthur

sosdarko commented 4 months ago

Thank you all, looking forward for this feature!

rohan-arthur commented 1 month ago

@sosdarko this feature is now available in the latest release, i.e., v1.43

sosdarko commented 2 days ago

Hi, sorry for late response, thanks a lot @rohan-arthur for this! My self-hosted instance has automatically updated (wasn't aware of this feature) and I've tested the key-pair authentication and it works :) Thanks a lot, this definitely raises confidence in this product!

appsmithorg / appsmith