psycopg2.OperationalError: FATAL: password authentication failed for user "<my UNIX user>"

As per the error, it is clear that the failure is when your Application is trying to postgres and the important part to concentrate is Authentication.

Do these steps to first understand and reproduce the issue. I assume it as a Linux Server and recommend these steps.

Step 1:

$ python3

>>>import psycopg2
>>>psycopg2.connect("dbname=postgres user=postgres host=localhost password=oracle port=5432")
>>>connection object at 0x5f03d2c402d8; dsn: 'host=localhost port=5432 dbname=postgres user=postgres password=xxx', closed: 0

You should get such a message. This is a success message.

When i use a wrong password, i get this error.

>>>psycopg2.connect("dbname=postgres user=postgres host=localhost password=wrongpassword port=5432")
>>>Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python3.5/dist-packages/psycopg2/__init__.py", line 130, in connect conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  password authentication failed for user "postgres"
FATAL:  password authentication failed for user "postgres"

When there is no entry in pg_hba.conf file, i get the following error.

>>> psycopg2.connect("dbname=postgres user=postgres host=localhost password=oracle port=5432 ")
>>> Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "/usr/local/lib/python3.5/dist-packages/psycopg2/__init__.py", line 130, in connect
conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  no pg_hba.conf entry for host "::1", user "postgres", database "postgres", SSL on
FATAL:  no pg_hba.conf entry for host "::1", user "postgres", database "postgres", SSL off

So, the issue is with password. Check if your password contains any special characters or spaces. if your password has spaces or special characters, use double quotes as i used below.

>>> psycopg2.connect(dbname="postgres", user="postgres", password="passwords with spaces", host="localhost", port ="5432")

If all is good with the above steps and you got success messages, it is very clear that the issue is with your dsn. Print the values passed to these variables.

DATABASES = {

'default': {
    'ENGINE': 'django.db.backends.postgresql_psycopg2',
    'NAME': config ('NAME'),
    'USER': config ('USER'),
    'PASSWORD': config ('PASSWORD'),
    'HOST': 'localhost',
    'PORT': '',
}

}

Validate if all the values are being substituted appropriately. You may have the correct password for the user but the dsn is not picking the correct password for the user. See if you can print the dsn and validate if the connection string is perfectly being generated. You will get the fix there.

So I was just stuck on this problem and I thought I'd save whoever comes across this post some time by posting the actual commands. This was done on my raspberry pi.

1. sudo su - postgres
2. postgres@raspberrypi:~$ psql
3. postgres=# CREATE DATABASE websitenamehere
4. postgres=# CREATE USER mywebsiteuser WITH PASSWORD 'Password';
5. postgres=# GRANT ALL PRIVILEDGES ON DATABASE websitenamehere to mywebsiteuser;
6. postgres=# \q

Done, you have now created a user.

What is setup as user in config ('USER'). Following the error:

FATAL: password authentication failed for user "myportfolio"

user is myportfolio, so you will need to create that user if it does not exist.