Password authentication fails with complex password

Question

I wrote a Python script which connects to the local PostgreSQL database using psycopg2 2.6 and Python 2.7.8. The connection settings and commands are the following:

HOST = '127.0.0.1'
DATABASE_NAME = 'myappdatabase'
DATABASE_PORT = '5432'
DATABASE_USER = 'myappuser'
DATABASE_PASSWORD = 'secret'
DATABASE_TABLE = 'myappdata'

def _database_connection():
    conn_string = "host='{0}' dbname='{1}' port='{2}' user='{3}' \
        password='{4}'".format(HOST, DATABASE_NAME, DATABASE_PORT, \
        DATABASE_USER, DATABASE_PASSWORD)
    return psycopg2.connect(conn_string)

The script works without problems on machine one which has PostgreSQL 9.4 installed. The configuration in sudo vi /etc/postgresql/9.4/main/pg_hba.conf is not modified and looks like this, without comments:

local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

I can also successfully connect to the same database using pgAdmin III.

The problem

On a second machine with the same setup I cannot connect via the script. The following error occurs:

psycopg2.OperationalError: FATAL: password authentication failed for user "myappuser"
FATAL: password authentication failed for user "myappuser"

The only difference between these two machines is the database password. So I changed it to a simple password - and boom, it works. So I changed it back to a complicated ... and it does not work again. The password is something like this:

DATABASE_PASSWORD = 'zyx@12AA\w2'

So I thought: "This is stupid. It must be my mistake." So I set the database password on machine one to the same as on the second machine. And whoop the Python script also fails.

Answer 1

The backslash in the password in this case is interpreted as escape character. Python would treat the backslash in \w as literal \ even if it's not specified as a raw string because \w is not a valid escape sequence, but the underlying library also parses escape sequences, therefore the backslash has to be escaped (doubled).

To avoid this, specify the connection parameters as keyword arguments instead:

psycopg2.connect(host=HOST,
                 database=DATABASE_NAME,
                 port=DATABASE_PORT,
                 user=DATABASE_USER,
                 password=DATABASE_PASSWORD)

This avoids problems with special characters in passwords. Also a single quote chacacter in the password would break the connection string.