MIME-Version: 1.0
From: Valere Binet <valere.binet@gmail.com>
Date: Fri, 20 Jun 2025 11:35:25 -0400
Message-ID: 
 <CAJn2Pj=dTF=LpYiO9SyyKQoyrDEMO=UeQxb+br4qmuAYpVUU5A@mail.gmail.com>
Subject: FATAL: connection requires a valid client certificate
To: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000007c26d0638029d72"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJn2Pj%3DdTF%3DLpYiO9SyyKQoyrDEMO%3DUeQxb%2Bbr4qmuAYpVUU5A%40mail.gmail.com>
Precedence: bulk

--00000000000007c26d0638029d72
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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 =3D 9700
max_connections =3D 100
ssl =3D on
ssl_ca_file =3D /data/db/root.crt
ssl_cert_file =3D /data/db/server.pem      # server + intermediate 2 +
intermediate 1
ssl_crl_file =3D /usr/local/share/OCIO_CA6.pem
ssl_key_file =3D /data/db/server.key
...
shared_preload_libraries =3D '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=3Drafe

pg_ident.conf:
rafe   server2   ccid

On the second server:
vbinet@server2:~$ psql "port=3D9700 host=3Dserver user=3Dccid
sslcert=3D~/.postgresql/server2.pem sslkey=3D~/.postgresql/server2.key
sslrootcert=3D~/.postgresql/root.crt sslmode=3Dverify_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=C3=A8re Binet

--00000000000007c26d0638029d72
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hi everyone,</div><div><br></div><div>I&#39;m complet=
ely new to postgresql and I&#39;m struggling with its SSL configuration.</d=
iv><div><br></div><div>Ubuntu 22.04 LTS</div><div>Postgresql 17.5-1.pgdg22.=
04+1</div><div>postgresql-17-citus-13.0 13.0.4.citus-1</div><div><br></div>=
<div>The certificate chain has 4 certificates, 1 root, 1 intermediate signe=
d by the root certificate, a second intermediate signed by the first one an=
d a server certificate signed bt the second intermediate certificate. I&#39=
;ll call it server.</div><div>I also have a second server certificate also =
signed by the second intermediate certificate. I&#39;ll call it server2.</d=
iv><div><br></div><div>Postgresql.conf:</div><div>port =3D 9700</div><div>m=
ax_connections =3D 100</div><div>ssl =3D on</div><div>ssl_ca_file =3D /data=
/db/root.crt</div><div>ssl_cert_file =3D /data/db/server.pem=C2=A0 =C2=A0 =
=C2=A0 # server=C2=A0+ intermediate 2=C2=A0+ intermediate 1</div><div>ssl_c=
rl_file =3D /usr/local/share/OCIO_CA6.pem</div><div>ssl_key_file =3D /data/=
db/server.key</div><div>...</div><div>shared_preload_libraries =3D &#39;cit=
us&#39;</div><div><br></div><div>pg_hba.conf:</div><div>local all=C2=A0 =C2=
=A0all=C2=A0 =C2=A0 =C2=A0 =C2=A0trust</div><div>host all=C2=A0 =C2=A0 all=
=C2=A0 <a href=3D"http://127.0.0.1/32">127.0.0.1/32</a>=C2=A0 trust</div><d=
iv>host all=C2=A0 =C2=A0 all=C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0trust</div><div>local replication=C2=A0 all=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0 =C2=A0trust</div><div><div></div><div>host replication=
=C2=A0 all=C2=A0 <a href=3D"http://127.0.0.1/32">127.0.0.1/32</a>=C2=A0 tru=
st</div><div>host replication=C2=A0 all=C2=A0 ::1/128=C2=A0 =C2=A0 =C2=A0 =
=C2=A0 =C2=A0 =C2=A0trust</div><div><br></div><div>hostssl all=C2=A0 =C2=A0=
ccid=C2=A0 =C2=A0all=C2=A0 cert map=3Drafe</div><div><br></div><div>pg_iden=
t.conf:</div><div>rafe=C2=A0 =C2=A0server2=C2=A0 =C2=A0ccid</div><div><br><=
/div><div>On the second server:</div><div>vbinet@server2:~$ psql &quot;port=
=3D9700 host=3Dserver user=3Dccid sslcert=3D~/.postgresql/server2.pem sslke=
y=3D~/.postgresql/server2.key sslrootcert=3D~/.postgresql/root.crt sslmode=
=3Dverify_ca&quot;</div><div>psql: error: connection to &quot;server&quot; =
(ip address), port 9700 failed: FATAL: connection requires a valid certific=
ate</div><div><br></div><div>server2.pem also includes the intermediate cer=
tificates. I tried with the root and the intermediate certificates together=
 in root.pem and just the server certificate in server.crt / server2.crt bu=
t that fails with the same message.</div><div><br></div><div>Can anyone poi=
nt me to what is wrong in my configuration?</div><div><br></div><div>Thank =
you,</div><div><br></div><div>Val=C3=A8re Binet</div><br></div></div>

--00000000000007c26d0638029d72--