keycloak: Slow user query by attribute

Describe the bug

If querying users by attribute values the response is really slow.

On further investigation the query is missing the database index because attribute name and value is queried by lower value, eg. lower(name) and lower(value).

Either remove lower and match the actual value (which seems to be the more reasonable approach) or add a matching new index to the attribute table: CREATE INDEX index ON user_attribute(lower(name), lower(value));

Version

18.0.1

Expected behavior

Query by attribute returns in a reasonable time.

Actual behavior

Query takes really long and produces a lot of load on the database.

How to Reproduce?

Just query by attribute.

Anything else?

Adding a max value to the query totally breaks the request. (Timeouts)

About this issue

Original URL
State: open
Created 2 years ago
Comments: 17 (13 by maintainers)

Commits related to this issue

Remove lowercasing upon search by attribute search Fixes: #12671 — committed to hmlnarik/keycloak by hmlnarik 2 years ago

Most upvoted comments

Actually, the bug @hmlnarik mentioned is still there. But I can provide a PR to fix that.

sschu on Aug 4, 2023

I just checked how we used to do it on the image and yeah, you would have to alter some jars - so it’s a bit more involved. We had a script to insert migrations:

#!/bin/bash

insert_changelog() {

    MATCH_FILE="$1"
    INSERT_FILE="$2"
    MASTER_FILE="$3"

    MATCH='    <include file=\"META-INF\/'${MATCH_FILE}'\"\/>'
    INSERT='    <include file=\"META-INF\/'${INSERT_FILE}'\"\/>'

    sed -i "s/${MATCH}/${MATCH}\n${INSERT}/" ${MASTER_FILE}
    mv ${INSERT_FILE} ./META-INF/
}

KEYCLOAK_VERSION=$1

MODEL_JPA="keycloak-model-jpa-${KEYCLOAK_VERSION}.jar"

MASTER_FILE="META-INF/jpa-changelog-master.xml"

jar -xf ${MODEL_JPA} ${MASTER_FILE}

insert_changelog "jpa-changelog-4.3.0.xml" "jpa-changelog-4.3.0-attributes.xml" "$MASTER_FILE"
insert_changelog "jpa-changelog-13.0.0.xml" "jpa-changelog-13.0.99-attributes.xml" "$MASTER_FILE"

jar -uf ${MODEL_JPA} ./META-INF/

You would have to call this script from while building the image like that:

# add custom migrations
cp /opt/jboss/custom/db_migrations/jpa-changelog-*.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

Maybe it would be in fact easier to just run a custom script against the database.

sschu on Feb 21, 2023