Persist session in JDBC store without using external infinispan cluster

[olivierboudet]

Description

We need to store session in a JDBC store, but the flag SKIP_CACHE_STORE has been introduced in commit KEYCLOAK-4187 Added UserSession support for cross-dc prevents writing in the store.

Thomas Darimont has proposed a patch in a previous discussion (https://groups.google.com/g/keycloak-dev/c/sOBzG76f2FE) and @mposolda seemed to approve the idea to be able do disable this flag (https://lists.jboss.org/pipermail/keycloak-dev/2018-August/011089.html). There was no change since then (2018).

At the moment, we have not found any way to natively store sessions in a JDBC store. Because of this, we are considering to use in production ( 😨 ) a workaround deploying a custom keycloak-infinispan-models jar. Moreover, we are wondering if this will be compatible with the future Quarkus new store.

Should we propose a new PR allowing to enable the SKIP_CACHE_STORE flag only when a remote-store is present in configuration ? Is there any chance that this PR will be approved ?

We were able to persist sessions with an external infinispan cluster with jdbc store, but this leads us to a new blocking issue : #10577

Discussion

https://groups.google.com/g/keycloak-dev/c/sOBzG76f2FE

Motivation

Business needs to guarantee to never lose user sessions

Details

We may detect if a remote-store is in used by reading the infinispan configuration. Only if this is the case, the SKIP_CACHE_STORE flag should be added on CacheDecorators.skipCacheStore and CacheDecorators.skipCacheLoaders.

[daviddelannoy]

Hi @mposolda

Sorry to ping you directly, we are still wondering if proposing a PR on this is relevant or not ?
thanks

[ghilainm]

I have the same issue. I am trying to configure a JDBC cache store for the sessions and no session is written to database because of this. This should be at least explained in the server installation documentation and it would be nice if a way to configure this could be provided.

Otherwise any guidance on how to implement durable sessions should be provided.

[daviddelannoy]

Hi,

The below configuration (from standalone-ha.xml) worked until Keycloak legacy 19.0.3, with jdbc-store persistence, patching the skipCacheStore flag in org.keycloak.models.sessions.infinispan.CacheDecorators (see @thomasdarimont comment here in cross PR #15619).

<cache-container name="keycloak" marshaller="JBOSS" modules="org.keycloak.keycloak-model-infinispan">
    <transport lock-timeout="60000"/>
    <local-cache name="realms">
        <heap-memory size="10000"/>
    </local-cache>
    <local-cache name="users">
        <heap-memory size="10000"/>
    </local-cache>
    <local-cache name="authorization">
        <heap-memory size="10000"/>
    </local-cache>
    <local-cache name="keys">
        <heap-memory size="1000"/>
        <expiration max-idle="3600000"/>
    </local-cache>
    <replicated-cache name="work">
        <expiration lifespan="900000000000000000"/>
    </replicated-cache>
    <distributed-cache name="authenticationSessions" owners="2">
        <expiration lifespan="900000000000000000"/>
    </distributed-cache>
    <distributed-cache name="offlineSessions" owners="2">
        <expiration lifespan="900000000000000000"/>
    </distributed-cache>
    <distributed-cache name="offlineClientSessions" owners="2">
        <expiration lifespan="900000000000000000"/>
    </distributed-cache>
    <distributed-cache name="loginFailures" owners="2">
        <expiration lifespan="900000000000000000"/>
    </distributed-cache>
    <distributed-cache name="actionTokens" owners="3">
        <heap-memory size="-1"/>
        <expiration interval="300000" lifespan="900000000000000000" max-idle="-1"/>
    </distributed-cache>
    <distributed-cache name="sessions" owners="2">
        <expiration lifespan="900000000000000000"/>
        <jdbc-store data-source="IspnDS" fetch-state="false" passivation="false" preload="true" purge="false" shared="true">
            <property name="databaseType">POSTGRES</property>
            <table fetch-size="10000" drop-on-stop="false" prefix="ispn">
                <data-column type="bytea"/>
                <timestamp-column name="timestamp" type="bigint"/>
            </table>
        </jdbc-store>
        <state-transfer timeout="0"/>
    </distributed-cache>
    <distributed-cache name="clientSessions" owners="2">
        <expiration lifespan="900000000000000000"/>
        <jdbc-store data-source="IspnDS" fetch-state="false" passivation="false" preload="true" purge="false" shared="true">
            <property name="databaseType">POSTGRES</property>
            <table fetch-size="10000" drop-on-stop="false" prefix="ispn">
                <data-column type="bytea"/>
                <timestamp-column name="timestamp" type="bigint"/>
            </table>
        </jdbc-store>
        <state-transfer timeout="0"/>
    </distributed-cache>
</cache-container>

Sessions and clientSessions are persisted and reloaded fine from PostgreSQL database.

However, while upgrading to Quarkus 20.0.2, even if below Infinispan configuration works fine in persisting and reloading new sessions (still with the skipCacheStore patch in keycloak-model-infinispan-20.0.2.jar), it fails on reloading existing sessions from database, because of a Marshaller problem :

<?xml version="1.0" encoding="UTF-8"?>
<infinispan
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xsi:schemaLocation="urn:infinispan:config:13.0 http://www.infinispan.org/schemas/infinispan-config-13.0.xsd"
        xmlns="urn:infinispan:config:13.0">

	<jgroups>
        <stack name="jdbc-ping-tcp" extends="tcp">

            <JDBC_PING connection_driver="org.postgresql.Driver"
                connection_username="${env.KC_DB_USERNAME}"
                connection_password="${env.KC_DB_PASSWORD}"
                connection_url="jdbc:postgresql://${env.KC_DB_URL_HOST}/jgroups"
                initialize_sql="CREATE TABLE IF NOT EXISTS JGROUPSPING (own_addr varchar(200) NOT NULL, bind_addr VARCHAR(200) NOT NULL, created timestamp NOT NULL, cluster_name varchar(200) NOT NULL, ping_data BYTEA, constraint PK_JGROUPSPING PRIMARY KEY (own_addr, cluster_name));"
                insert_single_sql="INSERT INTO JGROUPSPING (own_addr, bind_addr, created, cluster_name, ping_data) values (?,'${env.HOST_IPV4_ADDRESS}',NOW(), ?, ?);"
                delete_single_sql="DELETE FROM JGROUPSPING WHERE own_addr=? AND cluster_name=?;"
                select_all_pingdata_sql="SELECT ping_data FROM JGROUPSPING WHERE cluster_name=?;"
                info_writer_sleep_time="500"
                remove_all_data_on_view_change="true"
                stack.combine="REPLACE"
                stack.position="MPING" />
        </stack>
    </jgroups>

    <cache-container name="keycloak">
        <transport lock-timeout="60000" stack="jdbc-ping-tcp" />
        <local-cache name="realms">
            <encoding>
                <key media-type="application/x-java-object" />
                <value media-type="application/x-java-object" />
            </encoding>
            <memory max-count="10000" />
        </local-cache>
        <local-cache name="users">
            <encoding>
                <key media-type="application/x-java-object" />
                <value media-type="application/x-java-object" />
            </encoding>
            <memory max-count="10000" />
        </local-cache>
        <distributed-cache name="sessions" owners="2">
            <state-transfer enabled="true" await-initial-transfer="true" />
            <expiration />
            <persistence>
                <string-keyed-jdbc-store xmlns="urn:infinispan:config:store:jdbc:13.0"
                    dialect="POSTGRES" shared="true">
                    <connection-pool
                        properties-file="/opt/keycloak/conf/ispn-agroal.properties" />
                    <string-keyed-table create-on-start="true" prefix="ispn">
                        <id-column name="id" type="VARCHAR(255)" />
                        <data-column name="datum" type="BYTEA" />
                        <timestamp-column name="timestamp" type="BIGINT" />
                        <segment-column name="segment" type="INT" />
                    </string-keyed-table>
                </string-keyed-jdbc-store>
            </persistence>
        </distributed-cache>
        <distributed-cache name="authenticationSessions" owners="2">
            <expiration lifespan="-1" />
        </distributed-cache>
        <distributed-cache name="offlineSessions" owners="2">
            <expiration lifespan="-1" />
        </distributed-cache>
        <distributed-cache name="clientSessions" owners="2">
            <state-transfer enabled="true" await-initial-transfer="true" />
            <expiration />
            <persistence>
                <string-keyed-jdbc-store xmlns="urn:infinispan:config:store:jdbc:13.0"
                    dialect="POSTGRES" shared="true">
                    <connection-pool
                        properties-file="/opt/keycloak/conf/ispn-agroal.properties" />
                    <string-keyed-table create-on-start="true" prefix="ispn">
                        <id-column name="id" type="VARCHAR(255)" />
                        <data-column name="datum" type="BYTEA" />
                        <timestamp-column name="timestamp" type="BIGINT" />
                        <segment-column name="segment" type="INT" />
                    </string-keyed-table>
                </string-keyed-jdbc-store>
            </persistence>
        </distributed-cache>
        <distributed-cache name="offlineClientSessions" owners="2">
            <expiration lifespan="-1" />
        </distributed-cache>
        <distributed-cache name="loginFailures" owners="2">
            <expiration lifespan="-1" />
        </distributed-cache>
        <local-cache name="authorization">
            <encoding>
                <key media-type="application/x-java-object" />
                <value media-type="application/x-java-object" />
            </encoding>
            <memory max-count="10000" />
        </local-cache>
        <replicated-cache name="work">
            <expiration lifespan="-1" />
        </replicated-cache>
        <local-cache name="keys">
            <encoding>
                <key media-type="application/x-java-object" />
                <value media-type="application/x-java-object" />
            </encoding>
            <expiration max-idle="3600000" />
            <memory max-count="1000" />
        </local-cache>
        <distributed-cache name="actionTokens" owners="2">
            <encoding>
                <key media-type="application/x-java-object" />
                <value media-type="application/x-java-object" />
            </encoding>
            <expiration max-idle="-1" lifespan="-1" interval="300000" />
            <memory max-count="-1" />
        </distributed-cache>
    </cache-container>
</infinispan>

It is very close from the example you have committed here 2 months ago @thomasdarimont (thanks again for those useful examples)

Here is the exception we got with this configuration, the causedBy tells this when trying to unmarshall user session :

        "exceptionType": "org.infinispan.persistence.spi.PersistenceException",
        "message": "java.io.IOException: Unsupported protocol version 1",

{
  "timestamp": "2023-01-16T19:42:51.201Z",
  "sequence": 8681,
  "loggerClassName": "org.infinispan.util.logging.Log_$logger",
  "loggerName": "org.infinispan.interceptors.impl.InvocationContextInterceptor",
  "level": "ERROR",
  "message": "ISPN000136: Error executing command GetKeyValueCommand on Cache 'sessions', writing keys []",
  "threadName": "jgroups-13,ip-x-y-z-t-32121",
  "threadId": 58,
  "mdc": {},
  "ndc": "",
  "hostName": "ip-x-y-z-t",
  "processName": "QuarkusEntryPoint",
  "processId": 22543,
  "exception": {
    "refId": 1,
    "exceptionType": "org.infinispan.remoting.RemoteException",
    "message": "ISPN000217: Received exception from ip-a-z-e-r-23859, see cause for remote stack trace",
    "frames": [
      {
        "class": "org.infinispan.remoting.transport.ResponseCollectors",
        "method": "wrapRemoteException",
        "line": 25
      },
      {
        "class": "org.infinispan.interceptors.distribution.RemoteGetSingleKeyCollector",
        "method": "addResponse",
        "line": 34
      },
      {
        "class": "org.infinispan.interceptors.distribution.RemoteGetSingleKeyCollector",
        "method": "addResponse",
        "line": 25
      },
      {
        "class": "org.infinispan.remoting.transport.impl.MultiTargetRequest",
        "method": "onResponse",
        "line": 92
      },
      {
        "class": "org.infinispan.remoting.transport.jgroups.StaggeredRequest",
        "method": "onResponse",
        "line": 50
      },
      {
        "class": "org.infinispan.remoting.transport.impl.RequestRepository",
        "method": "addResponse",
        "line": 51
      },
      {
        "class": "org.infinispan.remoting.transport.jgroups.JGroupsTransport",
        "method": "processResponse",
        "line": 1496
      },
      {
        "class": "org.infinispan.remoting.transport.jgroups.JGroupsTransport",
        "method": "processMessage",
        "line": 1398
      },
      {
        "class": "org.infinispan.remoting.transport.jgroups.JGroupsTransport",
        "method": "access$300",
        "line": 146
      },
      {
        "class": "org.infinispan.remoting.transport.jgroups.JGroupsTransport$ChannelCallbacks",
        "method": "up",
        "line": 1586
      },
      [...]
    ],
    "causedBy": {
      "exception": {
        "refId": 2,
        "exceptionType": "org.infinispan.persistence.spi.PersistenceException",
        "message": "java.io.IOException: Unsupported protocol version 1",
        "frames": [
          {
            "class": "org.infinispan.marshall.exts.ThrowableExternalizer",
            "method": "readObject",
            "line": 234
          },
          {
            "class": "org.infinispan.marshall.exts.ThrowableExternalizer",
            "method": "readObject",
            "line": 42
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "readWithExternalizer",
            "line": 727
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "readNonNullableObject",
            "line": 708
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "readNullableObject",
            "line": 357
          },
          {
            "class": "org.infinispan.marshall.core.BytesObjectInput",
            "method": "readObject",
            "line": 32
          },
          {
            "class": "org.infinispan.remoting.responses.ExceptionResponse$Externalizer",
            "method": "readObject",
            "line": 49
          },
          {
            "class": "org.infinispan.remoting.responses.ExceptionResponse$Externalizer",
            "method": "readObject",
            "line": 41
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "readWithExternalizer",
            "line": 727
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "readNonNullableObject",
            "line": 708
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "readNullableObject",
            "line": 357
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "objectFromObjectInput",
            "line": 191
          },
          {
            "class": "org.infinispan.marshall.core.GlobalMarshaller",
            "method": "objectFromByteBuffer",
            "line": 220
          },
          {
            "class": "org.infinispan.remoting.transport.jgroups.JGroupsTransport",
            "method": "processResponse",
            "line": 1488
          },
          [...]
        ]
      }
    }
  }
}

We tried without success with

<encoding media-type="application/x-protostream"/>

instead of :

<encoding>
    <key media-type="application/x-java-object"/>
    <value media-type="application/x-java-object"/>
</encoding>

Also tried (but with infinispan 13.0.10.Final only) the remote cache store configuration you have suggested @martin-kanis here in issue #13926, especially the GenericJBossMarshaller and x-jboss-marchalling config, still same error ...

Last forwarding step is to try with Infinispan 14.0.x.Final and Keycloak 20.0.3, if you have any other configuration input or testing ideas that I could try

Do you think that we will not be able to handle and manage compatibility with both "old keycloak 19.0.3 legacy sessions" and "new KC 20.0.3 sessions" in the same ISPN database ?

thanks a lot for any lead !

cc @hmlnarik as you assigned this issue to yourself ;-)

[bigbellyburger]

I'd also like to be able to guarantee to never lose sessions by persisting them somehow. Is there any solution that works with latest keycloak?

[vilmosnagy]

@daviddelannoy Can keycloak20 with Quarkus re-read the sessions from the database if all sessions were written from kc20 with your solution?

Do you think that we will not be able to handle and manage compatibility with both "old keycloak 19.0.3 legacy sessions" and "new KC 20.0.3 sessions" in the same ISPN database ?

A couple of months ago I created a PoC which serialized these sessions into JSON from KC15-Wildfly (I think), and I was able to load these JSON objects into a KC18-Wildfly instance - and after that, the KC18 was able to handle those sessions. It was a proof of concept to migrate the sessions while upgrading the keycloak cluster. It still required some downtime, but I was able to move 10-15 sessions between keycloak versions. If you'd like to do something like that I'd give this a try. We never tried it on a larger userbase though, the decision was to log them out.

[daviddelannoy]

Hi @vilmosnagy

If sessions were written by a kc20 (so Quarkus) to the database, yes it works fine, they are loaded on startup.

It is only between a kc19.x.x-wildfly and a kc20-quarkus that session unmarshalling is broken

[vilmosnagy]

Yeah, but that was broken during previous updates as well (eg.: KC15Wildfly -> KC18Wildfly).

I've tried something similar, but it was a proof of concept and never tried with more than ~15 sessions.

class InternalSessionExportImport {
    @SuppressWarnings("unchecked")
    public InternalSessionExportImport(KeycloakSession session) {
        this.session = session;
        var userSessionProvider = (InfinispanUserSessionProvider) session.getProvider(UserSessionProvider.class);
        var rootAuthSessionProvider = (InfinispanAuthenticationSessionProvider) session.getProvider(AuthenticationSessionProvider.class);

        final var sessionCacheField = InfinispanUserSessionProvider.class.getDeclaredField("sessionCache");
        sessionCacheField.setAccessible(true);
        final var clientSessionCacheField = InfinispanUserSessionProvider.class.getDeclaredField("clientSessionCache");
        clientSessionCacheField.setAccessible(true);
        final var rootAuthSessionCacheField = InfinispanAuthenticationSessionProvider.class.getDeclaredField("cache");
        rootAuthSessionCacheField.setAccessible(true);

        this.sessionCache = (org.infinispan.Cache<String, SessionEntityWrapper<UserSessionEntity>>) sessionCacheField.get(userSessionProvider);
        this.clientSessionCache = (Cache<UUID, SessionEntityWrapper<AuthenticatedClientSessionEntity>>) clientSessionCacheField.get(userSessionProvider);
        this.rootAuthSessionCache = (Cache<UUID, RootAuthenticationSessionEntity>) rootAuthSessionCacheField.get(rootAuthSessionProvider);
    }

    public void sessions() throws Exception {
        var sessionList = new ArrayList<SessionEntityWrapper<UserSessionEntity>>();
        var clientSessionList = new ArrayList<SessionEntityWrapper<AuthenticatedClientSessionEntity>>();

        int size = sessionCache.keySet().size();
        for (String s : sessionCache.keySet()) {
            final var value = sessionCache.get(s);
            sessionList.add(value);
        }

        size = clientSessionCache.keySet().size();
        for (UUID key : clientSessionCache.keySet()) {
            final var value = clientSessionCache.get(key);
            clientSessionList.add(value);
        }

        // serialize sessionList & clientSessionList _somewhere_ to string (json?)
        // and on the newer keycloak versions we could deserialize them and put them back to the cache
    }
}

[ahus1]

I'm picking up @mposolda's suggestion of detecting if remote stores are used to enable/disable the read/write through.

See #24774 for a suggested PR. I'd be happy if the community would review and try it. Note the disclaimer at the beginning of the PR.