Date: Sun, 19 May 2019 11:59:04 -0400
From: Stephen Frost <sfrost@snowman.net>
To: Julien Riou <julien@riou.xyz>
Cc: pgsql-hackers@lists.postgresql.org
Subject: Re: PROXY protocol support
Message-ID: <20190519155903.GI6197@tamriel.snowman.net>
References: <20190519153623.GB13208@thinkpad>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha512;
	protocol="application/pgp-signature"; boundary="x6Lzvg/NA9KtLcGN"
Content-Disposition: inline
In-Reply-To: <20190519153623.GB13208@thinkpad>
User-Agent: Mutt/1.5.24 (2015-08-30)
Precedence: bulk


--x6Lzvg/NA9KtLcGN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

Greetings,

* Julien Riou (julien@riou.xyz) wrote:
> 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.
>=20
> There are two major problems with this topology:
>=20
> * 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.

You can instead have the IP-based checking done at the pooler.

> * It makes debugging harder. If we have a DDL or a slow query logged, we
> cannot use the source to identify who is responsible.

Protocol-level poolers are able to do this, and pgbouncer does (see
application_name_add_host).

> 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.
>=20
> 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.

Someone from HAProxy could certainly implement something similar by
having HAProxy understand PostgreSQL's 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.

pgbouncer is already a transparent proxy that understands the PG
protocol, and, even better, it has support for transaction-level pooling
(as well as connection-level), which is really critical for larger PG
deployments as PG backend startup is (relatively) expensive.

> 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.

That would be some *other* proxy system (Amazon's ELB) that apparently
also doesn't understand the PG protocol and therefore doesn't have a
feature similar to pgbouncer's application_name_add_host.

I haven't looked very closely at if it'd be possible to interpret the
PROXY protocol thing that Amazon's ELB can do without confusing it with
a regular PG authentication startup and I'm not sure if we'd really want
to wed ourselves to something like that.  Certainly, what pgbouncer does
works quite well and is about as transparent to clients as possible.

You'd almost certainly want something like pgbouncer after the ELB
anyway to avoid having tons of connections to PG and avoid spinning up
new backends constantly.

Thanks,

Stephen

--x6Lzvg/NA9KtLcGN
Content-Type: application/pgp-signature; name="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1

iQIcBAEBCgAGBQJc4X1HAAoJEO1sijiDR2RVdCMP/1SB1jxXWaxVr23I1qjoX5c4
bbqfWjdcySYt9juZCQV7MB8PZf1sbZ1zVxRe9uUF6Hd/zLwk+PonDA1fJ3rytuzW
dGjve5JC8OmbSeBPK0diu94QY59aZjpmWDAiJbIUnxeaER0UfV3Z0/riu77GGkco
O5SQywgFUzmcvIdtsU9AooXFbteTNZsmqrhbYTTS3d2JBUow8/NaneLYQI+s6NCH
UVDku5sLEA4nydCebWPz+hKU4c60OFFqAeXJLGfE+60pMsFdT848VzmiHOPFoKC1
9u5+8v9hAGeevNxLen6LUgIwGcA6nb+1TDPHG00mBZ3Rz7+T3apczgWr4sM4pzFK
L26UHG9efjbtoLJ9O3yV65tlLe28l8wJMQgdaamqKYb/xgMv4rnDpHlyA/1lD16g
S9IBCXgemkWJ/JZfFGfh+eZ5tHAT8sWk4Lf/+dIGcjISLx/dtdR0ISA3S3BPfSic
EDvQUxbAkaMZMFhWnn4+1+9+wPqVObp6RPvFCe59L4bGPUpR3dUGqpjUhYKYsWGB
aLtf+ELqnbM2qjwJpDOcv4cue4aXtaj0C+UjY7yo0u2LcQztBq3gfretw7/4/pY4
VSSQXr8Qa/M8iVNuOD+dqxgo+9QFSKonGLIRfZWfZcbGBi6VdpIkhMMcWtnQrmHZ
Nwo51GS5FIJZ3CIR4Xwh
=YbbB
-----END PGP SIGNATURE-----

--x6Lzvg/NA9KtLcGN--