Postgresql – Why does psql open two tcp connections to PostgreSQL

connectivitypostgresqlpsql

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=192.168.0.101 DST=192.168.0.101 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=192.168.0.101 DST=192.168.0.101 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 192.168.0.101 -p 5433

Best Answer

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.

Related Question