Re: TCP Resets when closing connection opened via SSL

Resending the original message without any formatting.

Hello,

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.

This has become very problematic and creates extensive noise in monitoring.

It also happens with IntelliJ DataGrip provided Java driver and PHP drivers. And hence I do not think this is the drivers problem, but PG instead.

Script

import psycopg2
import sys

def check():
   # Relying on .pgpass for password
   con = psycopg2.connect('user=monitoring dbname=postgres host=127.0.0.1')
   cur = con.cursor()
   cur.execute("select 'keepalived healthcheck'")
   cur.close()
   con.close()

try:
   check()
   print("ok")
except Exception as e:
   print("not ok")
   print(str(e))
   sys.exit(1)

tcpdump tcpdump -v 'tcp[tcpflags] & (tcp-rst) != 0' -ilo

16:27:45.307006 IP (tos 0x0, ttl 64, id 8123, offset 0, flags [DF], proto TCP (6), length 40)
   localhost.40797 > localhost.postgres: Flags [R], cksum 0x0cca (correct), seq 3830516781, win 0, length 0

From PG side of things, all seems to be OK

2019-04-23 16:27:45.300 CEST process=15615 c= t=0 s=5cbf20e1.3cff [unknown](at)127(dot)0(dot)0(dot)1:[unknown] app=[unknown] LOG:  connection received: host=127.0.0.1 port=40797
2019-04-23 16:27:45.304 CEST process=15615 c=authentication t=0 s=5cbf20e1.3cff monitoring(at)127(dot)0(dot)0(dot)1:postgres app=[unknown] LOG:  connection authorized: user=monitoring database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
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

I've tried various combinations of OS, psycopg2 and PG versions to no avail. My focus has been to reproduce with this driver only.

• OS: Centos 7 and Ubuntu 18.04
• psycopg2: From latest down to 2.7.x
• PG: Latest 11 down to 9.4 (we use 9.4-bdr currently in production)

I can not reproduce this, when SSL has been disabled on PG. However this is not a solution, as we are committed to use SSL for all our connections to DB.

Has anyone experienced this and could share any advice how I could prevent this from producing network packets with TCP [R] flag ?

Thank you very much in advance and any advice will be much appreciated.

P.S. I've also posted this on following channels:

• https://dba.stackexchange.com/questions/235497/tcp-resets-when-using-psycopg2
• https://github.com/psycopg/psycopg2/issues/906

Med vennlig hilsen.
Best regards, Janis Puris.

Med vennlig hilsen.
Best regards, Janis Puris.
On 26 Apr 2019, 09:34 +0200, Jānis Pūris <janis(at)puris(dot)lv>, wrote:
> Hello,
>
> 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.
> This has become very problematic and creates extensive noise in monitoring.
> It also happens with IntelliJ DataGrip provided Java driver and PHP drivers. And hence I do not think this is the drivers problem, but PG instead.
>
> Script
> import psycopg2
> import sys
>
>
> def check():
> # Relying on .pgpass for password
> con = psycopg2.connect('user=monitoring dbname=postgres host=127.0.0.1')
> cur = con.cursor()
> cur.execute("select 'keepalived healthcheck'")
> cur.close()
> con.close()
>
> try:
> check()
> print("ok")
> except Exception as e:
> print("not ok")
> print(str(e))
> sys.exit(1)
>
> tcpdump tcpdump -v 'tcp[tcpflags] & (tcp-rst) != 0' -ilo
> 16:27:45.307006 IP (tos 0x0, ttl 64, id 8123, offset 0, flags [DF], proto TCP (6), length 40)
> localhost.40797 > localhost.postgres: Flags [R], cksum 0x0cca (correct), seq 3830516781, win 0, length 0
> From PG side of things, all seems to be OK
> 2019-04-23 16:27:45.300 CEST process=15615 c= t=0 s=5cbf20e1.3cff [unknown](at)127(dot)0(dot)0(dot)1:[unknown] app=[unknown] LOG: connection received: host=127.0.0.1 port=40797
> 2019-04-23 16:27:45.304 CEST process=15615 c=authentication t=0 s=5cbf20e1.3cff monitoring(at)127(dot)0(dot)0(dot)1:postgres app=[unknown] LOG: connection authorized: user=monitoring database=postgres SSL enabled (protocol=TLSv1.2, cipher=ECDHE-RSA-AES256-GCM-SHA384, compression=off)
> 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
> I've tried various combinations of OS, psycopg2 and PG versions to no avail. My focus has been to reproduce with this driver only.
>
> • OS: Centos 7 and Ubuntu 18.04
> • psycopg2: From latest down to 2.7.x
> • PG: Latest 11 down to 9.4 (we use 9.4-bdr currently in production)
>
> I can not reproduce this, when SSL has been disabled on PG. However this is not a solution, as we are committed to use SSL for all our connections to DB.
>
> Has anyone experienced this and could share any advice how I could prevent this from producing network packets with TCP [R] flag ?
>
> Thank you very much in advance and any advice will be much appreciated.
>
> P.S. I've also posted this on following channels:
>
> • https://dba.stackexchange.com/questions/235497/tcp-resets-when-using-psycopg2
> • https://github.com/psycopg/psycopg2/issues/906
>
>
> Med vennlig hilsen.
> Best regards, Janis Puris.