In PostgreSQL URL I can't use a password containing special characters

Question

I have a postgresql database in Azure

The passwd string has special characters like these:

)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P?APOU

psql --dbname=postgresql://db_user@mydemoserver:)mvd99/iyH_=ag=Por/W}%%aKY^ygt+,sC7%%[email protected]:5432/mydb

when executing the psql command, it shows me in following error:

psql: invalid percent-encoded token: "iyH_=ag=Por/W}%%aKY^ygt+,sC7%%P"

I already tried putting the passwd string in quotation marks but it does not work

What other options do I have? I can not change the passwd

I'm interested in using the psql client, because I have to do very large backups

Answer 1

You have to use URI escapes for all problematic characters.

For this user:

CREATE ROLE "weird@name" PASSWORD '\/ @&?' LOGIN;

You can login like this:

psql postgresql://weird%40name:%5C%2F%20%40%26%3F@localhost:5432/test

The documentation has it:

Percent-encoding may be used to include symbols with special meaning in any of the URI parts, e.g. replace = with %3D.

Answer 2

Solutions (Linux)

Option A (environment variable): PGPASSWORD="my{@ugly}:password!#" psql -U admin -d my-db

Option B (url encode; requires that jq be installed): psql "postgresql://admin:$(printf %s "my{@ugly}:password!#"|jq -sRr @uri)@localhost:5432/my-db"

Solutions (Windows Powershell)

Option A (environment variable): $env:PGPASSWORD="my{@ugly}:password!#"; psql -U admin -d my-db

Option B (url encode): Add-Type -AssemblyName System.Web; psql "postgresql://admin:$([System.Web.HTTPUtility]::UrlEncode("my{@ugly}:password!#"))@localhost:5432/my-db"

Answer 3

It’s not a good idea to specify the password on the command line/in the URL anyway. If you use the .pgpass1 file or the PGPASSFILE environment variable you get around this.

Having said that, using a Alpha-Numeric (URL safe, like for example Hex) only password is just as safe and much more portable (if it is random and long enough).

Answer 4

If your password doesn't contain !

If your password doesn't include the ! character, you can URI encode the password using JavaScript's encodeURIComponent() or analogous function in other languages.

If your password contains !

If the password contains the ! character (or sometimes the * too), you can use this JavaScript function to escape all characters:

    function percentEncodeAllChars(str) {
        var strEncoded = '';
        for(var i = 0, ilen = str.length; i < ilen; i++) {
            var strHex = parseInt(str.charCodeAt(i)).toString(16);
            strEncoded += '%' + strHex;
        }
        return strEncoded;
    }

Answer 5

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