Possibility of adding snowflake key-pair authentication?

[koffemaria2u]

Hi - I'm researching as part of a POC, appreciate the forking from orig free/sql_exporter to add snowflake support!

Like the title states, wondering if this is a possible enhancement as noted in the snowflake docs.

[burningalchemist]

Hi @koffemaria2u,

Thanks!

I see that key-pair authentication seems to be implemented in gosnowflake driver, and as the latest version has the latest driver, it should be possible. I'm less familiar with Snowflake as there is no easy way to spin up an instance to test it out. I'll try to investigate the topic better, too.

At the same time, there is a ticket in Github, according to which you need to specify the connection string with a set of parameters: snowflakedb/gosnowflake#302

It might work for you. Could you try it out and let me know if there is an issue with it? 😃

[koffemaria2u]

@burningalchemist - somehow I missed this part on their repo, thanks for the heads up! I will try setting this up and get back to you.

[koffemaria2u]

Hi @burningalchemist - quick update, I do think it's possible as well. However I can't seem to put together the proper connection string to make it work.

I've tried the following (among other combinations) based on the doc examples but mostly getting "260001: user is empty" error. I'm aIso wondering if this particular error could be misleading as well, where it's actually not a missing user. But not too sure.

snowflake://<account>.snowflakecomputing.com/<dbname>?user=<username>&role=<role>&warehouse=<wh_name>&authenticator=SNOWFLAKE_JWT&privateKey=

snowflake://<username>@<account>.snowflakecomputing.com/<dbname>?role=<role>&warehouse=<wh_name>&authenticator=SNOWFLAKE_JWT&privateKey=

[burningalchemist]

Hi @koffemaria2u! Hmmm, ok let's dig further. Could you provide more details? I don't know exactly what I'd like to know, but maybe some ideas:

• logs from the app;
• how the private key is supplied;

From the link I previously shared, developers mention that:

Because the encoder is expecting URL safe base64 you cannot use the key as is, because base64 encoding from openssl is not URL safe.

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
egrep -v '^(-----BEGIN PRIVATE KEY|-----END PRIVATE KEY)' rsa_key.p8 | \
  tr -d '\n' | \
  sed 's/+/-/g; s/\//_/g' > rsa_key_nohdr_urlbase64.p8

The key in rsa_key_nohdr_urlbase64.p8 will be a base64 encoded url safe pkcs#8.
Do you supply the key in this format?

Currently, I think that since sql_exporter is using database/sql interface, we should not have specific errors on the app side. So I'd assume that having a simple Golang app to connect to the database won't help as well.

But yeah, please provide what you think might be useful except sensitive data, and let's see.

[koffemaria2u]

For some context, I am using AWS Secrets Manager to store the private key, and using konfd to generate a kubernetes secret resource which contains the entire configmap (base64 encoded). This works fine with user/pass auth.

This is how I build the connection string:
target: data_source_name: snowflake:// {{- secret "aws-secrets-path" "username"}}: {{- secret "aws-secrets-path" "password"}}@ {{- secret "aws-secrets-path" "account"}}.snowflakecomputing.com/ {{- secret "aws-secrets-path" "dbname"}}?role= {{- secret "aws-secrets-path" "rolename"}}&warehouse= {{- secret "aws-secrets-path" "warehouse"}}&authenticator=SNOWFLAKE_JWT&privateKey= {{- secret "aws-secrets-path" "private_key"}}
which ultimately comes out to:
data_source_name: snowflake://<user>:<password>@<account>.snowflakecomputing.com/<dbname>?role=<rolename>&warehouse=<warehouse>&authenticator=SNOWFLAKE_JWT&privateKey=<private_key>

I don't see why a password is needed in the string when we are already supplying the private key. But when I don't include password, it looks for a password with error# 260002.
eg. snowflake://<user>@<account>.snowflakecomputing.com...

There are two main log errors that I see.

This usually occurs when I supply the private key with line breaks (\n) whether there is a "BEGIN/END PRIVATE KEY" or not with the private key passed. So I think this format should be ruled out.
1 main.go:56] Starting SQL exporter (version=0.8.1, branch=heads/0.8.1, revision=7e2410db1ee70cfddbd59658c086ec5978da2efb) (go=go1.16.2, user=root@xyz, date=20210312-21:45:21) 1 config.go:18] Loading configuration from /config/sql-exporter.yml 1 main.go:60] Error creating exporter: yaml: line 11: could not find expected ':' goroutine 1 [running]: k8s.io/klog/v2.stacks(0xc00000e001, 0xc000490140, 0x6e, 0x12a) /go/pkg/mod/k8s.io/klog/v2@v2.7.0/klog.go:1020 +0xb9 k8s.io/klog/v2.(*loggingT).output(0x137fec0, 0xc000000003, 0x0, 0x0, 0xc00013c0e0, 0x100deff, 0x7, 0x3c, 0x0) /go/pkg/mod/k8s.io/klog/v2@v2.7.0/klog.go:969 +0x191 k8s.io/klog/v2.(*loggingT).printf(0x137fec0, 0xc000000003, 0x0, 0x0, 0x0, 0x0, 0xd25b70, 0x1b, 0xc00049c390, 0x1, ...) /go/pkg/mod/k8s.io/klog/v2@v2.7.0/klog.go:750 +0x191 k8s.io/klog/v2.Fatalf(...) /go/pkg/mod/k8s.io/klog/v2@v2.7.0/klog.go:1508 main.main() /go/src/github.com/burningalchemist/sql_exporter/cmd/sql_exporter/main.go:60 +0x5e6 goroutine 8 [chan receive]: k8s.io/klog/v2.(*loggingT).flushDaemon(0x137fec0).... goroutine 9 [chan receive]: github.com/ClickHouse/clickhouse-go.init.0.func1()....

