kdb Products
Overview
KDB.AI
kdb+
kdb Insights
kdb Insights Enterprise
Capabilities
The Data Timehouse
Vector Database Explained
kdb+ Time Series Database
PyKX Python Interoperability
Services & Support
Financial Services
Quant Research
Trading Analytics
Industry & IoT
Automotive
Energy & Utilities
Healthcare & Life Sciences
Manufacturing
Telco
Learn
Overview
Featured Courses
KX Academy
KX University Partnerships
Connect
KX Community
Community Events
Developer Blog
Build
Download
Documentation
Support
About Us
Partner with Us
Become a Partner
Find a Partner
Partner Signup
Join Us
Connect with Us
by Daniel Walsh and Simon Shanks
OAuth2 defines a framework as a means of authorization to access protected 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 authorization server (e.g. 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 API’s of platforms such as Salesforce, Eloqua & Marketo.
OAuth2 defines different methods of how an application may interact with an authorization server to gain authorization. These are often referred to as a ‘grant type’. Examples of grant types are:
For this example, we will concentrate on the ‘Authorization Code’ grant type.
In general, ‘Authorization Code Grant’ authentication follow a pattern which we list here, using kdb+ as the application:
For this example, we will use the OAuth2 features from Google ( https://developers.google.com/identity/protocols/OAuth2 )
This example will demonstrate a user accessing kdb+ using http with their browser. The user with be prompted to enter their gmail address. kdb+ will then initiate the workflow to authorize access to gain an access token from Google & present to the user their registered profile picture.
Prerequisites
q)Q.hg`$":https://www.google.com"
Configure a project within Google Cloud API console (https://console.cloud.google.com/). Reference material https://developers.google.com/identity/protocols/OAuth2
OAuth2 can be used from within q, using the native q functions,
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.
Notes
Schemas used in this example are:
Running the Example
To execute, run the oauth.q script from your local machine & open your browser to http://localhost:1234.
Note: If not running kdb+ from your localhost, edit oauth.q to change .oauth2.redirect_url to the machine running kdb+ (and also check your ‘Authorised redirect URI’ within the Google console that it allows this url).
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
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
----------------------------------------------| ----------------------------------------------------------------------------------
milgliegigfbageikaodhbeibafclbiguser@gmail.com| user@gmail.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
Within this http call to kdb+ will be an Authorization Code. The application can exchange this for an Access & Refresh token, see .oauth2.getTokens.
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 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
---------------------------------------------------------------------------------------------------------------------------------------
"milgliegigfbageikaodhbeibafclbiguser@gmail.com" user@gmail.com 2019.05.27D15:43:32.490926000 google "..redacted.." "...redacted..." 1
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.getAccessToken`$"user@gmail.com"]
name | "Daniel Walsh"
given_name | "Daniel"
family_name | "Walsh"
picture | "https://lh5.googleusercontent.com/-oefx0ydD3tM/AAAAAAAAAAI/AAAAAAAAAmU/nDHBOu6Vpv8/photo.jpg"
email | "user@gmail.com"
email_verified| 1b
locale | "en"
hd | "kx.com"
OAuth2 Scope
Token Refresh
The expiry time of an access token is provided when the access token is generated (and recorded in .oauth.state). If requiring an access token beyond this expiry time, a new access token can be generated using the refresh token (the refresh token has a longer expiry). An example of generating a new access token is provided via .oauth.refresh).
Server to Server
When there is no user present e.g. resources belong to the kdb+ application rather than an individual user, you can use oauth grant flow such as the ‘client credentials’ grant type rather than ‘authorization code’. This uses a different set of calls & parameter values. When performing this action with Google, reference https://developers.google.com/identity/protocols/oauth2/service-account
Revoking a Token
Google provides an endpoint which can be used to revoke an access/refresh token when it is no longer required. This has not been demonstrated as part of this example.
Google 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
Note: This blog, posted on June 29th, 2023, is an update to the original posting on June 12th, 2019, to capture interim developments.