[keycloak-dev] Events not deleted on realm deletion and missing indexes

Mon Oct 14 12:16:35 EDT 2019

Hi Johannes,

Thanks for your feedback. I agree that queries timing out is not a good final state. Even worse, this basically affects all realms if only one realm has lots of events.
Assuming this is not fixed soon, we will probably just add a custom migration to our Keycloak image, we did that before. It looks like this:

In our docker file, we add a "custom" directory under /opt/jboss/custom containing all our customizations.

In our "build-keycloak.sh" we add the migration file and a custom insert script:

cp /opt/jboss/custom/db_migrations/jpa-changelog-4.3.0-attributes.xml /opt/jboss/keycloak/modules/system/layers/keycloak/org/keycloak/keycloak-model-jpa/main
cp /opt/jboss/custom/db_migrations/insert-changelog.sh /opt/jboss/keycloak/modules/system/layers/keycloak/org/keycloak/keycloak-model-jpa/main
cd /opt/jboss/keycloak/modules/system/layers/keycloak/org/keycloak/keycloak-model-jpa/main
./insert-changelog.sh $KEYCLOAK_VERSION

The migration file " jpa-changelog-4.3.0-attributes.xml " looks like this (in our case it extended the length of the user custom attributes): 
<?xml version="1.0" encoding="UTF-8" standalone="no"?>

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

    <changeSet author="x.y at bosch-si.com" id="4.3.0-attributes">
        <preConditions onSqlOutput="TEST" onFail="MARK_RAN">
            <dbms type="mssql" />
        </preConditions>

        <modifyDataType tableName="USER_ATTRIBUTE" columnName="VALUE" newDataType="NVARCHAR(MAX)"/>
    </changeSet>

</databaseChangeLog>	

The insert script " insert-changelog.sh" looks like this (this is where the magic happens, it basically modifies the existing jar):
#!/bin/bash

KEYCLOAK_VERSION=$1

model_jpa="keycloak-model-jpa-${KEYCLOAK_VERSION}.jar"
match='    <include file=\"META-INF\/jpa-changelog-4.3.0.xml\"\/>'
insert='    <include file=\"META-INF\/jpa-changelog-4.3.0-attributes.xml\"\/>'
file="META-INF/jpa-changelog-master.xml"

jar -xf $model_jpa $file

sed -i "s/$match/$match\n$insert/" $file

mv jpa-changelog-4.3.0-attributes.xml ./META-INF/

jar -uf $model_jpa ./META-INF/

We inserted our migration at an older time and so far this worked well (even it is a bit hacky).

I hope this is useful to you.

Best regards,
Sebastian

Mit freundlichen Grüßen / Best regards

Dr.-Ing. Sebastian Schuster

Open Source Services (INST-CSS/BSV-OS2)
Bosch Software Innovations GmbH | Ullsteinstr. 128 | 12109 Berlin | GERMANY | www.bosch-si.com
Tel. +49 30 726112-485 | Mobil +49 152 02177668 | Telefax +49 30 726112-100 | Sebastian.Schuster at bosch-si.com

Sitz: Berlin, Registergericht: Amtsgericht Charlottenburg; HRB 148411 B
Aufsichtsratsvorsitzender: Dr.-Ing. Thorsten Lücke; Geschäftsführung: Dr. Stefan Ferber, Michael Hahn, Dr. Aleksandar Mitrovic 

-----Ursprüngliche Nachricht-----
Von: Johannes Knutsen <johannes at kodet.no> 
Gesendet: Montag, 14. Oktober 2019 09:12
An: Schuster Sebastian (INST-CSS/BSV-OS2) <Sebastian.Schuster at bosch-si.com>
Cc: keycloak-dev at lists.jboss.org
Betreff: Re: [keycloak-dev] Events not deleted on realm deletion and missing indexes

Hi Sebastian!
Not automatically deleting events when the realm is deleted makes sense to me, given it can be used as an audit log. But I think it would have been a nice feature to make it a checkable option, so you could delete it if you want to.

Thanks for pointing out the lack of indexes. I proposed a PR for this some weeks ago, https://github.com/keycloak/keycloak/pull/6324.
The conclusion from the Keycloak team, is that they don't want to add the index in an upgrade step because it might take a long time to generate the index. However, I think that conclusion is wrong because if you have a large event table, Keycloak is not operating normally anyways because the automatic event cleanup job, which runs every 15 minutes, impacts the normal operation.
I also tried to add support for concurrently adding the index, but when I tested it for Postgres, which requires the SQL statement to be run outside and transaction, Keycloak would hang on startup due to a lock in the database. I haven't had time to look more into the details, but there seems to be some locking in Keycloak to ensure that the upgrade is complete before continuing the startup. Which makes sense to me. Stian or Hynek, please feel free to contact me if you would like to discuss these details.
Honestly, I feel the lack of indexes should have been taken more seriously, because it currently doesn't scale.

We ended up adding the indexes ourselves in a manual step, but I really think it is a bad idea and something Keycloak should handle as part of the product.

- Johannes

On Sun, Oct 13, 2019 at 11:16 AM Schuster Sebastian (INST-CSS/BSV-OS2) <Sebastian.Schuster at bosch-si.com> wrote:
>
> Hi everybody,
>
> I noticed that neither Admin Events nor Login Events are deleted in the database when a realm is deleted. Is this by intention (e..g for auditing purposes) or a bug?
> I also saw that with a lots of events (10 Mio+) queries to the events endpoints typically time out since they filter by realm_id but there is not index for this leading to full table scans.
> I would assume index on realm_id and/or realm_id+timestamp would be necessary.
>
> WDYT?
>
> Best regards,
> Sebastian
>
> Mit freundlichen Grüßen / Best regards
>
> Dr.-Ing. Sebastian Schuster
>
> Open Source Services (INST-CSS/BSV-OS2) Bosch Software Innovations 
> GmbH | Ullsteinstr. 128 | 12109 Berlin | GERMANY | www.bosch-si.com 
> Tel. +49 30 726112-485 | Mobil +49 152 02177668 | Telefax +49 30 
> 726112-100 | 
> Sebastian.Schuster at bosch-si.com<mailto:Sebastian.Schuster at bosch-si.com
> >
>
> Sitz: Berlin, Registergericht: Amtsgericht Charlottenburg; HRB 148411 
> B
> Aufsichtsratsvorsitzender: Dr.-Ing. Thorsten Lücke; Geschäftsführung: 
> Dr. Stefan Ferber, Michael Hahn, Dr. Aleksandar Mitrovic
>
>
> _______________________________________________
> keycloak-dev mailing list
> keycloak-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-dev