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 ?

Answer 1

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.

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.