How to access Keycloak H2 Database

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:

keycloak how to recover admin password h2 database

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:

keycloak reset admin password

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.