MIME-Version: 1.0
References: 
 <CAJn2Pj=dTF=LpYiO9SyyKQoyrDEMO=UeQxb+br4qmuAYpVUU5A@mail.gmail.com>
 <CAMkU=1zRyvPOuLGuEC_jQqZgbCmhMHLjVVQDD7NqQgPs2BtLig@mail.gmail.com>
In-Reply-To: 
 <CAMkU=1zRyvPOuLGuEC_jQqZgbCmhMHLjVVQDD7NqQgPs2BtLig@mail.gmail.com>
From: Valere Binet <valere.binet@gmail.com>
Date: Mon, 23 Jun 2025 09:11:30 -0400
Message-ID: 
 <CAJn2Pjmd8krhnT8cFFYPB7XXG6ik96JrAeE+D7uL1oXKhc4JSQ@mail.gmail.com>
Subject: Re: FATAL: connection requires a valid client certificate
To: Jeff Janes <jeff.janes@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000eba9e506383cf3e2"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJn2Pjmd8krhnT8cFFYPB7XXG6ik96JrAeE%2BD7uL1oXKhc4JSQ%40mail.gmail.com>
Precedence: bulk

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

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=C3=A8re Binet

On Sat, Jun 21, 2025 at 1:29=E2=80=AFPM Jeff Janes <jeff.janes@gmail.com> w=
rote:

> On Fri, Jun 20, 2025 at 11:35=E2=80=AFAM Valere Binet <valere.binet@gmail=
.com>
> 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 a=
nd
>> 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 clien=
t
> cert is needed.  You don't describe having any client certs.  Maybe you a=
re
> 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 o=
r
> server2, not sure which) as the CN.
>
>
>> hostssl all   ccid   all  cert map=3Drafe
>>
>
> 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 nee=
d
> to set yourself up with one.
>
>  Cheers,
>
> Jeff
>

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

<div dir=3D"ltr"><div>Hi Jeff,</div><div><br></div><div>Yes, you are correc=
t, 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 cert=
ificate for our application but that is not available and we have to use a =
server certificate.</div><div>If I understood the documentation correctly, =
the map in pg_ident.conf matches the server2 certificate to the ccid postgr=
esql account, right?</div><div>#<em class=3D"gmail-replaceable"><code>map-n=
ame</code></em> <em class=3D"gmail-replaceable"><code>system-username</code=
></em> <em class=3D"gmail-replaceable"><code>database-username</code></em><=
/div><div><em class=3D"gmail-replaceable"><code>rafe=C2=A0 =C2=A0 =C2=A0 se=
rver2=C2=A0 =C2=A0 =C2=A0ccid</code></em></div><div><em class=3D"gmail-repl=
aceable"><code><br></code></em></div><div>Just FYA, mongo doesn&#39;t like =
it (warning in the logs) but lets us use a server certificate for the clien=
t connections, cockroach doesn&#39;t care. For different reasons, we need t=
o move away from both and are trying postgresql/citus to see if that will m=
eet our needs.</div><div><br></div><div>In the meantime I checked that all =
the certificates on both sides are valid so, I have no idea why I&#39;m get=
ting the &quot;certificate expired&quot; message.</div><div><br></div><div>=
Val=C3=A8re Binet</div></div><br><div class=3D"gmail_quote gmail_quote_cont=
ainer"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, Jun 21, 2025 at 1:29=
=E2=80=AFPM Jeff Janes &lt;<a href=3D"mailto:jeff.janes@gmail.com">jeff.jan=
es@gmail.com</a>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">On Fri, Jun 20, 2025 at 11:35=
=E2=80=AFAM Valere Binet &lt;<a href=3D"mailto:valere.binet@gmail.com" targ=
et=3D"_blank">valere.binet@gmail.com</a>&gt; wrote:</div><div class=3D"gmai=
l_quote"><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"=
><div>Hi everyone,</div><div><br></div><div>I&#39;m completely new to postg=
resql and I&#39;m struggling with its SSL configuration.</div><div><br></di=
v><div>...</div></div></blockquote><div>=C2=A0</div><blockquote class=3D"gm=
ail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,=
204,204);padding-left:1ex"><div dir=3D"ltr"><div>The certificate chain has =
4 certificates, 1 root, 1 intermediate signed by the root certificate, a se=
cond intermediate signed by the first one and a server certificate signed b=
t the second intermediate certificate. I&#39;ll call it server.</div><div>I=
 also have a second server certificate also signed by the second intermedia=
te certificate. I&#39;ll call it server2.</div></div></blockquote><div><br>=
</div><div>You only describe having server certs, but the=C2=A0error messag=
e says a client cert is needed.=C2=A0 You don&#39;t describe having any cli=
ent certs.=C2=A0 Maybe you are trying=C2=A0to use a server cert as if it we=
re a client cert, but that is unlikely to work.=C2=A0 The server cert needs=
 the=C2=A0hostname of the server as a CN (or SAN), while a client cert need=
s the username of client (either ccid or server2, not sure which) as the=C2=
=A0CN.</div><div><br></div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex=
"><div dir=3D"ltr"><div><div><br></div><div>hostssl all=C2=A0 =C2=A0ccid=C2=
=A0 =C2=A0all=C2=A0 cert map=3Drafe</div></div></div></blockquote><div><br>=
</div><div>This demands a client cert.=C2=A0 Server certs are common.=C2=A0=
 Client certs are somewhat rare, are you sure you actually want those?=C2=
=A0 If so, you will need to set yourself up with one.</div><div><br></div><=
div>=C2=A0Cheers,</div><div><br></div><div>Jeff</div></div></div>
</blockquote></div>

--000000000000eba9e506383cf3e2--