What I want to be able to do is connect to a postgres server like this:
psql -h postgres-a.example.com -p 9000
That connection should be received by a proxy server (like nginx or haproxy) and it will be redirected to database A because of host name postgres-a.example.com. If I use postgres-b.example.com and the same port, it should go to database B.
I have been researching this, but I am still not 100% sure of how this would work. I read that the only way to redirect a TCP connection (psql) based on host name is using the SNI header. But I still don't understand if we will need a SSL certificate for this, or if we will need to use https://postgres-a.example.com (That doesn't make any sense to me). How it will work?
Can someone help me understand this?
Yes. You will need a certificate for TLS/SSL and you can route the requests based on req.ssl_sni to the proper backend.
I'm not sure if psql uses SNI but i think this have you check.
frontend public_ssl
bind :::9000 v4v6 crt /usr/local/etc/haproxy-certs
option tcplog
tcp-request inspect-delay 5s
tcp-request content accept if { req.ssl_hello_type 1 }
use-server postgres-a if { req.ssl_sni -i postgres-a.example.com }
use-server postgres-b if { req.ssl_sni -i postgres-b.example.com }
backend postgres-a
server postgres-a FURTHER SERVER PARAMS
backend postgres-b
server postgres-b FURTHER SERVER PARAMS
I have created a blog post with a picture for a more detailed description.
https://www.me2digital.com/blog/2019/05/haproxy-sni-routing/