+1 on this one... MySQL and derivatives support it very well.. it is a standard that can be used with either haproxy or better, ProxySQL.
Would be nice to have it in core. It is a show stopper for us to use proxying because of compliance and tracability reasons. Le dim. 19 mai 2019 11:36 AM, Julien Riou <jul...@riou.xyz> a écrit : > Hello, > > Nowadays, PostgreSQL is often used behind proxies. Some are PostgreSQL > protocol aware (Pgpool, PgBouncer), some are pure TCP (HAProxy). From > the database instance point of view, all clients come from the proxy. > > There are two major problems with this topology: > > * It neutralizes the host based authentication. Every client shares > the same source. Either we allow this source or not but we cannot allow > clients on a more fine-grained basis, or not by the IP address. > > * It makes debugging harder. If we have a DDL or a slow query logged, we > cannot use the source to identify who is responsible. > > On one hand, we can move the authentication and logging mechanisms to > PostgreSQL based proxies but they will never be as complete as > PostgreSQL itself. And they don't have features like HTTP health checks > to redirect trafic to nodes (health, role, whatever behind the URL). On > the other hand, those features are not implemented at all because they > don't know the PostgreSQL protocol, they simply forward requests. > > In the HTTP reverse proxies world, there's a "dirty hack" to identify > the source IP address: add an HTTP header "X-Forwared-For" to the > request. It's the destination duty to do whatever they want with this > information. With this feature in mind, someone from HAProxy has > implemented this mechanism at the protocol level. It's called the PROXY > protocol. > > With this piece of logic at the beginning of the protocol, we could > implement a totally transparent proxy and benefit from the great > features of PostgreSQL regarding clients. Note that MariaDB support the > PROXY protocol in MaxScale (proxy) and MariaDB Server in recent > versions. > > My question is, what do you think of this feature? Is it worth to spend > time implementing it in PostgreSQL or not? > > Links: > - http://www.haproxy.org/download/1.8/doc/proxy-protocol.txt > - https://mariadb.com/kb/en/library/proxy-protocol-support/ > > Thanks, > Julien > > PS: I've already sent this message to a wrong mailing list. Stephen > Frost said it's implemented in pgbouncer but all I can find is an open > issue: https://github.com/pgbouncer/pgbouncer/issues/241. > > >
