Brute force protection in keycloak

Question

I am trying to implement brute force protection in keycloak. I am able to implement it via the admin console. However, I am not able to find where keycloak stores details of temporariliy locked users in keycoak. I am using keycloak 21.0.0. Any idea on the tables where the data of brute force is stored at keycloak's end will be immensely helpful. Thanks in advance.

It is not a good idea directly to access internal(or external) databases due to security and can't use sync DB trigger. Good ideas are using [REST API](https://www.keycloak.org/docs-api/20.0.2/rest-api/index.html#_attack_detection_resource) or [SPI](https://www.keycloak.org/docs/latest/server_development/index.html#_providers) — Bench Vue, Aug 10 '23 at 02:51
I am trying to find out which table of keycloak DB stores the temporary and permanently locked users to better understand the work flow. Nothing wrong in doing that. I am aware of the APIs to get the locked user details. Thanks anyways for replying. — jane, Aug 10 '23 at 07:23
The `event_entity` (regular user list information) table 's `error` column has a information `user_temporarily_disabled` or `invalid_user_credentials` or permanently locked when hit the maximum number of Login Failures. Not the `username_login_failure` table. you can see how to access keycloak tables and content in [here](https://stackoverflow.com/questions/74962002/data-from-spring-application-is-not-being-persisted-in-the-dockerized-postgres-d/74967322#74967322). — Bench Vue, Aug 10 '23 at 16:09
@BenchVue - I am able to see a column by name "ERROR" in "EVENT_ENTITY". However, the column has no entry even though the user is temporarily blocked for 1 day. Are you getting any entry as "user_temporarily_disabled" ? I am using mySql. — jane, Aug 10 '23 at 16:41
I tested mySQL 5.7 and Keycloak V19.0.3 by docker compose. The `permanentLockout` in USER_ATTRIBUTE table (VALUE column) and `invalid_user_credentials` or `user_disabled` in `EVENT_ENTITY` table (EVENT_ENTITY column) — Bench Vue, Aug 10 '23 at 17:45
@BenchVue - Thanks for your reply. I double checked but cant find any entry in either "USER_ATTRIBUTE" or "EVENT_ENTITY". I am using keycloak 21. I checked if infinispan is caching the data and not sending to persisting DB - thats not the case as well. I will continue to check further. — jane, Aug 10 '23 at 18:24
I will try it for V21.0.0 I don't know the cache involve or not. If yes, that I said why DB approaches is not good method even if to understand better by low level flow. — Bench Vue, Aug 10 '23 at 18:34
Can you share the docker-compose.yml file? I have no both support V21 and mySQL with docker-compose.yml file. — Bench Vue, Aug 10 '23 at 18:37
@BenchVue - thank you. I have disabled caching of user details. the problem persists. Even though the temporaray lock feature works perfectly fine, for some reason, I dont see the data in the tables mentioned by you. I am running keycloak in embedded springboot app, i believe, my docker-compose wont work at your end. — jane, Aug 10 '23 at 18:43
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/254878/discussion-between-jane-and-bench-vue). — jane, Aug 10 '23 at 19:04
I tested MariaDB V10 and Keycloak V 21.0.0. The `temporarily locked users` is in `EVENT_ENTITY` at `ERROR` column. And The `permanentLockout` is in `USER_ATTRIBUTE` table at `VALUE` column. — Bench Vue, Aug 11 '23 at 00:26
Thanks for your accepting, can you vote me too? It will make me happy. — Bench Vue, Aug 11 '23 at 10:07

Bench Vue · Accepted Answer · 2023-08-11T00:39:57.473

Permanent Lock Information

Permanent Lock Information is USER_ATTRIBUTE table at VALUE column

MariaDB [keycloak]> SELECT * FROM USER_ATTRIBUTE;
+----------------+------------------+--------------------------------------+--------------------------------------+
| NAME           | VALUE            | USER_ID                              | ID                                   |
+----------------+------------------+--------------------------------------+--------------------------------------+
| disabledReason | permanentLockout | a0cbe53b-26b2-4bd1-981a-0abac0a72483 | 8ac10d9d-ab07-4a02-9f31-b647b7ecff48 |
+----------------+------------------+--------------------------------------+--------------------------------------+

