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

[Johannes Knutsen]

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