Alexander Cullen
Protection of data is of the utmost importance in the world of financial services. We are always looking for ways in which we can make access to our data even more secure within kdb+. One area to improve this is by incorporating greater levels of authentication when seeking to make connections to kdb+ processes, specifically within our data handling frameworks.
OAuth is an industry standard token based authentication protocol. Tokens allow users to enter their username and password credentials once against a central service via a secure connection. The token is then validated by the receiving application to grant access. Tokens periodically expire. The token should be always be securely communicated, but even if the token is leaked the users credentials are not exposed.
Timestored’s qStudio has an extensible mechanism to build custom authentication routines. At Data Intellect we have integrated Microsoft’s OAuth protocol with qStudio, allowing greater level of authentication and protection when connecting to a kdb+ process and avoiding having to manage user names and passwords within kdb+.
What is the plugin and how does it work?
The goal of this project was to create a custom authentication plugin for Timestored’s qStudio. We were able to modify qStudio’s example custom authentication project to achieve this so it made a good starting point for the project. The project utilises the Microsoft identity platform and OAuth 2.0 authorization code flow and documentation for this can be found here.
This project utilizes the /oauth2/v2.0/authorize endpoint from the Microsoft Authentication Library for Java . When a user attempts to connect to a server in qStudio using our custom authentication, this endpoint opens a login window in their default browser. This leverages Microsoft’s secure authentication infrastructure to obtain the two essential tokens: an access token and a refresh token. Once the user successfully signs in through the Microsoft web portal, these tokens are returned by the endpoint and captured by the plugin. The plugin then transmits these tokens in place of a password to the q process that is connecting to through the server connection details.
Simplifying the Token Flow:
Our initial plan involved hitting the /authorize endpoint to obtain an authorization code and then using that code in a separate request to the /token endpoint for the actual tokens. However, we discovered a simpler approach. By directly hitting the authorize endpoint with appropriate parameters, we could bypass the need for a separate request and receive both the access token and refresh token in a single step.
Microsoft Entra Project Setup:
The first step involved creating a new project on Microsoft Entra. This project provided essential values for the authorize endpoint request, including:
- ClientId: This unique identifier is for the organisation and allows verification that the user is authorized to login, based on their membership in the Entra organization.
- AppId: A unique identifier for the project within Entra.
- Scopes: These define the permissions granted by the tokens. We use user.read to retrieve token values and offline_access to maintain access for up to an hour before needing to refresh it.
- Redirect URI: For this value we used a placeholder value of localhost. This is what Microsoft will redirect an authenticated user to when they’ve signed in but as we are not implementing a web project and our plugin is capturing the tokens it is not useful for us.
Addressing the Refresh Token Challenge:
Initially, we encountered an issue – the authorize endpoint response didn’t seem to include access to the refresh token. We could read the refresh token value while debugging but were unable to gain access to the variable. To overcome this, we were able to serialize the token cache from the response and iterate through it to extract the refresh token value.
When exporting this plugin for use within qStudio we needed to create a jar with dependencies. We used Shadow for this which allows us to package all necessary libraries within the JAR itself so it works seamlessly with qStudio.
OAuth with TorQ
So we’ve seen how the qStudio OAuth plug in was built and how it works. This next section will discuss how we have been able to integrate this into TorQ.
Our initial use case for this was to incorporate it into a gateway process, to simulate the authenticating of users wanting to perform queries through the gateway. In order to do this, we created a script that can be loaded in with any TorQ process to handle connections from a qStudio with OAuth instance.
But before talking about the components of the script, let’s first look at establishing a connection from qStudio to one of our TorQ processes, in particular the gateway.
We can open a connection to the desired process by opening qStudio and clicking the following: Server -> Add Server. After this, the following dialog box will open.
From here we want to enter the following details:
- Server Type: Click the dropdown and select ‘Kdb with Microsoft Authentication’
- Host: localhost
- Port: The port number where your process is running on within homer
- Name: Title for the connection (Can be given any name)
- Username: The email used for your Microsoft account. This needs to be included as it used as part of the verification process
- Password: This will be left blank as we don’t pass a password to the process for authentication
Once these details have been inputted correctly, press add to complete the connection set up.
Following the addition of Kdb with Microsoft Authentication server, you will be directed to a pop up browser dialog to log in to your Microsoft account. If you are already logged in to your Microsoft account on the browser, your account will automatically be authenticated.
So we’ve been able to open a connection to our process from qStudio. But how are we handling incoming connections within the TorQ process?
Within a script to be loaded in with your chosen TorQ process we have a number of functions used to verify an incoming connection with OAuth. Here is the flow of execution.
The gateway is passed the Username specified above (inputted email) and a string containing the produced access token and refresh token separated by a ; to .z.pw.
Within .z.pw we first check whether a connection is coming from another TorQ process. If it is, we carry out a standard password check to prevent this from going through our authentication procedure.
authconnection takes the user and password that was passed to .z.pw and first checks if the password being used is a combination of two tokens. If so, a function called verifyaccess is executed which runs a get request against the Microsoft graph /me API using the provided access token. This request returns a list of account information and we verify whether the username passed by the user matches the one tied to the account for this access token. If this is a match, then the user, access token, refresh token and expiry are added to a userdetails table to track OAuth connections to the gateway and then a 1b is returned to .z.pw.
Once .z.pw verifies the details and a connection is opened, .z.pc takes the handle of the connection and updates the last entry to the userdetails table to add the linked handle.
Once the initial connection has been completed, a user is now able to send queries from qStudio down to the gateway.
After our initial connection has been established, we need to refresh the access. A checktoken function runs on a timer to see if the expiry time has been exceeded for any connections. If it has, we take the refresh token and pass it to a function called refreshaccess that sends a post request against the Microsoft graph /token API to retrieve new tokens. After it is verified that this token matches the relevant account, the updated details are added to the userdetails table. If the token isn’t verified, then the connection is closed and the details are removed from the userdetails table.
Conclusion
Share this:
