Some like it OAth (BigQuery)

Second issue of the series about OAuth (you can see the previous here). I’ll try to explain how to use the Google OAuth 2.0 mechanism to authorize a server side scripts without the need of the user’s credentials, in order to automate the INSERT or SELECT (or WHATEVER) processes.

I am testing this with BigQuery service, so in another post I’ll be able to get data from the sample datasets provided by Google.


First we need to enable BigQuery for our project (go create one if you don’t have one):

  1. enable google api
  2.  bigquery api

When we click on the Enabled APIs tab, we should see it listed:

bigquery api enabled

Go to APIs & auth / Credentials section on the left and Create new Client ID with the Service account option:

service account

Your browser will download a json file, that you should copy it in a safe place, but we’re not going to use it.

email address

note down the Email address that was generated for your Client ID.

You’ll need to generate also a P12 file, by clicking on the Generate new P12 key button, and download it:

p12 key

notice the pass-phrase for the P12 file (by default is notasecret).

Suppose the downloaded P12 file is named tutorial.p12. We will convert this P12 file to a PEM by issuing this command in a terminal console:

[code language=”bash” gutter=”true” light=”false”]
openssl pkcs12 -passin pass:notasecret -in tutorial.p12 -nocerts -nodes -out tutorial.pem

this will remove the pass-phrase from the P12 file and generate a tutorial.pem that we can use with openssl.

Next step is to get an Access Token from Google with a JSON Web Token (JWT): we create the JWT with the Email address above plus some boilerplate constants. I’m not going into details on how the JWT is generated, but it is essentially a base64 encoded string composed by a header, a claim set and a signature; the signature is a little more tricky as it is calculated using SHA-256 hashing algorithm and has the characters forward slash [/] and underscore [_] substituted respectively with plus [+] and minus [-] signs. See documentation here. The JWT is finally sent via a HTTP POST call to

here is the complete bash script, please change the EMAIL_ADDRESS variable with the appropriate value:

[code language=”bash” gutter=”true” light=”false”]


JWT_HEADER="$(echo -n ‘{"alg":"RS256","typ":"JWT"}’ | /usr/bin/openssl base64 -A -e)"


JWT_CLAIM_SET="$(echo -n "{"iss":"$EMAIL_ADDRESS","scope":"","aud":"","exp":"$(($(date +%s)+3600))","iat":"$(date +%s)"}" | /usr/bin/openssl base64 -A -e  | /bin/sed ‘s/=//g’)"


JWT_SIGNATURE="$(echo -n $JWT_SIGNATURE_INPUT | /usr/bin/openssl sha -sha256 -sign tutorial.pem | /usr/bin/openssl base64 -A -e | /bin/sed ‘s/=//g’ | /usr/bin/tr ‘/+’ ‘_-‘)"

#echo JWT=$JWT

/usr/bin/curl -s -d "grant_type=urn:ietf:params:oauth:grant-type:jwt-bearer&assertion="$JWT -X POST "" | /usr/bin/jq -r .access_token


this script will output the Access Token retrieved from Google. The Access token is valid for 1 hour, you can store it in a text file and reuse for your API calls until it expires, then redo from start.

# standing on the shoulders of giants: thanks to

0 thoughts on “Some like it OAth (BigQuery)

Leave a Reply

Your email address will not be published. Required fields are marked *