Export all users from KeyCloak

Question

I have a specific use case in which we want to ask Keycloak for all the users and the groups and roles for each user, on a daily basis. For reconciliation purposes with other internal systems.

Currently we are using the provided Keycloak endpoints in the UsersResource for this. But we see that performance slows down after each call to a point we can't use this solution anymore. There are more then 30K users in the realm.

We've also seen that Keycloak can export the database, but only on system boot (I guess for migration purposes). Given that we want to extract all the users on a daily basis we cannot use this.

Are there some known functionalities or workarounds?

Gandhi

Are you using the KC provided database or some other external DB? – Atalie

external postgresql . So querying directly is possible but our last option ;-) – Gandhi

If it is for internal use I guess you could use some direct query... Still, for the performance problem, have you considered a more powerful machine? Or even upgrading the KC version? Are you using some kind of pagination to list them? – Atalie

Yes we do. 25 user takes 2 seconds and 50 takes 4 seconds. Upgrading the server may help but I guess the API isn't made for this kind of request. Direct queries seems to be the solution for now – Gandhi

@Gandhi did you find a solution to this? When I export realm, users are not exported. – Lactic

Direct queries on your source DB seems to be the solution for now – Gandhi

I am trying to export users from keycloak database. I am not using a separate source db. – Lactic

Answer 1

you need in your docker-compose-yml to bind your folder, not just the realm json file, like this:

keycloak:
    image: jboss/keycloak:8.0.1
    container_name: "keycloak"
     volumes:
      - ./realms/:/tmp/
    environment:
      - KEYCLOAK_USER=admin
      - KEYCLOAK_PASSWORD=admin
      - KEYCLOAK_IMPORT=/tmp/realm-export.json -Dkeycloak.profile.feature.upload_scripts=enabled 

where realms is your folder beside the yaml file. At this point you can run docker-compose up -d with your basic realm-export.json as always, go in your admin panel, adding users with credentials and roles, and then with this command you will able to export the entire configuration:

docker exec -it keycloak /opt/jboss/keycloak/bin/standalone.sh -Djboss.socket.binding.port-offset=100 -Dkeycloak.migration.action=export -Dkeycloak.migration.provider=singleFile -Dkeycloak.migration.realmName=ed-realm -Dkeycloak.migration.usersExportStrategy=REALM_FILE -Dkeycloak.migration.file=/tmp/export.json

You will see in your realms folder that a new file will be created, and it will contain the entire configuration, so you can run docker-compose down, replace your old file with this new and run again docker-compose up as many time you want, and redoing the process when you will change your realm again.

Lice

this is an amazing answer, didnt know you can actually do that. Just to add a bit, when you do docker compose down, if you have a database, be sure to add -v to remove the volumes so you can start fresh – Petepetechia

usually I do docker rm -fsv to remove all the cached information (v is for volumes) – Lice

Answer 2

I have done it with an parallel starting container via docker, which connects to the existing keycloak db.

Please use the same Version of the container keycloak as the real keacloak has. Because of db schema differences between versions.

EXPORT

docker run --rm\
    --name keycloak_exporter\
    -v /tmp:/tmp/keycloak-export:Z\
    -e POSTGRES_DATABASE=keycloak\
    -e POSTGRES_PASSWORD=PASSOWRD_PLEASE\
    -e POSTGRES_USER=keycloak\
    -e DB_VENDOR=POSTGRES\
    -e POSTGRES_PORT_5432_TCP_ADDR=postgresql.local\
    jboss/keycloak:3.4.3.Final\
    -Dkeycloak.migration.action=export\
    -Dkeycloak.migration.provider=dir\
    -Dkeycloak.migration.dir=/tmp/keycloak-export\
    -Dkeycloak.migration.usersExportStrategy=SAME_FILE\
    -Dkeycloak.migration.realmName=therealm

IMPORT

docker run --rm\
    --name keycloak_importer\
    -v /tmp:/tmp/keycloak-import:Z\
    -e POSTGRES_DATABASE=keycloak_dest\
    -e POSTGRES_PASSWORD=PASSOWRD_DEST_PLEASE\
    -e POSTGRES_USER=keycloak\
    -e DB_VENDOR=POSTGRES\
    -e POSTGRES_PORT_5432_TCP_ADDR=postgresql2.local\
    jboss/keycloak:3.4.3.Final\
    -Dkeycloak.migration.action=import\
    -Dkeycloak.migration.provider=dir\
    -Dkeycloak.migration.dir=/tmp/keycloak-import\
    -Dkeycloak.migration.strategy=IGNORE_EXISTING\
    -Dkeycloak.migration.usersExportStrategy=SAME_FILE\
    -Dkeycloak.migration.realmName=therealm

Possible config options: https://github.com/keycloak/keycloak-documentation/blob/master/server_admin/topics/export-import.adoc

Hemimorphic

Answer 3

you can add to your realm.json export file manually

"users": [
  {
    "username": "admin",
    "enabled": true,
    "emailVerified": true,
    "firstName": "Rootus",
    "lastName": "Adminus",
    "email": "[email protected]",
    "credentials": [
      {
        "type": "password",
        "value": "admin"
      }
    ],
    "realmRoles": [
      "ADMIN"
    ]
  },
  {
    "username": "operator",
    "enabled": true,
    "emailVerified": true,
    "firstName": "Operatus",
    "lastName": "Operando",
    "email": "[email protected]",
    "credentials": [
      {
        "type": "password",
        "value": "operator"
      }
    ],
    "realmRoles": [
      "ROLE_OPERATOR"
    ]
  }
]

Lum

Answer 4

We used this query to export user related data from the db:

SELECT U.EMAIL, U.FIRST_NAME, U.LAST_NAME, U.USERNAME, R.NAME, R.DESCRIPTION
FROM (USER_ENTITY U
join USER_ROLE_MAPPING M 
ON U.ID = M.USER_ID) join KEYCLOAK_ROLE R
ON M.ROLE_ID= R.ID;

the output looks like this:

| EMAIL | FIRST_NAME | LAST_NAME | USERNAME | NAME | DESCRIPTION | | [email protected] | Rosa | Persiani | rsper | view-profile | ${role_view-profile} |

the output can be easily converted to cvs or excel

Bibliopole