Hydra: Consider supporting separate DSNs for reading and writing

[ngrigoriev]

Is your feature request related to a problem? Please describe.

Hydra depends on the database and scales with the database as well. Some supported databases (MySQL, Postgres...) support geo-replication and may offer read-only replicas closer to the deployment location.

For example:

• MySQL active master in East US region (in Azure)
• MySQL read-only replica in West US region
• Hydra deployed in both

By default, Hydra would have to be pointed to the active master. This will incur some additional latency when doing the database operations.

Describe the solution you'd like

I think it would be cool to introduce two DSNs instead of one: one for reading and one for writing.

I think it would be particularly important for the performance of the token introspection queries - think Oathkeeper's oauth2_introspect authenticator's latency and Hydra accessing _local_ read-only DB replica instead of remote server. In fact, this is probably the main driving use case for this. Token acquisition is often not as latency-sensetive operation, for example.

Describe alternatives you've considered

I believe the database like CockroachDB could potentially offer a solution but it is a different operational problem. Given how easy is to deploy the configuration I explained above with the leading cloud providers, I think CockroachDB option is not for everyone.

[vancity-amir]

You can split read/write queries by putting a SQL proxy in front of your DB.

[ngrigoriev]

@vancity-amir Yes, I imagine, something like Vitess would be able to do that - while being another point of failure if I use PaaS DB offering. But this replication makes the database somewhat eventually-consistent (read replica will be lagging a bit). I imagine that the application is usually in best position to decide which of two database connection (pools) to use for which operation.

[aeneasr]

Please use something like HAProxy for this - Hydra is not responsible for managing your SQL load balancing or routing :)

[aeneasr]

It's also not possible to do read-only or write-only because some transactions require SELECTs in order to function. So it would be very messy anyways and not guaranteed to actually work well.

[ngrigoriev]

I am primarily interested in this from the point of view of token introspection by the API gateway (via Oathkeeper), opaque token, of course - not JWT. For this to be fast, the database needs to be close. And it is not exactly read-only and write-only, it is about "read-only" and "read-write".

[aeneasr]

I see, that is a valid point! So it's actually mostly about this endpoint, right?

[aeneasr]

In the case that that's true, I think one option would be to simply deploy two hydra instances, one with the DNS pointing to the write master and one with the DNS pointing to the read replica. Then you set up Nginx or whatever router/ingress you use in such a way that /oauth2/introspection ends up at the reader, not the writer. I know this is a bit of extra work on the deployment end, but if you're doing multi-region it's probably the least of your worries and better than handling two DSNs and separating between read/write connections in hydra :)

[ngrigoriev]

@aeneasr Actually...did not think about this option! This would be rather easy to deploy, I think.

So, basically, all Admin API, /flush etc will be routed to the "read-write" deployment/service (in K8S terms) and only something like /oauth2/introspect, /userinfo and probably some other frequent calls will be routed to "read-only" deployment/service. This is perfect!

[ngrigoriev]

I think this is awesome option what you described above and this option definitely reduces the value of the feature I proposed - since the same can be achieved just by deploying Hydra in a particular way. Maybe we could consider adding a global configuration parameter making a Hydra instance "read-only" so instead of attempting and failing the DB "write" operation it won't even do it. Or it would jusk mask the API methods making changes to the data model so instead of an error they would fail early and return something like HTTP 405.

[aeneasr]

@aeneasr Actually...did not think about this option! This would be rather easy to deploy, I think.

So, basically, all Admin API, /flush etc will be routed to the "read-write" deployment/service (in K8S terms) and only something like /oauth2/introspect, /userinfo and probably some other frequent calls will be routed to "read-only" deployment/service. This is perfect!

I'm happy that I was able to help here!

I think this is awesome option what you described above and this option definitely reduces the value of the feature I proposed - since the same can be achieved just by deploying Hydra in a particular way. Maybe we could consider adding a global configuration parameter making a Hydra instance "read-only" so instead of attempting and failing the DB "write" operation it won't even do it. Or it would jusk mask the API methods making changes to the data model so instead of an error they would fail early and return something like HTTP 405.

Wouldn't this happen automatically when accessing a read-only slave? :)

[aeneasr]

I'm closing this because I think it is resolved.