[keycloak] SPI (service provider interface) 를 이용하여 사용자DB를 연결하기 (version 20.0.5)

SSO 연동을 위해 keycloak을 사용할 경우가 있다.
그런데 대부분의 경우에는 user DB에 사용자를 등록해 놓고 user 정보를 연동하기를 원할 것이다.
이 경우에는 keycloak 에서 제공하는 provider 를 사용하여 외부 DB 와 연동할 수 있다.

아래는 연동 프로세스를 표현한 다이어 그램이다.

keycloak 에서 연동을 위해서는 Service Provider Interface 를 구현해야 한다. 해당 스펙에 대해서는 keycloak 공식문서에 잘 설명되어 있지만,,
https://www.keycloak.org/docs/latest/server_development/#_user-storage-spi

Server Developer Guide

직접 SPI 를 구현하기 보다는 잘 만들어져 있는 소스를 사용하는 것이 좀더 수월하게 대응할 수 있다.
그래서, 아래의 사이트에서 도움을 얻기로 했다.
https://github.com/opensingular/singular-keycloak-database-federation

GitHub - opensingular/singular-keycloak-database-federation: Keycloak User Storage SPI for Relational Databases (Keycloak User F

그런데 아쉽게도 해당 소스는 keycloak ver 17 기반으로 만들어져 있다.
그래서 20.0.5 버전에 대응하기 위해 PR(pull request) 소스를 참조하여 수정하기로 하였다.

Pull Request
https://github.com/opensingular/singular-keycloak-database-federation/pull/29/files

Upgrade to keycloak v20.0.0 by t7tran · Pull Request #29 · opensingular/singular-keycloak-database-federation

수정사항1 : pom.xml 에 설정되어 있는 keycloak 버전도 변경해 준다.

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">

    <modelVersion>4.0.0</modelVersion>


    <artifactId>singular-user-storage-provider</artifactId>
    <groupId>org.opensingular</groupId>
    <version>2.4.1</version>

    <dependencies>
        <dependency>
            <groupId>org.keycloak</groupId>
            <artifactId>keycloak-core</artifactId>
            <version>${keycloak.version}</version>
            <scope>provided</scope>
        </dependency>

        <dependency>
            <groupId>org.keycloak</groupId>
            <artifactId>keycloak-server-spi</artifactId>
            <version>${keycloak.version}</version>
            <scope>provided</scope>
        </dependency>
        <dependency>
            <groupId>org.keycloak</groupId>
            <artifactId>keycloak-model-jpa</artifactId>
            <version>${keycloak.version}</version>
            <scope>provided</scope>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-api</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
        <dependency>
            <groupId>org.jboss.logging</groupId>
            <artifactId>jboss-logging</artifactId>
            <version>${jboss-logging.version}</version>
            <scope>provided</scope>
        </dependency>

        <!-- demonstrates usage of custom dependencies in an ear -->
        <dependency>
            <groupId>com.google.guava</groupId>
            <artifactId>guava</artifactId>
            <version>21.0</version>
        </dependency>

        <dependency>
            <groupId>com.google.auto.service</groupId>
            <artifactId>auto-service</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <version>${lombok.version}</version>
            <optional>true</optional>
        </dependency>


        <dependency>
            <groupId>com.zaxxer</groupId>
            <artifactId>HikariCP</artifactId>
            <version>4.0.3</version>
        </dependency>


        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.12.0</version>
        </dependency>

        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
            <version>1.15</version>
        </dependency>


        <dependency>
            <groupId>commons-io</groupId>
            <artifactId>commons-io</artifactId>
            <version>2.6</version>
        </dependency>

        <dependency>
            <groupId>net.sourceforge.jtds</groupId>
            <artifactId>jtds</artifactId>
            <version>1.3.1</version>
        </dependency>

        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.12</version>
        </dependency>

        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.19</version>
        </dependency>

        <dependency>
            <groupId>com.ibm.db2.jcc</groupId>
            <artifactId>db2jcc</artifactId>
            <version>db2jcc4</version>
        </dependency>

        <dependency>
            <groupId>com.oracle.ojdbc</groupId>
            <artifactId>ojdbc8</artifactId>
            <version>19.3.0.0</version>
        </dependency>

        <dependency>
            <groupId>at.favre.lib</groupId>
            <artifactId>bcrypt</artifactId>
            <version>0.9.0</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>

        <dependency>
            <groupId>org.hibernate</groupId>
            <artifactId>hibernate-core</artifactId>
            <version>5.4.18.Final</version>
            <scope>provided</scope>
        </dependency>


    </dependencies>


    <build>
        <finalName>singular-user-storage-provider</finalName>
        <plugins>
            <plugin>
                <groupId>org.wildfly.plugins</groupId>
                <artifactId>wildfly-maven-plugin</artifactId>
                <version>2.0.2.Final</version>
                <configuration>
                    <skip>false</skip>
                </configuration>
            </plugin>
            <plugin>
                <artifactId>maven-dependency-plugin</artifactId>
                <executions>
                    <execution>
                        <phase>package</phase>
                        <goals>
                            <goal>copy-dependencies</goal>
                        </goals>
                        <configuration>
                            <excludeScope>provided</excludeScope>
                            <outputDirectory>${project.basedir}/dist</outputDirectory>
                        </configuration>
                    </execution>
                </executions>
            </plugin>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-jar-plugin</artifactId>
                <version>3.2.2</version>
                <configuration>
                    <outputDirectory>${project.basedir}/dist</outputDirectory>
                </configuration>
            </plugin>
        </plugins>
    </build>

    <properties>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        <maven.compiler.source>1.8</maven.compiler.source>
        <maven.compiler.target>1.8</maven.compiler.target>

        <lombok.version>1.18.22</lombok.version>
        <jboss-logging.version>3.3.1.Final</jboss-logging.version>
        <keycloak.version>20.0.5</keycloak.version>
        <auto-service.version>1.0-rc5</auto-service.version>
        <jboss.home>target/keycloak</jboss.home>
    </properties>

    <dependencyManagement>
        <dependencies>

            <dependency>
                <groupId>com.google.auto.service</groupId>
                <artifactId>auto-service</artifactId>
                <version>${auto-service.version}</version>
                <scope>provided</scope>
                <optional>true</optional>
            </dependency>

        </dependencies>
    </dependencyManagement>
</project>

수정사항2 : DBUserStorageProvider.java

package org.opensingular.dbuserprovider;

import lombok.extern.jbosslog.JBossLog;
import org.keycloak.component.ComponentModel;
import org.keycloak.credential.CredentialInput;
import org.keycloak.credential.CredentialInputUpdater;
import org.keycloak.credential.CredentialInputValidator;
import org.keycloak.models.*;
import org.keycloak.models.cache.CachedUserModel;
import org.keycloak.models.credential.PasswordCredentialModel;
import org.keycloak.storage.StorageId;
import org.keycloak.storage.UserStorageProvider;
import org.keycloak.storage.user.UserLookupProvider;
import org.keycloak.storage.user.UserQueryProvider;
import org.keycloak.storage.user.UserRegistrationProvider;
import org.opensingular.dbuserprovider.model.QueryConfigurations;
import org.opensingular.dbuserprovider.model.UserAdapter;
import org.opensingular.dbuserprovider.persistence.DataSourceProvider;
import org.opensingular.dbuserprovider.persistence.UserRepository;
import org.opensingular.dbuserprovider.util.PagingUtil;

import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.stream.Stream;

@JBossLog
public class DBUserStorageProvider implements UserStorageProvider,
                                              UserLookupProvider, UserQueryProvider, CredentialInputUpdater, CredentialInputValidator, UserRegistrationProvider {
    
    private final KeycloakSession session;
    private final ComponentModel  model;
    private final UserRepository  repository;
    private final boolean allowDatabaseToOverwriteKeycloak;

    DBUserStorageProvider(KeycloakSession session, ComponentModel model, DataSourceProvider dataSourceProvider, QueryConfigurations queryConfigurations) {
        this.session    = session;
        this.model      = model;
        this.repository = new UserRepository(dataSourceProvider, queryConfigurations);
        this.allowDatabaseToOverwriteKeycloak = queryConfigurations.getAllowDatabaseToOverwriteKeycloak();
    }
    
    
    private Stream<UserModel> toUserModel(RealmModel realm, List<Map<String, String>> users) {
        return users.stream()
                .map(m -> new UserAdapter(session, realm, model, m, allowDatabaseToOverwriteKeycloak));
    }
    
    
    @Override
    public boolean supportsCredentialType(String credentialType) {
        return PasswordCredentialModel.TYPE.equals(credentialType);
    }
    
    @Override
    public boolean isConfiguredFor(RealmModel realm, UserModel user, String credentialType) {
        return supportsCredentialType(credentialType);
    }
    
    @Override
    public boolean isValid(RealmModel realm, UserModel user, CredentialInput input) {
        
        log.infov("isValid user credential: userId={0}", user.getId());
        
        if (!supportsCredentialType(input.getType()) || !(input instanceof UserCredentialModel)) {
            return false;
        }
        
        UserCredentialModel cred = (UserCredentialModel) input;

        UserModel dbUser = user;
        // If the cache just got loaded in the last 500 millisec (i.e. probably part of the actual flow), there is no point in reloading the user.)
        if (allowDatabaseToOverwriteKeycloak && user instanceof CachedUserModel && (System.currentTimeMillis() - ((CachedUserModel) user).getCacheTimestamp()) > 500) {
          dbUser = this.getUserById(realm, user.getId());

          if (dbUser == null) {
            ((CachedUserModel) user).invalidate();
            return false;
          }

          // For now, we'll just invalidate the cache if username or email has changed. Eventually we could check all (or a parametered list of) attributes fetched from the DB.
          if (!java.util.Objects.equals(user.getUsername(), dbUser.getUsername()) || !java.util.Objects.equals(user.getEmail(), dbUser.getEmail())) {
            ((CachedUserModel) user).invalidate();
          }
        }
        return repository.validateCredentials(dbUser.getUsername(), cred.getChallengeResponse());
    }
    
    @Override
    public boolean updateCredential(RealmModel realm, UserModel user, CredentialInput input) {
        
        log.infov("updating credential: realm={0} user={1}", realm.getId(), user.getUsername());
        
        if (!supportsCredentialType(input.getType()) || !(input instanceof UserCredentialModel)) {
            return false;
        }
        
        UserCredentialModel cred = (UserCredentialModel) input;
        return repository.updateCredentials(user.getUsername(), cred.getChallengeResponse());
    }
    
    @Override
    public void disableCredentialType(RealmModel realm, UserModel user, String credentialType) {
    }
    
    @Override
    public Stream<String> getDisableableCredentialTypesStream(RealmModel realm, UserModel user)
    {
        return Stream.empty();
    }
    
    @Override
    public void preRemove(RealmModel realm) {
        
        log.infov("pre-remove realm");
    }
    
    @Override
    public void preRemove(RealmModel realm, GroupModel group) {
        
        log.infov("pre-remove group");
    }
    
    @Override
    public void preRemove(RealmModel realm, RoleModel role) {
        
        log.infov("pre-remove role");
    }
    
    @Override
    public void close() {
        log.debugv("closing");
    }
    
    @Override
    public UserModel getUserById(RealmModel realm, String id) {
        
        log.infov("lookup user by id: realm={0} userId={1}", realm.getId(), id);
        
        String externalId = StorageId.externalId(id);
        Map<String, String> user = repository.findUserById(externalId);

        if (user == null) {
            log.debugv("findUserById returned null, skipping creation of UserAdapter, expect login error");
            return null;
        } else {
            return new UserAdapter(session, realm, model, user, allowDatabaseToOverwriteKeycloak);
        }
    }
    
    @Override
    public UserModel getUserByUsername(RealmModel realm, String username) {
        
        log.infov("lookup user by username: realm={0} username={1}", realm.getId(), username);
        
        return repository.findUserByUsername(username).map(u -> new UserAdapter(session, realm, model, u, allowDatabaseToOverwriteKeycloak)).orElse(null);
    }
    
    @Override
    public UserModel getUserByEmail(RealmModel realm, String email) {
        
        log.infov("lookup user by username: realm={0} email={1}", realm.getId(), email);

        return getUserByUsername(realm, email);
    }
    
    @Override
    public int getUsersCount(RealmModel realm) {
        return repository.getUsersCount(null);
    }
    
    @Override
    public int getUsersCount(RealmModel realm, Set<String> groupIds) {
        return repository.getUsersCount(null);
    }
    
    @Override
    public int getUsersCount(RealmModel realm, String search) {
        return repository.getUsersCount(search);
    }
    
    @Override
    public int getUsersCount(RealmModel realm, String search, Set<String> groupIds) {
        return repository.getUsersCount(search);
    }
    
    @Override
    public int getUsersCount(RealmModel realm, Map<String, String> params) {
        return repository.getUsersCount(null);
    }
    
    @Override
    public int getUsersCount(RealmModel realm, Map<String, String> params, Set<String> groupIds) {
        return repository.getUsersCount(null);
    }
    
    @Override
    public int getUsersCount(RealmModel realm, boolean includeServiceAccount) {
        return repository.getUsersCount(null);
    }

    public Stream<UserModel> searchForUserStream(RealmModel realm, String search, Integer firstResult,
                                                 Integer maxResults)
    {
        
        log.infov("list users: realm={0} firstResult={1} maxResults={2}", realm.getId(), firstResult, maxResults);
        return internalSearchForUser(search, realm, new PagingUtil.Pageable(firstResult, maxResults));
    }
    
    @Override
    public Stream<UserModel> searchForUserStream(RealmModel realm, Map<String, String> params, Integer firstResult,
                                                 Integer maxResults)
    {
        log.infov("search for users with params: realm={0} params={1}", realm.getId(), params);
        return internalSearchForUser(params.values().stream().findFirst().orElse(null), realm, null);
    }
    

    @Override
    public Stream<UserModel> getGroupMembersStream(RealmModel realm, GroupModel group, Integer firstResult,
                                                   Integer maxResults)
    {
        log.infov("search for group members with params: realm={0} groupId={1} firstResult={2} maxResults={3}", realm.getId(), group.getId(), firstResult, maxResults);
        return Stream.empty();
    }

    private Stream<UserModel> internalSearchForUser(String search, RealmModel realm, PagingUtil.Pageable pageable) {
        return toUserModel(realm, repository.findUsers(search, pageable));
    }
    
    @Override
    public Stream<UserModel> searchForUserByUserAttributeStream(RealmModel realm, String attrName, String attrValue)
    {
        log.infov("search for group members: realm={0} attrName={1} attrValue={2}", realm.getId(), attrName, attrValue);
        return Stream.empty();
    }
    
    
    @Override
    public UserModel addUser(RealmModel realm, String username) {
        // from documentation: "If your provider has a configuration switch to turn off adding a user, returning null from this method will skip the provider and call the next one."
        return null;
    }
    
    
    @Override
    public boolean removeUser(RealmModel realm, UserModel user) {
        boolean userRemoved = repository.removeUser();
        
        if (userRemoved) {
            log.infov("deleted keycloak user: realm={0} userId={1} username={2}", realm.getId(), user.getId(), user.getUsername());
        }
        
        return userRemoved;
    }
}

이렇게 수정하고 나서 maven 으로 package 만들면, 아래의 파일이 /dist 폴더에 추가/갱신 된다.

bcrypt-0.9.0.jar
bytes-1.3.0.jar
singular-user-storage-provider.jar

이 파일을 포함하여 /dist 폴더의 모든 파일을  keycloak의 /opt/keycloak/providers 폴더에 넣는다. 

그리고 keycloak admin 로그인 > Realm 선택 > User federation > Add new provider > singular-db-user-provider를 선택한 후, 입력값을 넣는다. 

mysql의 경우,

jdbcurl : jdbc:mysql://{ipaddress}:{port}/{database name}
RDBMS : MySQL 5.7+
User count SQL query : select count(*) from users <= 커스텀 사용자DB 정보를 넣는다.

password hash function : SHA-1 <= User DB에 저장된 hash value는 소문자여가 한다. (주의!!!)

postgresql 의 경우,

jdbcurl : jdbc:postgresql://{ipaddress}:{port}/{database name}
RDBMS : Postgres 10+
User count SQL query : select count(*) from users <= 커스텀 사용자DB 정보를 넣는다.

password hash function : SHA-1 <= User DB에 저장된 hash value는 소문자여가 한다. (주의!!!)

SQL 쿼리 입력시 기본적으로 따옴표로 묶여 있는데, 이부분을 지워주어야 한다. (기동시 console 에서 에러를 확인할 수 있다)

* 에러 로그 *
2023-06-06 14:03:39,390 INFO  [org.opensingular.dbuserprovider.persistence.UserRepository] (executor-thread-13) Query: select "id",            "username",            "email",            "firstName",            "lastName",            "cpf",            "fullName" from public.users where upper("username") like (?)  limit 11 params: [%] 
2023-06-06 14:03:39,392 ERROR [org.opensingular.dbuserprovider.persistence.UserRepository] (executor-thread-13) ERROR: column "firstName" does not exist
  Hint: Perhaps you meant to reference the column "users.firstname".
  Position: 68: org.postgresql.util.PSQLException: ERROR: column "firstName" does not exist
  Hint: Perhaps you meant to reference the column "users.firstname".

이렇게 하고 나서, "Save" 버튼을 누르면 싱크가된다.

Action 구분 
Sync all users : 모든 유저를 싱크한다. (user Id는 변경되지 않는다.)
Unlink users :  모든 유저의 링크를 제거한다. (유저를 리셋할 경우 사용)

등록을 하게되면 자동으로 유저를 sync 하므로 일반적으로 "Unlink users" 를 사용하여 keycloak에 등록된 유저를 unlink 하여 모두 재동기화 한다.

끝. (설명이 거친 부분은 보강이 필요)