20

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%%P?APOU@mydemoserver.postgres.database.azure.com: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

Sanx
  • 301
  • 1
  • 2
  • 3

5 Answers5

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.

Laurenz Albe
  • 61,070
  • 4
  • 55
  • 90
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"

Venryx
  • 141
  • 4
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).

eckes
  • 1,456
  • 10
  • 18
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;
    }
Gunar Gessner
  • 409
  • 4
  • 5
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);
zemil
  • 101
  • 2