Keycloak stores its data in an embedded H2 database by default, which is a lightweight and easy-to-use database that ships in the the Keycloak distribution. In this article, we will discuss how to access the Keycloak H2 database and perform common tasks such as deleting an user. By the end of this article, you will have a better understanding of how to manage the Keycloak H2 database.
Connecting to the H2 DB from the Prompt
Firstly, you need to enter the shell prompt. We will need to access the H2 Database Jar file from $KEYCLOAK_HOME/lib/lib/main
Therefore, from the Command Line start the H2 Database:
java -jar com.h2database.h2-2.1.214.jar
The above command, uses Keycloak 20.0.2 distribution H2 Jar file. Make sure to replace with your H2 Jar file in your Keycloak distribution.
Next, move to the H2 Web Console which is available at: http://localhost:8082
From there, you will need to set the appropriate JDBC Connection String and the username and password:
Here are the default Keycloak credentials for the H2 Database
jdbc:h2:file:~/jboss/keycloak-20.0.2/data/h2/keycloakdb;AUTO_SERVER=TRUE Username=sa Password=password
Great. Now you are connected to Keycloak default H2 Database. In the next section we will learn how to perform operations on the DB, such as deleting the default Administrator, in case you have lost the password
Deleting the Admin user in Keycloak
Disclaimer: this step is not for production usage. It might not be safe and, in the future, the name of Database tables might change or additional contraints might be added. We are including it here only for development purposes.
Firstly, the list of Keycloak users are available in the USER_ENTITY table. Therefore, you will find the admin user by running a “SELECT * from USER_ENTITY”.
However, some constraints exist in other Database tables, therefore, in order to perform a safe clean of an User, you should perform the following SQL statements:
Therefore, assuming that your Administrator user is “admin”, here is the list of SQL statement to reset the Administrator User:
DELETE FROM CREDENTIAL where USER_ID = ( SELECT id FROM USER_ENTITY where USERNAME = 'admin' and REALM_ID = ( SELECT ID FROM REALM WHERE NAME = 'master')); DELETE FROM USER_ROLE_MAPPING where USER_ID = ( SELECT id FROM USER_ENTITY where USERNAME = 'admin' and REALM_ID = ( SELECT ID FROM REALM WHERE NAME = 'master')); DELETE FROM USER_ENTITY where USERNAME = 'admin' and REALM_ID = ( SELECT ID FROM REALM WHERE NAME = 'master');
Next, start Keycloak from the Command Line:
./kc.sh start-dev
Finally, access the Administration Console at http://localhost:8080
You will see that the Administration Console requests to re-create a new Administration User:
Conclusion
This article was a quick walk thorough Keycloak default H2 Database configuration. You should be able to perform some common tasks such as resetting keycloak admin password.
Found the article helpful? if so please follow us on Socials
