public inbox for [email protected]
help / color / mirror / Atom feedQuestion about UNIX socket connections and SSL
6+ messages / 3 participants
[nested] [flat]
* Question about UNIX socket connections and SSL
@ 2024-06-12 15:34 Casey & Gina <[email protected]>
2024-06-12 19:17 ` Re: Question about UNIX socket connections and SSL Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Casey & Gina @ 2024-06-12 15:34 UTC (permalink / raw)
To: pgsql-general
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?
--
Thanks,
- Casey
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about UNIX socket connections and SSL
2024-06-12 15:34 Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
@ 2024-06-12 19:17 ` Tom Lane <[email protected]>
2024-06-12 19:32 ` Re: Question about UNIX socket connections and SSL Daniel Gustafsson <[email protected]>
2024-06-12 20:46 ` Re: Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Tom Lane @ 2024-06-12 19:17 UTC (permalink / raw)
To: Casey & Gina <[email protected]>; +Cc: pgsql-general
Casey & Gina <[email protected]> writes:
> So why can't I use SSL when connecting from a client to a UNIX socket?
(1) It'd add overhead without adding any security. Data going through
a UNIX socket will only pass through the local kernel, and if that's
compromised then it's game over anyway.
(2) I'm less sure about this part, but I seem to recall that openssl
doesn't actually work if given a UNIX socket.
Maybe there are reasons why those arguments are obsolete, but you
haven't presented any.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about UNIX socket connections and SSL
2024-06-12 15:34 Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
2024-06-12 19:17 ` Re: Question about UNIX socket connections and SSL Tom Lane <[email protected]>
@ 2024-06-12 19:32 ` Daniel Gustafsson <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: Daniel Gustafsson @ 2024-06-12 19:32 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Casey & Gina <[email protected]>; pgsql-general
> On 12 Jun 2024, at 21:17, Tom Lane <[email protected]> wrote:
>
> Casey & Gina <[email protected]> writes:
>> So why can't I use SSL when connecting from a client to a UNIX socket?
>
> (1) It'd add overhead without adding any security. Data going through
> a UNIX socket will only pass through the local kernel, and if that's
> compromised then it's game over anyway.
>
> (2) I'm less sure about this part, but I seem to recall that openssl
> doesn't actually work if given a UNIX socket.
That indeed used to be the case, at least until 1.0.2 and possibly 1.1.1, but
AF_UNIX is supported in 3+ IIRC. That being said, I agree with your (1).
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about UNIX socket connections and SSL
2024-06-12 15:34 Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
2024-06-12 19:17 ` Re: Question about UNIX socket connections and SSL Tom Lane <[email protected]>
@ 2024-06-12 20:46 ` Casey & Gina <[email protected]>
2024-06-13 11:47 ` Re: Question about UNIX socket connections and SSL Daniel Gustafsson <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Casey & Gina @ 2024-06-12 20:46 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: pgsql-general
> On Jun 12, 2024, at 2:17 PM, Tom Lane <[email protected]> wrote:
>
> (1) It'd add overhead without adding any security. Data going through
> a UNIX socket will only pass through the local kernel, and if that's
> compromised then it's game over anyway.
That's true. My preference would be to have an unencrypted connection via UNIX socket from the application to haproxy, then an encrypted connection using SSL certificate authentication from haproxy to the database. I spent some time attempting this. But that doesn't seem to be possible since haproxy doesn't understand the postgres protocol.
--
Regards,
- Casey
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about UNIX socket connections and SSL
2024-06-12 15:34 Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
2024-06-12 19:17 ` Re: Question about UNIX socket connections and SSL Tom Lane <[email protected]>
2024-06-12 20:46 ` Re: Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
@ 2024-06-13 11:47 ` Daniel Gustafsson <[email protected]>
2024-06-13 20:18 ` Re: Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Daniel Gustafsson @ 2024-06-13 11:47 UTC (permalink / raw)
To: Casey & Gina <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-general
> On 12 Jun 2024, at 22:46, Casey & Gina <[email protected]> wrote:
> ..haproxy doesn't understand the postgres protocol.
While not strictly that, there was a patch not too long ago for teaching
postgres the PROXY protocol.
https://www.postgresql.org/message-id/flat/165903873765.1168.11139166899805820567.pgcf%40coridan.pos...
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Question about UNIX socket connections and SSL
2024-06-12 15:34 Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
2024-06-12 19:17 ` Re: Question about UNIX socket connections and SSL Tom Lane <[email protected]>
2024-06-12 20:46 ` Re: Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
2024-06-13 11:47 ` Re: Question about UNIX socket connections and SSL Daniel Gustafsson <[email protected]>
@ 2024-06-13 20:18 ` Casey & Gina <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Casey & Gina @ 2024-06-13 20:18 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Tom Lane <[email protected]>; pgsql-general
> On Jun 13, 2024, at 6:47 AM, Daniel Gustafsson <[email protected]> wrote:
>
> While not strictly that, there was a patch not too long ago for teaching
> postgres the PROXY protocol.
As I understand it, PROXY protocol support would be nice if one connects through haproxy on standalone hosts, so that postgres could show the originating app servers as the client_addr / client_hostname. We used to have standalone host haproxies, but moved to haproxy instances on each app node for performance and scalability reasons (many app nodes). I guess it could also help if we were to run pgbouncer on the db nodes?
We're using haproxy to route connections to the appropriate database nodes - RW connections go to the current master in the cluster, and RO are balanced between replicas. It seems that libpq could allow SSL on UNIX sockets which would avoid having to utilize TCP for the local connections from the application to haproxy.
Is there any way to utilize sslmode=verify-full through something routing connections to the appropriate database instances, whether that's with haproxy or something else?
--
Thanks,
- Casey
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-13 20:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-12 15:34 Question about UNIX socket connections and SSL Casey & Gina <[email protected]>
2024-06-12 19:17 ` Tom Lane <[email protected]>
2024-06-12 19:32 ` Daniel Gustafsson <[email protected]>
2024-06-12 20:46 ` Casey & Gina <[email protected]>
2024-06-13 11:47 ` Daniel Gustafsson <[email protected]>
2024-06-13 20:18 ` Casey & Gina <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox