Auth remote user table causing problems with duplicate entries

[Robert Lyon]

This is the problem:

The function "find_by_instanceid_username()" can cause the SQL error "ERROR: more than one row returned by a subquery used as an expression"

This happens because we check remote username against username by first converting it to lowercase.
We do this because username is always saved / matched as lowercase. But we don't do the same for remoteusername.

So if we have the scenario where an external system logs in, say SAML, as 'test01' it creates a new user with username = 'test01' and remoteusername = 'test01' (at this point all is fine)

Then if another external system logs in, say LTI, with 'Test01' it won't match on the auth_remote_user table but will subsequently match on the user table (as it will try the lowercase version of the value) but it will save a new record in the auth_remote_user as 'Test01'.

So then, on the next remote login, it will now find 2 records where the remoteusername = 'test01' and another row in the auth_remote_user table where remoteusername = 'Test01' then things break.
We get ERROR: more than one row returned by a subquery used as an expression

We need to:

1) adjust the places where we save data to "auth_remote_user" table, eg create_user() / LTI login etc so that we only save it as lowercase

2) Make sure all the places we query on it is done by matching lowercase values

3) Adjust the primary key on the auth_remote_user table so that we check the remoteusername as lowercase

eg change to "authremouser_autrem_pk" PRIMARY KEY, btree (authinstance, lower(remoteusername::text))

So that if (1, 'test01') exists then (1, 'Test01') is trying to be added it should be rejected at db level as being a duplicate

[Mahara Bot (dev-mahara)]

Patch for "main" branch: https://reviews.mahara.org/c/mahara/+/13880