Comments (17)
@koffemaria2u I'm also preparing a new documentation page, so I'll include proper configuration for Snowflake as well. Thanks for collaboration! 👍
from sql_exporter.
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? 😃
from sql_exporter.
@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.
from sql_exporter.
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/[email protected]/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/[email protected]/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/[email protected]/klog.go:750 +0x191 k8s.io/klog/v2.Fatalf(...) /go/pkg/mod/k8s.io/klog/[email protected]/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>
from sql_exporter.
@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.
from sql_exporter.
@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?
from sql_exporter.
@koffemaria2u yes, will do shortly. 👍
from sql_exporter.
@koffemaria2u sure, I meant the Snowflake environment. 👍 Cool, I'll take a look.
from sql_exporter.
@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. 😃
from sql_exporter.
@koffemaria2u I'm going to share the steps I made with regards to key generation and connection DSN, etc:
- Generate a new private key:
openssl genpkey -algorithm RSA -pkeyopt rsa_keygen_bits:2048 -pkeyopt rsa_keygen_pubexp:65537 > key.pem
- 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'
- 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'
- sql_exporter DSN:
data_source_name: snowflake://<username>@<host>:443/<database>/<schema>?account=<account-id>&warehouse=<warehouse>&authenticator=SNOWFLAKE_JWT&privateKey=<key>
- 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.
from sql_exporter.
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=
from sql_exporter.
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.
from sql_exporter.
@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. 👍
from sql_exporter.
@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.
from sql_exporter.
@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.
from sql_exporter.
@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.
from sql_exporter.
@burningalchemist amazing, this works! Thank you so much for your help!
Lessons learned:
- The substitution of URL-safe symbols (#3 in previous comment) in the private key is very important.
- 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>
from sql_exporter.
Related Issues (20)
- Missing job target labels HOT 8
- Return results even if some query fails HOT 2
- Releases built with incorrect Go toolchain HOT 2
- Add support for ASE database (sybase) HOT 1
- Context deadline exceeded error handling HOT 4
- The problem of specifying a schema when capturing PostgreSQL metrics HOT 6
- Different collector file for each differrent job HOT 1
- Allow to set metric timestamp from query results
- Caching context deadline exceeded result HOT 6
- Context Deadline Exceeded HOT 9
- Support for MSSQL named pipe (np:) and shared memory (lpc:) DSNs HOT 16
- `up` metric to show that database connection was successful HOT 5
- parameter to set log file location HOT 8
- Add custom metrics to expose MSSQL server hostname HOT 1
- can we have the Postgres collector yml file example
- Is it possible to distinguish between the sql exporter process status and the target database? HOT 3
- Scrape each job separately HOT 2
- [helm] Support getting data-source-name from an existing secret HOT 12
- Ignore no rows returned HOT 6
- Better handling of NULL return values in certain cases HOT 6
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from sql_exporter.