IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ix_role_name" when changing password

[galaxyproject-sentryintegration[bot]]

I suspect this user had another account previously for that email address, and hence a private role already exists (but is linked to another user id) ?

Sentry Issue: GALAXY-MAIN-W11

UniqueViolation: duplicate key value violates unique constraint "ix_role_name"
DETAIL:  Key (name)=(...) already exists.

  File "sqlalchemy/engine/base.py", line 1910, in _execute_context
    self.dialect.do_execute(
  File "sqlalchemy/engine/default.py", line 736, in do_execute
    cursor.execute(statement, parameters)

IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "ix_role_name"
DETAIL:  Key (name)=(...) already exists.

[SQL: INSERT INTO role (create_time, update_time, name, description, type, deleted) VALUES (%(create_time)s, %(update_time)s, %(name)s, %(description)s, %(type)s, %(deleted)s) RETURNING role.id]
[parameters: {'create_time': datetime.datetime(2024, 5, 24, 16, 22, 58, 947151), 'update_time': datetime.datetime(2024, 5, 24, 16, 22, 58, 947157), 'name': '..., 'description': 'Private Role for ...', 'type': <types.PRIVATE: 'private'>, 'deleted': False}]
(Background on this error at: https://sqlalche.me/e/14/gkpj)
(22 additional frame(s) were not displayed)
...
  File "galaxy/webapps/base/webapp.py", line 841, in handle_user_login
    self.app.security_agent.create_user_role(user, self.app)
  File "galaxy/model/security.py", line 754, in create_user_role
    self.get_private_user_role(user, auto_create=True)
  File "galaxy/model/security.py", line 782, in get_private_user_role
    return self.create_private_user_role(user)
  File "galaxy/model/security.py", line 764, in create_private_user_role
    user.attempt_create_private_role()
  File "galaxy/model/__init__.py", line 1142, in attempt_create_private_role
    session.commit()

Uncaught exception in exposed API method:

[mvdbeek]

maybe or maybe not relevant: https://github.com/galaxyproject/galaxy/pull/16349

[jdavcs]

This is caused by bad data in the database: purged users with non-redacted private roles and users with private roles where role.name != user.email. This will require a data fix-up migration.

[jdavcs]

No longer relevant (role name unique constraint removed), and fixed in 24.2, including potential bad data in database.