User login after changing OAUTH providers results in users_providerkey_email_unique db error

[matutter]

I have an instance where users currently login via OAUTH from KeyCloak. We're switching to a different provider and while testing the switch we've encountered an issue preventing user's from logging in.

When user's perform an SSO login, they're redirected to the provider, login, but when they're redirected back to WikiJS they are shown this error in the browser.

The error text is:

insert into "users" ("createdAt", "email", "isActive", "isSystem", "isVerified", "localeCode", "name", "pictureUrl", "providerId", "providerKey", "tfaIsActive", "updatedAt") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning "id" - duplicate key value violates unique constraint "users_providerkey_email_unique"

It seams the WikiJs is trying to create a new user but fails to insert because of the unique constraint on the email column.

The expected behavior is that the users, with matching user-info (e.g. email, username, etc...), will log into the same account.

The different OAUTH provider's have identical user-info. The email, username, etc, is all the same. I've restarted the server several times. I've used different browsers / incognito mode to ensure there's no session leftover cookies.

I've also made an attempt to modify the providerId column in the users table to set user's authentication provider to the new provider's UUID. This has no effect.

I wasn't able to find any documentation explaining how to move user's between different auth providers so this appeared like a bug.

My WikiJS version is wiki:2.5.276.

[eprouty]

I am getting this same error without changing the OIDC provider. Using Authelia, user logs in the first time and everything works... user tries to login the second time:

insert into "users" ("createdAt", "email", "isActive", "isSystem", "isVerified", "localeCode", "name", "pictureUrl", "providerId", "providerKey", "tfaIsActive", "updatedAt") values ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12) returning "id" - duplicate key value violates unique constraint "users_providerkey_email_unique"

Same error message you get.

[porterfc]

I am seeing the same thing. Login once using SAML authentication works great, second time get the duplicate key value error.

[matutter]

That sounds like WikiJS is using a different claim for each login. If your SSO provider settings hasn't changed you need to use a different field/property for the claim. WikiJS uses an email claim, so you probably have the wrong field set in the auth provider's settings.

[matutter]

How does a bug/error discussion get attention?

[matutter]

How I fixed it in my test environment:

I logged into the postgres database and looked at a few fields in the users table.

wikijs=# select id,email,name,"providerId","providerKey" from users;
 id |       email       |     name      |              providerId              |             providerKey              
----+-------------------+---------------+--------------------------------------+--------------------------------------
  2 | guest@example.com | Guest         |                                      | local
  3 | tester@test.lab   | tester        | acc0e963-d34c-458e-bab7-f72dffa48dcd | 7c29abd9-1fc7-50a8-ba95-b42a6d4b0c17
  1 | wikiadm@test.lab  | Administrator |                                      | local
  6 | tester3@test.lab  | tester3       | tester3                              | 7c29abd9-1fc7-50a8-ba95-b42a6d4b0c17

To change my OpenIDConnect provider I used the API (MITMProxy was used to determine how to use the API).

The GQL query looks like this:

        mutation ($strategies: [AuthenticationStrategyInput]!) {
          authentication {
            updateStrategies(strategies: $strategies) {
              responseResult {
                succeeded
                errorCode
                slug
                message
              }
            }
          }
        }

And the AuthenticationStrategyInput I used looks like this:

operationName: null
extensions: {}
variables:
  strategies:
    # Enable basic username/password login
    - key: local
      strategyKey: local
      displayName: Local
      order: 0
      isEnabled: true
      config: []
      selfRegistration: no
      domainWhitelist: []
      autoEnrollGroups: []
    - key: '{{ wikijs_oidc_key }}' # I use a UUID here and when I switched from Keycloak to Authentik I used the name UUID which replaces the odl config entirely.
      strategyKey: oidc
      displayName: Labs SSO
      order: 1
      isEnabled: true
      selfRegistration: true
      domainWhitelist: ['{{ pod_domain }}'] # This is just my test environment "test.lab"
      autoEnrollGroups: '{{ wikijs_oidc_default_group.ids }}' # whatever default group
      config:
      # I guess we're inserting JSON into a table or something here???
      - key: clientId
        value: '{{  { "v": ak_id } | to_json  }}'
      - key: clientSecret
        value: '{{  { "v": ak_secret } | to_json  }}'
      - key: authorizationURL
        value: '{{  { "v": ak_auth } | to_json  }}'
      - key: tokenURL
        value: '{{  { "v": ak_token } | to_json  }}'
      - key: userInfoURL
        value: '{{  { "v": ak_user_info } | to_json  }}'
      - key: issuer
        value: '{{  { "v": ak_issuer } | to_json  }}'
      - key: emailClaim
        value: '{{  { "v": "email" } | to_json  }}'
      - key: logoutURL
        value: '{{  { "v": ak_logout } | to_json  }}'

Okay so that covers changing the OIDC provider - now I just needed to fix the database. For simplicity I'm using the user's name as the user claim. Here is the query:

UPDATE users SET "providerId" = name WHERE "providerKey" = '7c29abd9-1fc7-50a8-ba95-b42a6d4b0c17';

And that's it.

[albert-a]

Unlike in @matutter answer, in my case providerId field contained not just name, but other fields: "f:{UUID}:name", so I changed SQL statement accordingly, see below.

The error started to occur when I reinstalled Keycloak. The reason: provider UUID which is included in providerId field changed, so I had to update providerId with new UUID for all users:

UPDATE users SET "providerId" = CONCAT('f:ad40f1fc-535b-4b7c-9c59-9cfd2d370717:',name)
WHERE "providerKey" = '1a0207af-c3d5-4484-a1cb-6c6aa289abdc';

here:

• ad40f1fc-535b-4b7c-9c59-9cfd2d370717 - NEW uuid,
• 1a0207af-c3d5-4484-a1cb-6c6aa289abdc - providerKey for Keycloak in Wikijs

Now it works.

[slominskir]

The providerId WikiJS DB field maps to simply User "ID" in the Keycloak Admin web interface. I had to do a lookup for EACH user and manually replace in the WikiJS DB on Keycloak upgrade. Should WikiJS instead use username or otherwise not store providerId. Not sure the purpose, other than make upgrading Keycloak painful.