SQL Notes

The Bekwam JDBC SPI uses several SQL queries to expose an external datastore to Keycloak. These are

• Users Query
• Roles Query
• Select All Users Query
• Search Users Query

Keycloak usernames are lower-case. If the usernames in the datastore are upper- or mixed-case, make sure you apply SQL functions like Postgres' LOWER() for a case-insensitive comparison.

Users Query and Roles Query work in tandem to produce authentication (password check) and authorization (list of roles) to Keycloak. However, there is not requirement that they both query the same table or view. Users Query will run a SELECT that returns a hashed password when given a username. Roles Query will run a SELECT that can return multiple role names when given a username. Both queries can return empty results set which would me a user not found in the datastore or an empty set of roles.

As an example, consider the following DDL which creates a US_User table, a US_Role table, and a US_User_Role join table.

CREATE TABLE us_user (
                         id SERIAL PRIMARY KEY,
                         username VARCHAR(100) UNIQUE,
                         password VARCHAR(100) NOT NULL,
                         email VARCHAR(100),
                         name VARCHAR(100)
);

CREATE TABLE us_role (
                         id SERIAL PRIMARY KEY,
                         name VARCHAR(100) UNIQUE
);

CREATE TABLE us_user_role (
                              id SERIAL PRIMARY KEY,
                              user_id int NOT NULL REFERENCES us_user(id),
                              role_id int NOT NULL REFERENCES us_role(id)
);

A case-insensitive Users Query could be something like

SELECT password FROM us_user WHERE LOWER(username) = ?

And a Roles Query could be a join that returns multiple rows

SELECT username, name
FROM us_user
         JOIN us_user_role ON (us_user_role.user_id = us_user.id)
         JOIN us_role ON (us_user_role.role_id = us_role.id)

Select All Users Query and Search Users Query return user records for use in the Keycloak Users screen. The information returned from these queries is limited -- a name and an email in addition to the username -- but it provides more context when doing Keycloak user operations. User operations include connecting Identity Provider accounts and Multi-factor Authentication devices.

For both of these, 4 columns are expected to be returned.

-- Select All
SELECT username, password, name, email FROM us_user 
ORDER BY username

-- Search Users (case insensitive)
SELECT username, password, name, email FROM us_user 
WHERE LOWER(username) LIKE ? OR LOWER(name) LIKE ? OR LOWER(email) LIKE ? 
ORDER BY username

The SQL is not limited to tables. If the datastore is more complicated, a view or a db link can be used.

Finally, there is an optional Validation Query. This works with the timeout field. For Postgres, use something like

SELECT 1 FROM DUAL;

Validation Query will be run periodically for all datasources in a pool. Pools are managed at the component level.

As of version 1.1 of the SPI, you will need to verify the SQL for all queries by examining the log if you suspect something is not working.