SNOW-1432112: JWT Authentication not stripping region from account when constructing DSN #1132
Comments
github-actions
bot
changed the title
|
hi and thanks for submitting this - will check. |
|
thanks for the pointers in this new issue ! so in #1033 , using the initial investigations with a simple test program shows that when only using vanilla gosnowflake, both repro: # cat my.go
package main
import (
"flag"
"fmt"
"os"
sf "github.com/snowflakedb/gosnowflake"
)
func main() {
if !flag.Parsed() {
flag.Parse()
}
accountName := os.Getenv("SFACCOUNT")
connectionString := "admin@" + accountName + ".privatelink/mydb/myschema?warehouse=compute_wh&authenticator=SNOWFLAKE_JWT"
config, err := sf.ParseDSN(connectionString)
if err != nil {
fmt.Println(err)
}
fmt.Println("===> Config from ParseDSN:")
fmt.Printf("%+v\n", config)
dsn, _ := sf.DSN(config)
fmt.Println("===> DSN from config generated by ParseDSN:")
fmt.Printf("%+v\n", dsn)
config = &sf.Config{
Account: accountName,
User: "admin",
Database: "mydb",
Schema: "myschema",
Warehouse: "compute_wh",
Authenticator: sf.AuthTypeJwt,
}
dsn, _ = sf.DSN(config)
fmt.Println("===> Config from manual Config:")
fmt.Printf("%+v\n", config)
fmt.Println("===> DSN from config defined manually:")
fmt.Printf("%+v\n", dsn)
}1. with
|
sfc-gh-dszmolka
added
status-information_needed
|
I think your test code with my.go should not add ".privatelink" to the account. Not every account has a .privatelink correct? connectionString := "admin@" + accountName + ".privatelink/mydb/myschema?warehouse=compute_wh&authenticator=SNOWFLAKE_JWT" Later there is.. I think the issue here is that .privatelink aka the region needs to stripped from account if there is one.. Here's what I'm seeing from ADBC with debugging on.. For adbc.snowflake.sql.account = "MYACCOUNT" https://MYACCOUNT.snowflakecomputing.com:443/session/v1/login-request? etc.. For adbc.snowflake.sql.account = "MYACCOUNT.privatelink" https://MYACCOUNT.privatelink.snowflakecomputing.com:443/session/v1/login-request? etc. BTW using MYACCOUNT.privatelink with alternative authentication other than JWT works fine with the Go Driver.. |
|
I think I found the bug.. in DSN() the cfg.Region is captured before it is stripped out of the account.. But in parseDSN() I don't see any logic to check or capture the Region.
This impacts the final value of cfg.Host: With no region and no host with a starting value of cfg.Account = MYACCOUNT.privatelink.. With no region and no host with a starting value of cfg.Account = MYACCOUNT |
|
hardcoded my demo account is in AWS US WEST Oregon, so account format is
this is a live account which conforms to a regular customer account, all values were read from running using latest gosnowflake from Then to rule out any issues possibly related to us-west-2 (which is the default deployment) I repeated the test with an account in AWS EU CENTRAL Frankfurt.
repro, with actually connecting to Snowflake using JWT: package main
import (
"flag"
"fmt"
"os"
"encoding/pem"
"crypto/rsa"
"crypto/x509"
"errors"
"database/sql"
"log"
sf "github.com/snowflakedb/gosnowflake"
)
func parsePrivateKeyFromFile(path string) (*rsa.PrivateKey, error) {
bytes, err := os.ReadFile(path)
if err != nil {
return nil, err
}
block, _ := pem.Decode(bytes)
if block == nil {
return nil, errors.New("failed to parse PEM block containing the private key")
}
privateKey, err := x509.ParsePKCS8PrivateKey(block.Bytes)
if err != nil {
return nil, err
}
pk, ok := privateKey.(*rsa.PrivateKey)
if !ok {
return nil, fmt.Errorf("interface convertion. expected type *rsa.PrivateKey, but got %T", privateKey)
}
return pk, nil
}
func doConnectAndQuery(dsn string, myquery string) {
db, err := sql.Open("snowflake", dsn)
if err != nil {
log.Fatalf("failed to connect. %v, err: %v", dsn, err)
}
defer db.Close()
query := myquery
rows, err := db.Query(query) // no cancel is allowed
if err != nil {
log.Fatalf("failed to run a query. %v, err: %v", query, err)
}
defer rows.Close()
var v string
for rows.Next() {
err := rows.Scan(&v)
if err != nil {
log.Fatalf("failed to get result. err: %v", err)
}
fmt.Println(v)
}
if rows.Err() != nil {
fmt.Printf("ERROR: %v\n", rows.Err())
return
}
}
func main() {
if !flag.Parsed() {
flag.Parse()
}
accountName := os.Getenv("SFACCOUNT")
userName := os.Getenv("SFUSER")
connectionString := userName + "@" + accountName + "/test_db/public?warehouse=compute_wh&authenticator=SNOWFLAKE_JWT"
config, err := sf.ParseDSN(connectionString)
pk, _ := parsePrivateKeyFromFile("rsa_key_unencrypted.p8")
config.PrivateKey = pk
if err != nil {
fmt.Println(err)
}
fmt.Println("===> Config from ParseDSN:")
fmt.Printf("%+v\n", config)
dsn, _ := sf.DSN(config)
fmt.Println("===> DSN from config generated by ParseDSN:")
fmt.Printf("%+v\n", dsn)
fmt.Println("===> Connecting to Snowflake - 1")
doConnectAndQuery(dsn, "SELECT 'ParseDSN with keypair';")
config = &sf.Config{
Account: accountName,
User: userName,
Database: "test_db",
Schema: "public",
Warehouse: "compute_wh",
Authenticator: sf.AuthTypeJwt,
PrivateKey: pk,
}
dsn, _ = sf.DSN(config)
fmt.Println("===> Config from manual Config:")
fmt.Printf("%+v\n", config)
fmt.Println("===> DSN from config defined manually:")
fmt.Printf("%+v\n", dsn)
fmt.Println("===> Connecting to Snowflake - 2")
doConnectAndQuery(dsn, "SELECT 'manual Config with keypair';")
}result in AWS US WEST 2:result in AWS EU CENTRAL 1:all 2 x 4 attempts (locator, regionless, privatelink locator, privatelink regionless) are successful when using only gosnowflake without ADBC. So for now, I still did not find any bug in gosnowflake which would result in incorrect parse of region/privatelink. Please observe I'm not using a manually specified
Makes sense, because there is no JWT which is then wrongly populated by feeding incorrect configuration into gosnowflake, coming from ADF GUI or ADBC (or, if there's a bug with certain type of url/connection string, this is yet to be proved)
I believe it comes from parseAccountHostPort |
|
Ok I looked at the ADBC code and it is calling..
The test code above is setting up a config and running DSN on it and then using the DSN to connect.. There is logic in DSN() to strip Account into cfg.Account and cfg.Region. The Connect() function in Go Snowflake doesn't call DSN() or parseDSN().. It jumps directly to fillMissingConfigParameters(). I think the right solution is to remove all the posDot functionality out of DSN() and ParseDSN() and just put it in fillMissingConfigParameters().. fillMissingConfigParameters is already handling other quirks like "-" in cfg.Account.. Here is the python snowflake connector code that handles ".", "-" and "global".. This logic isn't the same which is creating additional confusion how the same "account" used in the python driver / snowsql won't work in the Go driver.. Without logic to parse myaccount out of myaccount.privatelink in fillMissingConfigParameters the account value used to generate a JWT token is not valid.. The Azure Data Factory webui doesn't have the option to specify REGION or HOST independently from ACCOUNT so account cannot be treated as a pure account value in config.
|
|
Did you try running the above test program with your account name populated, without ADF ? I believe it should be successfuly able to connect with keypair, even when account name is regioned or has privatelink. With that said, I'm trying to set up a repro to see how exactly ADF is sending the account/connection string and yes, probably the next step would be
|
|
reproduced the issue from ADF GUI and reading their specs I also did not find any ways to force Whoever hits this same issue and not using privatelink; a quick workaround would be to use the regionless notation ( We'll see how we can move forward with enhancing gosnowflake, but the issue itself needs to be optimally fixed on ADF GUI, perhaps by allowing users to specify region or parsing the account name correctly when Keypair auth is used. |
sfc-gh-dszmolka
added
enhancement
|
I don’t use ADF and I’m not 100% sure why this issue was logged in ADBC, but I had initial problems with ADBC connections until I separated account and region when populating a config just for JWT authentication. With basic and web authentication account.privatelink worked fine for the account parameter with the go driver in ADBC so it was confusing why changing the authentication method to JWT ended up in a failed connection. It would be better to make the GO driver more resilient like the Python driver vs having every client like ADBC or ADF, etc implement REGION, etc.. |
sfc-gh-dszmolka
added
status-in_progress
|
on the gosnowflake side, prepared a change at #1146 which has been merged now and will be part of the next gosnowflake release cycle which should happen very soon as far as i know. when creating the JWT token in gosnowflake, the new logic now checks what has been fed into it as the How this version of gosnowflake with the extra |
|
released in May 2024 cycle with version 1.10.1 |
Can we reopen this one?
#1033
The test was using keypair.go to test JWT authentication and keypair.go calls DSN() under the hood.
The ADBC Go Snowlake driver is calling parseDSN() or is populating gosnowflake.Config to create a DSN. Both methods are missing logic to strip region from the account so ".privatelink" ends up in the JWT token id which makes it invalid..
apache/arrow-adbc#1777
apache/arrow-adbc#1422
The text was updated successfully, but these errors were encountered: