Keycloak on Distributed SQL (CockroachDB) - Part 1/2

With data breaches and cyber attacks becoming increasingly common, modern organisations require a robust security system in place that is also highly available. Keycloak is a popular open-source IAM solution that not only provides robust security features but also high availability between nodes by using a shared cache. It's built to be scalable and fault-tolerant, ensuring that your organisation's authentication and authorization services are always available when you need them. Keycloak can handle large user volumes and can be deployed in a distributed environment, making it suitable for businesses of all sizes.

The data within Keycloak is persisted in a database, however, and if that database does not provide the same level of resilience and availability as Keycloak then this compromises the solution. Traditional database backends such as postgres and MS SQL cannot provide the multi-region, multi-active capabilities and provide a single point of failure.

CockroachDB is a distributed SQL database designed for resilience and scalability. It's built to handle massive amounts of data and provide ACID transactions and distributed SQL for global scale. With CockroachDB, you can ensure that your data is always available, even in the face of network outages or hardware failures. Additionally because cockroach is postgres wire-compatible it is possible to configure Keycloak to connect to a CockroachDB. Below we will provide an example of how to implement Keycloak on a distributed CockroachDB on AWS EC2 instances.

This is the first half of a two-part post. In this post we will cover how to configure Keycloak to store data in Cockroach. In the subsequent post we will configure Keycloak to discover and share caches with other nodes by registering themselves on CockroachDB.

Target Architecture

For this implementation, the target architecture will be as shown below:

In this post we will only be looking at the first region (us-west-1).

The CockroachDB has been set up in advance on c5d.xlarge EC2 nodes with VPC peering used to allow connectivity between cockroach nodes in the disparate regions.

The keycloak nodes will also be c5d.xlarge nodes, with the following prerequisites:

• Openssl installed (sudo apt install openssl)
• Java installed (sudo apt install default-jdk)
• Ports 8080, 8443 and 7800 open between keycloak nodes
• Port 26257 open between keycloak nodes and the internal load balancers
• UDP buffers resized:

Edit /etc/sysctl.com

# Allow a 25MB UDP receive buffer for JGroups
net.core.rmem_max = 26214400
# Allow a 1MB UDP send buffer for JGroups
net.core.wmem_max = 1048576

Run the following:

sudo sysctl -p

Additionally, one Keycloak node will also require:

• Postgres database installed (sudo apt install postgresql)
• Links2 browser (or similar) installed (sudo apt install links2)

Basic Keycloak installation (Node1)

The first stage is to download Keycloak to the first node (in us-west-1 in this example):

wget https://github.com/keycloak/keycloak/releases/download/20.0.3/keycloak-20.0.3.tar.gz
gunzip ./keycloak-20.0.3.tar.gz
tar xvf ./keycloak-20.0.3.tar

Once Keycloak is downloaded, we will launch this is “dev” mode to check all is running as expected:

cd keycloak
nohup bin/kc.sh start-dev > keycloak.log 2> keycloak.err &

Once Keycloak is running in development mode we can then configure admin password using a browser on the local host:

links2 http://localhost:8080

The Keycloak Admin console can now be accessed:

This verifies the Keycloak server is functioning. We will now configure the server to be ready for production.

Configure Node1 for Production

The first stage will be to secure the interface with SSL, we will then configure Keycloak to use postgres as an interim step. Finally, we will configure Keycloak to connect to a CockroachDB. We require the interim step of using postgres to generate the schema as this cannot currently be generated directly in cockroach from Keycloak.

First off, we stop the running keycloak server.

To generate self-signed certificates, in the keycloak directory run the following commands:

mkdir ssl_certs
cd ssl_certs
openssl req -new > cert.csr
openssl rsa -in privkey.pem -out key.pem
openssl x509 -in cert.csr -out cert.pem -req -signkey key.pem -days 3650
cat key.pem>>cert.pem

