Hydra does not appears to detect database connection loss

[ArthurKnoep]

This issue follow a discussion on the ORY slack (https://ory-community.slack.com/archives/C012RBW0F18/p1615368062066700):

Hi everyone, I wanted to report a weird behavior with Hydra.

The database that we use for hydra is hosted on a VPS by OVH on the site of Strasbourg, this datacenter has been mainly destroyed by the flames this night (https://twitter.com/abonin_DNA/status/1369538028243456000). However the instances of Hydra are running on a different datacenter from another provider. So in brief we have lost the database but not hydra.
The weird thing is that hydra continue to run "normally", the health endpoint (/health/ready and /health/alive ) are returning a 200 status code, so kuberenetes hasn't taken any action.

And the even weirder thing is that since my access token in our frontend is expired, it tried to refresh it, but the refresh token endpoint return a status code 401 and not 5XX. And finally the auth endpoint never respond is goes on forever.
In contrast, we also use keto in the same configuration, but for keto the health endpoint have returned the correct invalid status code, so kubernetes was able to alert me.

We are running hydra in version 1.9.2 and we use a postgres database

[aeneasr]

Sorry to hear that you were affected by the fire, but glad to hear that you have replication set up!

Can you share your DSN connection parameters?

Also, the current master includes a patch which gives an error if the database is not yet fully migrated, which consists of actually querying some rows. This should prevent your current health check problem.

it tried to refresh it, but the refresh token endpoint return a status code 401 and not 5XX

I think we never return 5xx for that endpoint? Maybe I'm mistaken.

[ArthurKnoep]

The DSN is the following: postgres://user:[email protected]:port/db_name?sslmode=disable&max_conns=20&max_idle_conns=4

Just to reassure, it's only our staging environement that have been impacted, everything is up and running in production

[aeneasr]

Hm it's possible that a connection is living on in a broken state. There is no max_conn_lifetime set which might cause this connection to be in the pool of idle connections, and which returns weird errors due to a pgx bug. So it might be a good idea to have max_conn_lifetime set in your DSN to hard-reset the connection after a while (e.g. 1 minute, 5 minutes, whatever your SLA is) which would prevent this from happening in the future (although the new patch on master should also help prevent this).

[ArthurKnoep]

Ok thanks for the answer, I'll update the configuration.

However why keto does not have this problem since I'm using the exact same parameters (I just don't have the sslmode flag on keto configuration)

[aeneasr]

I don't know 😅