Password authentication fails with complex password

passwords, postgresql, psycopg2, python

Solution

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.

Problem

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.

Original source