From: Casey & Gina <cg@osss.net>
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3774.600.62\))
Subject: Question about UNIX socket connections and SSL
Message-Id: <5EDB1F7F-569B-48B2-8DCD-44EE17D41F1A@osss.net>
Date: Wed, 12 Jun 2024 10:34:56 -0500
To: pgsql-general@postgresql.org
Archived-At: <https://www.postgresql.org/message-id/5EDB1F7F-569B-48B2-8DCD-44EE17D41F1A%40osss.net>
Precedence: bulk

It seems that libpq (maybe?) disables SSL when connecting through a UNIX =
socket to the database.

My setup involves a HA database cluster managed by Patroni.  To route RW =
or RO connections to the correct node(s), we use haproxy, running =
locally on each application node.  In the interest of being as efficient =
as possible, not using TCP unnecessarily, and having the ability to set =
appropriate permissions on the socket files which increases security, we =
had configured the applications to connect to haproxy via local UNIX =
socket, and then haproxy would of course communicate over the network to =
the database servers via TCP.

More recently, we've started setting up SSL encryption and CA =
verification for all database connections going over the network.  I =
discovered when working on this that SSL was being disabled due to the =
client connecting to haproxy via UNIX socket.  After trying a bunch of =
things, I resigned to having to use TCP, and we changed the connection =
from the app to haproxy to TCP.

We also have a jump server set up for staff to connect to the database =
via an SSH tunnel.  When this is used, an individual's database =
connection goes from their client over TCP to the jump server via the =
SSH tunnel, which directs their connection to an haproxy instance =
running there via UNIX socket, which then in turn connects to the =
database using TCP.  Interestingly, even though traffic is being routed =
through a UNIX socket here, SSL encryption *does* work.

So why can't I use SSL when connecting from a client to a UNIX socket?  =
I can understand that verify-full wouldn't work without it, but =
verify-full doesn't work even when using TCP with haproxy, as =
"localhost" doesn't match the database hostname.  For now, I'm only =
concerned with the verify-ca sslmode.  Is there a workaround possible =
that doesn't involve using TCP unnecessarily?

--=20
Thanks,
- Casey=