Signing in via multiple federation protocols leads to sql duplicate entry error

[Georgina Shippey]

I am using an OSA All In One instance to look at federation options.
I have set up one idp with two federation protocols, SAML2 (Shib) and OpenId attached to it.
Each protocol has it's own mapping - but in the end they map to the same result, an ephemeral user with an email as the username, with the same permissions.

When I sign in with a user using one protocol for the first time, I am then unable to authenticate using a different protocol due to an SQL Duplicate Entry Error.

Should this be possible?
Is it because I've used two mappings instead of having a combined mapping, or is a user tied to a protocol? - Many questions!

A couple of logs:
keystone.federation.utils: mapped_properties: {'user': {'name': '<email address hidden>', 'type': 'ephemeral'}, 'group_ids': [], 'group_names': [{'domain': {'name': 'Default'}, 'name': 'fedgroup'}], 'projects': []}

keystone.common.sql.core: Conflict federated_user: (pymysql.err.IntegrityError) (1062, "Duplicate entry '628c91b90865ab0b22c088b3bb3120ca2045d784229a7588978f0424deef6085' for key 'PRIMARY'")

[SQL: INSERT INTO user (id, domain_id, enabled, extra, default_project_id, created_at, last_active_at) VALUES (%(id)s, %(domain_id)s, %(enabled)s, %(extra)s, %(default_project_id)s, %(created_at)s, %(last_active_at)s)]

[parameters: {'id': '628c91b90865ab0b22c088b3bb3120ca2045d784229a7588978f0424deef6085', 'domain_id': '3bfb8fcffe304dc991f2312399d8eada', 'enabled': 1, 'extra': '{}', 'default_project_id': None, 'created_at': datetime.datetime(2019, 11, 1, 11, 48, 17, 73570), 'last_active_at': None}]

(Background on this error at: http://sqlalche.me/e/gkpj) wrapper /openstack/venvs/keystone-20.0.0.0rc1/lib/python3.6/site-packages/keystone/common/sql/core.py:524

Conflict occurred attempting to store federated_user - Duplicate entry.: keystone.exception.Conflict: Conflict occurred attempting to store federated_user - Duplicate entry.

[Kristi Nikolla (knikolla)]

I'll try to set up a devstack next week and reproduce this.

In the meanwhile, this line at [0] may be the culprit, as it is essentially trying to create a new user for each federated user. Instead of checking if there is one, and then appending the federated user reference to it.

[0] https://github.com/openstack/keystone/blob/4d42e48ba24ceca0932b5b743680de5e1155c5f8/keystone/identity/shadow_backends/sql.py#L51

[Colleen Murphy (krinkle)]

This looks like a duplicate of https://bugs.launchpad.net/keystone/+bug/1848342 , can you try the proposed fix https://review.opendev.org/687990 and see if it fixes the issue for you?