If I for instance place a iproute, like this

sudo iptables -I INPUT -p tcp --syn -m conntrack --ctstate NEW --ctstatus CONFIRMED --dport 5433 -j LOG

I can see that there are two entries every time I try to connect to my database.

[598519.458578] IN=lo OUT= MAC=00:00:00:00:00:00:00:00:00:00:00:00:08:00 SRC= DST= LEN=60 TOS=0x00 PREC=0x00 TTL=64 ID=49795 DF PROTO=TCP SPT=58554 DPT=5433 WINDOW=43690 RES=0x00 SYN URGP=0 
[598520.875428] IN=lo OUT= MAC=00:00:00:00:00:00:00:00:00:00:00:00:08:00 SRC= DST= LEN=60 TOS=0x00 PREC=0x00 TTL=64 ID=65392 DF PROTO=TCP SPT=58560 DPT=5433 WINDOW=43690 RES=0x00 SYN URGP=0 

Why does psql need two connections to PostgreSQL Server on two separate ports (58554 and 58560)? That is to say, I get two messages like above when I run this once..

psql -d test -h -p 5433
  • 1
    I see the only one record in '/var/log/messages/` in my machine(tested you iptables rule and used the psql as exact manner as you mentioned). You might be extracting two entries. Check the date at the beginning of the log entries.
    – atokpas
    Mar 3 '17 at 3:58

This double connection depends on whether a password has to be submitted by the client (according to pg_hba.conf) and, if yes, whether the -W option is passed to psql.

In fact avoiding that second connection attempt is the only raison d'être of this option. According to psql manpage:

-W --password

Force psql to prompt for a password before connecting to a database.

This option is never essential, since psql will automatically prompt for a password if the server demands password authentication. However, psql will waste a connection attempt finding out that the server wants a password. In some cases it is worth typing -W to avoid the extra connection attempt.

Note that this option will remain set for the entire session, and so it affects uses of the meta-command \connect as well as the initial connection attempt.

I think that with this log from iptables, you're just noticing the mentioned "wasted connection attempt". Note the time elapsed between the two connections: [598519.458578] and [598520.875428]. Presumably this is the time it took you to enter the password: about 1.42 seconds.

If you try the same test with -W it should ask the password before attempting the connection and make only one connection.

  • Wow, fascinating and exactly what I was looking for. The pg backend must not be smart enough to handle auth, I wonder why this is necessary? I also wonder how I would differentiate the two connections to iptables knowing now that one of them is auth. Mar 3 '17 at 16:25

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.