How to handle special characters in the password of a Postgresql URL connection string?

Question

Using a Postgresql URL connection string in the format of:

postgresql://user:secret@localhost

How do I handle special characters in that string (e.g., $) so that it will actually function when I connect to my postgres database?

I've tried simply URL encoding it, so for example, "test$" becomes "test%24" ... but that seems to be a problem as I get a "FATAL: password authentication failed " error when attempting to use it.

Answer 1

See Connection URIs in the doc.

There are a few things that don't seem quite right in your question:

• URIs are supported by postgres since version 9.2 only, so with a 9.1 client that's not supposed to work at all. Or you're using a client that implements connection URIs itself.

• Percent-sign encoding is supported. Per doc:

  Percent-encoding may be used to include symbols with special meaning in any of the URI parts.

• Percent-encoding is not even necessary for a dollar character.

Tried with 9.3:

sql> alter user daniel password 'p$ass';

$ psql 'postgresql://daniel:p$ass@localhost/test'

works

$ psql 'postgresql://daniel:p%24ass@localhost'

works

psql 'postgresql://daniel:pass@localhost/test'

fails as expected: bad password.

Answer 2

Maybe your input shell has a different encoding and $ is not %24. Check it out on https://www.urlencoder.org/

Hint: If you use alter user "username" password 'p$ass''word' to set/change the password, single quotes have to be masked with another singlequote.