Why Keycloak Needs Postgres
56sHighlights a critical production pitfall that many developers overlook, sparking curiosity and a sense of urgency.
▶ Play ClipAI Summary
This video demonstrates how to configure Keycloak to use a Postgres database instead of its default embedded database, which is unsuitable for production. The tutorial covers setting up a Postgres service in Docker Compose, creating a dedicated schema, and configuring the necessary environment variables. It also verifies the setup by inspecting the database tables and confirming that Keycloak stores user data correctly.
Chapters
1
Why Postgres is Needed for Keycloak in Production
00:02
2
Setting Up the Postgres Database Service
01:09
3
Configuring Keycloak Environment Variables
02:04
4
Running Keycloak with Postgres and Testing
04:08
5
Verifying Data in Postgres and Schema Overview
05:35
6
Keycloak Data Model and Production Best Practices
07:12
[00:02]
Production Database Requirement
The default embedded database in Keycloak is only for development/testing; a production-ready database like Postgres is required for production workloads.
[00:40]
Postgres Service Configuration
Define a Postgres service in Docker Compose with image postgres:17, environment variables for user, password, and database name, a volume for data persistence, and port 5432.
[01:38]
Creating a Dedicated Schema
Connect to the Postgres instance and create a dedicated schema (e.g., 'keycloak') to isolate Keycloak tables from application tables.
[02:18]
Keycloak Dependencies and Restart Policy
Add a dependency from Keycloak to Postgres and set restart: unless-stopped to ensure Keycloak waits for Postgres to be ready.
[02:32]
Keycloak Environment Variables
Configure environment variables: KC_DB (postgres), KC_DB_USERNAME, KC_DB_PASSWORD, KC_DB_SCHEMA, KC_DB_URL_HOST (service name), KC_DB_URL_PORT (5432), and KC_DB_URL_DATABASE (database name).
[04:08]
Automatic Schema Scaffolding
After starting, Keycloak automatically scaffolds all required database tables (90 tables) in the specified schema.
[05:50]
Verifying Data in Postgres
Verify the setup by connecting to Postgres with a database viewer; the keycloak schema contains 90 tables, including user_entity, realm, and credential tables.
[06:33]
Password Storage Details
Keycloak uses Argon2 by default for password hashing, storing the hash, salt, iteration count, and algorithm in the credential table.
[07:12]
Keycloak Data Model Overview
The Keycloak data model is centered around the realm table, with all other tables (clients, users, etc.) relating to it.
Mentioned in this Video
Tutorial Checklist
1
00:40
Define a Postgres service in Docker Compose with image postgres:17, set environment variables (POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_DB), add a volume for data persistence, and expose port 5432.
2
01:38
Start the Postgres service and connect to it using a database client. Create a dedicated schema (e.g., 'keycloak') to isolate Keycloak tables.
3
02:18
In the Keycloak service definition, add a depends_on condition for Postgres and set restart: unless-stopped.
4
02:32
Set the following environment variables for Keycloak: KC_DB=postgres, KC_DB_USERNAME=postgres, KC_DB_PASSWORD=postgres, KC_DB_SCHEMA=keycloak, KC_DB_URL_HOST=postgres (service name), KC_DB_URL_PORT=5432, KC_DB_URL_DATABASE=keycloak_off.
5
04:08
Run docker-compose up and verify Keycloak starts successfully. Check logs for database connection and schema scaffolding.
6
05:50
Connect to the Postgres instance with a database viewer to confirm the keycloak schema contains 90 tables and user data is stored correctly.
Study Flashcards (6)
What type of database should you use for Keycloak in production?
easy
Click to reveal answer
What type of database should you use for Keycloak in production?
easy
Click to reveal answer
Postgres (or another production-ready database like SQL Server or Oracle).
00:02
Which environment variable specifies the database type for Keycloak?
easy
Click to reveal answer
Which environment variable specifies the database type for Keycloak?
easy
Click to reveal answer
KC_DB
02:45
What hashing algorithm does Keycloak use by default for password storage?
medium
Click to reveal answer
What hashing algorithm does Keycloak use by default for password storage?
medium
Click to reveal answer
Argon2
06:58
Which environment variable sets the hostname for the Postgres instance in Keycloak?
medium
Click to reveal answer
Which environment variable sets the hostname for the Postgres instance in Keycloak?
medium
Click to reveal answer
KC_DB_URL_HOST
03:13
How many tables does Keycloak create in the Postgres database for its schema?
hard
Click to reveal answer
How many tables does Keycloak create in the Postgres database for its schema?
hard
Click to reveal answer
90
06:04
What is the central table in the Keycloak data model?
hard
Click to reveal answer
What is the central table in the Keycloak data model?
hard
Click to reveal answer
The realm table is the core, and all other tables relate to it.
07:12
💡 Key Takeaways
🔧
Production Database Requirement
Clearly states that the embedded database is insufficient for production, setting the stage for the tutorial.
00:02
🔧
Isolating Keycloak Tables in a Schema
Demonstrates best practice of creating a dedicated schema to separate Keycloak tables from application tables.
01:38
📊
Keycloak Schema Size
Reveals that Keycloak creates 90 tables, highlighting the complexity of its data model.
06:04
💡
Password Storage Details
Shows how Keycloak stores password hashes, salts, and algorithm metadata, which is valuable for security audits.
06:46
⚖️
Production Best Practice
Reinforces that using a proper database like Postgres is the recommended approach for production deployments.
07:40Full Transcript
[00:02] identity provider in production, you're going to have to pair it with a production ready database like Postgres. The default embedded database that Keycloak runs in a development or testing setup isn't going to be enough
[00:15] for a production workload. So, in this video, I'll show you how you can connect Keycloak with your Postgres database. Here's my Docker Compose YAML file that Here's my Docker Compose YAML file that runs my .NET API, which is configured to
[00:28] connect to my Keycloak instance. And also got an Aspire dashboard for observability. So, first things first, we're going to actually need our database instance. So, I'm going to define that as another service. Let's
[00:40] call it Postgres. The image I'm going to run is going to be Postgres 17. Let's also give it a container name. I'll call it the Keycloak database. And then, let's give it a couple of environment variables. The first one is going to be
[00:54] the Postgres user, and I'll use the value of just Postgres. Let's also just use a dummy password for testing purposes. And let's also initialize a database inside by passing in Postgres DB, and I'm going to call this database
[01:09] Keycloak off. I'm going to define a volume mapping so that I can persist my data locally. And then, finally, for the ports, I'm going to expose the default port, which is 5432 for Postgres. Now, before we continue
[01:25] with the Keycloak setup, I want to just run this to confirm that my Postgres instance is starting up. And if I open up Docker Desktop, I should be able to see my Postgres database up and running, and we can find it here. Now, one more
[01:38] thing I want to do before we connect this to our Keycloak instance is to connect to our database so that we can create our database schema first. And I want to use a dedicated schema to just isolate all the Keycloak tables, and
[01:51] this will make it easier to run Keycloak together with our regular tables from whatever application it is that you're building. So, what I want to say here is building. So, what I want to say here is create schema if not exists, and I'm
[02:04] going to call it Keycloak. Let's execute this. It completed successfully. I'm going to exit, and then let's go back to our Docker Compose YAML file. What do we need to tell Keycloak to connect to our Postgres instance? Well, one thing we
[02:18] can do is add a dependency between our Keycloak service and our Postgres Keycloak service and our Postgres service. We can also tell Keycloak to restart until it can successfully run and connect to our Postgres instance.
[02:32] And then, everything else is going to be configuring the respective environment variables, and I'll walk you through what they are one by one. The first one is KCDB, where we define the type of database
[02:45] it's going to be just Postgres, and don't confuse this with the service name. This is just used to denote the specific DBMS used for persisting Keycloak data. It could be SQL Server, it could be Oracle, or any other
[02:58] database that Keycloak supports. Then, we've got KCDB username and also password, which is just Postgres and Postgres. And I can configure which schema we want to use, and we created this Keycloak schema just a moment ago.
[03:13] So, that's where I'm telling Keycloak to store its data. Then, I've got KCDB URL host, and this is just a host name for your Postgres instance. In my case, this
[03:25] matches the service name that I've configured in my Docker Compose file. If this were called something different, like Keycloak DB, then this is my host name, and that is what I would use in the environment variable. Typically,
[03:38] this is going to be your URI for a hosted Postgres instance, let's say somewhere in the Azure or AWS cloud. Then, you've got the KCDB port. We're running this with the default port of 5432. And finally, I've got KCDB URL
[03:53] database, and we've got a custom database where we are storing our data, actually, I've made a mistake. It should be Keycloak off. So, let me update this. But essentially, this is the database within Postgres where we are going to
[04:08] persist our data. And that's everything you need to do to set up Keycloak to use your Postgres instance as its database. So, now I should be able to just run this using Docker Compose. And if you go into the logs for our Keycloak instance,
[04:23] we should see it connecting to the Postgres instance. Now, because the database schema doesn't exist, Keycloak is going to go ahead and scaffold all of the database tables that it needs to run. And in a moment or two, it should
[04:38] be ready to start accepting connections. So, if I go to localhost:8080, and we type in our admin username and password, we should be able to log in to our master realm. So, if I go to manage realms, there's the master realm here,
[04:51] but I can go ahead and create my custom realm that's configured for use inside of my code base. So, let me quickly set this up by enabling user registration, and then creating a client that we can use to authenticate with Keycloak. I'll
[05:06] press next here, and next, we have to configure our redirect URIs and the web origins. And now, I should be able to go into my Swagger UI. From here, I can click authorize, specify the name of my client. Let's select both of the scopes,
[05:21] and then click authorize. This should redirect us to our Keycloak realm, where we can enter our username and password. Or if we don't have it, we can register as a new user. And after I fill in the data, I can click register. We get
[05:35] redirected back to our Swagger UI, and we've also got our access token. Now, I can send an authenticated request, and you can see that we get back our data. So, everything still works, but how are we so sure that we're actually using the
[05:50] Postgres database? The best way to know is to check. If we connect to our Postgres instance from some database viewer, we can find our Keycloak schema here. And inside of it, you can see we have 90 tables. So, these are all tables
[06:04] created by Keycloak when we configured our connection to Postgres. And for example, the user entity table is what contains our user data. And here's our test user that we just created, and you can see it's connected to our custom
[06:18] realm. So, our realm is a logical grouping that contains our users. We can also find our realm here in the respective realm table. So, there's the default master realm, and also our off demo realm. Another interesting thing is
[06:33] the user credentials, which you can find in the credential table. And if I scroll to the right, what I found interesting is how Keycloak stores the information for the hash function, but also the password data for the user. So, there's
[06:46] the hash part in the value, and it also contains the salt, which was used to hash the password. And in the credential data column, it stores the number of iterations used, the hashing algorithm, and you can see that Keycloak uses
[06:58] Argon2 by default. And then, a couple other configuration values. Now, if we take a look at the entire diagram of the Keycloak schema, we can begin to see just how complicated the data model is. So, as I said, it's 90 tables just for
[07:12] authentication, and you can see the tables are grouped based on what they belong to. But at the core of the Keycloak data model is the realm, and then everything else is related to it. So, you've got the realm table here with
[07:25] most of the data. Another big one is for the API clients that we have. And here's the user entity table, and everything else that's required to keep Keycloak up and running. And a reminder again that this is the recommended approach to run
[07:40] Keycloak in production, and you should not be using the embedded database that it comes with, which you're going to see in most of the Keycloak demos, including the ones I've previously published on my channel. This is the first time I'm
[07:52] showing how to actually use a proper database like Postgres for your Keycloak instance. And all you have to do is just set up a couple of environment variables And if you want to grab the source code for this video, it's going to be
[08:05] available for free in the pinned comment right below. If you care about security, you should not forget about two-factor authentication. And here's how you can implement this in ASP.NET Core using time-based one-time passwords. If you
[08:20] enjoyed this video, consider gently tapping the like button. Thanks a lot for watching, and until next time, stay awesome.
