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.

Toweling

What method of URL encoding are you using exactly? – Conventional

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.

Acidulate

what if the special char would be te "@" sign ? – Kati

@Kati @ must be passed in as %40. See also this question, against the right library to use for this ;) – Teaspoon

Even I have the same issue , password with "@", I tries escaping with {@}, '''@''', """@""", raw(@), \@ etc. none of them worked for me. – Bovill

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.

Profiteer

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);

Odontoid

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:[e-mail]:5432/aws-test"

Lanfri

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)

Viens

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

Waterrepellent

Please, edit and try for How to Answer, describe the effect of what you propose and explain why it helps to solve the problem. Find help with formatting your post here: stackoverflow.com/help/formatting . – Solarize