javax.ejb.EJBTransactionRolledbackException : For keycloak user spi provider

Question

Below is my pom.xml

<properties>
    <keycloak.version>4.3.0.Final</keycloak.version>
    <version.hibernate.javax.persistence>1.0.0.Final</version.hibernate.javax.persistence>
    <version.jboss-ejb-api>1.0.0.Final</version.jboss-ejb-api>
</properties>

<dependencies>
    <dependency>
        <groupId>org.keycloak</groupId>
        <artifactId>keycloak-core</artifactId>
        <version>${keycloak.version}</version>
    </dependency>
    <dependency>
        <groupId>org.keycloak</groupId>
        <artifactId>keycloak-server-spi-private</artifactId>
        <version>${keycloak.version}</version>
    </dependency>
    <dependency>
        <groupId>org.keycloak</groupId>
        <artifactId>keycloak-server-spi</artifactId>
        <version>${keycloak.version}</version>
    </dependency>
    <dependency>
        <groupId>org.jboss.logging</groupId>
        <artifactId>jboss-logging</artifactId>
        <version>3.3.1.Final</version>
    </dependency>
    <dependency>
        <groupId>org.hibernate.javax.persistence</groupId>
        <artifactId>hibernate-jpa-2.1-api</artifactId>
        <version>${version.hibernate.javax.persistence}</version>
        <scope>provided</scope>
    </dependency>
    <dependency>
        <groupId>org.jboss.spec.javax.ejb</groupId>
        <artifactId>jboss-ejb-api_3.2_spec</artifactId>
        <version>${version.jboss-ejb-api}</version>
        <scope>provided</scope>
    </dependency>
</dependencies>

The exception that I am getting here is:

2018-09-08 16:36:12,303 WARN  [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-4) SQL Error: 0, SQLState: null
2018-09-08 16:36:12,304 ERROR [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] (default task-4) IJ031070: Transaction cannot proceed: STATUS_MARKED_ROLLBACK
2018-09-08 16:36:12,313 ERROR [org.jboss.as.ejb3.invocation] (default task-4) WFLYEJB0034: EJB Invocation failed on component NeemiyaUsersProvider for method public org.keycloak.models.UserModel com.neemiya.keycloak.userstoragespi.NeemiyaUsersProvider.getUserByUsername(java.lang.String,org.keycloak.models.RealmModel): javax.ejb.EJBTransactionRolledbackException: org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleInCallerTx(CMTTxInterceptor.java:160)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInCallerTx(CMTTxInterceptor.java:257)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:334)
        at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:240)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
        at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41)


Caused by: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not prepare statement
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1692)
        at org.hibernate.jpa.spi.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1602)
        at org.hibernate.jpa.internal.QueryImpl.getResultList(QueryImpl.java:492)
        at com.neemiya.keycloak.userstoragespi.NeemiyaUsersProvider.getUserByUsername(NeemiyaUsersProvider.java:76)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
        at java.lang.reflect.Method.invoke(Method.java:498)
        at org.jboss.as.ee.component.ManagedReferenceMethodInterceptor.processInvocation(ManagedReferenceMethodInterceptor.java:52)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)
        at org.jboss.as.ejb3.component.invocationmetrics.ExecutionTimeInterceptor.processInvocation(ExecutionTimeInterceptor.java:43)
        at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:422)

