Re: PROXY protocol support

+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 <julien(at)riou(dot)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.
>
>
>