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.

Prerequisites:

• A Google account
• A Google Developer Console project
• cURL, openssl, sed, tr, jq
• In this article I’m using an Amazon Linux on EC2; with the appropriate modifications, it can be done in Windows as well.

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

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

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

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

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:

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
[/code]

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 https://www.googleapis.com/oauth2/v3/token.

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

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

#!/bin/bash

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

EMAIL_ADDRESS="11327003054-us……………..g@developer.gserviceaccount.com"

JWT_CLAIM_SET="$(echo -n "{"iss":"$EMAIL_ADDRESS","scope":"https://www.googleapis.com/auth/bigquery.readonly","aud":"https://www.googleapis.com/oauth2/v3/token","exp":"$(($(date +%s)+3600))","iat":"$(date +%s)"}" | /usr/bin/openssl base64 -A -e  | /bin/sed ‘s/=//g’)"
#echo JWT_CLAIM_SET=$JWT_CLAIM_SET

JWT_SIGNATURE_INPUT=$JWT_HEADER.$JWT_CLAIM_SET
#echo JWT_SIGNATURE_INPUT=$JWT_SIGNATURE_INPUT

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_SIGNATURE=$JWT_SIGNATURE

JWT=$JWT_HEADER.$JWT_CLAIM_SET.$JWT_SIGNATURE
#echo JWT=$JWT

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

[/code]

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 http://superuser.com/questions/606953/bash-oauth-2-0-jwt-script-for-server-to-google-server-applications