mahara-contributors team mailing list archive

[Kristina Hoeppner <1938740@xxxxxxxxxxxxxxxxxx>]

** Changed in: mahara
    Milestone: None => 23.04.0

-- 
You received this bug notification because you are a member of Mahara
Contributors, which is subscribed to Mahara.
Matching subscriptions: mahara-contributors
https://bugs.launchpad.net/bugs/1938740

Title:
  Auth remote user table causing problems with duplicate entries

Status in Mahara:
  Confirmed

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

To manage notifications about this bug go to:
https://bugs.launchpad.net/mahara/+bug/1938740/+subscriptions