Subject: psql: error: could not connect to server: Connection refused

From: BeeRich Lists <[email-masked]>
Date: Sun, 3 Jul 2022 21:06:33 -0400

Hi folks.  I cannot connect to my postgres server.  I’ve run through all the directives and I still can’t understand why this isn’t showing up.  

psql: error: could not connect to server: Connection refused
    Is the server running on host "server1.local" (192.168.1.23) and accepting
    TCP/IP connections on port 5432?

I cannot find an open port on that box using nmap for ports 5400-6500:

Starting Nmap 7.92 ( https://nmap.org ) at 2022-07-03 19:48 EDT
Nmap scan report for 192.168.1.23
Host is up (0.00041s latency).
All 1101 scanned ports on 192.168.1.23 are in ignored states.
Not shown: 1090 filtered tcp ports (no-response), 11 filtered tcp ports (admin-prohibited)
MAC Address: D0:50:99:87:7B:4A (ASRock Incorporation)

Nmap done: 1 IP address (1 host up) scanned in 5.94 seconds

listen_addresses = '*'

Here is my effective pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             all                                     			trust
host    all             all             	127.0.0.1/32            	md5
local   replication     all                                     		trust
host    replication     all             127.0.0.1/32            	trust
host    replication     all             ::1/128                 		trust

host    all             user1            192.168.1.4             	255.255.255.0    md5
host    all             user1            192.168.1.24            	255.255.255.0    md5
host    all             user1            192.168.1.0/24          	md5
host    all             all             	0.0.0.0/0                		md5

Upon restart/start of the server, it spits this out:

Jul 03 18:07:51 server1.project33.ca postmaster[2321]: 2022-07-03 18:07:51.206 EDT [2321] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jul 03 18:07:51 server1.project33.ca postmaster[2321]: 2022-07-03 18:07:51.213 EDT [2321] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"

Those directives are the same in the server settings:

unix_socket_directories                | /var/run/postgresql, /tmp           | Sets the directories where Unix-domain sockets will be created.
unix_socket_group                      |                                     | Sets the owning group of the Unix-domain socket.
unix_socket_permissions                | 0777                                | Sets the access permissions of the Unix-domain socket.
    
tcp_keepalives_count                   | 0                                   | Maximum number of TCP keepalive retransmits.
tcp_keepalives_idle                    | 0                                   | Time between issuing TCP keepalives.
tcp_keepalives_interval                | 0                                   | Time between TCP keepalive retransmits.
tcp_user_timeout                       | 0                                   | TCP user timeout.

How I can get this to be visible on the LAN and allow me to connect? For some reason TCP sockets aren’t showing up, yet I’ve set listen_addresses to '*'.  I can also ssh into the box, so TCP sockets are running and runctional. 

Cheers, Bee

---

From: Tom Lane <[email-masked]>
Date: Sun, 03 Jul 2022 21:45:31 -0400

> Hi folks.  I cannot connect to my postgres server.  I’ve run through all the directives and I still can’t understand why this isn’t showing up.  

It sure looks like the postmaster doesn't think you told it to
listen on any TCP sockets, because if it did they'd have been
listed in the initial log output.  Are you sure that this setting
is really the active one?

> listen_addresses = '*'

It could also be that there is something weird about your system
environment that is causing '*' to expand to nothing.  Try
explicitly specifying '127.0.0.1, ::1' instead.

			regards, tom lane

---

From: BeeRich Lists <[email-masked]>
Date: Sun, 3 Jul 2022 23:21:28 -0400

Oops, a re-reply:
Hi Tom. Thanks for the reply.

That’s what I’m thinking as it’s not showing up. However I have told it to listen on '*'. Here are relevant settings:

port | 5432 | Sets the TCP port the server listens on.

listen_addresses | * | Sets the host name or IP address(es) to listen to.

Other than that I’m not sure how to turn that on. BTW those items come from `show all;`. In my postgresql.conf I have this:

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

Oh look at this…I changed that out as you expected, as well as its static IP on the LAN, and this showed up on restart:

Jul 03 22:58:58 server_n.project33.ca postmaster[2534]: 2022-07-03 22:58:58.720 EDT [2534] LOG: listening on IPv4 address "192.168.1.23", port 5432
Jul 03 22:58:58 server_n.project33.ca postmaster[2534]: 2022-07-03 22:58:58.720 EDT [2534] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
Jul 03 22:58:58 server_n.project33.ca postmaster[2534]: 2022-07-03 22:58:58.728 EDT [2534] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"

More testing…

OK so it seems on the machine, the port is open (nmap localhost, nmap box.local, nmap <LAN IP address>, but it is not showing up on my workstation for some reason.  But I think I found out why:

Not shown: 5474 filtered tcp ports (no-response), 26 filtered tcp ports (admin-prohibited)

admin-prohibited?  Port 5432 is showing up on that machine, but not on my workstation.  So the firewall is active, and here’s the firewall list:

$ sudo firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s25
  sources:
  services: cockpit dhcpv6-client ssh
  ports:
  protocols:
  forward: yes
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

It seems I need to add postgresql as a `service`.  `firewall-cmd --get-services` shows postgresql as a service.  

It worked!  It was the firewall that needed a port hole poked in it.  This is what I did:

$ sudo firewall-cmd --zone=public --permanent --add-service=postgresql
$ sudo firewall-cmd --zone=public --permanent --add-port 5432/tcp
$ sudo firewall-cmd --reload
$ sudo firewall-cmd --list-all
public (active)
  target: default
  icmp-block-inversion: no
  interfaces: enp0s25
  sources:
  services: cockpit dhcpv6-client postgresql ssh
  ports: 5432/tcp
  protocols:
  forward: yes
  masquerade: no
  forward-ports:
  source-ports:
  icmp-blocks:
  rich rules:

And I can get in using psql as well.  

Well done, Tom!

Cheers, Bee

> On Jul 3, 2022, at 9:45 PM, Tom Lane <[email-masked]> wrote:
>

---

From: Tom Lane <[email-masked]>
Date: Sun, 03 Jul 2022 23:45:57 -0400

BeeRich Lists <[email-masked]> writes:
> It worked!  It was the firewall that needed a port hole poked in it.

Huh, interesting.  What I'd have expected from a firewall problem
is that the postmaster thinks it's listening on the port (and says
so at startup), but then no traffic actually reaches it.  The fact
that you didn't see any log entry about "listening on IPv4 address
so-and-so" suggests that your machine's version of a firewall is
interfering with even binding to the address.  Not impossible
I guess, but it's a new one on me.

			regards, tom lane