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

Question

我是网络开发的新手。

首先，我在我的 vps (Ubuntu 16.04) 上毫无问题地部署了一个静态网站，然后我尝试向它添加一个博客应用程序。

它在本地与 PostgreSQL 一起运行良好，但我无法使其在我的服务器上运行。它似乎试图用我的 Unix 用户连接到 Postgres。

为什么我的服务器会尝试这样做？

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

Settings.py + python-decouple:

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

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

‘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"

我尝试过了：

• 删除我的django代码，重新安装
• 删除/清除 postgres 并重新安装
• 修改 pg_hba.conf local 为 trust

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

原文由 Yannick 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

什么是配置中的用户设置（’USER’）。错误之后：

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

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

原文由 Zoran Pandovski 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

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

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

第1步：

$ 蟒蛇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

你应该得到这样的消息。这是一条成功消息。

当我使用错误的密码时，会出现此错误。

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

当 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

所以，问题出在密码上。检查您的密码是否包含任何特殊字符或空格。如果您的密码有空格或特殊字符，请使用我在下面使用的双引号。

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

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

数据库 = {

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

}

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

原文由 Avinash Kumar 发布，翻译遵循 CC BY-SA 3.0 许可协议