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 alright, until I close the connection, at which point a packet with TCP reset is sent.
Due to frequency and amount of services using psycopg2 in our environment, this has become very problematic and creates extensive noise in monitoring.
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]@127.0.0.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@127.0.0.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@127.0.0.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@127.0.0.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@127.0.0.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.
- 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)
Has anyone experienced this and could share any advice how I could prevent this from producing network packets with TCP [R] flag ?
Perhaps I'm misusing this library ?
Edit: After further testing and reproducing under different conditions, this seems to be happening when SSL connection is being used.
Edit2: To clarify the importance of this problem, this is the two nodes in the cluster and their tcp reset rate. The expectation here is to have very low rate, so appropriate alerts can be set. 
If we set up alert thresholds to high values, we may miss out on actual problems in the cluster, i.e. MIT attacks etc.
