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

[William Burns]

This looks like the same issue I mentioned at [1]. It seems that keycloak is writing to the underlying Infinispan Cache using SKIP_CACHE_STORE flag which is preventing it from writing the entry to any configured store when passivation is configured as false. It seems that Keycloak currently only supports persistence when passivation is true. Unfortunately, a shared store and passivation can have consistency guarantees and as such is no longer a valid configuration with Infinispan 9.4 [2].

[1] http://lists.jboss.org/pipermail/keycloak-user/2018-November/016214.html
[2] https://issues.jboss.org/browse/ISPN-7168

----- Original Message -----
> From: "Cedric Röck" <Cedric.Roeck at senacor.com>
> To: keycloak-user at lists.jboss.org
> Sent: Monday, November 12, 2018 7:24:40 AM
> Subject: [keycloak-user] Persist Keycloak session cache into JDBC store, no data is written into table
> 
> 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.
> _______________________________________________
> keycloak-user mailing list
> keycloak-user at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-user