Keycloak_CockroachDB.md

We want to use CockroachDB instead of Postgres as database for Keycloak. CockroachDB is a distributed and lockless database.

Thus there is conceptually some differences with Postgres.

In case of concurrent transaction, it may happens that one of the transaction is aborted, the application is in charge of retry the transaction. The way to do it with cockroach is described in https://www.cockroachlabs.com/docs/stable/transactions.html#client-side-transaction-retries.

In summary, we have to create a SAVEPOINT just after the BEGIN transaction. Once all the statements are executed, release the SAVEPOINT.If the is a transaction fails due to concurrent Tx, we rollback to the savepoint and reexecute the statements.

This is the standard behavior of Cockroach, so we need to implement this mechanism for each DB calls.

We have investigated multiple scenario. Among them we thought about implementing an automatic mechanism in Hibernate but it is not a good idea as the parameter request must be reevaluated before resubmit the request.

Thus hibernate is too deep and we came to the conclusion we need to implement it in Keycloak itself.

Currently, we are trying to determine the Db access point in Keycloak to evaluate technical possibilities with minimal impact. Thus, we are strongly interested in any information about your DB access layer.

We just began to analyze it and it seems there is servlet filter (KeycloakSessionServletFilter) to manage transaction coming from the REST API.

However, some transaction management code are present at some other place in the code:

(KeycloakModelUtils :222) public static void runJobInTransaction(KeycloakSessionFactory factory, KeycloakSessionTask task))
public static void runJobInTransaction(KeycloakSessionFactory factory, KeycloakSessionTask task) {
        KeycloakSession session = factory.create();
        KeycloakTransaction tx = session.getTransactionManager();
        try {
            tx.begin();
            task.run(session);
 
            if (tx.isActive()) {
                if (tx.getRollbackOnly()) {
                    tx.rollback();
                } else {
                    tx.commit();
                }
            }
        } catch (RuntimeException re) {
            if (tx.isActive()) {
                tx.rollback();
            }
            throw re;
        } finally {
            session.close();
        }
    }

KeycloakApplication :428
  for (RealmRepresentation realmRep : realms) {
                    for (UserRepresentation userRep : realmRep.getUsers()) {
                        KeycloakSession session = sessionFactory.create();
                        try {
                            session.getTransactionManager().begin();
 
                            RealmModel realm = session.realms().getRealmByName(realmRep.getRealm());
                            if (realm == null) {
                                ServicesLogger.LOGGER.addUserFailedRealmNotFound(userRep.getUsername(), realmRep.getRealm());
                            } else {
                                UserModel user = session.users().addUser(realm, userRep.getUsername());
                                user.setEnabled(userRep.isEnabled());
                                RepresentationToModel.createCredentials(userRep, session, realm, user, false);
                                RepresentationToModel.createRoleMappings(userRep, user, realm);
                            }
 
                            session.getTransactionManager().commit();
                            ServicesLogger.LOGGER.addUserSuccess(userRep.getUsername(), realmRep.getRealm());
                        } catch (ModelDuplicateException e) {
                            session.getTransactionManager().rollback();
                            ServicesLogger.LOGGER.addUserFailedUserExists(userRep.getUsername(), realmRep.getRealm());
                        } catch (Throwable t) {
                            session.getTransactionManager().rollback();
                            ServicesLogger.LOGGER.addUserFailed(t, userRep.getUsername(), realmRep.getRealm());
                        } finally {
                            session.close();
                        }
                    }
                }

As there is a TransactionManagement not only for JPA but also for other components, we are still not sure if it always relevant for DB.

The persistence with Infinityspan is also a topic we planned to investigate, so if you have any good entry point to provide or information which would help us to go in the code, it would also for sure be really useful for us.