UI Setting for Permanent Lock in Keycloak (*Figure 2)

user_temporarily_disabled

The user_temporarily_disabled is in EVENT_ENTITY Table at ERROR column

Simple EVENT_ENTITY Table (just 3 columns)

-+---------------------------+----------------+------------------------------
 | ERROR                     |  TYPE          | USER_ID
-+---------------------------+----------------+------------------------------
 | NULL                      |  LOGOUT        | c3fecc20-5fb3-4a02-be85-988b5
 | NULL                      |  LOGOUT        | 013cb4da-bb6f-487a-ba3b-a60fe
 | NULL                      |  CODE_TO_TOKEN | 013cb4da-bb6f-487a-ba3b-a60fe
 | invalid_user_credentials  |  LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
 | user_temporarily_disabled |  LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
 | NULL                      |  LOGIN         | 013cb4da-bb6f-487a-ba3b-a60fe
 | invalid_user_credentials  |  LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
 | invalid_user_credentials  |  LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
-+---------------------------+----------------+------------------------------

Full EVENT_ENTITY Table

MariaDB [keycloak]> SELECT * FROM EVENT_ENTITY;
+--------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+------------+--------------------------------------+--------------------------------------+---------------+---------------+------------------------------
--------+
| ID                                   | CLIENT_ID       | DETAILS_JSON                                                                                                                                                                                                                                                                                        | ERROR                     | IP_ADDRESS | REALM_ID                             | SESSION_ID                           | EVENT_TIME    | TYPE          | USER_ID
        |
+--------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+------------+--------------------------------------+--------------------------------------+---------------+---------------+------------------------------
--------+
| 079a5ca0-9ed2-41c1-81ad-c381bf2baa5a | NULL            | {"redirect_uri":"http://localhost:8080/realms/master/account/#/"}                                                                                                                                                                                                                                   | NULL                      | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | ad2493fc-24c4-4299-834c-56878f0758af | 1691711078287 | LOGOUT        | c3fecc20-5fb3-4a02-be85-988b5
c686467 |
| 693e86d1-b749-4b77-8d90-8650b26853df | NULL            | {"redirect_uri":"http://localhost:8080/realms/master/account/"}                                                                                                                                                                                                                                     | NULL                      | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | b17cebed-0726-4092-ac73-531accf2da50 | 1691711091836 | LOGOUT        | 013cb4da-bb6f-487a-ba3b-a60fe
8898ea6 |
| 8b94431c-f7dc-415d-8c84-7ce3d654efc8 | account-console | {"token_id":"4e687bef-af48-4703-a3c2-07ad76f69b17","grant_type":"authorization_code","refresh_token_type":"Refresh","scope":"openid profile email","refresh_token_id":"7bf6871f-48dc-4e7b-8e9a-86027b70d261","code_id":"b17cebed-0726-4092-ac73-531accf2da50","client_auth_method":"client-secret"} | NULL                      | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | b17cebed-0726-4092-ac73-531accf2da50 | 1691711088224 | CODE_TO_TOKEN | 013cb4da-bb6f-487a-ba3b-a60fe
8898ea6 |
| 8dc34bfd-df10-46c1-8236-e0dc7ab513d1 | account-console | {"auth_method":"openid-connect","auth_type":"code","redirect_uri":"http://localhost:8080/realms/master/account/#/security/signingin","code_id":"9bbb5166-bffb-4ebe-b743-918ee1d1aea6","username":"user2"}                                                                                           | invalid_user_credentials  | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | NULL                                 | 1691711106048 | LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
8898ea6 |
| a42f9d00-2d86-4f8b-8ba5-75e6f6e91501 | account-console | {"auth_method":"openid-connect","auth_type":"code","redirect_uri":"http://localhost:8080/realms/master/account/#/security/signingin","code_id":"9bbb5166-bffb-4ebe-b743-918ee1d1aea6","username":"user2"}                                                                                           | user_temporarily_disabled | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | NULL                                 | 1691711108318 | LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
8898ea6 |
| ab50a625-9252-4de0-8d0b-825636a7afa0 | account-console | {"auth_method":"openid-connect","auth_type":"code","redirect_uri":"http://localhost:8080/realms/master/account/#/security/signingin","consent":"no_consent_required","code_id":"b17cebed-0726-4092-ac73-531accf2da50","username":"user2"}                                                           | NULL                      | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | b17cebed-0726-4092-ac73-531accf2da50 | 1691711086823 | LOGIN         | 013cb4da-bb6f-487a-ba3b-a60fe
8898ea6 |
| ad370ce8-77cf-435d-b718-2d773ca1c5f8 | account-console | {"auth_method":"openid-connect","auth_type":"code","redirect_uri":"http://localhost:8080/realms/master/account/#/security/signingin","code_id":"9bbb5166-bffb-4ebe-b743-918ee1d1aea6","username":"user2"}                                                                                           | invalid_user_credentials  | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | NULL                                 | 1691711100798 | LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
8898ea6 |
| bc74260c-ad1b-49f5-b2dd-7b13723eba25 | account-console | {"auth_method":"openid-connect","auth_type":"code","redirect_uri":"http://localhost:8080/realms/master/account/#/security/signingin","code_id":"9bbb5166-bffb-4ebe-b743-918ee1d1aea6","username":"user2"}                                                                                           | invalid_user_credentials  | 172.22.0.1 | 5f9b5416-259d-4c38-b27b-afb5d6200789 | NULL                                 | 1691711103367 | LOGIN_ERROR   | 013cb4da-bb6f-487a-ba3b-a60fe
8898ea6 |
+--------------------------------------+-----------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------+------------+--------------------------------------+--------------------------------------+---------------+---------------+------------------------------
--------+