Supplying the private key via rsa_key_nohdr_urlbase64.p8 (w/o linebreaks) feels a lot closer to the proper format but still fails for an unknown reason (261004):
No metrics gathered, [from Gatherer #1] 261004 (08004): failed to auth for unknown reason. HTTP: 400, URL: https://<account>.snowflakecomputing.com:443/session/v1/login-request?<dsn_details>

[burningalchemist]

@koffemaria2u This is decent, thank you! 👍 Seems like it's a good field for the experiments. 😃 I've checked the docs, where they constantly mention that the private key should be base64 URL encoded PKCS8 rsa private key string, which we produce somehow (either via Golang or openssl command). But the public key as well should be base64 Standard encoded PKI public key string.

Could you make sure that the public key follows the same transformation? :)

Maybe you could also request from the Snowflake side, why the authentication might be invalid? They might have some internal logs.

One last idea for today is to pick this example from the official repo, and try to connect to the instance directly. If it works, it might be easier to debug and find a root cause. The example needs some adjustment, but I can help with that.

In the meantime I'm going to check how to spin up a simple snowflake instance, so I could try it, too.

[koffemaria2u]

@burningalchemist unfortunately, I'm unable to set a non-base64 public key on the snowflake user side. There is a policy that only takes proper formatted public keys. I'm also now in contact with some snowflake reps/engineers to see if they are able to check some internal logs. I will post an update of their findings once I get any.

Can you elaborate on connecting to the instance directly using the example link mentioned?

[burningalchemist]

@koffemaria2u yes, will do shortly. 👍

[burningalchemist]

@koffemaria2u that's actually good. As per documentation we need to provide base64-encoded keys on the both sides. I just wanted to make sure you do so, and use ALTER USER <your_user_name> SET RSA_PUBLIC_KEY='<your_public_key>'; with a proper value.

As for the example link above, it's a simple select query and it might be useful to try to connect to the database instance without any sql_exporter specifics. Currently, it only expects username and password parameters, but it's not difficult to change it and provide the private key instead. This might help to debug the connection process in an isolated manner.

Please let me know about Snowflake's feedback with regards to the error you observe. I'm eager to make it work with sql_exporter. 👍

[koffemaria2u]

@burningalchemist Hmm... I wonder how would it be possible to apply base-64 encoded public key on the snowflake username side, if there are requirements it must match? It's failing for me to add base-64 encoded, only non-encoded works.

No response yet from Snowflake reps.

[burningalchemist]

@koffemaria2u could be related, as that's what mentioned in the docs. Might be a bug or something. Let's see what they say.

How difficult for me would it be to recreate the environment you have? If you could provide some steps, I would pick a trial account to debug as well.

[koffemaria2u]

@burningalchemist I don't believe it's necessary to recreate my current env, it will just add more complexity. All we have to do is figure out the proper connection string format to pass to snowflake db. You can setup an instance of your sql-exporter on k8s or docker for example, and pass a hardcoded conn string via your ConfigMap yaml to your trial snowflake db.

On the snowflake side, it's pretty simple to setup a user for key pair auth.

[burningalchemist]

@koffemaria2u sure, I meant the Snowflake environment. 👍 Cool, I'll take a look.

[burningalchemist]

@koffemaria2u It seems I managed to connect with SNOWFLAKE_JWT properly. I'll share my findings tomorrow morning. 👍
UPD: Even better - without providing the password, just the username and the key. As intended. 😃

[burningalchemist]

@koffemaria2u I'm going to share the steps I made with regards to key generation and connection DSN, etc:

1. Generate a new private key:
   openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 -pkeyopt rsa_keygen_pubexp:65537 > key.pem
2. Extract a public key to add on the Snowflake side, remove the new lines and delimiters:
   openssl pkey -pubout -inform pem -outform pem -in key.pem -out key.pub | egrep -v '^(-----BEGIN PUBLIC KEY|-----END PUBLIC KEY)' | tr -d '\r' | tr -d '\n'
3. Substitute symbols: ['+' => '-', '/' => '_'] in the existing private key to make it URL-safe as it's used in the HTTPS authentication request:
   cat key.pem | egrep -v '^(-----BEGIN PRIVATE KEY|-----END PRIVATE KEY)' | tr -d '\r' | tr -d '\n' | sed 's/+/-/g; s/\//_/g'
4. sql_exporter DSN:
   data_source_name: snowflake://<username>@<host>:443/<database>/<schema>?account=<account-id>&warehouse=<warehouse>&authenticator=SNOWFLAKE_JWT&privateKey=<key>
5. start sql_exporter and hit /metrics endpoint.

I think in the case of DSN it doesn't matter, but this worked for me. Initially I had the same errors as you.
I believe the missing step was the replacement of '+' and '/'. These are the symbols that cannot be used in web URLs, but we can change them to something else.

[koffemaria2u]

@burningalchemist amazing, this works! Thank you so much for your help!

Lessons learned:

1. The substitution of URL-safe symbols (Update README.md #3 in previous comment) in the private key is very important.
2. I do believe the DSN format matters, with respect to the port (443). I think it has to be specified somehow or it did not work for me. I had not been including the port when using user/pw auth.

My working DSN ended up looking like:
data_source_name: snowflake://<username>@<account>.snowflakecomputing.com:443/<database>?role=<role>&warehouse=<warehouse>&authenticator=SNOWFLAKE_JWT&privateKey=<key>

[burningalchemist]

@koffemaria2u I'm also preparing a new documentation page, so I'll include proper configuration for Snowflake as well. Thanks for collaboration! 👍