MIME-Version: 1.0
References: <CAGWxRFfWbD3NNJcpiPA6+g+UYRHAmD6Z4+rQaHoNi5+EEt1gOg@mail.gmail.com>
 <603291.1718988382@sss.pgh.pa.us>
In-Reply-To: <603291.1718988382@sss.pgh.pa.us>
From: Drew Zoellner <drewtzoellner@gmail.com>
Date: Fri, 21 Jun 2024 12:21:07 -0500
Message-ID: <CAGWxRFdmR8W0O7CK-UTcBjPxRp-MSh0J_jC4GmE3JA2FaP_yaQ@mail.gmail.com>
Subject: Re: Replication using mTLS issue
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-general@postgresql.org, postgres@thewickedtribe.net
Content-Type: multipart/alternative; boundary="000000000000e3c656061b69a869"
Archived-At: <https://www.postgresql.org/message-id/CAGWxRFdmR8W0O7CK-UTcBjPxRp-MSh0J_jC4GmE3JA2FaP_yaQ%40mail.gmail.com>
Precedence: bulk

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

Hi Tom, thanks for the response!

So the same user is able to connect using a non replication connection
using the same mtls certificate and pg_ident.conf map. So it seems like the
cert & map are working for this user.

hostssl all pgrepmgr_nonprod 100.0.0.0/8 cert map=3Dpgrepmgr_nonprod_map

This above seems to be the rule that matched the non replication connection
which was successful.

I have tried relaxing the pg_hba.conf line to all like you suggested for
the username and also for IPs and other combinations, unfortunately nothing
was working.

I have been sure to use SELECT pg_reload_conf(); to update changes made to
the pg_hba.conf. I have additionally used SELECT pg_hba_file_rules(); to
verify the rules are showing up as expected from the live DB perspective.

Since non replication connections are working, and the only change to HBA
conf for the replication connection is just all -> replication , it seems
like it should be matching. Any other suggestions?

Thanks, Drew.

On Fri, Jun 21, 2024 at 11:46=E2=80=AFAM Tom Lane <tgl@sss.pgh.pa.us> wrote=
:

> Drew Zoellner <drewtzoellner@gmail.com> writes:
> > Hi Postgres team, I=E2=80=99m receiving an issue matching pg_hba rules =
that I
> can=E2=80=99t
> > seem to sort out. I am trying to use mtls certificate authentication fo=
r
> > physical replication connections but keep receiving the following error=
=E2=80=A6
>
> > pg_receivewal: error: FATAL:  no pg_hba.conf entry for replication
> > connection from host "100.84.12.223", user "pgrepmgr_nonprod", SSL on
>
> > My pg_hba.conf file contains
> >       hostssl replication pgrepmgr_nonprod 100.0.0.0/8 cert
> map=3Dpgrepmgr_nonprod_map
>
> Hm, the match failure must be on user name.  What certificate are you
> using on the client side, and what user name does pgrepmgr_nonprod_map
> map it to?  Does it succeed if you weaken the hba entry to
>
>         hostssl replication all 100.0.0.0/8 cert map=3Dpgrepmgr_nonprod_m=
ap
>
> > Is cert authentication supported for replication connections?
>
> Should be.  But you might find it easier to debug the auth failure
> in a non-replication context, ie add
>
>         hostssl all pgrepmgr_nonprod 100.0.0.0/8 cert
> map=3Dpgrepmgr_nonprod_map
>
> and then see if you can connect with the same credentials from psql
> or your favorite other client.
>
> BTW, don't forget you have to signal the postmaster to reload
> configuration after any change in these files.
>
>                         regards, tom lane
>

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

<div dir=3D"auto">Hi Tom, thanks for the response!</div><div dir=3D"auto"><=
br></div><div dir=3D"auto">So the same user is able to connect using a non =
replication connection using the same mtls certificate and pg_ident.conf ma=
p. So it seems like the cert &amp; map are working for this user.</div><div=
 dir=3D"auto"><br></div><div dir=3D"auto">hostssl all pgrepmgr_nonprod <a h=
