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

Question

I am a fairly new to web developement.

First I deployed a static website on my vps (Ubuntu 16.04) without problem and then I tried to add a blog app to it.

It works well locally with PostgreSQL but I can't make it work on my server. It seems like it tries to connect to Postgres with my Unix user.

Why would my server try to do that?

I did create a database and a owner via the postgres user, matching the login information in settings.py, I was expecting psycopg2 to try to connect to the database using these login informations:

Settings.py + python-decouple:

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

This is the error message I get each time I try to ./manage.py migrate

'myportfolio' is my Unix user name, the database username is different:

Traceback (most recent call last):
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/base/base.py", line 216, in ensure_connection
    self.connect()
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/base/base.py", line 194, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/postgresql/base.py", line 168, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/home/myportfolio/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
psycopg2.OperationalError: FATAL:  password authentication failed for user "myportfolio"
FATAL:  password authentication failed for user "myportfolio"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "./manage.py", line 15, in <module>
    execute_from_command_line(sys.argv)
  File "/home/myportfolio/lib/python3.5/site-packages/django/core/management/__init__.py", line 371, in execute_from_command_line
    utility.execute()
  File "/home/myportfolio/lib/python3.5/site-packages/django/core/management/__init__.py", line 365, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/myportfolio/lib/python3.5/site-packages/django/core/management/base.py", line 288, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/myportfolio/lib/python3.5/site-packages/django/core/management/base.py", line 335, in execute
    output = self.handle(*args, **options)
  File "/home/myportfolio/lib/python3.5/site-packages/django/core/management/commands/migrate.py", line 79, in handle
    executor = MigrationExecutor(connection, self.migration_progress_callback)
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/migrations/executor.py", line 18, in __init__
    self.loader = MigrationLoader(self.connection)
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/migrations/loader.py", line 49, in __init__
    self.build_graph()
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/migrations/loader.py", line 206, in build_graph
    self.applied_migrations = recorder.applied_migrations()
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/migrations/recorder.py", line 61, in applied_migrations
    if self.has_table():
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/migrations/recorder.py", line 44, in has_table
    return self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor())
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/base/base.py", line 255, in cursor
    return self._cursor()
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/base/base.py", line 232, in _cursor
    self.ensure_connection()
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/base/base.py", line 216, in ensure_connection
    self.connect()
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/utils.py", line 89, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/base/base.py", line 216, in ensure_connection
    self.connect()
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/base/base.py", line 194, in connect
    self.connection = self.get_new_connection(conn_params)
  File "/home/myportfolio/lib/python3.5/site-packages/django/db/backends/postgresql/base.py", line 168, in get_new_connection
    connection = Database.connect(**conn_params)
  File "/home/myportfolio/lib/python3.5/site-packages/psycopg2/__init__.py", line 130, in connect
    conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
django.db.utils.OperationalError: FATAL:  password authentication failed for user "myportfolio"
FATAL:  password authentication failed for user "myportfolio"

I tried to:

• delete my django code, re install
• delete/purge postgres and reinstall
• modify pg_hba.conf local to trust

At one point I did create a django superuser called 'myportfolio' as my unix user: could this have create a problem ?

Taction

What is setup as user in config ('USER'), following the error that is myportfolio, so you will need to create the user if it does not exist. – Ablaze

in a .env file I have: SECRET_KEY= ... NAME=portfolio_db USER=user_portfolio PASSWORD= ... ALLOWED_HOSTS= ... DEBUG=True EMAIL_HOST_PASSWORD= ... it does connect well locally and the django key works from the start so python-decouple works well. Do you mean I would have to create a postgres user named 'myportfolio' that has all granted permissions to my database ? I followed a renowned tutorial and in it the two user names are different and it supposly works. – Taction

@ZoranPandovski Thank you, it works well now. I thought it was better for security reason to create a postgres user with a name different from the unix user. That was really helpful so please publish an answer so I can validate it. – Taction

Answer 1

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.

Ablaze

Answer 2

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.

Quickie

You may also need to grant a connection for the user to the database if necessary, which can be done via GRANT CONNECT ON DATABASE <database-name> TO <username>;. – Haha

You have a typo on step 5: GRANT ALL PRIVILEGES ON DATABASE websitenamehere to mywebsiteuser; – Tortola

@Quickie -- The typo is the word PRIVILEDGES (we need to drop the D) – Zymo

Answer 3

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.

Cuevas

thank you for show me how to debug with shell. Using the password and user of my django settings I have a success message.<connection object at 0x7feb703043d8; dsn: 'port=5432 user=user_portfolio dbname=portfolio_db password=xxx host=localhost', closed: 0> ... so what does it mean about my configuration ? – Taction

Answer 4

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.

Kandykane

This does not really answer the question. If you have a different question, you can ask it by clicking Ask Question. To get notified when this question gets new answers, you can follow this question. Once you have enough reputation, you can also add a bounty to draw more attention to this question. - From Review – Piccadilly

Answer 5

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).

Bluegreen

Answer 6

Try something like this:

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

Popper

Answer 7

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

Heresy

Answer 8

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/

Epicarp

The linked site looks to be a low-quality scraper site that copies content off Stack Overflow and formats it badly. The original link is #43657626 – Pleura

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Predacious