search

kestra-io / plugin-jdbc

https://kestra.io/plugins/
Apache License 2.0
10 stars 7 forks source link

Snowflake auth with privateKey is not working #363

Open MarthaScheffler opened 2 weeks ago

MarthaScheffler commented 2 weeks ago

Expected Behavior

When I choose authentication via privateKey, the jdbc plugin can successfully connect to Snowflake.

code example:

id: select_from_snowflake
namespace: dev
description: testing Snowflake jdbc

tasks:
  - id: jdbc_snowflake
    type: io.kestra.plugin.jdbc.snowflake.Query
    url: jdbc:snowflake://xxx-xxx.snowflakecomputing.com
    username: KESTRA_USER
    privateKey: "{{ secret('KESTRA_USER_PRIVATE_KEY')}}"
    role: KESTRA_USER_ROLE
    warehouse: KESTRA_USER_WH
    sql: "SELECT COUNT(id) FROM my_database.my_schema.my_table;"
    fetch: true

Actual Behaviour

getting an error.

2024-08-23 15:57:48.907Invalid parameter value type: java.lang.String, expected type: java.security.PrivateKey.
2024-08-23 15:57:48.907net.snowflake.client.jdbc.SnowflakeSQLLoggedException: Invalid parameter value type: java.lang.String, expected type: java.security.PrivateKey.
 at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initialize(DefaultSFConnectionHandler.java:130)
 at net.snowflake.client.jdbc.DefaultSFConnectionHandler.initializeConnection(DefaultSFConnectionHandler.java:98)
 at net.snowflake.client.jdbc.SnowflakeConnectionV1.initConnectionWithImpl(SnowflakeConnectionV1.java:142)
 at net.snowflake.client.jdbc.SnowflakeConnectionV1.<init>(SnowflakeConnectionV1.java:122)
 at net.snowflake.client.jdbc.SnowflakeDriver.connect(SnowflakeDriver.java:214)
 at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
 at java.sql/java.sql.DriverManager.getConnection(Unknown Source)
 at io.kestra.plugin.jdbc.JdbcConnectionInterface.connection(JdbcConnectionInterface.java:63)
 at io.kestra.plugin.jdbc.AbstractJdbcQuery.run(AbstractJdbcQuery.java:77)
 at io.kestra.plugin.jdbc.snowflake.Query.run(Query.java:22)
 at io.kestra.core.runners.WorkerTaskThread.doRun(WorkerTaskThread.java:76)
 at io.kestra.core.runners.AbstractWorkerThread.run(AbstractWorkerThread.java:57)

Steps To Reproduce

  1. create user with private key on Snowflake (https://docs.snowflake.com/en/user-guide/key-pair-auth) and test on some tool that the private key works for connection
  2. create Kestra flow with jdbc plugin and connect to Snowflake via password (works)
  3. try privateKey option with same Kestra flow (doesn't work, no matter what formatting of the key)

several formattings that I already tried:

- id: jdbc_snowflake
    type: io.kestra.plugin.jdbc.snowflake.Query
    url: jdbc:snowflake://zzzz-zzz.snowflakecomputing.com
    role: KESTRA_USER_ROLE
    warehouse: KESTRA_USER_WH
    sql: "SELECT COUNT(inspection_id) FROM my_database.my_schema.my_table;"
    fetch: true
    username: KESTRA_USER
    password: yyyy

    privateKey: |
      -----BEGIN RSA PRIVATE KEY-----
      MIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG
      ...
      W3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk=
      -----END RSA PRIVATE KEY-----

    privateKey: |
      MIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG
      ...
      W3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk=

    privateKey: "-----BEGIN RSA PRIVATE KEY-----MIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG...W3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk=-----END RSA PRIVATE KEY-----

    privateKey: "MIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG...W3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk="

    privateKey: MIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG...W3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk=

    privateKey: "-----BEGIN RSA PRIVATE KEY-----\nMIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG\n...\nW3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk=\n-----END RSA PRIVATE KEY-----\n"

    privateKey: -----BEGIN RSA PRIVATE KEY-----\nMIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG\n......\nW3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk=\n-----END RSA PRIVATE KEY-----\n

    privateKey: "MIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG\n...\nW3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk="

    privateKey: MIIEpQIBAAKCAQEAspCKB+IVgZ0Jd2hyBAzlX/nIlu4AlG8EbX7pISoDU3wA78AG\n...\nW3OLz2iVIIw2j5g9ib2149BmHDXKOW0I+suvVWUTpc7JwfkHdTSFitk=

Environment Information

Example flow

No response