No psql md5 auth, psql 14.1 to PG 11
I'm unable to authenticate with psql to a PG 11 database on server A from
server B which has PG 14.1 installed. So it's psql 14.1 not authenticating
to PG 11. Other clients can and do authenticate to the PG 11 database, it
only seems to fail with the psql 14.1 client.
The PG 11 server uses md5. So is the PG 14.1, but I didn't expect that to
matter for a straight psql connection plus interactive password entry.
The error is "fe_sendauth: error sending password authentication".
The bigger picture is that I'm trying to set up a FDW connection from
Server B's PG 14.1 back to Server A's PG 11. I hit this same error and
shifted to troubleshooting with just psql but kept getting the error.
I've checked for md5 use on both servers, but I still don't see a role for
that with psql on Server B's psql.
What can I do to get authentication working?
Thanks,
Pete O'Such
On 4/18/22 13:12, Pete O'Such wrote:
I'm unable to authenticate with psql to a PG 11 database on server A
from server B which has PG 14.1 installed. So it's psql 14.1 not
authenticating to PG 11. Other clients can and do authenticate to the
PG 11 database, it only seems to fail with the psql 14.1 client.The PG 11 server uses md5. So is the PG 14.1, but I didn't expect that
to matter for a straight psql connection plus interactive password entry.
My first thought was that is was a md5/scram-sha-256 issue but I'm not
sure how as both versions support both password types.
So:
1) How where the Postgres instances installed on both machines?
2) What is the exact psql command you are using?
3) What is the complete error message?
Thanks,
Pete O'Such
--
Adrian Klaver
adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes:
On 4/18/22 13:12, Pete O'Such wrote:
I'm unable to authenticate with psql to a PG 11 database on server A
from server B which has PG 14.1 installed. So it's psql 14.1 not
authenticating to PG 11. Other clients can and do authenticate to the
PG 11 database, it only seems to fail with the psql 14.1 client.The PG 11 server uses md5. So is the PG 14.1, but I didn't expect that
to matter for a straight psql connection plus interactive password entry.
My first thought was that is was a md5/scram-sha-256 issue but I'm not
sure how as both versions support both password types.
I just checked the case here, and I can connect fine from psql 14 to
a v11 server with a password stored in md5. So I'm not sure what's
up either, but there has to be something off-the-beaten-path about
this.
Looking at the v14 code, the error message tells us that
pg_password_sendauth failed, which has several possible explanations:
* pqGetnchar failed, which'd imply a malformed server challenge message.
Seems unlikely, if other clients can connect.
* pg_md5_encrypt failed. Hard to believe ... unless the v14 psql
is running on a FIPS-mode machine, which'd refuse all MD5 operations?
* pqPacketSend failed, ie server disconnected after sending the
challenge. Also seems doubtful.
So my recommendation is to check for FIPS mode.
If it's a recent Linux, what does "sysctl crypto.fips_enabled" say?
(This theory also requires that v14 was built with openssl support.)
FWIW, v15 will provide a more on-point error message in such cases.
regards, tom lane
Yup, FIPS-mode is on for both ends. I'd love a pointer to docs or other
resources for figuring out what the path forward looks like.
Thanks for the fast and insightful diagnostic tip..
-Pete O'Such
On Mon, Apr 18, 2022 at 5:08 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Adrian Klaver <adrian.klaver@aklaver.com> writes:
On 4/18/22 13:12, Pete O'Such wrote:
I'm unable to authenticate with psql to a PG 11 database on server A
from server B which has PG 14.1 installed. So it's psql 14.1 not
authenticating to PG 11. Other clients can and do authenticate to the
PG 11 database, it only seems to fail with the psql 14.1 client.The PG 11 server uses md5. So is the PG 14.1, but I didn't expect that
to matter for a straight psql connection plus interactive passwordentry.
My first thought was that is was a md5/scram-sha-256 issue but I'm not
sure how as both versions support both password types.I just checked the case here, and I can connect fine from psql 14 to
a v11 server with a password stored in md5. So I'm not sure what's
up either, but there has to be something off-the-beaten-path about
this.Looking at the v14 code, the error message tells us that
pg_password_sendauth failed, which has several possible explanations:* pqGetnchar failed, which'd imply a malformed server challenge message.
Seems unlikely, if other clients can connect.* pg_md5_encrypt failed. Hard to believe ... unless the v14 psql
is running on a FIPS-mode machine, which'd refuse all MD5 operations?* pqPacketSend failed, ie server disconnected after sending the
challenge. Also seems doubtful.So my recommendation is to check for FIPS mode.
If it's a recent Linux, what does "sysctl crypto.fips_enabled" say?
(This theory also requires that v14 was built with openssl support.)FWIW, v15 will provide a more on-point error message in such cases.
regards, tom lane
"Pete O'Such" <posuch@gmail.com> writes:
Yup, FIPS-mode is on for both ends. I'd love a pointer to docs or other
resources for figuring out what the path forward looks like.
You could switch to SCRAM passwords, if you don't need to support any
clients using pre-v10 libpq.
regards, tom lane