public inbox for [email protected]help / color / mirror / Atom feed
FATAL: connection requires a valid client certificate 5+ messages / 3 participants [nested] [flat]
* FATAL: connection requires a valid client certificate @ 2025-06-20 15:35 Valere Binet <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Valere Binet @ 2025-06-20 15:35 UTC (permalink / raw) To: [email protected] Hi everyone, I'm completely new to postgresql and I'm struggling with its SSL configuration. Ubuntu 22.04 LTS Postgresql 17.5-1.pgdg22.04+1 postgresql-17-citus-13.0 13.0.4.citus-1 The certificate chain has 4 certificates, 1 root, 1 intermediate signed by the root certificate, a second intermediate signed by the first one and a server certificate signed bt the second intermediate certificate. I'll call it server. I also have a second server certificate also signed by the second intermediate certificate. I'll call it server2. Postgresql.conf: port = 9700 max_connections = 100 ssl = on ssl_ca_file = /data/db/root.crt ssl_cert_file = /data/db/server.pem # server + intermediate 2 + intermediate 1 ssl_crl_file = /usr/local/share/OCIO_CA6.pem ssl_key_file = /data/db/server.key ... shared_preload_libraries = 'citus' pg_hba.conf: local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust hostssl all ccid all cert map=rafe pg_ident.conf: rafe server2 ccid On the second server: vbinet@server2:~$ psql "port=9700 host=server user=ccid sslcert=~/.postgresql/server2.pem sslkey=~/.postgresql/server2.key sslrootcert=~/.postgresql/root.crt sslmode=verify_ca" psql: error: connection to "server" (ip address), port 9700 failed: FATAL: connection requires a valid certificate server2.pem also includes the intermediate certificates. I tried with the root and the intermediate certificates together in root.pem and just the server certificate in server.crt / server2.crt but that fails with the same message. Can anyone point me to what is wrong in my configuration? Thank you, Valère Binet ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: FATAL: connection requires a valid client certificate @ 2025-06-20 16:02 Tom Lane <[email protected]> parent: Valere Binet <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Tom Lane @ 2025-06-20 16:02 UTC (permalink / raw) To: Valere Binet <[email protected]>; +Cc: [email protected] Valere Binet <[email protected]> writes: > I'm completely new to postgresql and I'm struggling with its SSL > configuration. It sounds like you have the right certs in the right files. I wonder though whether the client is actually picking up the client-side cert/key. In particular, a quick look at the libpq source code indicates that it doesn't have any mechanism for expanding "~" in the sslcert etc. parameters: you need to write out the full path verbatim. (But it also looks like you should have gotten an error about not finding the sslrootcert file, so I'm not quite sure if this theory is correct.) Another thing to look into is whether the order of the certs in the multi-cert files matters. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: FATAL: connection requires a valid client certificate @ 2025-06-20 17:24 Valere Binet <[email protected]> parent: Tom Lane <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Valere Binet @ 2025-06-20 17:24 UTC (permalink / raw) To: Tom Lane <[email protected]>; +Cc: [email protected] Thank you Tom, I already had the full path for the root certificate, sorry I got lazy retyping the command on my personal computer. After also entering the full path for sslcert and sslkey, I'm getting "sslv3 alert certificate expired". Now I just need to figure out which one but I already have a pretty good idea. Thank you again! Regards, Valère On Fri, Jun 20, 2025 at 12:02 PM Tom Lane <[email protected]> wrote: > Valere Binet <[email protected]> writes: > > I'm completely new to postgresql and I'm struggling with its SSL > > configuration. > > It sounds like you have the right certs in the right files. > I wonder though whether the client is actually picking up the > client-side cert/key. > > In particular, a quick look at the libpq source code indicates > that it doesn't have any mechanism for expanding "~" in the sslcert > etc. parameters: you need to write out the full path verbatim. > (But it also looks like you should have gotten an error about > not finding the sslrootcert file, so I'm not quite sure if this > theory is correct.) > > Another thing to look into is whether the order of the certs > in the multi-cert files matters. > > regards, tom lane > ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: FATAL: connection requires a valid client certificate @ 2025-06-21 17:28 Jeff Janes <[email protected]> parent: Valere Binet <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Jeff Janes @ 2025-06-21 17:28 UTC (permalink / raw) To: Valere Binet <[email protected]>; +Cc: [email protected] On Fri, Jun 20, 2025 at 11:35 AM Valere Binet <[email protected]> wrote: > Hi everyone, > > I'm completely new to postgresql and I'm struggling with its SSL > configuration. > > ... > > The certificate chain has 4 certificates, 1 root, 1 intermediate signed by > the root certificate, a second intermediate signed by the first one and a > server certificate signed bt the second intermediate certificate. I'll call > it server. > I also have a second server certificate also signed by the second > intermediate certificate. I'll call it server2. > You only describe having server certs, but the error message says a client cert is needed. You don't describe having any client certs. Maybe you are trying to use a server cert as if it were a client cert, but that is unlikely to work. The server cert needs the hostname of the server as a CN (or SAN), while a client cert needs the username of client (either ccid or server2, not sure which) as the CN. > hostssl all ccid all cert map=rafe > This demands a client cert. Server certs are common. Client certs are somewhat rare, are you sure you actually want those? If so, you will need to set yourself up with one. Cheers, Jeff ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: FATAL: connection requires a valid client certificate @ 2025-06-23 13:11 Valere Binet <[email protected]> parent: Jeff Janes <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Valere Binet @ 2025-06-23 13:11 UTC (permalink / raw) To: Jeff Janes <[email protected]>; +Cc: [email protected] Hi Jeff, Yes, you are correct, I use server certificates as these are the only ones I can get. The only client certificates we can get are on our PIV cards. We need a client certificate for our application but that is not available and we have to use a server certificate. If I understood the documentation correctly, the map in pg_ident.conf matches the server2 certificate to the ccid postgresql account, right? #*map-name* *system-username* *database-username* *rafe server2 ccid* Just FYA, mongo doesn't like it (warning in the logs) but lets us use a server certificate for the client connections, cockroach doesn't care. For different reasons, we need to move away from both and are trying postgresql/citus to see if that will meet our needs. In the meantime I checked that all the certificates on both sides are valid so, I have no idea why I'm getting the "certificate expired" message. Valère Binet On Sat, Jun 21, 2025 at 1:29 PM Jeff Janes <[email protected]> wrote: > On Fri, Jun 20, 2025 at 11:35 AM Valere Binet <[email protected]> > wrote: > >> Hi everyone, >> >> I'm completely new to postgresql and I'm struggling with its SSL >> configuration. >> >> ... >> > > >> The certificate chain has 4 certificates, 1 root, 1 intermediate signed >> by the root certificate, a second intermediate signed by the first one and >> a server certificate signed bt the second intermediate certificate. I'll >> call it server. >> I also have a second server certificate also signed by the second >> intermediate certificate. I'll call it server2. >> > > You only describe having server certs, but the error message says a client > cert is needed. You don't describe having any client certs. Maybe you are > trying to use a server cert as if it were a client cert, but that is > unlikely to work. The server cert needs the hostname of the server as a CN > (or SAN), while a client cert needs the username of client (either ccid or > server2, not sure which) as the CN. > > >> hostssl all ccid all cert map=rafe >> > > This demands a client cert. Server certs are common. Client certs are > somewhat rare, are you sure you actually want those? If so, you will need > to set yourself up with one. > > Cheers, > > Jeff > ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2025-06-23 13:11 UTC | newest] Thread overview: 5+ messages (download: mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2025-06-20 15:35 FATAL: connection requires a valid client certificate Valere Binet <[email protected]> 2025-06-20 16:02 ` Tom Lane <[email protected]> 2025-06-20 17:24 ` Valere Binet <[email protected]> 2025-06-21 17:28 ` Jeff Janes <[email protected]> 2025-06-23 13:11 ` Valere Binet <[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