Re: Resolving host to IP address

Hi!

That works for my case now:

postgresql.conf:

listen_addresses = '0.0.0.0' (only IPv4 is ok for me)

pg_hba.conf:

hostssl all pgsuser samenet md5 clientcert=verify-ca

In /etc/hosts, I still have:

127.0.1.1 toro.strasbourg.4js.com toro

But that could go away if I understand well, as long as toro[.stras...] host names can be resolved.

Note: I did not need to re-create the certificates.

Thanks a lot,
Seb
________________________________
From: Francisco Olarte <folarte(at)peoplecall(dot)com>
Sent: Monday, September 12, 2022 5:30 PM
To: Sebastien Flaesch <sebastien(dot)flaesch(at)4js(dot)com>
Cc: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>; pgsql-general(at)lists(dot)postgresql(dot)org <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Re: Resolving host to IP address

EXTERNAL: Do not click links or open attachments if you do not recognize the sender.

Hi Sebastien:

On Mon, 12 Sept 2022 at 16:40, Sebastien Flaesch
<sebastien(dot)flaesch(at)4js(dot)com> wrote:

> I think I got it: PostgreSQL should listen to the real, non-loopback network interface.

Not really. Your problem is not where postgres listens, but where your
host line resolves, the 127.0.1.1 stuff, plus your rules.

In many OS you can bind a listening TCP socket to IN_ADDR_ANY plus a
port. The good thing of doing it is it will work even if you add /
delete new IP addresses. Postgres does this and, IMO, is a good thing.
You seem to have it configured that way-----

> Just for info (local dev config, not prod):
> sf(at)toro:/opt3/dbs/pgs/14.1-openssl/data$ grep listen_addresses postgresql.conf
> listen_addresses = 'localhost,toro.strasbourg.4js.com'

No, you do not, you list your interfaces...... Unless you have a
complex setup, which does not seem to be the case, I would recommend
just using the default "*", specially if you intend to run your
servers firewalled ( which 10.xxx hints to ). This would lead to
something like ..

tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN
tcp6 0 0 :::5432 :::* LISTEN

( not done with pg, done with nc -l, but it worked the same last time
I checked it )

> sf(at)toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W | grep 5437
> tcp 0 0 localhost:5437 0.0.0.0:* LISTEN
> tcp 0 0 toro.strasbourg.4js.com:5437 0.0.0.0:* LISTEN
> tcp6 0 0 localhost:5437 [::]:* LISTEN

There is a piece of info missing here, where does your localhost resolve to.....

> sf(at)toro:/opt3/dbs/pgs/14.1-openssl/data$ netstat -tl -W --numeric-hosts | grep 5437
> tcp 0 0 127.0.0.1:5437 0.0.0.0:* LISTEN
> tcp 0 0 127.0.1.1:5437 0.0.0.0:* LISTEN
> tcp6 0 0 ::1:5437 :::* LISTEN

But from this it seems, if you have restored the 127.0.1.1 hosts line,
to 127.0.0.1+::1

> sf(at)toro:/opt3/dbs/pgs/14.1-openssl/data$ ip route get 127.0.1.1
> local 127.0.1.1 dev lo src 127.0.0.1 uid 1000
> cache <local>
This is expected.

IMO your problem arises from your pg_hba.conf, and your bizarre ( IMO,
I use debian too and one of the first things I do after installing is
dropping the 127.0.1.1 line, but I used fixed IP on a desktop, so YMMV
).
If you just lists localhost as origin ( which means you have to
duplicate the pg_hba.conf lines, listen_address=* will work for both )
it will work right, whatever your listen_adresses is ). You can also
RTFM a bit and notice the line "You can also write all to match any IP
address, samehost to match any of the server's own IP addresses, or
samenet to match any address in any subnet that the server is directly
connected to." ( it is easy to miss, I had to search for it, even if I
knew it existed ).

Note: localhost is a name, like toro, and is resolved,
samehost/samenet/all are magic.

Sumarizing, I would:

- Use listen_adresses=*
- Use samehost in your rules.

Which will lead to a lean config which probably does what you want.

Francisco Olarte.