FK constraint hydra_oauth2_access_challenge_id_fk violation

[woylie]

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

I just tried to upgrade Hydra from 1.11.10 to 2.0.1 (tried upgrading to 2.0.0 first as well). However, I'm getting a FK constraint error when running the migrations.

ERROR: insert or update on table "hydra_oauth2_access" violates foreign key constraint "hydra_oauth2_access_challenge_id_fk" (SQLSTATE 23503)

How should I proceed?

Reproducing the bug

1. run Hydra 1.10 in Docker
2. update the image to 2.0.1 or 2.0.0
3. run hydra migrate sql -e --yes --config /etc/config/hydra/hydra.yml

Relevant log output

app-hydra-1  | Could not apply migrations:
app-hydra-1  | ERROR: insert or update on table "hydra_oauth2_access" violates foreign key constraint "hydra_oauth2_access_challenge_id_fk" (SQLSTATE 23503)
app-hydra-1  | error executing migrations/20211019000001000002_merge_authentication_request_tables.postgres.up.sql, sql: -- Migration generated by the command below; DO NOT EDIT.
app-hydra-1  | -- hydra:generate hydra migrate gen
app-hydra-1  |
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_client_id_subject_idx ON public.hydra_oauth2_flow USING btree (client_id, subject);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_cid_idx ON public.hydra_oauth2_flow USING btree (client_id);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_login_session_id_idx ON public.hydra_oauth2_flow USING btree (login_session_id);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_sub_idx ON public.hydra_oauth2_flow USING btree (subject);
app-hydra-1  | CREATE UNIQUE INDEX hydra_oauth2_flow_consent_challenge_idx ON public.hydra_oauth2_flow USING btree (consent_challenge_id);
app-hydra-1  | CREATE UNIQUE INDEX hydra_oauth2_flow_login_verifier_idx ON public.hydra_oauth2_flow USING btree (login_verifier);
app-hydra-1  | CREATE INDEX hydra_oauth2_flow_consent_verifier_idx ON public.hydra_oauth2_flow USING btree (consent_verifier);
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_pkey PRIMARY KEY (login_challenge);
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_client_id_fk FOREIGN KEY (client_id) REFERENCES public.hydra_client(id) ON DELETE CASCADE;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_flow ADD CONSTRAINT hydra_oauth2_flow_login_session_id_fk FOREIGN KEY (login_session_id) REFERENCES public.hydra_oauth2_authentication_session(id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_access DROP CONSTRAINT hydra_oauth2_access_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_access ADD CONSTRAINT hydra_oauth2_access_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_code DROP CONSTRAINT hydra_oauth2_code_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_code ADD CONSTRAINT hydra_oauth2_code_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_oidc DROP CONSTRAINT hydra_oauth2_oidc_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_oidc ADD CONSTRAINT hydra_oauth2_oidc_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_pkce DROP CONSTRAINT hydra_oauth2_pkce_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_pkce ADD CONSTRAINT hydra_oauth2_pkce_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_refresh DROP CONSTRAINT hydra_oauth2_refresh_challenge_id_fk;
app-hydra-1  | ALTER TABLE ONLY public.hydra_oauth2_refresh ADD CONSTRAINT hydra_oauth2_refresh_challenge_id_fk FOREIGN KEY (challenge_id) REFERENCES public.hydra_oauth2_flow(consent_challenge_id) ON DELETE CASCADE;
app-hydra-1  |
app-hydra-1  |
app-hydra-1  | github.com/ory/x/popx.NewMigrationBox.func1.1
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migration_box.go:158
app-hydra-1  | github.com/ory/x/popx.Migration.Run
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migration_info.go:34
app-hydra-1  | github.com/ory/x/popx.(*Migrator).UpTo.func1.2
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:146
app-hydra-1  | github.com/ory/x/popx.(*Migrator).isolatedTransaction
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:320
app-hydra-1  | github.com/ory/x/popx.(*Migrator).UpTo.func1
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:145
app-hydra-1  | github.com/ory/x/popx.(*Migrator).exec
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:564
app-hydra-1  | github.com/ory/x/popx.(*Migrator).UpTo
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:99
app-hydra-1  | github.com/ory/x/popx.(*Migrator).Up
app-hydra-1  |  /go/pkg/mod/github.com/ory/x@v0.0.486/popx/migrator.go:85
app-hydra-1  | github.com/ory/hydra/persistence/sql.(*Persister).MigrateUp
app-hydra-1  |  /project/persistence/sql/persister_migration.go:48
app-hydra-1  | github.com/ory/hydra/cmd/cli.(*MigrateHandler).MigrateSQL
app-hydra-1  |  /project/cmd/cli/handler_migrate.go:341
app-hydra-1  | github.com/spf13/cobra.(*Command).execute
app-hydra-1  |  /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:872
app-hydra-1  | github.com/spf13/cobra.(*Command).ExecuteC
app-hydra-1  |  /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:990
app-hydra-1  | github.com/spf13/cobra.(*Command).Execute
app-hydra-1  |  /go/pkg/mod/github.com/spf13/cobra@v1.5.0/command.go:918
app-hydra-1  | github.com/ory/hydra/cmd.Execute
app-hydra-1  |  /project/cmd/root.go:118
app-hydra-1  | main.main
app-hydra-1  |  /project/main.go:31
app-hydra-1  | runtime.main
app-hydra-1  |  /usr/local/go/src/runtime/proc.go:250
app-hydra-1  | runtime.goexit
app-hydra-1  |  /usr/local/go/src/runtime/asm_arm64.s:1172
app-hydra-1  | this error should never be printed

Relevant configuration

No response

Version

2.0.1

On which operating system are you observing this issue?

macOS

In which environment are you deploying?

Docker Compose

Additional Context

No response

[aeneasr]

Thank you for the report! It seems that this is affecting a couple of environments. Is there an easy way to reproduce the problem?

Another question is whether you could identify the row with the FK that is missing.

I would also like to be interested if you ran the hydra janitor or any other clean up routine.

Thank you!

[aeneasr]

ory/k8s#537

[woylie]

I did not run hydra janitor or other clean up routines. This occurs in my local dev environment without any sensitive data. The Hydra database dump only has 852 KB. I could send it to you on a private channel including the relevant parts of the docker compose config and hydra config. Just let me know where to send it.

[woylie]

Well, I think I didn't run any clean up routines. I might have manually deleted consent requests from the DB during development at some point.

[aeneasr]

Well, I think I didn't run any clean up routines. I might have manually deleted consent requests from the DB during development at some point.

That could be the problem!

[woylie]

Alright, I'll finish up updating our application first and try to run the migration on actual data I didn't tamper with then. I'll let you know whether I'll run into trouble.

[woylie]

@aeneasr We upgraded our staging environment to Hydra 2.0, ran the migrations, and we're seeing the same error. The data in the staging database has not been tampered with.

[aeneasr]

Thank you for the update! Do you have the rows affected by this available? It would be helpful to understand why they are pointing to something that does not exist!

[woylie]

Thank you for the update! Do you have the rows affected by this available? It would be helpful to understand why they are pointing to something that does not exist!

Is there an easy way to figure that out?

[aeneasr]

You will need to find all rows of hydra_oauth2_access that have a field of consent_challenge_id for which there is no row in hydra_oauth2_flow (key consent_challenge_id)

[woylie]

I sent you the query result via Slack.

[aeneasr]

I've tried to reproduce this doing the following:

1. Checkout v1.11.10
2. Run e2e tests for this version against postgres
3. Do some manual flows too with different remember me configurations
4. Create a database copy of the v1.11.10 db
5. Run migration of 2.0.1 against that snapshot

For me this passed without problems. So the question is, why is the oauth2_flow missing in your database and how can we reproduce that failure. You said you had this problem on docker-compose as well, if you have a clear reproducible path that would be tremendously helpful to fix the issue. Thanks!

[woylie]

I was able to run the migrations after running these queries.

update hydra_oauth2_access as t1 set challenge_id = null where challenge_id is not null and not exists (select null from hydra_oauth2_flow t2 where t1.challenge_id = t2.consent_challenge_id);

update hydra_oauth2_code as t1 set challenge_id = null where challenge_id is not null and not exists (select null from hydra_oauth2_flow t2 where t1.challenge_id = t2.consent_challenge_id);

update hydra_oauth2_oidc as t1 set challenge_id = null where challenge_id is not null and not exists (select null from hydra_oauth2_flow t2 where t1.challenge_id = t2.consent_challenge_id);

Unfortunately, I wouldn't know how to reproduce the issue either. I can only provide you with my local dev DB dump, which as I said I may have tampered with.

[aeneasr]

I think this can happen when a login session was deleted, which in turn deletes the login request/challenge. Then, in the migration, we merge login and consent tables, and potentially rows without a login reference are ignored, causing this problem.

Thank you for the query fix, we will retrospectively add this to the migrations. I'm wondering though if this has an effect on related access / refresh tokens and flows.

[aeneasr]

I checked the code, it does not seem that fosite requires the challenge_id to function. So it should not have a major impact.

[AndersSoee]

We got the same issue in local dev environment when upgrading from 1.11.5 to 2.0.2
No janitor or any other custom cleanup has been done.

[AndersSoee]

And the following update was needed in addition to the 3 mentioned above:
update hydra_oauth2_refresh as t1 set challenge_id = null where challenge_id is not null and not exists(select null from hydra_oauth2_flow t2 where t1.challenge_id = t2.consent_challenge_id);