OAuth2 kdb+, Using The Native Q Functions - KX

Kdb+/q Insights: OAuth2 authorization using kdb+

12 June 2019

By Daniel Walsh

What is OAuth2?

OAuth provides a means of accessing resources using the HTTP protocol.

It is an open-standard authorization protocol or framework that provides applications the ability for “secure designated access”. A practical example would be the ability to download contacts from Gmail, into an application or alternatively, upload contacts from the application to Gmail. All done without having to leave the application. It was designed as an authorization tool rather than an authentication tool.

OAuth doesn’t share password data but instead uses authorization tokens to prove an identity between consumers and service providers. As a result, your password is not known by anyone other than Google. OAuth is an authorization protocol that allows you to approve one application interacting with another on your behalf without giving away your password.

OAuth2 integration is available through the REST API’s of platforms such as Salesforce, Eloqua & Marketo

OAuth2 & kdb+

OAuth2 can be used from within q, using the native q functions,

.Q.hp (http post)
.Q.hg (http get)
.z.ph. (http handle)
.j.k

For the purpose of this article, we’ve overwritten the call to .z.ph. However, it could easily be manipulated as a hook into the standard definition of .z.ph.

All code used in available here https://github.com/kxcontrib/oauth2

Notes

As per the code.kx.com wiki. these http calls support using proxy settling.
https://code.kx.com/v2/ref/dotq/#qhp-http-post
Some of the functions above have been modified to illustrate the token exchange.

q)getenv[`http_proxy]
"http://user:pass@proxy.com:8080"
q).Q.hg`:http://www.google.com
"<!doctype html><html itemscope=\"\" itemtype=\"http://schema.org/WebPage\" l..

OAuth2 workflow

For the purposes of this example, let’s consider a user is logged on to the MRP Prelytix application, and they wish to view their Google profile from within the MRP Prelytix application.

There are 3 parties involved in the workflow, the user, the application MRP Prelytix and Google

In general, OAuth authentication follows a six step pattern:

The application requests authorization to Google on the user’s behalf to view their own Google profile.
The application obtains a Grant Token. This is a temporary token and typically expires after a few seconds
The application requests to exchange the Grant Token for an Access Token.
Google validates the Grant Token and issues an Access Token and a Refresh Token. An Access Token from Google lasts 60 mins, a refresh token expires after 1 year.
The user requests their profile off of Google, through the application using the Access Token.
Google verifies the Access Token and returns the users Google profile.

Kdb+ OAuth2 Workflow - KX

Prerequisites

Configure the different endpoints that the resource has.
Different operations are routed to different Google endpoints

q)3 _ .oauth2.PROVIDER`google
auth_endpoint      | "https://accounts.google.com/o/oauth2/v2/auth"
token_endpoint     | "https://oauth2.googleapis.com/token"
userinfo_endpoint  | "https://openidconnect.googleapis.com/v1/userinfo"
revocation_endpoint| "https://oauth2.googleapis.com/revoke"

OAuth2 must be configured by the user, or the administrator of the users account, within Google
Instructions available here
https://developers.google.com/identity/protocols/OAuth2
Once this is in place, a client id & secret will be available to use.
The code stores this information in a dictionary, .oauth2.provider

q).oauth2.provider[`google]
scope              | "openid email profile"
client_id          | "redacted"
client_secret      | "redacted"
auth_endpoint      | "https://accounts.google.com/o/oauth2/v2/auth"
token_endpoint     | "https://oauth2.googleapis.com/token"
userinfo_endpoint  | "https://openidconnect.googleapis.com/v1/userinfo"
revocation_endpoint| "https://oauth2.googleapis.com/revoke"

Define a callback environment
When making calls to Google, a callback endpoint needs to be passed as part of the initial authorization request. This must be one of the pre-configured callback URL’s defined in your Google account
In this example, we’ve used,
localhost:1234

OAuth2 Example

To illustrate the use case, we’ll start a q instance that will act as the application,

OAuth2 Example Native Q - KX

Localhost:1234 Login - KX

Upon entering an email and hitting ‘Login’ a call is made to .z.ph, which recognizes an email address is present, parses out the email, and identifies the provider the email is configured for, in this case Google.

