[enhancement]: read-only connections for SQL storage backends (postgres, mysql and mariadb)

Which feature or improvement would you like to request?

When deploying Postgres, Mariadb or Mysql clusters they are often deployed with a single leader instance that supports read & write queries and one or more follow instances that can be used for read-only queries.

Example from typical Zalando postgres deployment (credits: https://github.com/zalando/postgres-operator):

It would be great if we can configure a second connection for read-only queries, something like this for postgres (full reuse of the normal configuration):

[store."postgresql".readonly]
type = "postgresql"
host = "localhost"
port = 5432
database = "stalwart"
user = "postgres"
password = "mysecretpassword"
timeout = "15s"

[store."postgresql".readonly.tls]
enable = false
allow-invalid-certs = false

[store."postgresql".readonly.pool]
max-connections = 10

I can than imagine that the code is structured in such a way that the readonly connection object simply uses the main connection object if NO "readonly" configuration is provided. Else, it creates a second connection object for read-only queries. All read-only queries should always use this secondary read-only connection object. That way if this is not configured will use the same connection object else will use secondary read-only pool.

Is your feature request related to a problem?

No response

Code of Conduct

[X] I agree to follow this project's Code of Conduct

stalwartlabs / mail-server