1

I'm trying to use Snowpipe rest api as is pointed in Snowflake site:
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-rest-apis.html#data-file-ingestion

I found a python example in here, my code and steps are pretty much the same:
https://community.snowflake.com/s/article/Connect-to-Snowpipe-Rest-API-by-using-JWT-Token

I checked the token in https://jwt.io/#debugger and is a valid jwt token.

However Snowpipe api responds always with:

{
  "code": "390144",
  "data": null,
  "message": "JWT token is invalid.",
  "success": false,
  "headers": null
}

Am I missing something?

I created the keys using exactly these steps:
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-rest-gs.html#step-3-configure-security-per-user

Also I tried thise other python code (and other ones), but having the same error:
https://docs.snowflake.com/en/user-guide/data-load-snowpipe-rest-load.html#sample-program-for-the-python-sdk

Francisco Albert
  • 1,577
  • 2
  • 17
  • 34

1 Answers1

3

I ran into this as well and wanted to call the REST API from Go so needed to convert the Python script over. One issue I ran into was that standard Go libraries do not support encrypted PKCS 8 keys, I'd actually recommend starting without the key encrypted to remove an extra obstacle. I understand you went through these steps from the docs, but I'll put them in for a step by step.

Generate Keys (Unencrypted)

$ openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
$ cat rsa_key.p8
-----BEGIN PRIVATE KEY-----
MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQChSSRI8qUHxvoe
TME1CQuUtGWQ+a2esAZ/yOaaVbGpAo8B3X8....

$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
$ cat rsa_key.pub
-----BEGIN PUBLIC KEY-----
MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAoUkkSPKlB8b6HkzBNQkL
lLRlkPmtnrAGf8jmmlWxqQKPAd1/Aw+kn....

Update Snowflake User

Set the public key for the user, only the key itself remove the first and last lines from the

ALTER USER MY_USER SET rsa_public_key='MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEAoUkkSPKlB8b6HkzBNQkL
lLRlkPmtnrAGf8jmmlWxqQKPAd1/Aw+kn....';

Get the fingerprint stored on the user after updating.

DESCRIBE USER MY_USER;

Find the property RSA_PUBLIC_KEY_FP and copy the value. This will be used in creating the JWT.

SHA256:+Uys1...

Creating the Fingerprint

The Python code for creating this fingerprint can be found here. https://github.com/snowflakedb/snowflake-ingest-python/blob/master/snowflake/ingest/utils/tokentools.py#L108

That was a big starting point for me, I wanted to make sure I could create the fingerprint that matched what was in Snowflake. Here is my code and tests in Go.

import (
    "crypto/rsa"
    "crypto/sha256"
    "crypto/x509"
    "encoding/base64"
    "encoding/pem"
    "github.com/pkg/errors"
)

func calculatePublicKeyFingerprint(privateKey string) (string, error) {
    pemBlock, _ := pem.Decode([]byte(privateKey))
    parsedKey, err := x509.ParsePKCS8PrivateKey(pemBlock.Bytes)
    if err != nil {
        return "", errors.Wrap(err, "parse error")
    }

    var privKey *rsa.PrivateKey
    var ok bool
    if privKey, ok = parsedKey.(*rsa.PrivateKey); !ok {
        return "", errors.New("Unable to parse RSA private key")
    }

    pubKey := privKey.Public().(*rsa.PublicKey)
    pubDER, err := x509.MarshalPKIXPublicKey(pubKey)
    if err != nil {
        return "", err
    }

    hasher := sha256.New()
    hasher.Write(pubDER)
    shaB64encoded := base64.StdEncoding.EncodeToString(hasher.Sum(nil))

    return "SHA256:" + shaB64encoded, nil
}

Unit test - Private key and expected fingerprint from Snowflake have been truncated use your full values. Also using Testify for assertions.

const privateKey = `-----BEGIN PRIVATE KEY-----
MIIEvgIBADANBgkqhkiG9w0BAQEFAASCBKgwggSkAgEAAoIBAQChSSRI8qUHxvoe
TME1CQuUtGWQ+a2esAZ/yOaaVbGpAo8B3X8....`

func Test_calculatePublicKeyFingerprint(t *testing.T) {
    gotFingerprint, err := calculatePublicKeyFingerprint(privateKey)
    require.NoError(t, err)
    fromSnowflake := "SHA256:+Uys1..."
    require.Equal(t, fromSnowflake, gotFingerprint)
}

Creating the JWT

The get_token part of the Python Security Manager calls the fingerprint creation and returns the JWT.

And this is my Go code, it is not fancy in that it doesn't check a current token like the Python code. It is creating a new token each time based on your Snowflake account, user, and private key.

import (
    "github.com/dgrijalva/jwt-go"
)

const (
    issuer         = "iss"
    expireTime     = "exp"
    issueTime      = "iat"
    subject        = "sub"
    expireDuration = time.Hour
)

func CreateJWT(account, user, privateKey string) (string, error) {
    qualifiedUsername := strings.ToUpper(account + "." + user)
    publicKeyFp, err := calculatePublicKeyFingerprint(privateKey)
    if err != nil {
        return "", err
    }

    claims := jwt.MapClaims{
        issuer:     qualifiedUsername + "." + publicKeyFp,
        subject:    qualifiedUsername,
        issueTime:  time.Now().Unix(),
        expireTime: time.Now().Add(expireDuration).Unix(),
    }
    token := jwt.NewWithClaims(jwt.SigningMethodRS256, claims)
    pk, err := jwt.ParseRSAPrivateKeyFromPEM([]byte(privateKey))
    if err != nil {
        return "", err
    }

    return token.SignedString(pk)
}

For clarity my claims would look like

claims = jwt.MapClaims{
        "iss": "MYSFACCT.MY_USER.SHA256:+Uys1...",
        "sub": "MYSFACCT.MY_USER",
        "iat": 1620322087,
        "exp": 1620325687,
    }

Make a Request

curl --location --request POST 'https://mysfacct.snowflakecomputing.com/v1/data/pipes/MY_DATABASE.MY_SCHEMA.MY_PIPE/insertFiles' \
--header 'Authorization: Bearer generated.JWT.from-CreateJWT(account, user, privatekey)' \
--header 'Content-Type: application/json' \
--data-raw '{"files":[{"path":"my-file.json"}]}'

Making sure to replace the URL with your account and fully qualified pipe. Also replace the header with the your generated JWT.

stsmurf
  • 430
  • 4
  • 8