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?

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.

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

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.