q).oauth2.domains
domain| provider
------| --------
kx.com| google

The user is then directed to a Google authorization page where they can provide their consent. At this point, we record the users attempt to access Google.

A callback is included in the URL which the user is routed to. Google will use this URL as the callback to redirect the user once consent has been granted.

q).oauth2.state
state                                         | username      created                       provider access_token refresh_token ok
----------------------------------------------| ----------------------------------------------------------------------------------
milgliegigfbageikaodhbeibafclbigdwalsh@devweb.kx.com| dwalsh@devweb.kx.com 2019.05.27D15:12:16.004840000 google                              0
// redirect url included in the fields passed to Google
response_type| `code
redirect_uri | "http://localhost:1234/"
scope        | "openid email profile"
access_type  | `offline
prompt       | `consent

Kdb+ OAuth2 Google Integration - KX

Presuming the credentials are accurate, Google will return a Grant Token to the application via the callback URL. The application can exchange this for an Access & Refresh token, see .oauth2.authenticate.

This exchange is made using an HTTP Post call (.Q.hp). Some of the parameters used in this exchange are below. Not displayed is the client_id & client_secret.

The code field contains the Grant Token. The naming convention follows the specifications of the Google REST API.

https://developers.google.com/identity/protocols/OAuth2WebServer

q) // the code field is the Grant Token mentioned above.
q)3#"&" vs postdata
"grant_type=authorization_code"
"redirect_uri=http%3a%2f%2flocalhost%3a1234%2f"
"code=4%2fVwFxFzln8T1y1Z7ynuNslTVZqih_CJ9HlwmnKs6JFeB9OmupIijDAmUYk91hO-UGj23ie8hL7HucJW_dbkAkCbI"

q).Q.hp["https://oauth2.googleapis.com/token";`GET; postdata]

.the above to represent below – uses q markup and provides scrolling if required

The result back is the Access & Refresh tokens which we store locally to reuse.

q).j.k  .Q.hp["https://oauth2.googleapis.com/token";`GET; postdata]
access_token | "..redacted.."
expires_in   | 3600f
refresh_token| "....redacted...."
scope        | "https://www.googleapis.com/auth/userinfo.profile https://www.googleapis.com/auth/userinfo.email openid"
token_type   | "Bearer"
q).oauth2.state
state                                           username      created                       provider access_token   refresh_token    ok
---------------------------------------------------------------------------------------------------------------------------------------
"milgliegigfbageikaodhbeibafclbigdwalsh@devweb.kx.com" dwalsh@devweb.kx.com 2019.05.27D15:43:32.490926000 google   "..redacted.." "...redacted..." 1

.the above to represent below – uses q markup and provides scrolling if required

In order to illustrate the complete workflow, we have configured .z.ph to immediately request the Google profile, upon receipt of an Access Token.

Obtaining the user profile is done manually by calling a modified version of .Q.hmb, .oauth2.hmb. The reason for using this modified version is that .Q.hmb is currently limited to using a Basic token, and cannot use a Bearer token

This uses the Access Token received from Google.

q)hsym`$.oauth2.provider[`google;`userinfo_endpoint]
`:https://openidconnect.googleapis.com/v1/userinfo
q) .j.k .oauth2.hmb[hsym`$.oauth2.provider[`google;`userinfo_endpoint];`GET; .oauth2.getToken`$"dwalsh@devweb.kx.com"]
name          | "Daniel Walsh"
given_name    | "Daniel"
family_name   | "Walsh"
picture       | "https://lh5.googleusercontent.com/-oefx0ydD3tM/AAAAAAAAAAI/AAAAAAAAAmU/nDHBOu6Vpv8/photo.jpg"
email         | "dwalsh@devweb.kx.com"
email_verified| 1b
locale        | "en"
hd            | "kx.com"
OAuth2 Scope

.the above to represent below – uses q markup and provides scrolling if required

OAuth2 Scope

The different resources that can be retrieved using OAuth2 will be controlled by those in ‘scope’ of the Google account.
In this example, we have limited the access for the user to their Google profile. However, this scope could be extended to allow for access to email, contact, calenders etc

The full list of scopes on Google can be viewed at the link below

https://developers.google.com/identity/protocols/googlescopes