Resolving host to IP address

Sebastien Flaesch <sebastien.flaesch@4js.com> writes:

$ psql 'postgresql://toro.strasbourg.4js.com:5437/test1?user=pgsuser&sslmode=verify-ca&sslrootcert=./root.crt&sslcert=./client.crt&sslkey=./client.key'
psql: error: connection to server at "toro.strasbourg.4js.com" (127.0.1.1), port 5437 failed: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "pgsuser", database "test1", SSL encryption

What looks strange to me in this error is that first it mentions 127.0.1.1 (ok) but then, 127.0.0.1

Linux doesn't seem to show its routing of the loopback domain in netstat
output, but it's probably much like what macOS shows explicitly:

$ netstat -r -n
Destination Gateway Flags Netif Expire
...
127 127.0.0.1 UCS lo0
127.0.0.1 127.0.0.1 UH lo0
...

That is, the entire 127/8 range is "routed" through 127.0.0.1 and
will look like that to the server. I see similar entries on NetBSD.

Confirming that guess, I see this behavior on RHEL8,
with no particular OpenSSL involvement:

$ psql -h 127.0.1.1
psql (16devel)
Type "help" for help.

postgres=# select client_addr from pg_stat_activity;
client_addr
-------------
...
127.0.0.1
...

Moral: don't try to use addresses in that range as real addresses.

regards, tom lane

On Mon, 12 Sept 2022 at 14:23, Sebastien Flaesch
<sebastien.flaesch@4js.com> wrote:

I managed to establish the secure connection, by using DN=root.strasbourg.4js.com for the self-signed root CA, and DN=toro.strasbourg.4js.com for the server certificate, DN=pgsuser for the client certificate.
I have created my client certificate by using the root CA.
pg_hba.conf:
hostssl all pgsuser toro.strasbourg.4js.com md5 clientcert=verify-ca

Server and client are on the same Debian 11 machine.
It works, if I comment out the /etc/hosts line set by Debian Linux for my host name:
# 127.0.1.1 toro.strasbourg.4js.com toro

The name "toro" is then resolved to the IP address provided by my DHCP server:
root@toro:~# host toro
toro.strasbourg.4js.com has address 10.0.40.61

root@toro:~# host toro.strasbourg.4js.com
toro.strasbourg.4js.com has address 10.0.40.61

However, if I put the 127.0.1.1 line back in /etc/hosts, re-create all certificates (is this actually needed? I guess no), restart the PostgreSQL server, I get this error:
$ psql 'postgresql://toro.strasbourg.4js.com:5437/test1?user=pgsuser&sslmode=verify-ca&sslrootcert=./root.crt&sslcert=./client.crt&sslkey=./client.key'
psql: error: connection to server at "toro.strasbourg.4js.com" (127.0.1.1), port 5437 failed: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "pgsuser", database "test1", SSL encryption

What looks strange to me in this error is that first it mentions 127.0.1.1 (ok) but then, 127.0.0.1

(not having your full data, guessing a bit on typical configs here ).

Your loopback interface, "lo" which is used to connect to net 127.*
has probably the address localhost=127.0.0.1.

Postgres is probably binding to wilcard address. So when you tell psql
to connect to 127.0.1.1 it starts the tcp connection selecting the
interface address as source, per the route table, so your connection
is source=127.0.0.1, destination 127.0.1.1.

The error message up to the "failed:" is probably psql telling you
where it sent the connection, to toro=127.0.1.1.

The second part is the server telling you where it sees the connection
comming from.

What am I missing here?

Probably some tcp tracing to see it in action. If you bind to *:5437
you can receive connections on any 127.* address. Your hosts uses this
trick for unknown reasons.

When you zap the host line everything works well because your
interface is probably 10.0.40.61, so the route table says use
10.0.40.61 as source.

You would probably experience the same problem if you added a second
IP address, say 1.2.3.4 to your interface and told dhcp to resolve
toro to it. In localhost you do not have to do anything because any
127 address can be used as source or connected to in loopback, it is
magic.

Also, if you want it to work you would need a second hostssl line
listing localhost as the source address, or, IIRC, you can try to
force the source address for connections to be toro using some deep
magic jujus, as psql does not seem to suipport setting it.

Francisco Olarte.

A little off topic but may be useful for someone:

On Mon, 12 Sept 2022 at 16:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
...

Linux doesn't seem to show its routing of the loopback domain in netstat
output, but it's probably much like what macOS shows explicitly:

Among other things it can be shown by "ip route show", although it is
a little too verbose:

$ ip route show table all
default via 192.168.0.1 dev enp39s0 onlink
192.168.0.0/24 dev enp39s0 proto kernel scope link src 192.168.0.2
broadcast 127.0.0.0 dev lo table local proto kernel scope link src 127.0.0.1
local 127.0.0.0/8 dev lo table local proto kernel scope host src 127.0.0.1
local 127.0.0.1 dev lo table local proto kernel scope host src 127.0.0.1
broadcast 127.255.255.255 dev lo table local proto kernel scope link
src 127.0.0.1
broadcast 192.168.0.0 dev enp39s0 table local proto kernel scope link
src 192.168.0.2
local 192.168.0.2 dev enp39s0 table local proto kernel scope host src
192.168.0.2
broadcast 192.168.0.255 dev enp39s0 table local proto kernel scope
link src 192.168.0.2
::1 dev lo proto kernel metric 256 pref medium
fe80::/64 dev enp39s0 proto kernel metric 256 pref medium
local ::1 dev lo table local proto kernel metric 0 pref medium
local fe80::2d8:61ff:fe9f:267b dev enp39s0 table local proto kernel
metric 0 pref medium
multicast ff00::/8 dev enp39s0 table local proto kernel metric 256 pref medium

( table local trims it a bit ).

For debugging this things my first go is to "ip route get", which
gives the selected one:

$ ip route get 127.1.2.3
local 127.1.2.3 dev lo src 127.0.0.1 uid 1000
cache <local>

Francisco Olarte.

Thank you all for your comments.

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

Just for info (local dev config, not prod):

sf@toro:/opt3/dbs/pgs/14.1-openssl/data$ grep listen_addresses postgresql.conf

listen_addresses = 'localhost,toro.strasbourg.4js.com'

sf@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

sf@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

sf@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>

Seb

Hi Sebastien:

On Mon, 12 Sept 2022 at 16:40, Sebastien Flaesch
<sebastien.flaesch@4js.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@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@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@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@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.

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@peoplecall.com>
Sent: Monday, September 12, 2022 5:30 PM
To: Sebastien Flaesch <sebastien.flaesch@4js.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.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.flaesch@4js.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@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@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@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@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.