Duplicate key value violates unique constraint "uniq_external_login", upon attempting Log In with GitLab

SonarQube
, ,
1

SonarQube was authenticating (authn) users just fine through GitLab. Then our GitLab crashed and had to be partially rebuilt (all new users, though the source code itself was fine). Now some Qube users can authn through GitLab while others can’t.

This is from logs/web.log:

2021.02.05 21:32:51 WARN web[AXbfVzZ8NHCHadaOACU8][o.s.s.a.AuthenticationError] Fail to callback authentication with ‘gitlab’ org.apache.ibatis.exceptions.PersistenceException:
Error updating database. Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “uniq_external_login”
Detail: Key (external_identity_provider, external_login)=(gitlab, johndoe) already exists.
The error may exist in org.sonar.db.user.UserMapper
The error may involve org.sonar.db.user.UserMapper.update-Inline
The error occurred while setting parameters
SQL: update users set login = ?, name = ?, email = ?, active = ?, scm_accounts = ?, external_id = ?, external_login = ?, external_identity_provider = ?, user_local = ?, onboarded = ?, salt = ?, crypted_password = ?, hash_method = ?, homepage_type = ?, homepage_parameter = ?, last_connection_date = ?, updated_at = ? where uuid = ?
Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “uniq_external_login”
Detail: Key (external_identity_provider, external_login)=(gitlab, johndoe) already exists.
at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:199)
… lots more …
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
at java.base/java.lang.Thread.run(Thread.java:834)
Caused by: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “uniq_external_login”
Detail: Key (external_identity_provider, external_login)=(gitlab, johndoe) already exists.

Version information: SonarQube CE Version 8.4.2 (build 36762), GitLab CE 12.4.1

How to duplicate the problem:

  1. Setup SonarQube to authenticate users through GitLab as normal (ALM integration).
  2. Prove it works: authenticate SonarQube using the “Log In with GitLab” button.
  3. Blow away GitLab, or make another one, with the same usernames as step 1. (Re)point SonarQube authentication to the new GitLab (as done in step 1). (In our case, the callback URL was the same, but the almintegration keys (Application ID and Secret) had to change since the GitLab instance was new.)
  4. Repeat step 2. It will fail with some users, giving the error message shown above (web.log).

Strangeness: I would think that the “duplicate key value” problem would be the same for all users, as all of them had to be recreated in the new GitLab instance. But that is not the case. Clearly the GitLab authn is setup correctly, or no users could get into Qube through GitLab.

3

Hello @dke ,

Thanks for posting the issue and for the detailed scenario. I was able to reproduce it.

The problem is that when you try to login as a user from the new gitlab instance the gitlab USER ID does not match with the one which was previously created in SQ from the previous gitlab instance. Query matches with different user, hence the error you see as SQ instance tries to update data of the different user which ends with DB constraint error.

I’ve created a ticket to address the issue on our side here, feel free to vote for it: SONAR-14491

and to resolve the issue while waiting for bug fix, I see three solutions:

  1. You can try to update gitlab user ids, so that they match with the previous instance (probably can be hard if you don’t have db backup or even impossible)
  2. You could prepare a simple script to update SonarQube users DB table with new data like:
for each gitlabUser in gitlabInstance:
   #execute SQL script against SQ DB
   update users set external_id = ${gitlabUser.id} where external_identity_provider = 'gitlab' and external_login = ${gitlabUser.login};
  1. Use SonarQube WS api/users/update_identity_provider available since 8.7 with a script like:
for each sqUser in sonarqube:
  if ("gitlab".equals(sqUser.externalIdentity)){
    call api/users/update_identity_provider?login=sqUser.login&newExternalProvider=gitlab&newExternalIdentity=sqUser.externalLogin
  }

To iterate over user list you can use api/users/search.

I recommend 3rd solution, let me know if you have any doubts.