SQL Resolver based on a view

I want to use Keycloak with Privacyidea and tried to create a mysql view for my users

View on Keycloak Users
CREATE VIEW users (userid,username,givenname,surname,email,mobile,realm ) AS
SELECT USER_ENTITY.id as userid, username,FIRST_NAME as givenname,LAST_NAME as surname, EMAIL as email, value as mobile,REALM_ID as realm FROM keycloak.USER_ENTITY LEFT JOIN keycloak.USER_ATTRIBUTE ON USER_ENTITY.id=USER_ATTRIBUTE.user_id and USER_ATTRIBUTE.name=‘mobile’;

Whe I try to use the view in a SQL Resolver I get the error message
“failed to retrieve users: table ‘users’ does not have a primary key defined”

Wouldn’t it make sense to enable “Read only SQL Resolver based on a view”

I found a workaround - I have created an event to Insert/Update my users table

Table

CREATE TABLE users (userid varchar(100) key,username varchar(100),givenname varchar(100),surname varchar(100),email varchar(200),mobile varchar(100),realm varchar(100));

Event

CREATE EVENT Keycloak_Users
ON SCHEDULE EVERY 10 MINUTE
ON COMPLETION PRESERVE
DO
INSERT INTO users (userid,username,givenname,surname,email,mobile,realm)
SELECT USER_ENTITY.id as userid, username,FIRST_NAME as givenname,LAST_NAME as surname, EMAIL as email, value as mobile,REALM_ID as realm FROM keycloak.USER_ENTITY LEFT JOIN keycloak.USER_ATTRIBUTE ON USER_ENTITY.id=USER_ATTRIBUTE.user_id and USER_ATTRIBUTE.name=‘mobile’
ON DUPLICATE KEY UPDATE username=USER_ENTITY.username, givenname=USER_ENTITY.FIRST_NAME, surname=USER_ENTITY.LAST_NAME,email=USER_ENTITY.email,mobile=USER_ATTRIBUTE.value,realm=USER_ENTITY.REALM_I

SQL Resolver (Syntax for where clause)
realm == value

1 Like

Welcome to the privacyIDEA community.
I do not totally understand, what you want to achieve or what is missing in your user table.
But you are right: Views do not have a primary key, We are using SQLSoup to create an ORM object on the fly for SQLalchemy. And in this scenarios Views are not supported or rather the injection of a primary (or foreign key) is at least not programmed in our code. (I do not know, if this would even be possible).

Usually in more complex scenarios users are relying on ldap.