3

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. enter image description here

If we set up alert thresholds to high values, we may miss out on actual problems in the cluster, i.e. MIT attacks etc.

  • 1
    For those of us who are DBAs and not network engineers, can you explain why this is a problem? – jjanes Apr 23 '19 at 22:27
  • I don't know if this will help you, but I see this occur only when connecting over SSL. It is a property of the underlying libpq library, not specific to psycopg2. – jjanes Apr 23 '19 at 22:56
  • Hi @jjanes. TCP reset is ungraceful close of connection network conversation, and should not be happening, unless there is some error etc. This is also an issue as in it creates noise in monitoring for any of our postgres instances. Edit: I will test this without TLS and see, if this makes any difference. – JPm Apr 24 '19 at 7:00
  • Why does it create noise? Also, please edit your question so that all these comments can be removed. – Colin 't Hart Apr 24 '19 at 8:49
  • Hello @Colin'tHart, I've updated the question with more clarification, as to why I consider this as a problem. Thanks. – JPm Apr 24 '19 at 11:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.