Keycloak and CRDB integration

[boraozkan]

Is your feature request related to a problem? Please describe.
I'm always frustrated when I want to connect keycloak to crdb cloud. It can connect and create the schema and keycloak pod is never come stable.

Is there any study for crdb and keycloak integration, or do you have a plan for supporting keycloak in future.

Describe the solution you'd like
We have cdrb cloud database for keycloak. It is hosted by kubernetes.

Describe alternatives you've considered
We tried to dump schema and make it crdb suitable, but the situation is same.

Additional context
Add any other context or screenshots about the feature request here.

Jira issue: CRDB-3476

[blathers-crl]

Hello, I am Blathers. I am here to help you get the issue triaged.

I have CC'd a few people who may be able to assist you:

• @joshimhoff (found keywords: kubernetes,cloud)

If we have not gotten back to your issue within a few business days, you can try the following:

• Join our community slack channel and ask on #cockroachdb.
• Try find someone from here if you know they worked closely on the area and CC them.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}

[rafiss]

cc @vy-ton @piyush-singh

This has come up before multiple times. One example is here from the forum: https://forum.cockroachlabs.com/t/select-for-update-more-restrictive-than-postgresql/3860

Keycloak uses a syntax that's not supported in CockroachDB. It might be possible to talk to the Keycloak devs and get them to make a change upstream such as this one: ajwerner/keycloak#1. Unfortunately this alone is not sufficient to make cockroach work with Keycloak. Even after that patch, it hits another limitation in cockroachdb: #54477

So to have full support, Keycloak would probably need to add full awareness of CockroachDB.

[kismanhong]

I try to run keycloak(11.0.3) with cockroachdb by the following steps:

1. run keycloak with postgresql, after the database is created, dump the sql script (plain). I need to do this step because I cannot find any postgresql manual script, so i need to create it in postgresql first
2. create database in cockroachdb. run cockroach posgresql migration script from dump file (1)
3. point keycloak to cockroachdb

So far, keycloak is working well. I have tried to add realm, users, and generate token. Still in testing

[timveil]

thought i would reference the Keycloak issue on this integration... https://issues.redhat.com/browse/KEYCLOAK-18110

[werdnum]

2. create database in cockroachdb. run cockroach posgresql migration script from dump file (1)

@kismanhong Which script is that? Has a link from your comment been stripped out?

[kismanhong]

@werdnum init script for keycloak. Because init script not working in crdb, so we need to create the db in postgresql and import to crdb

[pd1drone]

Hi @kismanhong do you have detailed steps in migrating to cockroackdb ? Also could you teach me on which parts should be removed in the dump sql script ? Thank you.

[kismanhong]

Hi @pd1drone
I tried it on keycloak ( 9.9.5 from codecentric helm chart ) and cockroachdb 5.1.2 from cockroachdb helm chart ( version 20.2.7 ). Here is the step that I do:

1. setup keycloak with postgresql
2. dump the keycloak DB using pg_dump
3. IMPORT script to cockroachdb
4. switch keycloak DB connection to CRDN
   Script is running well for that version mention above

I just try latest version, keycloak ( chart version 17.0.3 ) and crdb ( chart version 7.0.1 ), the script cannot be executed well. Here are some problem:

1. expression index break the IMPORT to crdb
2. some comment in script also cause the problem
3. ...

To make it run, I dump from postgresql PLAIN sql and I execute the script one by one, from CREATE TABLE till DATA INSERT. We need to make sure the version in migration_model table to prevent keycloak run liquibase. So far, it run.

Things need to be concerned if we do the step that I do:
We cannot upgrade keycloak directly if any changes of the script is not supported by crdb

Hopefully, keycloak can support crdb for next...

Cheers

[pd1drone]

Hi @kismanhong - Thank you for your detailed answer. Yeah, I actually tried importing the postgresql PLAIN sql to cockroachdb (latest version: v21.2) and keycloak (v17.0.1). but I am getting an error of :

ERROR: unimplemented: to import into a table with expression indexes

I also noticed there were comments in the pgdump output of postgresql. Anyway I'll try to do follow your steps and pointers and hopefully it runs.

Cheers

[kismanhong]

Hi @pd1drone
You are welcome. If get DuplicateModelException, it means liquibase run again. To prevent it run, you can check migration_model table, compare to liquibase version. Keycloak save liquibase versioning there.

I get the same error about expression indexes

Hopefully you can run it

Cheers

[pd1drone]

Hi @kismanhong do you happen to know how to disable using liquibase due to it is trying to create the tables again when I start it even if I was able to create those tables manually.
This is the error that I am getting

ERROR: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-1.0.0.Final.xml::1.0.0.Final-KEYCLOAK-5461::sthorger@redhat.com:
     Reason: liquibase.exception.DatabaseException: ERROR: relation "keycloak.public.realm_attribute" already exists [Failed SQL: (0) CREATE TABLE public.REALM_ATTRIBUTE (NAME VARCHAR(255) NOT NULL, VALUE VARCHAR(255), REALM_ID VARCHAR(36) NOT NULL)]

Thank you

[pd1drone]

Hi @kismanhong - I managed to make it work, I edited the pg_dump file and remove all the "CREATE INDEX" and "ALTER TABLE" queries and also I remove this line:

ALTER TABLE public.role_attribute OWNER TO postgres;

I also removed all the comments that is creating an error whenever you import the pg_dump file to the cockroachdb.

The pg_dump file contains only the "CREATE TABLE" and "COPY" queries.

When I run keycloak; it is now working and I am able to create realms, and users.

Thank you.

[kismanhong]

Hi @pd1drone - Nice, glad to hear that

Yes, create index script cause the import problem in crdb. We can create index in crdb directly if needed

cheers

[pd1drone]

Hi @kismanhong - Yes I actually tried using the database without the index script and a database with index scripts executed manually. And both of them works file so I think you can either choose to execute it manually or to not include it in your postgresql -> cockroachdb migration.

Thanks again for you help @kismanhong.

Cheers!

[kismanhong]

Hi @pd1drone - You are welcome

Cheers!

[xgp]

A bit late to this thread, but there is now a fork maintained by Phase Two that includes the appropriate patches to run Keycloak on CRDB. They make releases every time a new Keycloak release comes out, usually within a day or two. It solves the JTA problem mentioned above, and includes the necessary schema migrations that are compatible with CRDB.

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

There is a long thread over at the Keycloak 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

Note that 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.

Hope this helps for people looking for an easy way to run Keycloak on CRDB!