SNOW-975540: Conflict between the project and Snowflake driver Google SDKs with ADC (Application Default Credentials)

[phvalerii]

Since version 1.6.21 Snowflake SDK (NodeJS) is using google-sdk/storage to upload files to GCS when using PUT command.

This works correctly unless the Snowflake SDK is invoked from an environment where google-sdk/storage is already in use and is authenticated using GOOGLE_APPLICATION_CREDENTIALS environment variable.

These credentials take priority over the GCS_ACCESS_TOKEN kept in my Snowflake account. So when the SDK tries to upload file, it will attempt uploading to the incorrect storage.

This must be affecting all the customers using GCP as their provider.

To reproduce:

• setup a nodejs project with an environment variable GOOGLE_APPLICATION_CREDENTIALS pointing to a GCP account, preferably with very limited permission so it can't accept any file uploads.
• create a snowflake test account with GCP as service provider
• in the nodejs project, include and configure snowflake-sdk
• invoke PUT command using snowflake-sdk

Observe an error where the PUT command will fail since it's got no rights to upload to the private GCP account. Something like following:

"Unable to upload file. Error: Error: [email protected] does not have storage.objects.get access to the Google Cloud Storage object. Permission 'storage.objects.get' denied on resource (or it may not exist)"

[sfc-gh-dszmolka]

hi and thank you for submitting this issue - also especially for the detailed reproduction scenario ! Managed to reproduce the same issue, so we'll take a look how the driver could handle this situation better. This issue will be updated with the progress.

As a workaround, unsetting the envvar inside the process' context with something like

if (process.env.GOOGLE_APPLICATION_CREDENTIALS) {
	delete process.env.GOOGLE_APPLICATION_CREDENTIALS;
}

worked for me during the reproduction; maybe it's not so simple in a 'live' environment but perhaps helps. Anyways we're taking a look.

[pharturs]

The google credentials may be also read from the configuration file $HOME/.config/gcloud/application_default_credentials.json. so unsetting of the process.env.G... is not feasible in that case.

[sfc-gh-dszmolka]

as mentioned in the support case: there might be several workarounds; one of which is unsetting the envvar perhaps inside a wrapper which is around the connection.execute and can unset/set the envvar before/after calling the storage operations.
Probably there would be many other more elegant ways around it too.

I'll keep this issue posted with the new information as the investigation / fix effort progresses. Thank you for bearing with us !

[pharturs]

Just sharing here for the record, this obviously is also taking into account google metadata server auth as a third way of authentication for google libraries using ADC.

[dbleaken]

Any estimate of when this might be fixed? Thanks

[sfc-gh-dszmolka]

unfortunately no ETTR available at the moment of writing, but will keep this thread posted.
In the meantime, hopefully one of the suggested workarounds can help mitigating and working around the issue.

[sfc-gh-dszmolka]

thank you for your patience on the matter ! PR #754 is ready for review and it seems to work in our internal tests.

However since we probably don't have the exact environment and settings as you have, if it would be possible, i think it might be a good idea to check out the fix branch https://github.com/snowflakedb/snowflake-connector-nodejs/tree/gcpoperationerror and see if it resolves the error for you too . Let us know please if you had a chance to test and whether it helped.

[sfc-gh-dszmolka]

hey @pharturs @naira-petrosyan-m thank you for taking the time to test the fix proposal, and also for the comments on the PR, really appreciated !

As a summary, there are three different auths,

• the env variable
• a global config file
• a Google Meta server

and we only considered the first authentication, which was resolved with the PR. According to the dev team investigation, the Google storage library ignored the storage client created by the connector if there are prioritized auth methods on the machine. This is to say that the reason why you faced the result not found error is that the Google storage library sent the query request not to the GCP on the Snowflake server, but to the GCP server of which the credential was saved on your machine.

At this point, it would be really useful for us to have the reproduction steps for the second and the third part. It seems like there are many ways to use the config file etc) save the config data in the package.json or using config modules, and at this point it's not sure how you use the Google meta server.

Do you think it would be possible to provide us with steps/setup for reproducing the remaining issue?

[naira-petrosyan-m]

@sfc-gh-dszmolka we have created a small script that could help to reproduce both issues we have found in the fix. Here is a link to the package https://github.com/naira-petrosyan-m/snowflake-issue. Please follow the readme for reproducing 2 issues with different setups. Thank you.
cc: @pharturs

[sfc-gh-dszmolka]

excellent, thank you so much !! really appreciated. we'll take a look.

[dbleaken]

Hello, I can see there is some progress being made on this, I wondered is it possible yet to estimate how soon it might be resolved, even roughly? Many Thanks

[sfc-gh-dszmolka]

hi - indeed we're actively working on this; you can follow the above PR for the current progress. Can't really estimate how much long it takes, unfortunately there are multiple moving parts. Thank you for bearing with us !

[sfc-gh-dszmolka]

I see the PR has been now merged, so the fix will now be released with the next upcoming driver version in the coming days. Thank you very much again for your contribution !

[sfc-gh-dszmolka]

fix released with snowflake-sdk 1.10.0, closing this issue

[APTy]

For anyone experiencing this problem in some of the cases they didn't address yet, this may help you: Snowflake-sdk patch to fix #705 when using application_default_credentials.json