Netlify cannot read bigquery credentials with newline characters

[matthewbrandt]

Steps To Reproduce

1. Configure a BigQuery connection using a service account (json key) in evidence locally.
2. Deploy your project to netlify and copy the variables from the deployment settings page to your Netlify environment variables.
3. Deploy your build. The build will succeed, but SQL queries will not execute.

Environment

• Node version (node -v): 20.0.0
• npm version (npm -v): 9.6.4
• OS: MacOS Ventura 13.4.1
• Browser: Brave
• Database: BigQuery (native table)

Expected Behavior

The SQL queries should execute.

Actual Behaviour

The SQL queries return errors (as do any visuals that use them).

Workarounds

No known workaround currently, as I did not de-authorise my BigQuery service account.

[matthewbrandt]

Preliminary research suggests that breaking up the service account credentials into separate environment variables is not allowed and is the cause of the error.

The solution is described as using a single variable with base64 encoding.

Source: https://stackoverflow.com/a/74137803

[archiewood]

Hey there,

Briefly dipped into twitch channel but had to leave before you got here but found your recording on twitch.

~~Breaking up the env vars should be fine - (we've deployed BQ projects using a service accounts and Netlify). I think the issue is that you created a new evidence project "channel-points" inside of your "evidence-poc" directory, which was already pushed to GitHub. What you need to do instead is move the channel-points directory out of the evidence-poc directory, and push that channel-points directory to GitHub as its own repo Then point netlify at that "channel-points" repo!~~

See below

[archiewood]

Btw thanks for the mention. And was cool to watch someone try the thing for the first time!

[archiewood]

I think the issue is that you created a new evidence project "channel-points" inside of your "evidence-poc" directory, which was already pushed to GitHub.

I take it back, I have reproduced your error, this is a legit bug with the way the private key is being parsed

Thanks for reporting!

[archiewood]

https://github.com/googleapis/google-cloud-node/issues/1173

[matthewbrandt]

Hey @archiewood, thanks for joining the stream! I actually really like this approach and with the embedded credentials, I can see how this can be part of something larger that I am currently building. I see you already linked a PR that would fix this, so will keep my eyes open for this to get resolved sometime soon 😊

[archiewood]

This is now released If you upgrade to latest, this should deploy successfully.

VSCode command: Evidence: Update to Latest Version

[matthewbrandt]

Have updated to latest version via VSCode, pushed to a new branch and PR'd my repository to generate a deploy preview on Netlify - but I get the same issue still 😢 https://deploy-preview-1--jin-channelpoints.netlify.app/

[archiewood]

Hmm. I have this working on my deployment now.

What version of evidence are you on? npm --list

[archiewood]

(if you're open to making your repo public I can also take a look there)

[archiewood]

btw the way to test this locally is to do

EVIDENCE_DATABASE="bigquery" EVIDENCE_BIGQUERY_PROJECT_ID="....." EVIDENCE_BIGQUERY_PRIVATE_KEY="... [all your other env vars] npm run build:strict

If this is successful, it should work on netlify too

[matthewbrandt]

i'm currently on:

my-evidence-project@0.0.1 /Users/matthewbrandt/github/justinnumbers-cp ├── @evidence-dev/core-components@2.0.4 └── @evidence-dev/evidence@22.0.1

i made the repo public now as I am not sure how to best debug locally. I may switch to Postgres but wanted to confirm this was fixed first as I do want to use BQ for something as well

[archiewood]

okay so I'm almost 100% sure this problem is to do with how netlify parses newline characters in environment variables.

Seems my fix attempted didn't work for this. I have tested and replicated your error in the latest.

Workaround

The issues is specifically with the bigquery private key, which contains many newline characters. Something in the copy/paste operation into Netlify doesn't seem to respect these, such that Netlify is not seeing them.

The solution I found is instead to go find the private key saved in the .evidence/template/evidence.settings.json file stored in your local machine, and copy and paste that into netlify. This actually copies each of the characters as the string \n rather than a newline, but we explicitly replace \n with newline characters in the BigQuery Connector.

Another alternative would be to deploy with Evidence Cloud which does not have this issue.

Let me know if this resolves your issue

[matthewbrandt]

You're right! Copy-pasting the key from there with newline characters into Netlify worked :) Thank you so much for your help!