6

Running postgreSQL 13.2 with ssl on.

I need to connect to a database from a third party application that requires a connection URI, but it is not working. So I tested connecting with psql using two different formats with the same credentials. One worked and one didn't.

The following command logs me into mydb as user myuser:

# psql -U myuser -d mydb -h 127.0.0.1 -p 5432 -W
Password:
psql (13.2 (Debian 13.2-1.pgdg100+1))
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
Type "help" for help.

mydb=>

However this command fails:

# psql postgresql://myuser:MYPASSWORD@127.0.0.1:5432/mydb?sslmode=require
psql: error: FATAL: password authentication failed for user "myuser"

I am using exactly the same credentials. I've verified it more than 10 times. Removing "sslmode=require" does not fix the problem.

My pg_hba.conf file contains:

host   mydb   myuser   127.0.0.1/32   password

I made it the first line in my pg_hba.conf file, so it can't be getting hung up on any other line.

What am I doing wrong?

New contributor
Zephyrus is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
12

The password I was using happened to contain %d0. That works fine when logging in using the parameters. But if you use a connection URI, it is interpreted as a percent-encoded value. Just by coincidence, my password contained the percent sign followed by a valid hex number.

Changing the password, or properly percent-encoding the connection URI, is the solution to this problem. There are several characters that have to be percent-encoded in a connection URI.

New contributor
Zephyrus is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1
  • based on your question it would have been quite impossible to deduct this. consider adding enough information to the question for someone to be able to reproduce the issue. – eis May 3 at 17:09

Your Answer

Zephyrus is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.