MIME-Version: 1.0
References: <31ded2b6-d8f8-497c-59ea-c7885b4a7d26@gushi.org>
In-Reply-To: <31ded2b6-d8f8-497c-59ea-c7885b4a7d26@gushi.org>
From: Ron Johnson <ronljohnsonjr@gmail.com>
Date: Fri, 26 Sep 2025 08:47:48 -0400
Message-ID: <CANzqJaAfxs6ip+8mbriqiqiqCj8nk28_SxdeyxLb6Tnmf9CuoA@mail.gmail.com>
Subject: Re: pgpass file in postresql.auto.conf?
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000193594063fb3b2db"
Archived-At: <https://www.postgresql.org/message-id/CANzqJaAfxs6ip%2B8mbriqiqiqCj8nk28_SxdeyxLb6Tnmf9CuoA%40mail.gmail.com>
Precedence: bulk

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

On Fri, Sep 26, 2025 at 8:06=E2=80=AFAM Dan Mahoney (Gushi) <postgres@gushi=
.org>
wrote:

> Hey folks,
>
> In the interest of automation, I've set up a pgpass file for my
> pg_basebackup between master and standby.  This all works, thusly:
>
> pg_basebackup -d
> 'postgres://repuser@10.1.1.1:5432/foo?sslmode=3Dverify-ca' -F p
> --wal-method=3Dstream -P -R -D /var/db/postgres/data17-test3
>
> However, instead of the password getting baked into the pgsql.auto.conf,
> the reference to the passfile gets put in, instead:
>

It's still early in the morning, so I might still be fuzzy-brained, but are
you asking why the repuser password is not hard-coded
into postresql.auto.conf?


> # Do not edit this file manually!
> # It will be overwritten by the ALTER SYSTEM command.
> primary_conninfo =3D 'user=3Drepuser passfile=3D''/var/db/postgres/.pgpas=
s''
> channel_binding=3Dprefer host=3D10.1.1.1 port=3D5432 sslmode=3D''verify-c=
a''
> sslnegotiation=3Dpostgres sslcompression=3D0 sslcertmode=3Dallow sslsni=
=3D1
> ssl_min_protocol_version=3DTLSv1.2 gssencmode=3Ddisable krbsrvname=3Dpost=
gres
> gssdelegation=3D0 target_session_attrs=3Dany load_balance_hosts=3Ddisable
> dbname=3Dfoo'
>
> But it seems postgres won't actually read the passfile.
>
> Sep 26 12:01:27 hostname postgres[42455]: [7-1] 2025-09-26 12:01:27.658
> UTC [42455] FATAL:  could not connect to the primary server: connection t=
o
> server at "10.1.1.1", port 5432 failed: fe_sendauth: no password supplied
>
> Am I doing something wrong here?
>

*When* do you get that message?  And what does "for my
pg_basebackup between master and standby" mean?


> I'm loathe to hand-edit the file, because of that warning there.
>
> Why does pg_basebackup put a reference to a file it it won't read it?
>

Because you have a subtle bug in the .pgpass file.  It's case sensitive,
and requires the domain name of that's part of $HOSTNAME.


> Is there an alter system command that can be used to properly populate th=
e
> password into this file?
>

Does the .pgpass file work for "regular" connections?

--=20
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

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

<div dir=3D"ltr"><div dir=3D"ltr">On Fri, Sep 26, 2025 at 8:06=E2=80=AFAM D=
an Mahoney (Gushi) &lt;<a href=3D"mailto:postgres@gushi.org">postgres@gushi=
.org</a>&gt; wrote:</div><div class=3D"gmail_quote gmail_quote_container"><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex">Hey folks,<br>
<br>
In the interest of automation, I&#39;ve set up a pgpass file for my <br>
pg_basebackup between master and standby.=C2=A0 This all works, thusly:<br>
<br>
pg_basebackup -d <br>
&#39;postgres://<a href=3D"http://repuser@10.1.1.1:5432/foo?sslmode=3Dverif=
y-ca" rel=3D"noreferrer" target=3D"_blank">repuser@10.1.1.1:5432/foo?sslmod=
e=3Dverify-ca</a>&#39; -F p <br>
--wal-method=3Dstream -P -R -D /var/db/postgres/data17-test3<br>
<br>
However, instead of the password getting baked into the pgsql.auto.conf, <b=
r>
the reference to the passfile gets put in, instead:<br></blockquote><div><b=
r></div><div>It&#39;s still early in the morning, so I might still be fuzzy=
-brained, but are you asking why the repuser password is not hard-coded int=
o=C2=A0postresql.auto.conf?</div><div>=C2=A0</div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"># Do not edit this file manually!<br>
# It will be overwritten by the ALTER SYSTEM command.<br>
primary_conninfo =3D &#39;user=3Drepuser passfile=3D&#39;&#39;/var/db/postg=
res/.pgpass&#39;&#39; <br>
channel_binding=3Dprefer host=3D10.1.1.1 port=3D5432 sslmode=3D&#39;&#39;ve=
rify-ca&#39;&#39; <br>
sslnegotiation=3Dpostgres sslcompression=3D0 sslcertmode=3Dallow sslsni=3D1=
 <br>
ssl_min_protocol_version=3DTLSv1.2 gssencmode=3Ddisable krbsrvname=3Dpostgr=
es <br>
gssdelegation=3D0 target_session_attrs=3Dany load_balance_hosts=3Ddisable <=
br>
dbname=3Dfoo&#39;<br>
<br>
But it seems postgres won&#39;t actually read the passfile.<br>
<br>
Sep 26 12:01:27 hostname postgres[42455]: [7-1] 2025-09-26 12:01:27.658 <br=
>
UTC [42455] FATAL:=C2=A0 could not connect to the primary server: connectio=
n to <br>
server at &quot;10.1.1.1&quot;, port 5432 failed: fe_sendauth: no password =
supplied<br>
<br>
Am I doing something wrong here?<br></blockquote><div><br></div><div>*When*=
 do you get that message?=C2=A0 And what does &quot;for my</div>pg_baseback=
up between master and standby&quot; mean?</div><div class=3D"gmail_quote gm=
ail_quote_container"><div>=C2=A0</div><blockquote class=3D"gmail_quote" sty=
le=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddi=
ng-left:1ex">I&#39;m loathe to hand-edit the file, because of that warning =
there.<br>
<br>
Why does pg_basebackup put a reference to a file it it won&#39;t read it?<b=
r></blockquote><div><br></div><div>Because you have a subtle bug in the .pg=
pass file.=C2=A0 It&#39;s case sensitive, and requires the domain name of t=
hat&#39;s part of $HOSTNAME.</div><div>=C2=A0</div><blockquote class=3D"gma=
il_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,2=
04,204);padding-left:1ex">Is there an alter system command that can be used=
 to properly populate the <br>
password into this file?<br></blockquote></div><div><br clear=3D"all"></div=
><div>Does the .pgpass file work for &quot;regular&quot; connections?</div>=
<div><br></div><span class=3D"gmail_signature_prefix">-- </span><br><div di=
r=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr">Death to &lt;Redacted&=
gt;, and butter sauce.<div>Don&#39;t boil me, I&#39;m still alive.<br><div>=
<div>&lt;Redacted&gt; lobster!</div></div></div></div></div></div>

--000000000000193594063fb3b2db--