[ADAP-593] [Bug] Inconsistent key format for key pair authentication from file vs env variable

[az-hienas]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

I am using dbt-snowflake 1.5.0 with the key pair authentication where I get the private key from an environment variable, as implemented and merged in: #292 (Yes, I am aware that this is not documented at the time of me writing this).

When using dbt with private key from env (private_key property in profiles.yml) if the key is of the form:

-----BEGIN PRIVATE KEY-----
The key here
-----END PRIVATE KEY-----

then I get an error:

Runtime Error
  Database error while listing schemas in database "<DB NAME>"
  Database Error
    Incorrect padding

It works fine if I use a private key file that has the same above format with BEGIN and END.

The usage of

-----BEGIN PRIVATE KEY-----
The key here
-----END PRIVATE KEY-----

is also needed for Python to work with Snowflake as shown at:
https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-example#using-key-pair-authentication-key-pair-rotation

If I don't include the BEGIN and END parts in the key then both Python (key pair authentication) and dbt (with key pair authentication from file) fail with similar errors like:

('Could not deserialize key data. The data may be in an incorrect format, it may be encrypted with an unsupported algorithm, or it may be an unsupported key type (e.g. EC curves with explicit parameters).', 
[_OpenSSLErrorWithText(code=151584876, lib=9, reason=108, reason_text=b'error:0909006C:PEM routines:get_name:no start line')])

The tests used in: #292 , namely in: https://github.com/dbt-labs/dbt-snowflake/pull/292/files#diff-9aa9bb0303b227925dba4a2525865f635633a375650c1c0f6aefdaeee0b851de
have examples of keys that don't have the BEGIN and END parts.

Expected Behavior

dbt Snowflake key pair authentication from environment variable should work with the same types of keys as the dbt Snowflake key pair authentication from a key file. Namely both methods of dbt Snowflake key pair authentication should work with keys of the form:

-----BEGIN PRIVATE KEY-----
The key here
-----END PRIVATE KEY-----

The Snowflake documentation for Python connectors using key pair authentication at:
https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-example#using-key-pair-authentication-key-pair-rotation
also use keys of the form:

-----BEGIN PRIVATE KEY-----
The key here
-----END PRIVATE KEY-----

and fails if the BEGIN and END parts are missing.

Steps To Reproduce

Example of profiles.yml

dbt_transform:
  outputs:
    dev:
      account: "{{ env_var('SNOWFLAKE_ACCOUNT') }}"
      database: "{{ env_var('SNOWFLAKE_DBNAME') }}"
      private_key: "{{ env_var('SNOWFLAKE_PRIVATE_KEY') }}"
      role: "{{ env_var('SNOWFLAKE_ROLE') }}"
      schema: dbt
      threads: 4
      type: snowflake
      user: "{{ env_var('SNOWFLAKE_USER') }}"
      warehouse: "{{ env_var('SNOWFLAKE_WAREHOUSE') }}"
      reuse_connections: True
  target: dev

and private key of the form:

-----BEGIN PRIVATE KEY-----
The key here
-----END PRIVATE KEY-----

For the above profile and key any dbt test or dbt run command will fail with the error:

Runtime Error
  Database error while listing schemas in database "<DB NAME>"
  Database Error
    Incorrect padding

Relevant log output

No response

Environment

- OS:MacOS 13.3.1 (22E261) (M1 Mac) using dbt in devcontainer (mcr.microsoft.com/devcontainers/python:0-3.9-bullseye)
- Python: 3.9.16
- dbt-core: 1.5.0
- dbt-snowflake: 1.5.0

Additional Context

No response

[az-hienas]

Wanted to also mention that the dbt Snowflake with key pair authentication from environment variable works fine if the BEGIN and END parts are missing from the key.

[dataders]

@az-hienas hear me out, do you think that #619 is asking for the same thing? What's great is that @gabeschenz even provides some sample code that would enable what you both are asking for

main...gabeschenz:dbt-snowflake:fix/private-keys

afaik, there's two standard formats for private keys (or so SO tells me):

• DER and
• PEM

PEM is a base64-encoded string that has the -----BEGIN PRIVATE KEY----- prefix and -----END PRIVATE KEY----- suffix.

#292 added support for DER format, and this was just documented by @b-per in dbt-labs/docs.getdbt.com#3432.

duplicate of: #619

lmk if you'd rather keep it open!

p.s. dbt-bigquery has the opposite problem!
dbt-labs/dbt-bigquery#625

[az-hienas]

Hi @dataders , apologies for the late reply.

As far as I know for dbt key pair authentication where the private is in a file , using private_key_path , they key in that file has to have the -----BEGIN PRIVATE KEY----- prefix and -----END PRIVATE KEY----- suffix.

So I think it's more about consistency. Either support both or support the same one for private_key and private_key_path. Otherwise I see it as just being weird. I think it will be confusing for users who will look at the documentation for the two methods to use key pair and see that they need to have the same key in 2 different formats depending on if they want the key in a file or passed via an environment variable.

I would keep this issue open.

Just my two cents on this.

[jschenz-chwy]

I agree with @az-hienas. It seems odd that it can only be DER if using an environment variable, and can only be PEM if using a file. Why not a DER file or a PEM environment variable?

I have an open PR to allow the use of a PEM environment variable: #671