Answer 1

    package com.neemiya.keycloak.userstoragespi;
    
    import java.util.UUID;
    
    import javax.ejb.Local;
    import javax.ejb.Stateful;
    import javax.persistence.EntityManager;
    import javax.persistence.PersistenceContext;
    import javax.persistence.TypedQuery;
    
    import org.jboss.logging.Logger;
    import org.keycloak.component.ComponentModel;
    import org.keycloak.credential.CredentialInput;
    import org.keycloak.credential.CredentialInputValidator;
    import org.keycloak.credential.CredentialModel;
    import org.keycloak.models.KeycloakSession;
    import org.keycloak.models.RealmModel;
    import org.keycloak.models.UserCredentialModel;
    import org.keycloak.models.UserModel;
    import org.keycloak.models.cache.CachedUserModel;
    import org.keycloak.models.cache.OnUserCache;
    import org.keycloak.storage.StorageId;
    import org.keycloak.storage.UserStorageProvider;
    import org.keycloak.storage.user.UserLookupProvider;
    
    import com.neemiya.keycloak.userstoragespi.entity.UserAccountMapping;
    import com.neemiya.keycloak.userstoragespi.entity.UserCredentialEntity;
    import com.neemiya.keycloak.userstoragespi.entity.UserEntity;
    import com.neemiya.keycloak.userstoragespi.utils.Crypto;
    
    @Stateful(passivationCapable = false)
    @Local(NeemiyaUsersProvider.class)
    public class NeemiyaUsersProvider
            implements UserStorageProvider, UserLookupProvider, CredentialInputValidator, OnUserCache {
    
        private static final Logger logger = Logger.getLogger(NeemiyaUsersProvider.class);
        public static final String PASSWORD_CACHE_KEY = UserAdapter.class.getName() + ".password";
        private static final String GET_USER_FOR_USERNAME = "select uc from UserCredentialEntity uc where uc.username=:username";
        private static final String GET_USER_ACCOUNT_MAPPING_FOR_OWNER = "select uam from UserAccountMapping uam where uam.userId = :userId and"
                + " uam.requestedByUserId =:userId and uam.role = com.neemiya.keycloak.userstoragespi.enums.ROLE.owner and "
                + " uam.request_status = com.neemiya.keycloak.userstoragespi.enums.REQUEST_STATUS.completed";
    
        @PersistenceContext
        protected EntityManager em;
    
        protected ComponentModel model;
        protected KeycloakSession session;
    
        public void setModel(ComponentModel model) {
            this.model = model;
        }
    
        public void setSession(KeycloakSession session) {
            this.session = session;
        }
    
        @Override
        public void close() {
        }
    
        @Override
        public UserModel getUserById(String id, RealmModel realm) {
            logger.info("getUserById: " + id);
            try {
                String persistenceId = StorageId.externalId(id);
                UUID persistenceUUID = UUID.fromString(persistenceId);
                UserCredentialEntity credentialEntity = em.find(UserCredentialEntity.class, persistenceUUID);
                if (credentialEntity == null) {
                    logger.info("could not find user by id: " + id);
                    return null;
                }
                TypedQuery<UserAccountMapping> uamQuery = em.createQuery(GET_USER_ACCOUNT_MAPPING_FOR_OWNER,
                        UserAccountMapping.class);
                uamQuery.setParameter("userId", credentialEntity.getUserEntity().getUserId());
                UserAccountMapping uam = uamQuery.getSingleResult();
                credentialEntity.setAccountId(uam.getAccountId());
    
                return new UserAdapter(session, realm, model, credentialEntity);
            } catch (Exception e) {
                logger.error("Couldn't fetch user by id " + e.getMessage());
            }
            return null;
        }
    
        @Override
        public UserModel getUserByUsername(String username, RealmModel realm) {
            logger.info("getUserByUsername: " + username);
            try {
                TypedQuery<UserCredentialEntity> query = em.createQuery(GET_USER_FOR_USERNAME, UserCredentialEntity.class);
                query.setParameter("username", username);
                UserCredentialEntity credentialEntity = query.getSingleResult();
    
                if (credentialEntity == null) {
                    logger.error("could not find username: " + username);
                    return null;
                }
    
                UserEntity entity = credentialEntity.getUserEntity();
                if (entity == null) {
                    logger.error("Couldn't fetch user for given username ");
                    return null;
                }
    
                TypedQuery<UserAccountMapping> uamQuery = em.createQuery(GET_USER_ACCOUNT_MAPPING_FOR_OWNER,
                        UserAccountMapping.class);
                uamQuery.setParameter("userId", entity.getUserId());
                UserAccountMapping uam = uamQuery.getSingleResult();
    
                if (uam == null) {
                    logger.error("Couldn't find an owner mapping for this user ");
                    return null;
                }
                credentialEntity.setAccountId(uam.getAccountId());
                logger.info("Fetch USerCredentialEntity :: " + credentialEntity.toString());
    
                return new UserAdapter(session, realm, model, credentialEntity);
            } catch (Exception e) {
                logger.error("Couldn't validate user credentials " + e.getMessage());
            }
            return null;
        }
    
        @Override
        public UserModel getUserByEmail(String email, RealmModel realm) {
            return null;
        }
    
        @Override
        public void onCache(RealmModel realm, CachedUserModel user, UserModel delegate) {
            String password = ((UserAdapter) delegate).getPassword();
            if (password != null) {
                user.getCachedWith().put(PASSWORD_CACHE_KEY, password);
            }
        }
    
        @Override
        public boolean supportsCredentialType(String credentialType) {
            return CredentialModel.PASSWORD.equals(credentialType);
        }
    
        @Override
        public boolean isConfiguredFor(RealmModel realm, UserModel user, String credentialType) {
            return supportsCredentialType(credentialType) && getPassword(user) != null;
        }
    
        @Override
        public boolean isValid(RealmModel realm, UserModel user, CredentialInput input) {
            if (!supportsCredentialType(input.getType()) || !(input instanceof UserCredentialModel))
                return false;
            try {
                UserCredentialModel cred = (UserCredentialModel) input;
                String password = getPassword(user);
                String encryptedPassword = Crypto.encryptSHA1(cred.getValue());
                if (password != null) {
                    if (password.equals(encryptedPassword)) {
                        return true;
                    } else {
                        UserModel model = getUserByUsername(user.getUsername(), realm);
                        String currentPassword = getPassword(model);
                        boolean isPasswordValid = currentPassword.equals(encryptedPassword);
                        if (isPasswordValid) {
                            logger.info(
                                    "It appears user has changed his password.Invalidating Cache and getting latest password from db");
                            ((CachedUserModel) user).getCachedWith().put(PASSWORD_CACHE_KEY, currentPassword);
                            return isPasswordValid;
                        }
                    }
                }
            } catch (Exception e) {
                logger.error("Couldn't validate user credentials " + e.getMessage());
            }
            return false;
        }
    
        public String getPassword(UserModel user) {
            String password = null;
            if (user instanceof CachedUserModel) {
                password = (String) ((CachedUserModel) user).getCachedWith().get(PASSWORD_CACHE_KEY);
            } else if (user instanceof UserAdapter) {
                password = ((UserAdapter) user).getPassword();
            }
            return password;
        }
    
        public EntityManager getEm() {
            return em;
        }
    
        public void setEm(EntityManager em) {
            this.em = em;
        }
    }