Keycloack user has a one-to-many relationship

Hello, i’m new to keycloak and i need some guidance please. I’m trying to store the users in an external DB like MySQL, but in my UML diagrams, the user has a one-to-many relationship with another entity called “Product” how can i implement this ?

Do you mean using the existing Keycloak users table or creating a new users table in a different DB referencing the UUID of the user?

1 Like

That’s what i’m confused at. So what i’ve read from the docs is that i can use an external DB and the db gets filled with a lot of tables by keycloak. and in my case i have a “User” with a one-to-many relationship with a “Product” entity.

proposition 1 : using the existing Keycloak DB

So in here, is it okay to add tables in Keycloack DB (Which of course is in MySQL) that have relationship with a User ? If yes, then there’s another concern.

Let’s say the admin deteled the user from the keycloak GUI, so is there a way to delegate this deletion to his products as well ? The second concern, does a user has access to the keycloak DB in MySQL ?

proposition 2 : new users table in a different DB referencing the UUID of the user
for this proposition, in case of a deletion is there a way to delegate it to the user’s products ??

Sorry for this long reply, i’m just trying to see if using Keycloak does suit my use cases, instead of working with it and getting stucked in the middle.

Thank you.

I would suggest going for option 2.

I personally would not touch the DB scheme of Keycloak itself or extend it if you can avoid it.

You can hook into Keycloak with a custom SPI to listen to deletions of users and act upon them.
But your app probably already has an admin section or will write one so you could just delete a user from there and propagate it to keycloak, this way you don’t need the SPI

My app has it’s own DB and I use option 2, as the user UUID is unique it’s perfect to use as an identifier.

I don’t know your use case but mine is as follows.
When a user confirms registration he is redirected to my app, I read the token and check if the user already exists in my backend, if it does not I create the user.
I have my own admin app, not publicly accessible and manage some things from there, stats, user block/deletion, events, … so it’s my app who uses the Keycloak admin REST api.

1 Like

Yes i have the same case. My app has two roles: User and Admin. So if i created a user with role ‘Admin’, can i specify if he can delete a user or not ? or is it only the super admin (that i created when using Keycloack the first time) who is eligible for such operation !!

Another thing, How can i create a custom user table in another table with Id is UUID !?
Thank you.

For allowing a user in Keycloak to manage users you need to look into the realm roles, more specifically the ‘manage-users’ role. There’s a difference between roles you define yourself or Keycloak realm roles.
https://www.keycloak.org/docs/latest/server_admin/

What do you mean with create a custom user table in another table?
You mean create a user table in your own DB schema probably?
UUID is a datatype in most DB’s.
You could use it as a primary key or you could choose to add it as another field and make it unique.

I don’t use the user UUID as my primary key, I’ve just added a new column with datatype UUID and made it a unique field. I personally don’t like a UUID as my primary key as it takes up more disk space and some other reasons. Just my preference.

1 Like

Hey, sorry for late repy. I’ve given a user the ‘manage-users’ role.

What i did is i’ve accessed keycloak as super admin and followed this path : Users/userID/role-mappings and from there, there’s ‘Client Roles’ select List; I’ve clicked on it and i’ve chosed realm-managemtn, which then gave me the roles, so i’ve given my regular user those roles.

But now from my application, i can’t access the console that will allow me to manage the users. Below, you can see that i can go to the user account console that will allow him to edit his account but not other users.

Regarding UUID, as you said the UUID gonna be a new column with unique field, but i have some concerns:
1 - As a new user, he will be registering first right ? so is is possible that after successful registration, i can grab his ID and then add him in my DB ?

2- If an admin deleted a user, is there a way to notify my app so that i can delegate the deletion to the entities that he’s related to ?

Hi,

concerning the realm roles you need to look at Realm Specific Roles, you can add multiple roles like view-realm, view-users, …

My flow is as follows.

A user registers -> confirmation email is send -> user confirms account and is redirect to my app -> when a user visits my app I do an API call to my backend and retrieve the user profile -> if no user profile exists I know I need to create the user first based on the incoming token.

You could write a custom even listener SPI that on deletion notifies your service of that event so you can act upon it. Take a look at my repo for a custom event listener ->https://github.com/zonaut/keycloak-extensions

I personally don’t allow anybody access to Keycloak. I’ve written a small admin section in my app that is accessible by users witch a certain role and from there they have a limited way to manage users.

Hey @MoroccanGeek

This is perfectly valid. and can be used, you just have to be careful when deleting a user (e.g add a ON DELETE CASCADE for your related entities ) in order not have some inconsistent state or orphan entities. The name of the entity (table) is user_entity: https://github.com/keycloak/keycloak/blob/master/model/jpa/src/main/java/org/keycloak/models/jpa/entities/UserEntity.java

and this should work with no issue. You can reference the keycloak tables from your tables, but you should not modify them, because keycloak uses liquibase for schema management and you might not be able get any db updates if you update your keycloak version in the future.