19

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

5 Answers 5

17

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.

4

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"

2

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

1

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;
    }
1
  • 1
    I upvoted this answer because it was useful to me; you can almost use jq to do the same thing (e.g. echo '"hello world"' | jq '.|@uri'). That uses encodeURIComponent() underneath which unfortunately doesn't encode all characters but it works if you're not dealing with ! Oct 8, 2021 at 15:50
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.