Content-Type: text/plain;
	charset=utf-8
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3696.100.31\))
Subject: Re: psql: error: could not connect to server: Connection refused
From: BeeRich Lists <bee.lists@gmail.com>
In-Reply-To: <1435901.1656899131@sss.pgh.pa.us>
Date: Sun, 3 Jul 2022 23:21:28 -0400
Cc: Submit Postgresql Novice <pgsql-novice@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <AA0098DA-EB2F-4092-AF82-922E1A54A7EF@gmail.com>
References: <5C6C7AA2-88FF-4F15-B7AC-92B2C2584877@gmail.com>
 <1435901.1656899131@sss.pgh.pa.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/AA0098DA-EB2F-4092-AF82-922E1A54A7EF%40gmail.com>
Precedence: bulk

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

That=E2=80=99s what I=E2=80=99m thinking as it=E2=80=99s 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.
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.

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.

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

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

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

Oh look at this=E2=80=A6I 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=E2=80=A6

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=E2=80=99s 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. =20

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

$ sudo firewall-cmd --zone=3Dpublic --permanent --add-service=3Dpostgresql=

$ sudo firewall-cmd --zone=3Dpublic --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. =20

Well done, Tom!

Cheers, Bee

> On Jul 3, 2022, at 9:45 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>=20
> BeeRich Lists <bee.lists@gmail.com> writes:
>> Hi folks.  I cannot connect to my postgres server.  I=E2=80=99ve run =
through all the directives and I still can=E2=80=99t understand why this =
isn=E2=80=99t showing up. =20
>=20
> 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?
>=20
>> listen_addresses =3D '*'
>=20
> 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.
>=20
> 			regards, tom lane