We now change the Keycloak server to use https, edit conf/keycloak.conf to include the following values for:

# If the server should expose healthcheck endpoints.
health-enabled=true
# If the server should expose metrics endpoints.
metrics-enabled=true
# HTTP
# The file path to a server certificate or certificate chain in PEM format.
https-certificate-file=${kc.home.dir}/ssl_certs/cert.pem
# The file path to a private key in PEM format.
https-certificate-key-file=${kc.home.dir}/ssl_certs/key.pem
# Hostname for the Keycloak server.
hostname=keycloak01

Once this has been done, we build and run the keycloak server again:

bin/kc.sh build
nohup bin/kc.sh start > keycloak.log 2>keycloak.err &

Ensure port 8443 is open between the browser and the Keycloak node, and connect to the admin interface.

Now we have an encrypted connection to keycloak, we can configure the database backend.

stop keycloak and then setup the postgres databases and user:

sudo su postgres
psql
postgres=# create database keycloak;
postgres=# create user keycloak with password 'keycloak';
postgres=# grant all on database keycloak to keycloak;
postgres=# create database jgroups;
postgres=# grant all on database jgroups to keycloak;

Confirm can log on from keycloak user to both dbs:

Now we change the database backend to postgres. Again edit the conf/keycloak.conf file and ensure the following values are set:

# The database vendor.
db=postgres
# The username of the database user.
db-username=keycloak
# The password of the database user.
db-password=keycloak
# The full database JDBC URL. If not provided, a default URL is set based on the selected database vendor.
db-url=jdbc:postgresql://localhost/keycloak

Again build the Keycloak configuration and start the server. Once this has been done check to confirm database created.

As per the Keycloak documentation (https://www.keycloak.org/getting-started/getting-started-zip) we will create a new realm and a new user to ensure the database is being populated.

Setting the new users password:

Once this has been done, we can test we can log in with the new user:

We then confirm that the data is being created in the database. This can be done via the psql command line tool:

psql –host=localhost -U keycloak -d keycloak
postgres=# select from realm;
postgres=# select * from user_entity;

Now to change the connection to use the Cockroach Distributed database, we first need to stop keycloak and then use pg_dump to extract the data from postgres:

pg_dump –host=localhost -U keycloak -d keycloak > keycloak.dmp

Once we have the output we can put this into cockroach. We first need to create the databases and the user. Using your preferred sql client (in this example I am using the cockroach sql command from the database node), connect to CockroachDB and issue the following commands:

create user keycloak with password 'keycloak';
create database keycloak;
create database jgroups;
grant all on database keycloak to keycloak;
grant all on database jgroups to keycloak;

We then verify that we can connect as the newly created user:

We now populate the Cockroach database from pg_dump output.

cockroach sql --host=ajs-db01:26257 --certs-dir=certs -d keycloak -u keycloak -f keycloak.dmp

Verify data has been created by querying the “realm” and “user_entity_ tables.

We now need to put the Cockroach DB ca.crt file on the keycloak node as cockroach requires ssl secured communications.

We stop Keycloak server and configure keycloak to connect to the CockroachDB by editing conf/keycloak.conf to edit the database url and disable XA transactions as Cockroach does not support XA transactions. Include the following lines:

# The full database JDBC URL. If not provided, a default URL is set based on the selected database vendor.
# db-url=jdbc:postgresql://localhost/keycloak
db-url=jdbc:postgresql://ajs-db-demo-int-1b85bcdea0b73e74.elb.us-west-1.amazonaws.com:26257/keycloak?sslmode=require&sslrootcert=${kc.home.dir}/crdb_certs/ca.crt
# disable XA transactions
transaction-xa-enabled=false

We can now start keycloak again and confirm login and data being created by creating a new user and checking the “user_entity” table in CockroachDB.

In part 2 we will cover how to add more nodes to the cluster and ensure that the keycloak nodes are able to register themselves on CockroachDB which, then allows Keycloak to share cache information.