indigo-iam / iam

INDIGO Identity and Access Management Service
https://indigo-iam.github.io/
Other
102 stars 43 forks source link

Tokens page error due to the presence of orphan records on database #581

Open enricovianello opened 1 year ago

enricovianello commented 1 year ago

The current RemoveOrphans logic doesn't fix an error see on iam.cloud.infn.it:

{"error":"Account for antonacci@infn.it not found"}

Screenshot 2023-03-02 alle 15 43 11

There's some other unexpected orphan.

enricovianello commented 1 year ago

Note: There's a complete lack of foreign keys on MitreID database

enricovianello commented 1 year ago

The error comes from this line: https://github.com/indigo-iam/iam/blob/master/iam-login-service/src/main/java/it/infn/mw/iam/api/tokens/converter/TokensConverter.java#L106-L107 The account is searched from saved_user_auth.name value considered as the username. But username is not found. Probably we could identify this orphans with a query like this:

SELECT count(*) FROM saved_user_auth sua, authentication_holder ah, refresh_token rt WHERE rt.auth_holder_id = ah.id and ah.user_auth_id = sua.id and name NOT IN (SELECT username FROM iam_account);

tests in progress!

enricovianello commented 1 year ago

The remaining orphans have been deleted with the following queries.

DELETE FROM refresh_token WHERE auth_holder_id IN (SELECT ah.id FROM saved_user_auth sua, authentication_holder ah  WHERE ah.user_auth_id = sua.id and sua.name NOT IN (SELECT username FROM iam_account));

DELETE FROM refresh_token WHERE auth_holder_id NOT IN (SELECT id FROM authentication_holder);

DELETE FROM authentication_holder_authority WHERE owner_id NOT IN (SELECT id FROM authentication_holder);
DELETE FROM authentication_holder_extension WHERE owner_id NOT IN (SELECT id FROM authentication_holder);
DELETE FROM authentication_holder_request_parameter WHERE owner_id NOT IN (SELECT id FROM authentication_holder);
DELETE FROM authentication_holder_resource_id WHERE owner_id NOT IN (SELECT id FROM authentication_holder);
DELETE FROM authentication_holder_response_type WHERE owner_id NOT IN (SELECT id FROM authentication_holder);
DELETE FROM authentication_holder_scope WHERE owner_id NOT IN (SELECT id FROM authentication_holder);

The problem that generated this discrepancy of username/saved_user_auth.name can be solved not allowing users to change their username. Otherwise if user owns some token there's no logic in the MitreID library that manages the sync of these two values.