Keycloak User Federation does not support email-based login (getUserByEmail not implemented)

[Shivam-dev925]

Summary

Users cannot log in to Keycloak using their email address despite Login with email being enabled in the Raxa realm. Login fails with invalid_grant at the Keycloak level — before ever reaching OpenMRS.

---

Root Cause (Confirmed via JAR Decompilation)

The openmrs-keycloak-userstore-2.0.0.jar User Storage Provider has a getUserByEmail() method that only queries the users.email column in OpenMRS:

// UserDao.java — current implementation
select u from OpenmrsUserModel u where u.email = :email

The users.email column is empty for virtually all existing OpenMRS accounts. Email is stored in person_attribute where person_attribute_type.name = 'Email' (person_attribute_type_id = 111 on staging).

So when a user enters their email at the Keycloak login screen:

1. Keycloak calls getUserByEmail("chshivam963758@gmail.com") on the federation provider
2. UserDao queries users.email → NULL → returns null
3. Keycloak gets null → responds with invalid_grant

The user is never found, even though their email exists in the DB under person_attribute.

---

Evidence

DB state (openmrs_staging)

SELECT username, email AS users_email, pa.value AS person_attr_email
FROM users u
LEFT JOIN person_attribute pa ON pa.person_id = u.person_id
  AND pa.person_attribute_type_id = 111
WHERE u.username = 'shubam.bhai6953';

-- username: shubam.bhai6953 | users_email: (empty) | person_attr_email: chshivam963758@gmail.com

SELECT person_attribute_type_id, name FROM person_attribute_type WHERE name ILIKE '%email%';
-- person_attribute_type_id: 111 | name: Email

Login with username → works

POST /realms/raxa/protocol/openid-connect/token
username=shubam.bhai6953 & password=Shivamdev@123
→ 200 OK
  preferred_username: shubam.bhai6953
  email: chshivam963758@gmail.com   ← federation loads this correctly from person_attribute

Login with email → fails at Keycloak

POST /realms/raxa/protocol/openid-connect/token
username=chshivam963758@gmail.com & password=Shivamdev@123
→ { "error": "invalid_grant", "error_description": "Invalid user credentials" }

Note: loadUserDetails() in UserDao already correctly fetches email from person_attribute (using hardcoded person_attribute_type_id = 111) — but only after the user is found by username. The same logic is missing from getUserByEmail().

Keycloak admin user search also broken

GET /admin/realms/raxa/users?search=chshivam
→ { "error": "unknown_error" }

The searchForUser methods throw an unhandled exception, making the Keycloak admin console unable to list or search any users in the raxa realm.

---

Realm Settings (staging)

Setting	Value
Login with email	✅ On
Email as username	❌ Off
Duplicate emails	❌ Off
Verify email	❌ Off
User Federation provider	openmrs-authentication-provider → openmrs-keycloak-userstore-2.0.0.jar
OpenMRS DB	jdbc:postgresql://db-pg-staging.raxa.io:5432/openmrs_staging

---

Architecture (How This Deploys)

The Keycloak server lives in the Authentication-2026 repo. It is a Docker image deployed on AWS ECS Fargate (cluster: Raxa-Backend-Staging), sitting behind an ALB that serves auth-staging.raxa.io.

User login
    ↓
API Gateway (auth-staging.raxa.io)
    ↓
AWS ALB
    ↓
ECS Fargate — authentication-2026-staging task
  Docker image: 450742301786.dkr.ecr.ap-south-1.amazonaws.com/authentication-2026:staging-v1
  ↓                              ↓
Keycloak DB (MySQL)         OpenMRS PG DB
raxa-2026/keycloak-db       raxa-2026/openmrs-db
(AWS Secrets Manager)       (AWS Secrets Manager)

The 3 custom JARs (openmrs-keycloak-userstore, openmrs-keycloak-smart-auth, keycloak-openfga-event-publisher) are baked into the Docker image at build time via providers/*.jar. DB credentials are injected at runtime from AWS Secrets Manager.

---

Fix Required

File to change: UserDao.java → getOpenmrsUserByEmail()

(source of openmrs-keycloak-userstore-2.0.0.jar)

Current query only checks users.email (empty for most users). Needs a fallback to person_attribute:

public OpenmrsUserModel getOpenmrsUserByEmail(String email) {
    // Step 1: check users.email column (fast path)
    try {
        TypedQuery<OpenmrsUserModel> query = em.createQuery(
            "select u from OpenmrsUserModel u where u.email = :email",
            OpenmrsUserModel.class);
        query.setParameter("email", email);
        OpenmrsUserModel user = query.getSingleResult();
        if (user != null) {
            loadUserDetails(user);
            return user;
        }
    } catch (NoResultException e) {
        // fall through to person_attribute lookup
    }

    // Step 2: check person_attribute "Email" (covers all existing accounts)
    // person_attribute_type_id = 111 is the "Email" attribute type
    try {
        Query nativeQuery = em.createNativeQuery(
            "SELECT u.user_id FROM users u " +
            "JOIN person_attribute pa ON pa.person_id = u.person_id " +
            "WHERE LOWER(pa.value) = LOWER(:email) " +
            "AND pa.person_attribute_type_id = 111 " +
            "AND pa.voided = false " +
            "AND u.retired = false " +
            "LIMIT 1");
        nativeQuery.setParameter("email", email);
        Object result = nativeQuery.getSingleResult();
        if (result != null) {
            Integer userId = Integer.valueOf(result.toString());
            return getOpenmrsUserById(userId);
        }
    } catch (NoResultException e) {
        // no match
    }

    return null;
}

Deployment steps after fix:

1. Fix UserDao.java → rebuild JAR → bump to openmrs-keycloak-userstore-2.0.1.jar
2. Replace providers/openmrs-keycloak-userstore-2.0.0.jar in Authentication-2026/providers/
3. Run scripts/build-and-push.sh staging-v2 → pushes new Docker image to ECR
4. Update ECS task definition image tag to staging-v2 → ECS redeploys Keycloak

---

Related Fix (Already Deployed on OpenMRS Side)

A parallel fix was made in OAuth2UserInfoAuthenticationScheme.java (branch fix/keycloak-email-username-lookup in this repo) to handle the case where a local Keycloak user (non-federated, with email as preferred_username) hits OpenMRS. That fix handles the lookup on the OpenMRS side after Keycloak authenticates.

However, this Keycloak-side fix (this ticket) is needed so that federated users (all real OpenMRS users) can log in using their email address — fixing it at the Keycloak authentication step itself.