In this blog post, I will cover the steps required to enable SSO in a CockroachDB cluster, specifically, when running locally on MacOS, but the steps are similar for any environment.
Installation
We need to install the following software:
- CockroachDB
- PostgreSQL Server
- OpenJDK
- Keycloak
Let's get started!
PostgreSQL Server
Install PostgreSQL Server for MacOS: https://postgresapp.com/
Upon installation you will see the elephant in your toolbar close to the clock
Make sure you create a username and password with admin privileges, and start the server.
CockroachDB
Make sure CRDB is running locally and listens on port 8080.
Instructions on where to download the binary is on the Release page.
This is the command I use
/usr/local/bin/cockroach start-single-node --certs-dir /Users/fabio/certs --store /Users/fabio/cockroach-data
I use my fancy CRDB Launcher for MacOS. It's not strictly required, but I loved how the PostgresApp menu bar looks and wanted the same for CockroachDB.
OpenJDK 21+
Keycloak requires Java 21+, so I installed openJDK from brew.
brew install openjdk
After installation, I was still seeing Java 14 when issuing a java -version.
Check for solutions in the keg instructions:
$ brew info openjdk
==> openjdk: stable 23.0.2 (bottled) [keg-only]
Development kit for the Java programming language
https://openjdk.java.net/
Installed
/usr/local/Cellar/openjdk/23.0.2 (602 files, 337.0MB)
Poured from bottle using the formulae.brew.sh API on 2025-06-05 at 13:28:19
From: https://github.com/Homebrew/homebrew-core/blob/HEAD/Formula/o/openjdk.rb
License: GPL-2.0-only WITH Classpath-exception-2.0
==> Dependencies
Build: autoconf ✘, pkgconf ✘
Required: freetype ✔, giflib ✔, harfbuzz ✔, jpeg-turbo ✔, libpng ✔, little-cms2 ✔
==> Requirements
Build: Xcode (on macOS) ✔
Required: macOS >= 10.15 (or Linux) ✔
==> Caveats
For the system Java wrappers to find this JDK, symlink it with
sudo ln -sfn /usr/local/opt/openjdk/libexec/openjdk.jdk /Library/Java/JavaVirtualMachines/openjdk.jdk
[...]
I thus run as suggested
sudo ln -sfn /usr/local/opt/openjdk/libexec/openjdk.jdk /Library/Java/JavaVirtualMachines/openjdk.jdk
and I now get
$ java -version
openjdk version "23.0.2" 2025-01-21
Keycloak
Installation and basic config
Download the Keycloak binaries for the OpenJDK platform, see link in the OpenJDK Getting Started page.
Unzip it, cd into the dir and issue the start command.
We just want to confirm there are no problem getting it up and running.
Note, I use port 8081 as the default, 8080, is reserved for my locally running CockroachDB cluster.
$ bin/kc.sh start-dev --http-port 8081
Running the server in development mode. DO NOT use this configuration in production.
2025-06-05 13:45:53,073 INFO [org.Keycloak.quarkus.runtime.storage.infinispan.CacheManagerFactory] (main) Starting Infinispan embedded cache manager
2025-06-05 13:45:53,144 INFO [org.Keycloak.quarkus.runtime.storage.infinispan.CacheManagerFactory] (main) JGroups JDBC_PING discovery enabled.
2025-06-05 13:45:53,270 INFO [org.infinispan.CONTAINER] (main) Virtual threads support enabled
2025-06-05 13:45:53,463 INFO [org.infinispan.CONTAINER] (main) ISPN000556: Starting user marshaller 'org.infinispan.commons.marshall.ImmutableProtoStreamMarshaller'
2025-06-05 13:45:53,832 INFO [org.Keycloak.connections.infinispan.DefaultInfinispanConnectionProviderFactory] (main) Node name: node_904367, Site name: null
2025-06-05 13:45:54,791 INFO [io.quarkus] (main) Keycloak 26.2.5 on JVM (powered by Quarkus 3.20.1) started in 5.618s. Listening on: http://0.0.0.0:8081
2025-06-05 13:45:54,791 INFO [io.quarkus] (main) Profile dev activated.
2025-06-05 13:45:54,791 INFO [io.quarkus] (main) Installed features: [agroal, cdi, hibernate-orm, jdbc-h2, Keycloak, narayana-jta, opentelemetry, reactive-routes, rest, rest-jackson, smallrye-context-propagation, vertx]
Go to http://localhost:8081 to make sure you see the website.
You'll be prompted to create a temporary admin user, then to login.
Upon login, you should see something like the below
Now you can Ctrl+C to stop the server.
While you can keep using the builtin database, I prefer using Postgres in case data gets lost.
Related docs are here.
Start Keycloak pointing at your local PostgreSQL Server.
bin/kc.sh start-dev --http-port 8081 --db-url-host=localhost --db-password=postgres --db-username=fabio --db postgres
The server should be up and running, and if you inspect PostgreSQL server, you'll see a new database keycloak was created with a bunch of tables.
fabio: ~ $ psql -d keycloak
Timing is on.
psql (17.4, server 16.2 (Postgres.app))
Type "help" for help.
keycloak=# \d
List of relations
Schema | Name | Type | Owner
--------+-------------------------------+-------+-------
public | admin_event_entity | table | fabio
public | associated_policy | table | fabio
public | authentication_execution | table | fabio
public | authentication_flow | table | fabio
public | authenticator_config | table | fabio
...
Configuration
Everything is ready, we can start configuring Keycloak and CRDB for SSO integration.
Create admin user in Keycloak
Now you can login into Keycloak with the temp user. Since you will be prompted to delete it after the first login, call it tempadmin and password tempadmin.
Upon login, go to Users, click Add User button and create a username called, simply, admin and optionally set the "Email Verified" toggle to On.
Click Create, then click on the newly created user.
Go to the Credential tab and create a password, simply admin to make it easy to remember.
Then go to tab Role mapping to add the admin role to the user admin. So click on Assign Role, make sure you choose the Filter by realm roles and there you'll see the role_admin.
Now you can logout, log back in as the admin user, go to the Users section and delete the tempadmin user.
Create regular users and group
You can now go ahead and create a regular user, set a password, then create a group, and assign the user to the group.
Make sure the user you create has a corresponding username in CRDB.
For example, I created my Keycloak user fabio with password fabio, and there's also a fabio username in my CRDB cluster.
The keycloack and crdb password for the user don't have to match.
> show users;
username | options | member_of
---------------+---------+------------
admin | | {}
cockroach | | {admin}
fabio | | {admin}
root | | {admin}
(4 rows)
Time: 7ms total (execution 7ms / network 0ms)
Create a Realm main
Setup a new realm and client as instructed in the Getting Started doc.
I called my realm main.
When you set your current realm to main, go to Realm Settings page, and scroll to the bottom to see the Endpoints.
The well-known page for a realm called main is http://localhost:8081/realms/main/.well-known/openid-configuration.
If you open the endpoint, you'll see something like this: we need these details to configure the SSO integration.
{
"issuer": "http://localhost:8081/realms/main",
"authorization_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/auth",
"token_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/token",
"introspection_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/token/introspect",
"userinfo_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/userinfo",
"end_session_endpoint": "http://localhost:8081/realms/main/protocol/openid-connect/logout",
"frontchannel_logout_session_supported": true,
"frontchannel_logout_supported": true,
"jwks_uri": "http://localhost:8081/realms/main/protocol/openid-connect/certs",
"check_session_iframe": "http://localhost:8081/realms/main/protocol/openid-connect/login-status-iframe.html",
"grant_types_supported": [
"authorization_code",
"client_credentials",
"implicit",
"password",
...
Try clicking on the link in jwks_uri: you'll see the keys that will help CRDB validate the authenticity of the JWT.
Create a Client BankApp
Go to the Clients page and create a new client called BankApp.
Set client_authentication to on, make sure Standard flow and Direct Access Grant is checked.
Set the valid redirect URI to CockroachDB's callback url: https://localhost:8080/oidc/v1/callback.
Set the web origin to CockroachDB's webserver: https://localhost:8080.
Create the client, then go to the Credentials tab and jot down the client secret.
In my case, the client secret is 401O9E8awNtCL934rPx3pbWgIoJxv1Bb.
Save the configuration, then verify we can get a JWT from Keycloak manually from the terminal client, simulating any webservice such as CockroachDB.
Open a Terminal, and run the below. I hope you have jq installed else try installing it with brew.
Notice that we're hitting the token_endpoint taken from the well-known configuration for this realm.
We are also passing the client_id and client_secret, plus the username and password for the user that wishes to authenticate, in this case, user fabio.
$ curl -X POST "http://localhost:8081/realms/main/protocol/openid-connect/token" \
-H "Content-Type: application/x-www-form-urlencoded" \
-d "grant_type=password" \
-d "client_id=BankApp" \
-d "client_secret=401O9E8awNtCL934rPx3pbWgIoJxv1Bb" \
-d "username=fabio" \
-d "password=fabio" \
-d "scope=openid" | jq
The output is truncated here as the tokens are very large
{
"access_token": "eyJhb...",
"expires_in": 300,
"refresh_expires_in": 1800,
"refresh_token": "ey...",
"token_type": "Bearer",
"id_token": "ey...",
"not-before-policy": 0,
"session_state": "ba6328d5-39d6-42df-9ceb-225319245541",
"scope": "openid email profile"
}
Grab the id_token and paste it into a JWT debugger such as https://token.dev/, it will decode it.
Please note the token is not encrypted, it's just base64 encoded.
The website will show these interesting details about who logged in.
Notice that preferred_username is what Keycloak uses to share the username field.
{
"exp": 1749153006,
"iat": 1749152706,
"jti": "dda84f1c-3f07-4e59-8553-5d1672f8a66d",
"iss": "http://localhost:8081/realms/main",
"aud": "BankApp",
"sub": "18596f04-b197-4752-8e6c-790222e235c7",
"typ": "ID",
"azp": "BankApp",
"sid": "5f20475c-4930-407e-b5c1-37d736eba6e8",
"at_hash": "epLu6r16ndQosFol9K6sOw",
"acr": "1",
"email_verified": true,
"name": "Fabio Ghirardello",
"preferred_username": "fabio",
"given_name": "Fabio",
"family_name": "Ghirardello",
"email": "fabio@cockroachlabs.com"
}
At the bottom of the page you'll also see the private and public keys.
These are used by CockroachDB to validate the authenticity of the JWT, that is, it will provide proof that the JWT comes indeed from the Keycloak IdP,
and it can do so because we will share the keys that were used to sign the JWT (remember, those are the keys in jwks_uri in the well-known).
Well, we get the token just fine, so Keycloak is ready!
CockroachDB configuration
Relevant docs are:
Thus I setup these cluster settings.
Notice that the values are taken mostly from the well-known page.
--
-- SQL SSO
--
-- trivial
SET CLUSTER SETTING server.jwt_authentication.enabled = true;
-- taken from field "issuer" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.issuers.configuration = 'http://localhost:8081/realms/main';
-- taken by navigating to the URL in field "jwks_uri" in the .well-known page
SET CLUSTER SETTING server.jwt_authentication.jwks = '{"keys":[{"kid":"h....."}]}';
-- this is the client_id
SET CLUSTER SETTING server.jwt_authentication.audience = '["BankApp"]';
SET cluster setting server.oidc_authentication.client_secret = '401O9E8awNtCL934rPx3pbWgIoJxv1Bb';
-- this is the field we grab from the token to use as the db's username
SET CLUSTER SETTING server.jwt_authentication.claim = 'preferred_username';
-- reference only: this strips the @bank.com in, say, fabio@bank.com
-- SET CLUSTER SETTING server.identity_map.configuration = 'http://localhost:8081/realms/main /^(\S+)(?:@) \1';
-- takes everything passed in the `claim`, that is, `preferred_username`.
SET CLUSTER SETTING server.identity_map.configuration = 'http://localhost:8081/realms/main /^(\S+) \1';
--
-- DBConsole SSO
--
SET cluster setting server.oidc_authentication.provider_url = 'http://localhost:8081/realms/main';
SET cluster setting server.oidc_authentication.redirect_url = 'https://localhost:8080/oidc/v1/callback';
SET cluster setting server.oidc_authentication.scopes = 'openid';
SET cluster setting server.oidc_authentication.button_text = 'Keycloak SSO';
SET CLUSTER SETTING server.oidc_authentication.generate_cluster_sso_token.enabled = true;
Once these are set, you should see these 2 buttons appear in the Login page
DB Console SSO login
Clicking on Keycloak SSO will direct you to the Keycloack SSO website.
Login as fabio/fabio as that's the Keycloak username.
Once you hit enter, Keycloak will authenticate you and, if successful, redirect you to the DBConsole.
Behind the scene, a JWT is sent by Keycloak to CRDB that confirms your authentication.
CockroachDB verifies and validates the JWT, and grants you access.
SQL SSO login
Either using the curl command above or by clicking on the Generate JWT auth token button in the DBConsole, get hold of your id_token.
Then use it as a password in your connection DBURL.
Make sure you add options=--crdb:jwt_auth_enabled=true to the DBURL string to inform CRDB that what you're passing is not a password, but a JWT.
$ export TOKEN=ey...
$ cockroach sql --url "postgres://fabio:$TOKEN@localhost:26257/defaultdb?sslmode=require&options=--crdb:jwt_auth_enabled=true"
#
# Welcome to the CockroachDB SQL shell.
# All statements must be terminated by a semicolon.
# To exit, type: \q.
#
# Client version: CockroachDB CCL v25.1.0 (x86_64-apple-darwin19, built 2025/02/14 18:09:08, go1.22.8 X:nocoverageredesign)
# Server version: CockroachDB CCL v25.1.7 (x86_64-apple-darwin19, built 2025/05/26 13:41:39, go1.22.8 X:nocoverageredesign)
# Cluster ID: e360faa9-2ba3-4e92-bd51-fc7e88cf24a8
# Organization: Workshop
#
# Enter \? for a brief introduction.
#
fabio@localhost:26257/defaultdb>
This concludes the tutorial, I hope you had fun setting up your local IdP!
For any question, reach out to the CockroachDB Community Slack channel.