postgresqldockerpasswordspsql

psql connection with postgres uri and special character

I was messing around with postgres in docker and wanted to connect to it via url URI.

If the password contains a substring that could be transformed as a character thanks to decodeURIComponent, it will be treated as well, leading to a connection fail.

Is it possible to specify some character in the uri that should not be considered as special characters to decode?

Here is an example:

%23 is automatically encoded as #

$> docker run --name test -p 5433:5432 -e POSTGRES_USER=user -e POSTGRES_DB=db -e POSTGRES_PASSWORD=secret%23 --rm -d postgres
$> psql postgres://user:secret%23@localhost:5433/db
psql: error: could not connect to server: FATAL:  password authentication failed for user "user"
$> PGPASSWORD=secret%23 psql --user=user --host=localhost --port=5433 --dbname=db
psql (12.3, server 13.2 (Debian 13.2-1.pgdg100+1))
WARNING: psql major version 12, server major version 13.
         Some psql features might not work.
Type "help" for help.
db=#

Working example (without a percent followed by two digits):

$> docker run --name test2 -p 5434:5432 -e POSTGRES_USER=user -e POSTGRES_DB=db -e POSTGRES_PASSWORD=secret# --rm -d postgres
$> psql postgres://user:secret%23@localhost:5434/db
psql (12.3, server 13.2 (Debian 13.2-1.pgdg100+1))
WARNING: psql major version 12, server major version 13.
         Some psql features might not work.
Type "help" for help.
db=# 
$> PGPASSWORD=secret# psql --user=user --host=localhost --port=5434 --dbname=db
psql (12.3, server 13.2 (Debian 13.2-1.pgdg100+1))
WARNING: psql major version 12, server major version 13.
         Some psql features might not work.
Type "help" for help.
db=#

psql postgres://user:secret%23@localhost:5433/db does not work with a password set to secret%23 but does with one set to secret#.

Is it possible to use database uri with a password like secret%23?

Solution

  • The doc says the connection URI must be encoded, so your value of %23 must be encoded to %2523.