Re: TCP Resets when closing connection opened via SSL

Thanks for the insight, Tom.

> It's fairly obvious from the postmaster log that the client side
> is not bothering to close the transaction it started

Thats what I was also thinking, but I've managed to reproduce it with autocommit or commit before closing connection as well.
This is only reproducible with SSL connection.

I've reproduced it with ubuntu package bundles certs, my own created, generated with CA etc hence I do not think it is a problem with certificates themselves.
This happens with various clients, php driver, java driver and also libpq used by psycopg2. We are running a lot of different kind of microservices and thus far I can tell, almost (if not all) of them have this problem manifest.

I can not however reproduce this when doing something like
$ psql "user=postgres sslmode=require host=localhost dbname=postgres"
postgres=# \q

> So, please re-think why you are considering them problematic in the first place.

This is an issue because we have a lot of connections being initiated and closed. And this creates a lot of TCP resets.

We could go with something like pgbouncer in front of it to pool the connections in order to reduce the reset count, but this would not be solving the root cause, but merely working around it.

Other than that, this does not seem to "break" anything. This is basically large noise for monitoring and huge numbers of resets, when looking at something like netstat. And by no means, this happens casually.

Has no one expieriencing this ?
Should I just assume this is "normal" and forget about it ? I'd still want to understand what's going on thought.

Med vennlig hilsen.
Best regards, Janis Puris.
On 26 Apr 2019, 16:17 +0200, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, wrote:
> =?utf-8?Q?J=C4=81nis_P=C5=ABris?= <janis(at)puris(dot)lv> writes:
> > I'm trying to do a simple health check for keepalived and other services via a python script and psycopg2 library. All seems to be all right, until I close the connection, at which point a packet with TCP reset is produced.
>
> Hm. It's fairly obvious from the postmaster log that the client side
> is not bothering to close the transaction it started:
>
> > 2019-04-23 16:27:45.306 CEST process=15615 c=BEGIN t=0 s=5cbf20e1.3cff monitoring(at)127(dot)0(dot)0(dot)1:postgres app=[unknown] LOG: duration: 0.095 ms
> > 2019-04-23 16:27:45.306 CEST process=15615 c=SELECT t=0 s=5cbf20e1.3cff monitoring(at)127(dot)0(dot)0(dot)1:postgres app=[unknown] LOG: duration: 0.234 ms
> > 2019-04-23 16:27:45.306 CEST process=15615 c=idle in transaction t=0 s=5cbf20e1.3cff monitoring(at)127(dot)0(dot)0(dot)1:postgres app=[unknown] LOG: disconnection: session time: 0:00:00.006 user=monitoring database=postgres host=127.0.0.1 port=40797
>
> There's no COMMIT or ROLLBACK to go with the BEGIN, and that's reflected
> in the fact that the disconnection message shows c=idle in transaction.
> Now, I doubt that would have any impact on the TCP-level session behavior,
> but it suggests that maybe the client isn't bothering to close the session
> cleanly either. The RST rather than FIN would then likely be caused by
> SSL having to do an unclean shutdown.
>
> regards, tom lane