kdb+ has a long-standing reputation for speed and efficiency. Used in some of the world’s most demanding data environments and powering the top financial organizations, developers know that unauthorized access could lead to financial loss, system instability, or compliance and regulatory breaches.
This blog will overview kdb+ identity and access management (IAM) and offer practical examples of troubleshooting, fortifying, and navigating access management.
Let’s begin
.z handlers
.z handlers are crucial in managing and monitoring user interactions with the system. Each handler serves a specific purpose, from validating credentials to logging connection events.
Some of the more prominent include:
- .z.pw: Called when a user is validated
- .z.po & .z.pc: Called when a handle is opened or closed
- .z.pg: Called when a get operations are performed
- .z.ps: Called when a set operation is performed
- .z.ph: Called for HTTP get requests.
Let’s explore how some are used to build a secure, role-based access solution. To follow along, please download kdb+ and follow these installation instructions.
Step 1: Configure access control with .z.pw
To begin, we will start a new kdb+ instance on port 5001:
$ q -p 5001Working from a remote port, we create a new table containing usernames and passwords.
$ q -p 5000
q)h:hopen 5001
7i
// Defining .perm.users on 5001
q)users:([user:`mary`john`ann ] class:`basicUser`superUser`basicUser; password:("pwd";"pwd";"pwd"))
q)h(set;`.perm.users;users)
q)h".perm.users"
user| class password
----| ------------------
mary| basicUser "pwd"
john| superUser "pwd"
ann | basicUser "pwd"Notice that we have used several methods to call the remote process. To learn more about these and IPC in kdb+, check out the free KX Academy module.
Step 2: Set authentication rules
With the account table defined, we can now check password authentication attempts using the handle .z.pw.
q)h".z.pw:{[user;pswd] $[pswd~.perm.users[user][`password]; 1b; 0b]}"In this case, successful password authentication will return 1b for true or 0b for false.
Step 3: Test connectivity
You can test connectivity by opening a new session. Let’s try using both the correct and incorrect password credentials.
q)hopen `::5001:mary:wrongpwd // incorrect credentials
'access
q)hopen `::5001:mary:pwd // correct credentials
8i
Notice the authentication error when submitting the wrong password.
Note: If you run into issues and wish to reset passwords, you can undo the definition of .z.pw and allow all users to connect via \x.
q)h"\\x .z.pw"Note: Whilst this example highlights authentication capability, we strongly advise using external authentication services such as LDAP or Kerberos in production environments.
Connection logging with .z.po and .z.pc
Connections are logged using the handlers .z.po (port open) and .z.pc (port close), capturing information such as user and IP identifiers.
Step 1: Let’s define a new table and explore
q)h".ipc.connections: ([handle:()];time: ();user:();id:();state:())"Step 2: Next, we will define .x.po to update the table with connect details.
q)ID:`Client5000
q)h".z.po:{ `.ipc.connections insert (x;.z.p;.z.u;.z.w `ID;`open)}"
q)h".ipc.connections"
handle| time user id state
------| ------------------Step 3: Let’s now open several new connections to test.
q)hopen 5001
10i
q)hopen 5001
11i
q)hopen 5001
12i
q)h".ipc.connections"
handle| time user id state
------| -----------------------------------------------------
9 | 2024.11.21D17:12:13.572488665 mwoods Client5000 open
10 | 2024.11.21D17:12:18.428053708 mwoods Client5000 open
11 | 2024.11.21D17:12:19.434530122 mwoods Client5000 open
12 | 2024.11.21D17:12:20.217362997 mwoods Client5000 openNotice how the table records each connection attempt with the following information:
- Handle (x)
- Time (.z.p)
- User (.z.u)
- ID on Remote process — can reference with .z.w
- State (open/close)
Step 4: Close connections using .z.pc
q)h".z.pc:{`.ipc.connections upsert `handle`time`state!(x;.z.p;`close)}"
q)hclose 9i
q)h".ipc.connections"
handle| time user id state
------| -----------------------------------------------------
9 | 2024.11.21D17:13:36.937625898 mwoods Client5000 close
10 | 2024.11.21D17:12:18.428053708 mwoods Client5000 open
11 | 2024.11.21D17:12:19.434530122 mwoods Client5000 open
12 | 2024.11.21D17:12:20.217362997 mwoods Client5000 openNotice that after closing handle 9i, the state changed to close.
I should also mention that the client handle 9i matches the server handle 9i in our example, which is purely coincidental. If you want to check which handle to close, run 9″.z.w” and close the handle returned.
Restrict queries with .z.pg.
Let’s explore permission control and restrict query execution to specific users using the .z.pg handle.
Step 1: Identify user classes in the user table
The table below shows that “Mary” and “Ann” are defined as basic users, and “John” is defined as a super user.
user| class password
----| ------------------
mary| basicUser "pwd"
john| superUser "pwd"
ann | basicUser "pwd"kdb+ splits users into three distinct classes:
- BasicUser: Can only execute specific stored procedures defined on the server
- PowerUser: Can write free-form queries but not write to the database unless they are executing a stored procedure
- SuperUsers: Can execute all code
To ensure that only John and his fellow super users can connect and run queries, we will define the following permissions via the .z.pg handle:
q)h".z.pg:{[query]class:.perm.users[.z.u][`class];
$[class~`superUser;
value query;
\"No Permissions\"]}"Step 2: Test access control
Let’s test access control by running a simple query for Mary and John.
q)johnHandle"2+2" // all ok
4
q)maryHandle"2+2" // not ok anymore
"No Permissions"Mary is assigned the basic user class, so she cannot execute the query; however, John has no issues as a super user.
Asynchronous message control .z.ps
The asynchronous message handler (.z.ps) differs from the previously discussed message handler (.z.pg) in that it does not return a result to the calling process. However, by assigning it as an empty function, you can prevent external connections from invoking asynchronous messages.
For example, it will prevent any asynchronous requests from being executed.
q)h".z.ps:{}" // Disables asynchronous message handlingHTTP event handlers .z.ph
The .z.ph handler processes HTTP GET requests and can fetch data from the server, compose HTML pages, execute queries, and format results into an HTML table.
Even if you rely on KX’s commercial solutions for enterprise-grade security, understanding the fundamentals of kdb+ permissions is invaluable. By customizing .z handlers, you can:
- Secure your system against unauthorized access
- Log and monitor connections
- Enforce role-based query restrictions
To learn more and apply what I have discussed to a real-world scenario, why not try our Formula1 capstone project, which involves securing the infrastructure of a fictitious team for racing engineers?
You can also reach out to me on our Slack community.
Happy coding!
