identity_verification_codes table violates foreign key constraint error

[adamstrawson]

Preflight checklist

• I could not find a solution in the existing issues, docs, nor discussions.
• I agree to follow this project's Code of Conduct.
• I have read and am following this repository's Contribution Guidelines.
• This issue affects my Ory Network project.
• I have joined the Ory Community Slack.
• I am signed up to the Ory Security Patch Newsletter.

Describe the bug

Our logging flagged an exception from Kratos for the below:

{
  "code": 500,
  "status": "Internal Server Error",
  "message": "named insert: ERROR: insert or update on table \"identity_verification_codes\" violates foreign key constraint \"identity_verification_codes_identity_verifiable_addresses_id_fk\" (SQLSTATE 23503)"
}

The user was setting their password via the User Recovery flow, they received the email to recover the password, upon entering the password and submitting it, the process failed with the above error.

It's unclear why this key constraint violation happened at the time, all interactions were through Kratos so I'm not sure why the duplication occurred, or what we can do to ensure it doesn't happen again.

Reproducing the bug

Although this isn't something we can reproduce, I'm not confident that we won't see it again in the future.

The steps the user took:

1. The user was created in our UI
2. The user received the email to setup their password via the recovery flow
3. They clicked the link, which took them to the recovery flow
4. Entered password, and submitted
5. Exception thrown

They then re-attempted the flow, which then succeeded.

Relevant log output

{
  "code": 500,
  "status": "Internal Server Error",
  "message": "named insert: ERROR: insert or update on table \"identity_verification_codes\" violates foreign key constraint \"identity_verification_codes_identity_verifiable_addresses_id_fk\" (SQLSTATE 23503)"
}

Relevant configuration

No response

Version

v0.13.0

On which operating system are you observing this issue?

Linux

In which environment are you deploying?

Kubernetes with Helm

Additional Context

No response

[aeneasr]

Thank you for the report! Tagging @jonas-jonas who worked on this. @jonas-jonas I'll add it to the backlog for one of the next iterations

[aeneasr]

Is it possible that this happens when the user requests a verification email, and then performs an update on their profile which updates the verifiable address (read: it deletes the original value) which then in turn messes with the insert?

@adamstrawson Can you provide 100% reproducible steps for this?

[adamstrawson]

Unfortunately it's not something we can reproduce ourselves, but our tooling (Sentry) has caught the exception occur twice on separate occasions over the past fortnight.

We don't currently have any functionality that would allow users the ability to update their profile (or any user facing user management currently), so there wouldn't be any updates on their profile at that time. Currently a user is created via our internal tooling during our (manual) onboarding process, which is when the verification email is sent. Once that user has been created, it can't (currently) be modified.

[aeneasr]

Is any other application except Kratos accessing the Kratos database?

[hperl]

In #3328 I added some stricter error checking when adding a verification flow. I could not find any branches that set the verification flow without also setting the verifiable address. The foreign key is annotated with ON DELETE CASCADE, so there shouldn't be any invalid verification codes in the database.

To continue debugging this beyond the fixes in the PR above, steps to reproduce would be helpful / needed.

[adamstrawson]

We've just had another user report with this error.

Is any other application except Kratos accessing the Kratos database?

Nope, Kratos is the only application that can access this database. We use IAM Policies on CloudSQL, which ensure only the assigned service is permitted to access it's own data.

It's still not something I'm able to directly reproduce myself, but it's definitely something our users are still experiencing.

[adamstrawson]

The team have been able to reproduce it, it looks like a regression of this bug - #1357

If a user is created with an email that contains upper case characters, upon clicking the link in the Account Activation email (recovery flow), they then see the exception.

adam.strawson+one@***.com - Worked
Adam.Strawson+Two@***.com - Failed

[hperl]

Very nice, thanks! I'll look into it!