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

Question

For some reason I cannot connect to a postgreSQL server on my LAN. I can ssh into the box and connect using psql just fine. The server is running. But...

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? Cheers

I had the same experience. look for pages such as this one: [enable-remote-postgresql-connection/](https://www.thegeekstuff.com/2014/02/enable-remote-postgresql-connection/) - you will find the way. — topsail, Jul 04 '22 at 00:16
@pm100 I will update the post to show `listen_addresses = '*'`. Isn't that what turns that on? — Rich_F, Jul 04 '22 at 00:19

score 1 · Answer 1 · answered Jul 04 '22 at 03:28

SOLVED. It was two things:

First, using listen_addresses needs to be expanded from '*' to listen_addresses ='127.0.0.1, ::1, localhost, 192.168.1.55', as the TCP socket doesn't show up using '*'.

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"

Second, the AlmaLinux 9 firewall needs a specific hole poked in it for this service to be visible in the public zone:

$ 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:

Restart postgresql and we are gold. It shows up in nmap from my workstation, and is visible to log into psql.

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

1 Answers1