[Fixed]-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 PRIVILEGES ON DATABASE websitenamehere to mywebsiteuser;
6. postgres=# \q

Done, you have now created a user.

I had something similar. My issue was that I did not set the environment variables correctly so it couldn’t connect. Ensure that if you go to Edit Configurations, then Environment Variables, and put in your answers in that column.

This problem might also occur if you have some special characters within your password that Postgres cannot cope with (unless you do some special encoding).

Try something like this:

DATABASES = {
   'default': {
   'ENGINE': 'django.db.backends.sqlite3',
   'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
 }
}

For me, I had the wrong port. Additional characters.

This solved for me:

from sqlalchemy import create_engine

connection_string_orig = "postgres://user_with_%34_in_the_string:pw@host:port/db"

**connection_string = connection_string_orig.replace("%", "%25")**

engine = create_engine(connection_string)

print(engine.url) # should be identical to connection_string_orig

engine.connect()

from:
https://stackoverflow.com/a/64894203/