Keycloak doesn’t actually use PostgreSQL for storing its own configuration data; it uses it as an external identity provider, which is a subtle but critical distinction.
Let’s see Keycloak talking to PostgreSQL as an identity provider. Imagine we have a PostgreSQL database running at pg.example.com:5432 with a database named userdb and a user keycloak_reader with the password s3cr3tP@ssw0rd. This user has read-only access to a table named users with columns username and password_hash.
First, we need to configure Keycloak’s realm to use this PostgreSQL database. In the Keycloak Admin Console, navigate to your realm, then to "Identity Providers." Click "Add provider" and select "OpenID Connect v1.0" if you’ve set up PostgreSQL with an OIDC layer, or configure a direct JDBC connection if you’re using a Keycloak extension for that. For this example, let’s assume a direct JDBC connection for simplicity, which would involve a custom Keycloak SPI.
If we were using a direct JDBC connection via a custom SPI, the configuration would look something like this in the realm’s JSON export (realm.json):
{
"id": "my-postgres-idp",
"alias": "PostgreSQL-Users",
"providerId": "postgresql-user-spi", // This would be a custom SPI
"enabled": true,
"config": {
"jdbcUrl": ["jdbc:postgresql://pg.example.com:5432/userdb"],
"username": ["keycloak_reader"],
"password": ["s3cr3tP@ssw0rd"],
"driverClass": ["org.postgresql.Driver"],
"fetchUsersByUsernameLdapSpiName": [""],
"user-search-filter": ["(objectClass=user)"],
"user-login-filter": ["(uid={0})"],
"users-dn": [""],
"custom-user-search-filter": ["username", "password_hash"], // Mapping to our table columns
"connection-pool-max-size": ["20"],
"validate-certificates": ["true"],
"authn-context-class-for-idp-initiated-sso-url": [""]
}
}
Now, when a user tries to log in to Keycloak, and their authentication is delegated to this "PostgreSQL-Users" identity provider, Keycloak will attempt to connect to pg.example.com:5432/userdb using keycloak_reader/s3cr3tP@ssw0rd. It would then query the users table to validate the username and password hash.
The core problem Keycloak solves here is centralizing user authentication. Instead of managing users separately in Keycloak and in your PostgreSQL database, you can use your existing PostgreSQL user base as a source of truth for authentication. This is particularly useful for applications that already rely heavily on a PostgreSQL database for user data. Keycloak acts as the gatekeeper, verifying credentials against this external source before issuing its own tokens.
Internally, this relies on Keycloak’s Service Provider Interface (SPI) mechanism. For direct database connections like this (if supported by an SPI), Keycloak uses JDBC drivers to interact with the database. The providerId in the configuration points to a specific SPI implementation that knows how to translate Keycloak’s authentication requests into SQL queries. The config section then provides the necessary connection details and query parameters.
The levers you control are primarily the jdbcUrl, username, password, and importantly, the custom-user-search-filter or similar configurations that map Keycloak’s expected user attributes to your PostgreSQL table’s columns. You can also tune connection pool settings like connection-pool-max-size for performance and reliability.
A common misconception is that Keycloak migrates your PostgreSQL users. It doesn’t. It reads from them. If you delete a user in PostgreSQL, they will immediately become unauthenticated in Keycloak. Conversely, if you add a user to Keycloak’s internal store, they won’t appear in PostgreSQL. This is a federated identity setup, not a synchronization.
The next problem you’ll likely encounter is how to handle user provisioning and de-provisioning when using an external identity provider like this.