Duplicate Moodle accounts created by "Sync users from Microsoft Entra ID" task – missing unique constraint in auth_oidc_token table?

Duplicate Moodle accounts created by "Sync users from Microsoft Entra ID" task – missing unique constraint in auth_oidc_token table?

by Jerry Lau -
Number of replies: 0

Hello folks.

I have reported this to the plugin developer as well about this anomaly

We are running Moodle 4.5.4 on RHEL Linux with MySQL with:

  • auth_oidc version 4.5.2 (2024100710)
  • local_o365 (latest compatible)

The scheduled task local_o365\task\usersync ("Sync users from Microsoft Entra ID") has previously created multiple Moodle accounts for the same Entra ID user (same numeric samAccountName as username, different emails – one UPN-based, others derived numeric).

I have also confirmed that the auth_oidc_token table currently has no duplicate rows for the same oidcuniqid + resource. Thankfully there are no duplicates yet in the table.

However, the table still lacks a unique constraint on (oidcuniqid, tokenresource), so duplicates could easily re-appear once we turn the sync task back on (due to the well-known race-condition / token-lookup-failure pattern reported many times over the years, e.g. #2102, #1723, #249, #231, etc.).

Questions:

  1. Is this still considered an open issue in the current 4.5.x release line, or has a unique index on (oidcuniqid, tokenresource) been added in a newer version (or in the Moodle 5.x branch)?
  2. What is the recommended/best way to fix or mitigate this problem permanently? - Should the plugin install this unique constraint by default (or during upgrade)?
    • Is there an official supported method, or is the community workaround (clean duplicates + manually add the unique index) the expected approach?

The manual unique index workaround is something we could test in our test platform, but it would be ideal if the plugin handled it out-of-the-box to prevent the issue for everyone. This may cause an issue if we upgrade to Moodle as this may or may not break the process when the manual index addition is added and for every Moodle upgrade.

The index I want to add is:

ALTER TABLE mdl_auth_oidc_token
ADD UNIQUE INDEX idx_oidc_unique (oidcuniqid(191), tokenresource(50));

Would the above work?

Thank you for the excellent plugins – they’re a critical part of our Microsoft integration!

Regards

Moodle version 4.5.4 | auth_oidc 4.5.2 (2024100710)

Average of ratings: -