Keycloak uses a local database by default to manage users. However, many organizations have existing user credentials available in a relational database. This article will teach you how to use Keycloak’s User Storage SPI to federate users from a PostgreSQL Database.
Concept of User Federation
User Federation in Keycloak refers to the ability to connect to external user stores, such as LDAP servers, Active Directory (AD), or custom databases. Keycloak acts as an intermediary, handling authentication requests and retrieving user information from external sources without migrating users and their credentials.
This approach allows centralized management of user identities across different realms or systems.
Out of the box, Keycloak supports LDAP and SAML Federation. This article discusses how to federate users from a relational Database such as PostgreSQL. With some changes in the configuration, you can easily adapt to another Database Provider.
In order to perform Database Federation we will leverage Keycloak’s User Storage SPI (Service Provider Interface) that allows developers to write extensions for connecting to other storages.
Let’s see how to do it in practice.
Setting up the Keycloak Project
To build the Custom DB Provider I will extend an existing GitHub useful project from Tilman Kranz. I have applied some changes to automate the database set up. At the end of this article you will find the link to the Source code for it.
Within the project you will find the following Components:
1) Entity Classes
The following Entity Classes are mapping the Storage Schema where Users are available for each Client and Realm.
- ClientRoleEntity : Entity mapping the Table
client_roles - RealmRoleEntity Entity mapping the Table
realm_roles - UserEntity: Entity mapping the Table
users
2) UserStorageProvider Class
Provider classes do all the heavy lifting of user lookup from the storage provider and other user operations. They must implement the org.keycloak.storage.UserStorageProvider . Additionally, you need to implement a set of capability interfaces methods for locating or managing users. (Ex: UserQueryProvider, UserLookupProvider etc). To create a UserStorageProvider instance, you need to implement the UserStorageProviderFactory.
3) UserAdapter
This Class extends an utility class called org.keycloak.storage.adapter.AbstractUserAdapterFederatedStorage. This class handles most of the UserModel methods, except for the username-related ones, which it delegates to an external user data store. If needed, you can customize and override specific methods to work with your external data store.
Overall, this is the database schema which contains the relations between users, clients, roles, and realms.
To build the Project you can run from the command line:
mvn install
Install the Test Provider
Next, we will install the custom provider in Keycloak. To do that, copy the JAR file from the previous build into the providers folder of your Keycloak distribution:
cp target/rdbms-federation.jar /path/keycloak-25.0.4/providers
Add the Database configuration
The newest Keycloak distributions rely on Quarkus as Runtime engine. ( More details in this article: Getting started with Keycloak powered by Quarkus )
Therefore, to configure Keycloak’s Datasource to use a PostgreSQL database copy the following quarkus.properties to the conf folder of Keycloak.
quarkus.datasource.user-store.db-kind=postgresql quarkus.datasource.user-store.username=postgres quarkus.datasource.user-store.password=postgres quarkus.datasource.user-store.jdbc.url=jdbc:postgresql://localhost:5432/userdb quarkus.datasource.user-store.jdbc.acquisition-timeout=30
cp conf/qauarkus.properties /path/keycloak-25.0.4/conf
Finally, to set up the Database and the Schema, I have added a docker-compose.yml file in the project which, out of the box, prepares your Database environment.
services:
postgres:
image: postgres
container_name: postgres-keycloak
environment:
POSTGRES_USER: postgres
POSTGRES_PASSWORD: postgres
POSTGRES_DB: userdb
ports:
- "5432:5432"
volumes:
- postgres_data:/var/lib/postgresql/data
- ./init.sql:/docker-entrypoint-initdb.d/init.sql
volumes:
postgres_data:
You can run it as follows:
docker-compose up
The docker-compose log should print on the Console the execution of the init SQL script. You can verify that the Database tables actually exist by logging into the Container:
# su postgres
postgres@1972a6179a77:/$ psql
psql (16.4 (Debian 16.4-1.pgdg120+1))
Type "help" for help.
postgres=# \c userdb
You are now connected to database "userdb" as user "postgres".
userdb=# \dt;
List of relations
Schema | Name | Type | Owner
--------+-----------------------+-------+--------
public | client_roles | table | userdb
public | realm_roles | table | userdb
public | users | table | userdb
public | users_to_client_roles | table | userdb
public | users_to_realm_roles | table | userdb
The init.sql script included a single user with the following credentials:
- Username: fmarchioni
- Password: secret (hashed with SHA-512)
Take note of the credentials since we will use them to test our application.
Configuring the Keycloak Realm
In the following section we will show how to set up a Keycloak example Realm from a standard distribution. If you want to use the Keycloak Docker image we recommend checking this article: How to run Keycloak with Docker
Firstly, rebuild your Keycloak distribution so that you will be able to find the new provider
./kc.sh build
Then, start Keycloak and verify that the Console does not contain any Connection errors to the Database:
kc.sh start-dev
Next, login to the Keycloak Admin Console with the admin Credentials and perform the following steps:
Create a new Realm “db-federation”:
Add the Database Provider to the Realm. To do that, select “User Federation” and Add “rdbms-federation provider”
Create an OIDC Client “testclient” with Redirect URI to “http://localhost:8180” ( where our application will run):
Next, create the Test Realm Role
Then, create a role “testrole” in realm “master”:
Create the Test Client Role
Create a role “admin” in client “testclient” using the ID value from the previous command:
Finally, from the Client UI download the Adapter Config we will use in our application:
{
"realm": "db-federation",
"auth-server-url": "http://localhost:8080/",
"ssl-required": "external",
"resource": "testclient",
"public-client": true,
"verify-token-audience": true,
"use-resource-role-mappings": true,
"confidential-port": 0
}
Testing the Database Federation with an Application
You can test an example application which uses a Servlet and an HttpMethodConstraint that requires an “admin” Role:
@WebServlet("/secured")
@ServletSecurity(httpMethodConstraints = { @HttpMethodConstraint(value = "GET", rolesAllowed = { "admin" }) })
public class SecuredServlet extends HttpServlet {
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse resp) throws ServletException, IOException {
try (PrintWriter writer = resp.getWriter()) {
writer.println("<html>");
writer.println(" <head><title>Secured Servlet</title></head>");
writer.println(" <body>");
writer.println(" <h1>Secured Servlet</h1>");
writer.println(" <p>");
writer.print(" Current Principal '");
Principal user = req.getUserPrincipal();
writer.print(user != null ? user.getName() : "NO AUTHENTICATED USER");
writer.print("'");
writer.println(" </p>");
writer.println(" </body>");
writer.println("</html>");
}
}
}
You will need to include the oidc.json file in the WEB-INF folder of your application:
├───src │ └───main │ ├───java │ │ └───com │ │ └───mastertheboss │ │ └───servlet │ │ SecuredServlet.java │ │ │ └───webapp │ └───WEB-INF │ oidc.json │ web.xml
Finally, the web.xml needs to set OIDC as the Login Configuration:
<login-config>
<auth-method>OIDC</auth-method>
</login-config>
Start WildFly with an offset ( for example 100) in order to avoid conflicts with Keycloak.
./standalone.sh -Djboss.socket.binding.port-offset=100
Then, request the /secured Servlet at http://localhost:8180/db-provider/secured :
You will be redirected to Keycloak login for the DB Federation:
Enter the credentials and verify that you can access the Secured Servlet:
Secured Servlet Current Principal 'f:52e7998d-4f5d-46a6-aab0-7832c4c6cb4a:1'
Source code
The Keycloak Database Provider example is available in this repository: https://github.com/fmarchioni/keycloak-user-storage-test
The source code for the Jakarta EE project is available here: https://github.com/fmarchioni/mastertheboss/tree/master/keycloak/db-provider
Conclusion
This article was a step-by-step guide through the creation of a Keycloak DB Provider that will let you authenticate with a PostgreSQL database. We have gone through the steps to load the DBProvider in a Keycloak Realm and finally we have tested the application on WildFly by exporting the Client Adapter JSON configuration.