Syncing LDAP usernames with Mahara's internal username leads to incorrect user record matching

[Ghada El-Zoghbi]

Mahara: 19.04.0
OS: Linux 16.04
DB: Postgres
Browser: FF / Chrome (really n/a)

The LDAP sync_users() functionality is not correctly matching user records - i.e. LDAP username with Mahara username.

Scenario:

1. Create an institution with and LDAP authentication
- Update user info on login = Yes
- We auto-create users = Yes
- Sync users automatically via cron job = Yes
- Update user info in cron = Yes
- Auto-create users in cron = Yes
- If a user is no longer present in LDAP = Suspend user's account

2. Create an XML-RPC authentication with a Moodle for this institution
- Parent authority = LDAP auth created above
- SSO direction = Yes
- Update user info on login = Yes
- We auto-create users = Yes
- We import content = Yes

* The Moodle instance needs to have LDAP set up as well.

3. Create an internal Mahara user (i.e. not LDAP)
- username: AAA (make sure this username exists in the LDAP directory)

* The username 'AAA' needs to be in Moodle created via LDAP.

4. Login to Moodle with the username AAA - the LDAP user

5. SSO to Mahara

This will do the following
- a new user record is created with username AAA1
- auth_remote_user table will link Mahara user AAA1 to Moodle user AAA

6. As Admin, update AAA1's auth method to LDAP.

7. Logout of Mahara and Moodle.

8. Run cron so LDAP syncs users

Expected Results: user AAA1 to remain active in Mahara
Actual Results: user AAA1 is suspended because it matches on username (i.e. AAA)

That is, this join is correct:

from {usr} u
inner join {auth_ldap_extusers_temp} e on u.username = e.extusername

The username in auth_ldap_extusers_temp is: AAA
The LDAP username in Mahara is: AAA1

Hence, it will not match on username and think the user is no longer in the LDAP directory and suspend the user.

The join needs to be on another field that is present in both LDAP and Mahara.

We should also join to the auth_remote_user table to get the correct external username.

i.e.

select
u.id as id,
aru.remoteusername as username,
u.suspendedreason as suspendedreason,
...
from {usr} u
inner join {auth_remote_user} aru on u.id = aru.localusr
inner join {auth_ldap_extusers_temp} e on aru.remoteusername = e.extusername

[Ghada El-Zoghbi (ghada-z)]

Please note that this issue arises when LDAP is the parent of an auth that uses the auth_remote_user table.

[Mahara Bot (dev-mahara)]

Patch for "master" branch: https://reviews.mahara.org/10140

[Mahara Bot (dev-mahara)]

Reviewed: https://reviews.mahara.org/10140
Committed: https://git.mahara.org/mahara/mahara/commit/ea6baf488b986233622af3e069c6ac0115c423b6
Submitter: Robert Lyon (<email address hidden>)
Branch: master

commit ea6baf488b986233622af3e069c6ac0115c423b6
Author: Ghada El-Zoghbi <email address hidden>
Date: Fri Jun 28 09:02:16 2019 +1000

Bug 1833495: LDAP user sync check for remote username

Sponsored by The Australian National University

behatnotneeded

Change-Id: Ia6b95a6f0298e9e2dc7864de2b03e03844cfc590