The user_temporarily_disabled is in EVENT_ENTITY Table at ERROR column

UI Event Logs for user2 login failed

UI Setting for user_temporarily_disabled of Brute force detection (*Figure 3)

Demo `docker-compose.yml`

For Keycloak V21.0.0 and mariadb V10

services:
  mysql:
    image: docker.io/mariadb:10
    container_name: my_mysql
    environment:
      MARIADB_DATABASE: keycloak
      MARIADB_ROOT_PASSWORD: password
      MARIADB_PASSWORD: password
      MARIADB_USER: keycloak

  keycloak:
    image: quay.io/keycloak/keycloak:21.0.0
    container_name: my_keycloak
    environment:
      KC_HOSTNAME: localhost
      KC_HOSTNAME_PORT: 8080
      KC_HOSTNAME_STRICT_BACKCHANNEL: "true"
      KC_DB: mariadb
      KC_DB_URL: jdbc:mariadb://mysql:3306/keycloak?characterEncoding=UTF-8
      KC_DB_USERNAME: keycloak
      KC_DB_PASSWORD: password
      KEYCLOAK_ADMIN: admin
      KEYCLOAK_ADMIN_PASSWORD: admin
      KC_HEALTH_ENABLED: "true"
      KC_LOG_LEVEL: info
    healthcheck:
      test: [ "CMD", "curl", "-f", "http://localhost:8080/health/ready" ]
      interval: 15s
      timeout: 2s
      retries: 15
    command: start-dev
    ports:
      - 8080:8080
    depends_on:
      - mysql

Run it

docker compose up

Access mySQL Database to see tables and contents

docker exec -it my_mysql mysql -u root -p keycloak

or (if use Windows git bash shell)

winpty docker exec -it my_mysql mysql -u root -p keycloak

You needs to enter password (password is password)

password

Connect keycloak database

connect keycloak;

Show tables

show tables;

User Event monitoring setup

Permanent Lock

Setting UI for Permanent Lock (upper image - Figure 2)

Select Impersonate for user1 failed 3 times

Failed login

Setting UI for user_temporarily_disabled (upper image - Figure 3)

Select Impersonate for user2 failed 3 times

Show the USER_ENTITY table for Getting User list

SELECT * FROM USER_ENTITY;

Show the USER_ATTRIBUTE table

SELECT * FROM USER_ATTRIBUTE;

Show the EVENT_ENTITY table

SELECT * EVENT_ENTITY;

Result is already posted in upper.

Many thanks for the detailed answer. – jane Aug 11 '23 at 10:21 — jane, Aug 11 '23 at 10:21
You are welcome, I also learn a lot from your question. – Bench Vue Aug 11 '23 at 10:23 — Bench Vue, Aug 11 '23 at 10:23