Auth remote user table causing problems with duplicate entries

Bug #1938740 reported by Robert Lyon
This bug affects 1 person
Affects Status Importance Assigned to Milestone

Bug Description

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

Robert Lyon (robertl-9)
Changed in mahara:
status: New → Confirmed
importance: Undecided → High
tags: added: remoteusername
Changed in mahara:
milestone: none → 23.04.0
To post a comment you must log in.
This report contains Public information  
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.