keycloak and cockroachdb cloud

Question

I tried to use keycloak db against crdb cloud. I have used https://github.com/codecentric/helm-charts/tree/master/charts/keycloak charts for deployment k8s. I create a db for the keycloak and give the above configuration to success connection. I use my values.yaml and added additional env var:

extraEnv: |
  - name: DB_VENDOR
    value: postgres
  - name: DB_ADDR
    value: xxxx.xxx.cockroachlabs.cloud
  - name: DB_PORT
    value: "xxx"
  - name: DB_DATABASE
    value: keycloak
  - name: DB_USER_FILE
    value: /secrets/db-creds/user
  - name: DB_PASSWORD_FILE
    value:  /secrets/db-creds/password
  - name: JDBC_PARAMS
    value: sslmode=verify-ca&sslrootcert=/secrets/crdb-creds/xxx.crt
  - name: JDBC_PARAMS_FILE
    value: /secrets/crdb-creds/xxx.crt
and also

# Add additional volumes, e. g. for custom themes
extraVolumes: |
  - name: crdb-creds
    secret:
      secretName: keycloak-crdb-creds
  - name: db-creds
    secret:
      secretName: keycloak-db-creds
and mounting

# Add additional volumes mounts, e. g. for custom themes
extraVolumeMounts: |
  - name: crdb-creds
    mountPath: /secrets/crdb-creds
    readOnly: true
  - name: db-creds
    mountPath: /secrets/db-creds
    readOnly: true

So in theory there is no restriction for using cockroach the with postgres db vendor in keycloak(!). And I am going to give a try for this and actually it wasn't give an error but it restaring after while and keeping restarting same period. So it gives an :

Caused by: liquibase.exception.DatabaseException: liquibase.exception.DatabaseException: java.sql.SQLException: IJ031040: Connection is not associated with a managed connection: org.jboss.jca.adapters.jdbc.jdk8.WrappedConnectionJDK8@3a612dd6
or
10:55:31,907 FATAL [org.keycloak.services] (ServerService Thread Pool -- 64) Error during startup: java.lang.IllegalStateException: Failed to retrieve lock  

So my question is what is the variable for giving .crt path and is there any additional progress need to run this correctly

Answer 1

A bit late to this one, but I think there are many people that are potentially looking to understand how to run Keycloak on CockroachDB.

So in theory there is no restriction for using cockroach the with postgres db vendor in keycloak(!).

"In theory" you are correct, but sadly, this is not the case. That exception you are getting is because of an incompatibility in how XA and JTA are setup in Keycloak. And if you got through that, there are further barriers from schema incompatibility. There is a long thread about some of the problems getting CockroachDB working with the Keycloak "legacy" store: Github Issue: Keycloak unable to start using legacy JPA storage with CockroachDB There are plans to support CockroachDB in the "new" store, but that implementation is currently an experimental feature, and will not be the default for another 6-12 months. There will never be CockroachDB support for the “legacy” store from the main distribution, but there is a patched Keycloak maintained by Phase Two in order to enable it. They maintain a docker image that is up to date with Keycloak releases that contains the patch: Keycloak on CockroachDB docker images It must be run with a few configuration options set:

KC_DB=cockroach
KC_TRANSACTION_XA_ENABLED=false
KC_TRANSACTION_JTA_ENABLED=false

Also, regarding your extraEnv section, the Keycloak configuration environment variable keys have changed in recent releases (>18). To set your database connection parameters, you will use the following keys (values are examples):

KC_DB_URL_HOST=xxxx.xxx.cockroachlabs.cloud
KC_DB_URL_PORT=26257
KC_DB_URL_DATABASE=defaultdb
KC_DB_SCHEMA=public
KC_DB_USERNAME=dbuser
KC_DB_PASSWORD=xxx
KC_DB_URL_PROPERTIES='?'