BUG: Error during OAuth callback / missing constraints for `role_membership` table

[tillprochaska]

Describe the bug Some users experience a 500 error when signing in using OAuth.

The role_membership table stores which groups a user is part of. It has two columns, group_id and role_id (migration). However, it doesn’t define any constraints (composite primary key or a unique constraint, so it is possible to add duplicate rows to the table.

Duplicate rows in this table can cause errors during the OAuth callback. After authenticating a user, Aleph "syncs" the groups the user is part of. It does this by first deleting all rows in the role_membership for the user, then adding new rows for all of the groups the users is currently part of. Groups are encoded in the OAuth token returned by the identity provider, e.g. Keycloak.

In case of duplicate rows, there’s a mismatch between the number of rows SQLAlchemy expects to delete and the number of rows that are actually deleted. This results in the following error:

Traceback (most recent call last):
  File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 2190, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1486, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python3.8/dist-packages/flask_cors/extension.py", line 176, in wrapped_function
    return cors_after_request(app.make_response(f(*args, **kwargs)))
  File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1484, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python3.8/dist-packages/flask/app.py", line 1469, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "/aleph/aleph/views/sessions_api.py", line 123, in oauth_callback
    role = handle_oauth(oauth.provider, oauth_token)
  File "/aleph/aleph/oauth.py", line 100, in handle_oauth
    role.clear_roles()
  File "/aleph/aleph/model/role.py", line 109, in clear_roles
    db.session.flush()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/scoping.py", line 897, in flush
    return self._proxied.flush(objects=objects)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 4179, in flush
    self._flush(objects)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 4315, in _flush
    transaction.rollback(_capture_exception=True)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/session.py", line 4275, in _flush
    flush_context.execute()
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 466, in execute
    rec.execute(self)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/unitofwork.py", line 591, in execute
    self.dependency_processor.process_saves(uow, states)
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/dependency.py", line 1195, in process_saves
    self._run_crud(
  File "/usr/local/lib/python3.8/dist-packages/sqlalchemy/orm/dependency.py", line 1220, in _run_crud
    raise exc.StaleDataError(
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table 'role_membership' expected to delete 1 row(s); Only 2 were matched.

To Reproduce Steps to reproduce the behavior:

1. Set up a local Keycloak instance for development as explained in this guide.
2. Create a user and add the user to a user group as explained in the second section of the group.
3. Open Aleph and sign in using OAuth.
4. You should be able to sign in successfully.
5. When inspecting the contents of the role_membership table, you should see one row representing the group membership set up in step 2.
6. Manually add another duplicate row to the table.
7. Sign out of Aleph and sign in again using OAuth.
8. You should see an internal server error. The API logs should contain the error from above.

Aleph version 3.17.0 and 4.0.0-rc33

Additional context

• It is unclear how exactly you’d end up with duplicate rows in that table. Possibly some weird edge case, but it seems to have happened before.
• A quick workaround in these cases is to remove the duplicate rows.