MIME-Version: 1.0
References: <CAADeyWjQNmfiQxaiRF0tkZ-kZyApPJT00xbmcMtj+oToSPGoWQ@mail.gmail.com>
 <056ebaa8-94bb-461d-905c-4035eab9ceac@aklaver.com> <CAADeyWjAUV8q_gdmEA=G4H5b66gaZopp42bTch27okCxPTS=hQ@mail.gmail.com>
 <d9cb5aab-417d-40b3-80f1-6c26acc7c839@aklaver.com> <2602739.1740681158@sss.pgh.pa.us>
In-Reply-To: <2602739.1740681158@sss.pgh.pa.us>
From: Greg Sabino Mullane <htamfids@gmail.com>
Date: Thu, 27 Feb 2025 13:57:43 -0500
Message-ID: <CAKAnmm+VPxUkZ3jq7CtGoUT-gJ0NrFGTukyp8g_GvJsUxXiicg@mail.gmail.com>
Subject: Re: How to debug: password authentication failed for user
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Adrian Klaver <adrian.klaver@aklaver.com>, 
	Alexander Farber <alexander.farber@gmail.com>, pgsql-general <pgsql-general@postgresql.org>
Content-Type: multipart/alternative; boundary="00000000000010f356062f2446e0"
Archived-At: <https://www.postgresql.org/message-id/CAKAnmm%2BVPxUkZ3jq7CtGoUT-gJ0NrFGTukyp8g_GvJsUxXiicg%40mail.gmail.com>
Precedence: bulk

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

On Thu, Feb 27, 2025 at 1:32=E2=80=AFPM Tom Lane <tgl@sss.pgh.pa.us> wrote:

> > -c 'ALTER USER timeshift_user PASSWORD '"'"'timeshift_pass'"'"';'
> > I am still trying to work out what that quoting is doing?
>

That's standard for -x output for some versions of bash. FWIW, none of the
shells I had access to output it quite like that, but who knows what shell
the OP has. It's basically trying to stop the current ALTER USER statement,
add a new single quote (but wrap it in double quotes!), then start a new
single-quoted string (the actual password). (So single,
double-single-double, single). Then do it all again at the end. It's valid,
and it should really be equivalent to PASSWORD 'timeshift_pass' so it's
hard to see what the problem is.

The fact that a manual ALTER ROLE cleared it up certainly suggests that
something is going wrong, however, and the SELECT rolpassword output
definitely means it had some password. A possibility is that somehow the
user password was already set and this particular statement was not run (or
run on a different cluster).

Another debug technique might be to have the shell script write the
ALTER USER command to a temp file, then slurp it in via psql -f. Then you
can cat that file as part of the script's output

Cheers,
Greg

--
Crunchy Data - https://www.crunchydata.com
Enterprise Postgres Software Products & Tech Support

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

<div dir=3D"ltr"><div dir=3D"ltr">On Thu, Feb 27, 2025 at 1:32=E2=80=AFPM T=
om Lane &lt;<a href=3D"mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>&gt; =
wrote:</div><div class=3D"gmail_quote gmail_quote_container"><blockquote cl=
ass=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid=
 rgb(204,204,204);padding-left:1ex">&gt; -c &#39;ALTER USER timeshift_user =
PASSWORD &#39;&quot;&#39;&quot;&#39;timeshift_pass&#39;&quot;&#39;&quot;=
9;;&#39;<br>
&gt; I am still trying to work out what that quoting is doing?<br></blockqu=
ote><div><br></div><div>That&#39;s standard for -x output for some versions=
 of bash. FWIW, none of the shells I had access to output it quite like tha=
t, but who knows what shell the OP has. It&#39;s basically trying to stop t=
he current ALTER USER statement, add a new single quote (but wrap it in dou=
ble quotes!), then start a new single-quoted string (the actual password). =
(So single, double-single-double, single). Then do it all again at the end.=
 It&#39;s valid, and it should really be equivalent to PASSWORD &#39;timesh=
ift_pass&#39; so it&#39;s hard to see what the problem=C2=A0is.</div><div><=
br></div><div>The fact that a manual ALTER ROLE cleared it up certainly sug=
gests that something is going=C2=A0wrong, however, and the SELECT rolpasswo=
rd=C2=A0output definitely means it had some password. A possibility is that=
 somehow the user password was already set and this particular statement wa=
s not run (or run on a different cluster).</div><div><br></div><div>Another=
 debug technique might be to have the shell script write the ALTER=C2=A0USE=
R command to a temp file, then slurp it in via psql -f. Then you can cat th=
at file as part of the script&#39;s output</div><div><br></div><div>Cheers,=
</div></div><div dir=3D"ltr" class=3D"gmail_signature"><div dir=3D"ltr"><di=
v>Greg</div><div><br></div><div>--</div><div>Crunchy Data - <a href=3D"http=
s://www.crunchydata.com" target=3D"_blank">https://www.crunchydata.com</a><=
/div><div>Enterprise Postgres Software Products &amp; Tech Support</div><di=
v><br></div></div></div></div>

--00000000000010f356062f2446e0--