ref=3D"http://100.0.0.0/8">100.0.0.0/8</a> cert map=3Dpgrepmgr_nonprod_map<=
/div><div dir=3D"auto"><br></div><div dir=3D"auto">This above seems to be t=
he rule that matched the non replication connection which was successful.=
=C2=A0</div><div dir=3D"auto"><br></div><div dir=3D"auto">I have tried rela=
xing the pg_hba.conf line to all like you suggested for the username and al=
so for IPs and other combinations, unfortunately nothing was working.=C2=A0=
</div><div dir=3D"auto"><br></div><div dir=3D"auto">I have been sure to use=
 SELECT pg_reload_conf(); to update changes made to the pg_hba.conf. I have=
 additionally used SELECT pg_hba_file_rules(); to verify the rules are show=
ing up as expected from the live DB perspective.</div><div dir=3D"auto"><br=
></div><div dir=3D"auto">Since non replication connections are working, and=
 the only change to HBA conf for the replication connection is just all -&g=
t; replication , it seems like it should be matching. Any other suggestions=
?</div><div dir=3D"auto"><br></div><div dir=3D"auto">Thanks, Drew.</div><di=
v><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On F=
ri, Jun 21, 2024 at 11:46=E2=80=AFAM Tom Lane &lt;<a href=3D"mailto:tgl@sss=
.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; wrote:<br></div><blockquote class=3D"=
gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left-width:1px;border=
-left-style:solid;padding-left:1ex;border-left-color:rgb(204,204,204)">Drew=
 Zoellner &lt;<a href=3D"mailto:drewtzoellner@gmail.com" target=3D"_blank">=
drewtzoellner@gmail.com</a>&gt; writes:<br>
&gt; Hi Postgres team, I=E2=80=99m receiving an issue matching pg_hba rules=
 that I can=E2=80=99t<br>
&gt; seem to sort out. I am trying to use mtls certificate authentication f=
or<br>
&gt; physical replication connections but keep receiving the following erro=
r=E2=80=A6<br>
<br>
&gt; pg_receivewal: error: FATAL:=C2=A0 no pg_hba.conf entry for replicatio=
n<br>
&gt; connection from host &quot;100.84.12.223&quot;, user &quot;pgrepmgr_no=
nprod&quot;, SSL on<br>
<br>
&gt; My pg_hba.conf file contains<br>
&gt;=C2=A0 =C2=A0 =C2=A0 =C2=A0hostssl replication pgrepmgr_nonprod <a href=
=3D"http://100.0.0.0/8" rel=3D"noreferrer" target=3D"_blank">100.0.0.0/8</a=
> cert map=3Dpgrepmgr_nonprod_map<br>
<br>
Hm, the match failure must be on user name.=C2=A0 What certificate are you<=
br>
using on the client side, and what user name does pgrepmgr_nonprod_map<br>
map it to?=C2=A0 Does it succeed if you weaken the hba entry to<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 hostssl replication all <a href=3D"http://100.0=
.0.0/8" rel=3D"noreferrer" target=3D"_blank">100.0.0.0/8</a> cert map=3Dpgr=
epmgr_nonprod_map<br>
<br>
&gt; Is cert authentication supported for replication connections?<br>
<br>
Should be.=C2=A0 But you might find it easier to debug the auth failure<br>
in a non-replication context, ie add<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 hostssl all pgrepmgr_nonprod <a href=3D"http://=
100.0.0.0/8" rel=3D"noreferrer" target=3D"_blank">100.0.0.0/8</a> cert map=
=3Dpgrepmgr_nonprod_map<br>
<br>
and then see if you can connect with the same credentials from psql<br>
or your favorite other client.<br>
<br>
BTW, don&#39;t forget you have to signal the postmaster to reload<br>
configuration after any change in these files.<br>
<br>
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 regards, tom lane<br>
</blockquote></div></div>

--000000000000e3c656061b69a869--