[keycloak-dev] Keycloak with CockroachDB

[Stian Thorgersen]

CockroachDB is not something we will consider including in the core code
base at the moment. It is a very high overhead supporting multiple DB and
something like CockroachDB that is less like a traditional relational DB
will be too much effort for us to consider at this stage.

I would suggest that you maintain this as a separate extension, but we can
discuss how that can be done. What changes are needed to make this into a
drop-in extension that doesn't require a forked Keycloak build for instance.

On Fri, 2 Nov 2018 at 09:56, Poiffaut Romain <romain.poiffaut at elca.ch>
wrote:

> Hello,
>
> For the Cloudtrust project (https://github.com/cloudtrust), I have
> investigated the feasibility of supporting CockroachDB in Keycloak.
>
> I have created a fork based on Keycloak 3.4.3 (it was the latest version
> when I started on this) working with CockroachDB 2.0.2. It is now working
> with this DB, and passes all unit tests. Some resilience tests have also
> been performed to validate the whole integration.
> Address of the repo :
> https://github.com/cloudtrust/keycloak/tree/cockroach-db
>
> For those who might be interested, here are some details:
>
> CockroachDB is a multi-master SQL database designed to run in the cloud
> and being resilient to failures (https://www.cockroachlabs.com/).
> This database has a lot of very interesting properties such as being
> lockless, distributed and supporting serializable isolation.
> CockroachDB introduces the notion of SAVEPOINT. As this DB is lockless, a
> transaction may fail due to a concurrent transaction. In such case, we can
> rollback to the SAVEPOINT and retry the transaction. Retrying transactions
> has the benefit of increasing their priority each time they are retried,
> thus increasing their likelihood to succeed. (More detailed information are
> available in their very good documentation (e.g.
> https://www.cockroachlabs.com/docs/stable/transactions.html#client-side-transaction-retries,
>
> https://www.cockroachlabs.com/blog/how-cockroachdb-distributes-atomic-transactions/,
>
> https://www.cockroachlabs.com/blog/serializable-lockless-distributed-isolation-cockroachdb/
> ))
>
> So even if CockroachDB uses PostgreSQL driver to communicate with the DB,
> one of the challenges was to add an automatic transaction retry mechanism
> with the smallest impact on Keycloak.
> Thanks to the architecture of Keycloak, this mechanism can be added in
> KeycloakSessionServletFilter with a very limited impact.
>

This can be achieved with a custom KeycloakTransaction wrapper instead.
That way you do not need to modify Keycloak source code.


>
> A second challenge is due to the rollbackOnly mechanism implemented in
> Keycloak and Hibernate: after a rollback, a transaction cannot be used
> anymore.
> The retry operation must be performed in the same transaction to increase
> its priority.
> Thus the rollbackOnly mechanism is disabled/bypassed in order to keep the
> transaction active even after a rollback is issued.
> As suggested by CockroachDB, we replace the default Hibernate transaction
> coordinator class to a custom one (
> https://github.com/cockroachdb/hibernate-savepoint-fix).
> Moreover, we mainly modify JpaKeycloakTransaction so that if the
> transaction fails to commit due to retryable transaction error, we disable
> the rollbackOnly mechansim to able to retry the transaction.
>

We could add an option to the connection provider to allow setting a custom
transaction coordinator.


>
> CockroachDB does not support addition of some constraints (e.g. primary
> keys) after table creation.
> To circumvent this limitation, we can create a new table, migrate the
> data, delete the old table, rename the new table with the correct name.
> As CockroachDb was not supported by Keycloak until now, we didn’t adapt
> all existing liquibase scripts. We decided to create a new liquibase script
> which creates the whole database schema for the current version.
> This current limitation is being discussed and will be fixed in future
> release (https://github.com/cockroachdb/cockroach/issues/19141).
>

Not sure how to handle this. If you need to modify Liquibase scripts that
pretty much leaves you with having to maintain your own "duplicates".


>
> Some tests have also been slightly adapted to support SERIALIZABLE
> isolation, so other DBs configured with such level can also benefit from
> this adaptation (i.e. PostgreSQL)
>
> These challenges have been solved and our forked version of Keycloak is
> now compatible with CockroachDB, but it currently is at the cost of
> breaking the usage of standard databases.
> Our company is really interested to add the support of this DB into
> Keycloak and to provide it to the community.
> The next steps now would be to migrate our fork to the latest version of
> Keycloak and add the support of this DB without breaking support of the
> others and we would be happy to discuss it.
>
>
> Cheers,
> Romain Poiffaut
>
> _______________________________________________
> keycloak-dev mailing list
> keycloak-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-dev