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.

Answer 3

You can encode this kind of urls in browser console with encodeURI:

For example get pg_dump command:

var DB_USER = 'postgres';
var DB_PASSWORD = 'password';
var DB_HOST = 'localhost';
var DB_PORT = '5432';
var DB_NAME = 'postgres';

var pghost = encodeURI(`${DB_USER}:${DB_PASSWORD}@${DB_HOST}:${DB_PORT}/${DB_NAME}`);
var pgdump = `pg_dump --dbname=postgresql://${pghost}`;

console.log(pgdump);

Answer 4

just for any one who is strugling like me, i have a password like "a}1K&{LuGe}jw*BCasFdhxp83y7f" in middle of my postgres connection string, just encoding this part of url solved my issue, no need to encode the whole url

First String:

"postgres://root:a}1K&{LuGe}jw*[email protected]:5432/aws-test"

Correct String:

postgres://root:a%7D1K%26%7BLuGe%7Djw%2ABCasFdhxp83y7f@test-db.c1lri2mnc8os.us-east-2.rds.amazonaws.com:5432/aws-test"

Answer 5

I needed a solution for Golang and this is what I used

old_password := "@#foo#bar123$5454"
new_password := url.QueryEscape(old_password)
fmt.Println(new_password)

connect_str := fmt.Sprintf(`postgres://USERNAME:%s@HOST:PORT/DATABASE_NAME`, new_password)

Answer 6

This is my finding: when I connect to postgresql server v15.5 that installed in Debian v10.2, I need to encode the hashtag # char in the password with %23 but when I connect to the pg server that installed in my local Windows 11 notebook, I can use the hashtag char in password string directly. Fyi, I create the connection using python psycopg2. My python script run in WSL2