postgresql psycopg2.OperationalError：致命：用户“<我的 UNIX 用户>”的密码身份验证失败

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

提问by Yannick

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.

首先，我在我的 vps（Ubuntu 16.04）上部署了一个静态网站，没有问题，然后我尝试向其中添加一个博客应用程序。

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.

它在本地与 PostgreSQL 一起工作得很好，但我不能让它在我的服务器上工作。它似乎试图与我的 Unix 用户连接到 Postgres。

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:

我确实通过 postgres 用户创建了一个数据库和一个所有者，匹配 settings.py 中的登录信息，我期待 psycopg2 尝试使用这些登录信息连接到数据库：

Settings.py + python-decouple:

Settings.py + python 解耦：

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

这是我每次尝试时收到的错误消息 ./manage.py migrate

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

'myportfolio'是我的Unix用户名，数据库用户名不同：

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

• 删除我的 Django 代码，重新安装
• 删除/清除 postgres 并重新安装
• 修改 pg_hba.conf 本地信任

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

有一次，我确实创建了一个名为“myportfolio”的 Django 超级用户作为我的 unix 用户：这会产生问题吗？

回答by Avinash Kumar

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.

根据错误，很明显失败是当您的应用程序尝试 postgres 时，需要集中精力的重要部分是身份验证。

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

执行这些步骤以首先了解并重现该问题。我假设它是一个 Linux 服务器并推荐这些步骤。

Step 1:

步骤1：

$ python3

$蟒蛇3

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

当 pg_hba.conf 文件中没有条目时，我收到以下错误。

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

如果上述步骤一切顺利并且您收到成功消息，则很明显问题出在您的 dsn 上。打印传递给这些变量的值。

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.

验证是否所有值都被适当替换。您可能拥有正确的用户密码，但 dsn 没有为用户选择正确的密码。查看是否可以打印 dsn 并验证连接字符串是否正在完美生成。你会在那里得到修复。

回答by Zoran Pandovski

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

在配置（'USER'）中设置为用户的内容。遵循错误：

FATAL: password authentication failed for user "myportfolio"

致命：用户“myportfolio”的密码验证失败

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

用户是myportfolio，因此如果该用户不存在，您将需要创建该用户。

回答by Garo

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

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.

完成，您现在已经创建了一个用户。

回答by Greg Holst

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

如果您的密码中有一些 Postgres 无法处理的特殊字符（除非您进行一些特殊编码），也可能会出现此问题。

回答by praneeth

Try something like this:

尝试这样的事情：

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