Network : port listening but not accepting connexions

[Eraserhead]

Hello.

I have a Debian 9 machine with a PostgreSQL (PSQL) 9.6 server installed.
This PSQL server does not accept any connexion from other machines (only from itself).

I have been doing my best to work out this problem and I guess some of you will think this is a topic for a PostgreSQL forum, BUT let me explain :

I also have a Ubuntu 16.04 machin running also a PostgreSQL 9.5 server and I don't have this problem with it.

Other-than-PSQL connexions to both the Debian and the Ubuntu machines coming from other machines (ping, x2gp) work fine. Firewalls are deactivated.
The PSQL server rules for accepting connexions is based on a config file called pg_hba.conf. Its content on the Debian machine is :

Code: Select all

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             192.168.1.0/24          md5

On the Ubuntu machine, the file is very similar :

Code: Select all

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             192.168.1.0/24          md5

I also tried copying-pasting the config_file from the Ubuntu machine to the Debian one to see if it worked. It did not.
Of course, I don't forget restarting the PSQL server.

Now when I do a netstat -an | grep 5432 (PSQL server listens on port 5432), on the Debian machine, I get :

Code: Select all

$ netstat -an | grep 5432
tcp        0      0 127.0.0.1:5432          0.0.0.0:*               LISTEN     
tcp6       0      0 ::1:5432                :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     15854569 /var/run/postgresql/.s.PGSQL.5432

From the little I understand about networks, this means indeed that the machine only listen on the 5432 port for localhost inbound connexions.

But when I do the same on the Ubuntu machine, I get :

Code: Select all

 $ netstat -an | grep 5432
tcp        0      0 0.0.0.0:5432            0.0.0.0:*               LISTEN     
tcp6       0      0 :::5432                 :::*                    LISTEN     
unix  2      [ ACC ]     STREAM     LISTENING     19472    /var/run/postgresql/.s.PGSQL.5432

which means, I believe, it listens to inbound connexions from any IP address.

But why ?

Why this difference in behaviour between the two machines ?

[Segfault]

You are messing with wrong file, pg_hba.conf is to control how server reacts to the connections, I. E. whether it answers or not.

[Eraserhead]

Then, with which file should I mess ?

[Segfault]

postgresql.conf
https://www.postgresql.org/docs/9.6/sta ... N-SETTINGS

Generally, if this is in a corporate environment, you should call your IT guy instead.

[Eraserhead]

Hello again , and thanks for you help.

I indeed found in postgresql.conf a line that looked like the source of the problem :

Code: Select all

#listen_addresses = 'localhost'		# what IP address(es) to listen on

which I changed to

Code: Select all

#listen_addresses = '*'		# what IP address(es) to listen on

But even after a complete OS reboot, it still does not work and the result of the netstat command is the same.

[Eraserhead]

Found it !

I finally noticed that on the Debian machine, the line

Code: Select all

#listen_addresses = '*'		# what IP address(es) to listen on

in the postgresql.conf file started with a # and was hence not active, and that it is not the case on the Ubuntu machine.

I deleted the # to un-comment the line and it now works.

Thanks again !

[reinob]

Found it !

I finally noticed that on the Debian machine, the line

Code: Select all

#listen_addresses = '*'		# what IP address(es) to listen on

in the postgresql.conf file started with a # and was hence not active, and that it is not the case on the Ubuntu machine.

I deleted the # to un-comment the line and it now works.

Thanks again !

Good for you. I just hope you're not exposing an unsecured service to all of the internet..
(if you want it to listen on 0.0.0.0 you might still want to consider using the firewall to restrict that to whatever is actually required).

[Eraserhead]

Yes, I did reenabled the firewall as soon as the problem was solved.