Open davlee1972 opened 8 months ago
I was also able to use the python snowflake connector successfully with the same rsa_key.p8 file..
https://docs.snowflake.com/en/developer-guide/python-connector/python-connector-connect
import snowflake.connector
import os
from cryptography.hazmat.backends import default_backend
from cryptography.hazmat.primitives.asymmetric import rsa
from cryptography.hazmat.primitives.asymmetric import dsa
from cryptography.hazmat.primitives import serialization
with open("<path>/rsa_key.p8", "rb") as key:
p_key= serialization.load_pem_private_key(
key.read(),
password=os.environ['PRIVATE_KEY_PASSPHRASE'].encode(),
backend=default_backend()
)
pkb = p_key.private_bytes(
encoding=serialization.Encoding.DER,
format=serialization.PrivateFormat.PKCS8,
encryption_algorithm=serialization.NoEncryption())
ctx = snowflake.connector.connect(
user='<user>',
account='<account_identifier>',
private_key=pkb,
warehouse=WAREHOUSE,
database=DATABASE,
schema=SCHEMA
)
cs = ctx.cursor()
After upgrading to adbc 0.9.0, I'm seeing a different error.
Traceback (most recent call last):
File "
394300 | JWT_TOKEN_INVALID_USER_IN_ISSUER | The user name specified in the issuer does not exist in the Snowflake account. For possible solutions, see Common Errors and Solutions.
This is even more odd. It looks like JWT is working now, but the ADBC "username" is not a valid snowflake "LOGIN_NAME".
Will try to trace this..
@zeroshade any ideas?
@davlee1972 does the username you're using for login match the same user that the JWT was issued for?
In theory, based on that error message, it looks like the "username" is being seen as valid just fine, but the "issuer" on the JWT is the user name that is not being recognized. That said, I'm not sure why it's working for the snowflake python connector and not working for this. I'll see if i can try to replicate the issue on my end but i need to bug my own snowflake account admins to give me permissions to create RSA key stuff.
Here's some additional debugging using SNOWSQL to generate a JWT token and using it to connect.. I mentioned above, the debugging options for the JWT token including the issuer generated by the ADBC / GO driver code are limited and don't show the JWT token value being generated..
Using https://jwt.io/ I verified that the jwt token used by SNOWSQL is:
"iss": "<my_account>.<my_login>.SHA256:qvmbDr2 etc.. etc.. etc.. Fs+1CYlU=",
which is account.login.fingerprint..
Testing steps:
snowsql --private-key-path rsa_key.p8 -a <my_account>.privatelink -u <my_login>
Private Key Passphrase:
* SnowSQL * v1.2.30
Type SQL statements or !help
snowsql --private-key-path rsa_key.p8 -a <my_account>.privatelink -u <my_login> --generate-jwt
Enter private key file password (Press enter if none) []:
eyJhbGciOiJSUzI1Ni
etc.. etc.. etc..
i86gxRk7w7QOTR8QvoxwQ
>>> import adbc_driver_snowflake.dbapi
>>>
>>> jwt_token = "eyJhbGciOiJSUzI1Ni etc.. etc.. etc.. i86gxRk7w7QOTR8QvoxwQ"
>>>
>>> db_kwargs={
... "adbc.snowflake.sql.account": "<my_account>",
... "adbc.snowflake.sql.auth_type": "auth_jwt",
... "adbc.snowflake.sql.client_option.auth_token": jwt_token,
... "adbc.snowflake.sql.client_option.jwt_expire_timeout": "24h",
... "adbc.snowflake.sql.client_option.tracing": "DEBUG",
... "username": "<my_login>",
... }
>>>
>>> a_conn = adbc_driver_snowflake.dbapi.connect(db_kwargs=db_kwargs)
time="2024-01-16T10:39:28-08:00" level=info msg=OpenWithConfig func="gosnowflake.(*defaultLogger).Info" file="log.go:216"
time="2024-01-16T10:39:28-08:00" level=debug msg="OCSP Cache Server for Privatelink: http://ocsp.<my_account>.privatelink.snowflakecomputing.com/ocsp_response_cache.json\n" func="gosnowflake.(*defaultLogger).Debugf" file="log.go:148"
time="2024-01-16T10:39:28-08:00" level=debug msg="OCSP Retry URL for Privatelink: http://ocsp.<my_account>.privatelink.snowflakecomputing.com/retry/%v/%v\n" func="gosnowflake.(*defaultLogger).Debugf" file="log.go:148"
time="2024-01-16T10:39:28-08:00" level=info msg="Authenticating via SNOWFLAKE_JWT" func="gosnowflake.(*defaultLogger).Infof" file="log.go:154"
time="2024-01-16T10:39:28-08:00" level=info msg="PARAMS for Auth: &map[], https, <my_account>.privatelink.snowflakecomputing.com, 443, 5m0s, SNOWFLAKE_JWT" func=gosnowflake.authenticate file="auth.go:353"
time="2024-01-16T10:39:28-08:00" level=info msg="full URL: https://<my_account>.privatelink.snowflakecomputing.com:443/session/v1/login-request?requestId=ea8252a2-64fe-4526-4b72-e2024078d4aa&request_guid=eca249bc-5f1a-4172-4250-19931c223f89" func="gosnowflake.(*defaultLogger).Infof" file="log.go:154"
time="2024-01-16T10:39:28-08:00" level=info msg="retryHTTP.totalTimeout: 5m0s" func="gosnowflake.(*retryHTTP).execute" file="retry.go:294"
time="2024-01-16T10:39:28-08:00" level=debug msg="retry count: 0" func="gosnowflake.(*defaultLogger).Debugf" file="log.go:148"
Snowflake driver panicked, stack traces:
goroutine 17 [running, locked to thread]:
main.poison(0x0?, {0x7ffd4b1c2dbc, 0x12}, {0x7ffd4afc7e80, 0x7ffd4ae5e610})
D:/a/arrow-adbc/arrow-adbc/adbc/go/adbc/pkg/snowflake/driver.go:165 +0x87
main.SnowflakeConnectionInit.func1()
D:/a/arrow-adbc/arrow-adbc/adbc/go/adbc/pkg/snowflake/driver.go:952 +0x51
panic({0x7ffd4afc7e80, 0x7ffd4ae5e610})
C:/hostedtoolcache/windows/go/1.20.8/x64/src/runtime/panic.go:884 +0x213
crypto/rsa.(*PrivateKey).Public(...)
C:/hostedtoolcache/windows/go/1.20.8/x64/src/crypto/rsa/rsa.go:123
github.com/snowflakedb/gosnowflake.prepareJWTToken(0xc0000c2e00)
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/auth.go:462 +0x38
github.com/snowflakedb/gosnowflake.createRequestBody(0xc0006b1b60, 0xc000090200?, {{0x7ffd4b1b3bab, 0x2}, {0x7ffd4b1b8393, 0x7}, {0xc0000c6158, 0x8}, {0x7ffd4b1b95bf, 0x9}}, ...)
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/auth.go:425 +0x429
github.com/snowflakedb/gosnowflake.authenticate.func1()
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/auth.go:336 +0x6d
github.com/snowflakedb/gosnowflake.(*retryHTTP).execute(0xc0005067e8)
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/retry.go:305 +0x209
github.com/snowflakedb/gosnowflake.postAuthRestful({0x7ffd4b40def8?, 0xc00092cb40?}, 0x19?, 0xc000911190?, 0x7ffd4af273a0?, 0xc0000a8000?, 0xc000491f58?, 0xc000491f30?)
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/restful.go:197 +0x10f
github.com/snowflakedb/gosnowflake.postAuth({0x7ffd4b40def8, 0xc00092cb40}, 0xc0000c5040, 0x22?, 0xc00000a0f8, 0xc000506b00?, 0x7ffd4a94905a?, 0xc000491e30?)
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/auth.go:229 +0x34d
github.com/snowflakedb/gosnowflake.authenticate({0x7ffd4b40def8, 0xc00092cb40}, 0xc0006b1b60, {0x0, 0x0, 0x0}, {0x0, 0x0, 0x0})
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/auth.go:356 +0xf8e
github.com/snowflakedb/gosnowflake.authenticateWithConfig(0xc0006b1b60)
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/auth.go:547 +0x398
github.com/snowflakedb/gosnowflake.SnowflakeDriver.OpenWithConfig({}, {_, _}, {{0xc0008c47c0, 0x20}, {0xc0008cf920, 0x4}, {0x0, 0x0}, {0x0, ...}, ...})
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/driver.go:43 +0x15e
github.com/snowflakedb/gosnowflake.Connector.Connect({{0x7ffd4b400678, 0x7ffd4bf0bc78}, {{0xc0008c47c0, 0x20}, {0xc0008cf920, 0x4}, {0x0, 0x0}, {0x0, 0x0}, ...}}, ...)
C:/Users/runneradmin/go/pkg/mod/github.com/snowflakedb/gosnowflake@v1.6.23-0.20231106142408-8445dcaf46d9/connector.go:34 +0x105
github.com/apache/arrow-adbc/go/adbc/driver/snowflake.(*databaseImpl).Open(0xc000923080, {0x7ffd4b40de88?, 0xc000038070?})
D:/a/arrow-adbc/arrow-adbc/adbc/go/adbc/driver/snowflake/snowflake_database.go:454 +0x1f9
github.com/apache/arrow-adbc/go/adbc/driver/driverbase.(*database).Open(0x195b0446480?, {0x7ffd4b40de88?, 0xc000038070?})
D:/a/arrow-adbc/arrow-adbc/adbc/go/adbc/driver/driverbase/database.go:134 +0x2b
main.SnowflakeConnectionInit(0x195b0493c78, 0x0?, 0xbd57eeee8)
D:/a/arrow-adbc/arrow-adbc/adbc/go/adbc/pkg/snowflake/driver.go:968 +0x118
Traceback (most recent call last):
File "C:\Users\<my_login>\Anaconda3\lib\site-packages\adbc_driver_snowflake\dbapi.py", line 120, in connect
conn = adbc_driver_manager.AdbcConnection(db, **(conn_kwargs or {}))
File "adbc_driver_manager\\_lib.pyx", line 676, in adbc_driver_manager._lib.AdbcConnection.__init__
File "adbc_driver_manager\\_lib.pyx", line 232, in adbc_driver_manager._lib.check_error
adbc_driver_manager.InternalError: INTERNAL: [Snowflake] AdbcConnectionInit: Go panic in Snowflake driver (see stderr): "invalid memory address or nil pointer dereference"
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "C:\Users\<my_login>\Anaconda3\lib\site-packages\adbc_driver_snowflake\dbapi.py", line 126, in connect
db.close()
File "adbc_driver_manager\\_lib.pyx", line 470, in adbc_driver_manager._lib.AdbcDatabase.close
File "adbc_driver_manager\\_lib.pyx", line 476, in adbc_driver_manager._lib.AdbcDatabase.close
File "adbc_driver_manager\\_lib.pyx", line 232, in adbc_driver_manager._lib.check_error
adbc_driver_manager.ProgrammingError: INVALID_STATE: [Snowflake] AdbcDatabaseRelease: Go panicked, driver is in unknown state
I've been away for a bit, but I just got my RSA key stuff set up so I can start trying to debug this myself. I should hopefully have an update tomorrow
I also tried passing in adbc.snowflake.sql.account": "time="2024-01-16T10:53:53-08:00" level=warning msg="failed http connection. err: Post \"https://<my_account>.snowflakecomputing.com:443/
I think the problem might be this "privatelink" string in the driver code..
The correct iss should not include "privatelink" text..
"iss": "<my_account>.<my_login>.SHA256:qvmbDr2 etc.. etc.. etc.. Fs+1CYlU=",
However the account url has to have "privatelink" in it..
snowsql --private-key-path rsa_key.p8 -a <my_account>.privatelink -u MY_LOGIN -s MY_SCHEMA -d MY_DB -r MY_ROLE -w MY_WAREHOUSE -o log_level=DEBUG -o log_file=c:\temp\snow.log
For consistency with both SNOWSQL and the Snowflake Python Connector lib, ,the account should be in
Ok the ".privatelink" is the problem.. it needs to be addressed when generating the host url string..
I got it to work using the following combination..
"adbc.snowflake.sql.account": "<my_account_without_privatelink>",
"adbc.snowflake.sql.uri.host": "<my_account_with_privatelink>".snowflakecomputing.com"
For consistency with other Snowflake clients the account should be:
"adbc.snowflake.sql.account": "
As far as I can tell from the docs, the .privatelink
suffix is only for use with AWS PrivateLink. Where in the docs does it say you should use the .privatelink
when specifying the account? If this relies on the .privatelink
suffix, then I likely won't be able to replicate the issue on my end as my configuration doesn't utilize AWS PrivateLink.
The account param is used to generate the default uri so in my case I have to pass in .privatelink as part of the account.
But the jwt issuer is just account. Can we strip .privatelink from the account param when generating the jwt?
BTW using account with .privatelink works fine when using adbc with auth external instead of jwt.
if the JWT issuer is just account, it feels like we shouldn't have ADBC strip the .privatelink
but rather it should be explicitly stated that the "account" provided has to match the issuer for the JWT. I don't want to add stripping of the .privatelink
because what if the JWT issuer actually does have the .privatelink
on it?
This could be an issue with the go driver or handled in adbc.. Here's the equivalent snowflake python connector code..
host in my case needs to be passed in with account = my_account.privatelink host = f"{account}.snowflakecomputing.com"
the parsed_account turns my_account.privatelink to just my_account which essentially strips ".privatelink" url_parts = account.split(".") parsed_account = url_parts[0]
def construct_hostname(region: str | None, account: str) -> str:
"""Constructs hostname from region and account."""
if region == "us-west-2":
region = ""
if region:
if account.find(".") > 0:
account = account[0 : account.find(".")]
host = f"{account}.{region}.snowflakecomputing.com"
else:
host = f"{account}.snowflakecomputing.com"
return host
def parse_account(account):
url_parts = account.split(".")
# if this condition is true, then we have some extra
# stuff in the account field.
if len(url_parts) > 1:
if url_parts[1] == "global":
# remove external ID from account
parsed_account = url_parts[0][0 : url_parts[0].rfind("-")]
else:
# remove region subdomain
parsed_account = url_parts[0]
else:
parsed_account = account
return parsed_account
If something is to remove the privatelink part, it should be the user or gosnowflake; adbc is just forwarding the values in this case and it doesn't make sense to patch around gosnowflake's behavior
Yeah and here's the valid JWT test which clearly strips stuff after "." unless it is ".GLOBAL" It looks "-" could also be problematic.
@pytest.mark.parametrize(
"input_account,expected_account",
[
("s3testaccount.global", "S3TESTACCOUNT.GLOBAL"),
("acct-with-dashes", "ACCT-WITH-DASHES"),
("testaccount.extra", "TESTACCOUNT"),
("testaccount-user.global", "TESTACCOUNT"),
("normalaccount", "NORMALACCOUNT"),
],
)
@davlee1972 Can you file an issue on https://github.com/snowflakedb/gosnowflake referencing this. I agree with @lidavidm that this should either be handled by the user or by gosnowflake, not at the ADBC level.
I'll open a ticket with Snowflake. Using a single account for both the URL and JWT won't work. This should be handled by the Snowflake GO driver just like how they handle it with the Snowflake PYTHON driver.
I'm trying to connect via python to Snowflake using a P8 encrypted key file, but haven't had any success with either an encrypted or unencrypted file. I looked through the code and I didn't see many options to increase Go debugging/tracing to help me figure out what isn't working..
-----BEGIN ENCRYPTED PRIVATE KEY----- or -----BEGIN RSA PRIVATE KEY-----
With snowsql I've was able to use the rsa_key.p8 encrypted key file to successfully connect. I verified both the fingerprint and decrypted jwt token were also valid within Snowflake user settings and formatting:
Passing the same rsa_key.p8 file with the decryption password using the following python code gives me an invalid JWT token error with limited debugging info:
Returns with these errors: