On Mon, 12 Sept 2022 at 14:23, Sebastien Flaesch <sebastien.flaesch@xxxxxxx> 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.
