[keycloak-user] Persist Keycloak session cache into JDBC store, no data is written into table

[Röck, Cedric]

Hi,

we are currently trying to persist the in-memory session cache of our Keycloak (9.5.0.Final) deployment into a persistent store, preferably JDBC based.

In order to achieve this, we already updated the configuration and ended up with this config for the Infinispan subsystem:

<subsystem xmlns="urn:jboss:domain:infinispan:6.0">
    <cache-container name="keycloak">
        <transport lock-timeout="60000"/>
        <local-cache name="realms">
            <object-memory size="10000"/>
        </local-cache>
        <local-cache name="users">
            <object-memory size="10000"/>
        </local-cache>
        <local-cache name="authorization">
            <object-memory size="10000"/>
        </local-cache>
        <local-cache name="keys">
            <object-memory size="1000"/>
            <expiration max-idle="3600000"/>
        </local-cache>
        <replicated-cache name="work"/>
        <distributed-cache name="sessions" statistics-enabled="true" owners="${env.CACHE_OWNERS:1}">
            <jdbc-store data-source="KeycloakDS" dialect="SQL_SERVER" fetch-state="true" passivation="false" preload="true" purge="false" shared="true" singleton="false">
                <property name="dropTableOnExit">
                    false
                </property>
                <property name="createTableOnStart">
                    true
                </property>
                <table/>
            </jdbc-store>
        </distributed-cache>
        <distributed-cache name="clientSessions" statistics-enabled="true" owners="${env.CACHE_OWNERS:1}"/>
        <distributed-cache name="authenticationSessions" statistics-enabled="true" owners="${env.CACHE_OWNERS:1}"/>
        [...]
    </cache-container>
    [...]
</subsystem>

Even though the table „ispn_entry_sessions“ gets created once Keycloak starts, no data is being persisted there. Not after 5min and also not once several hours passed. To exclude batch sizes and alike as error cause, our test creates 300 users and performs repeated logins for all of them, so there should also be enough load on the system.

Some more details:

  *   The statistics already show more than 600 cache-loader-misses for the jdbc store, but no successful load.
  *   Our deployment consists of three Keycloak instances running in Kubernetes pods / docker containers.
  *   Target JDBC Database is an Azure managed SQL DB / SQL Server
  *   We can’t see any errors in the logs and also the cache distribution appears to still work amongst all nodes in the cluster.


If you need more details, log excerpts, the full config, …, just give me a ping.

What are we missing? Any help is very much appreciated.

Thanks and kind regards
Cedric

Cedric Röck
______________________________
Senacor Technologies AG
Äußere Cramer-Klett-Str. 21
90489 Nürnberg

M +49 (170) 2274 878

Cedric.Roeck at senacor.com
www.senacor.com


Senacor Technologies Aktiengesellschaft - Sitz: Eschborn - Amtsgericht Frankfurt am Main - Reg.-Nr.: HRB 110482
Vorstand: Matthias Tomann, Marcus Purzer - Aufsichtsratsvorsitzender: Daniel Grözinger

Diese E-Mail inklusive Anlagen enthält vertrauliche und/oder rechtlich geschützte Informationen. Wenn Sie
nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten, informieren Sie bitte den  Absender
und vernichten Sie diese E-Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser  E-Mail ist
nicht gestattet.

This e-mail including any attachments may contain confidential and/or privileged information. If you are
not the intended recipient (or have received this e-mail in error) please notify the sender immediately and
destroy this e-mail. Any unauthorized copying, disclosure or distribution of the materials in this e-mail is
strictly forbidden.