PostgreSQL：致命-用户的对等身份验证失败（PG :: ConnectionBad） [英] PostgreSQL: FATAL - Peer authentication failed for user (PG::ConnectionBad)

问题描述

我正在使用PostgreSQL，并且我的用户密码与database.yml中指定的密码匹配。

I am working with PostgreSQL and I have a user with password matching the one specified in database.yml

postgres=# select * from pg_user
;
  usename   | usesysid | usecreatedb | usesuper | usecatupd | userepl |  passwd  | valuntil | useconfig 
------------+----------+-------------+----------+-----------+---------+----------+----------+-----------
 goodsounds |    16386 | t           | t        | t         | t       | ******** |          | 
 postgres   |       10 | t           | t        | t         | t       | ******** |          | 
(2 rows)

但是当我尝试通过运行命令创建数据库时

But when I try creating a database by running the command

rails db:create

我收到错误

致命：用户 goodsounds的对等身份验证失败

FATAL: Peer authentication failed for user "goodsounds"

这是我的pg_hba.conf：

Here is my pg_hba.conf:

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

以前，上面的信任是md5，但我改变了以查看是否有帮助。

Previously "trust" above was md5 but I changed to see if that would help.

这里是我的数据库。yml：

Here is my database.yml:

# PostgreSQL. Versions 8.2 and up are supported.
#
# Install the pg driver:
#   gem install pg
# On Mac OS X with macports:
#   gem install pg -- --with-pg-config=/opt/local/lib/postgresql84/bin/pg_config
# On Windows:
#   gem install pg
#       Choose the win32 build.
#       Install PostgreSQL and put its /bin directory on your path.
#
# Configure Using Gemfile
# gem 'pg'
#
development:
  adapter: postgresql
  encoding: unicode
  database: goodsounds_development
  pool: 5
  username: goodsounds
  password: test

  # Connect on a TCP socket. Omitted by default since the client uses a
  # domain socket that doesn't need configuration. Windows does not have
  # domain sockets, so uncomment these lines.
  host: localhost
  port: 5432

  # Schema search path. The server defaults to $user,public
  #schema_search_path: myapp,sharedapp,public

  # Minimum log levels, in increasing order:
  #   debug5, debug4, debug3, debug2, debug1,
  #   log, notice, warning, error, fatal, and panic
  # The server defaults to notice.
  #min_messages: warning

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  adapter: postgresql
  encoding: unicode
  database: goodsounds_test
  pool: 5
  username: goodsounds
  password: test

production:
  adapter: postgresql
  encoding: unicode
  database: goodsounds_production
  pool: 5
  username: goodsounds
  password: test

推荐答案

对等身份验证表示它正在使用unix套接字并希望连接的Unix用户具有与PostgreSQL用户名相同的Unix用户名。

"Peer authentication" means that it's using a unix socket and expecting the connecting unix user to have the same unix username as the postgresql username.

由于您本地的Unix用户名是 funkdified ，而您尝试通过用户 goodsounds 通过Unix域套接字（ local ）连接，而您的 pg_hba.conf 指定 peer 身份验证，Pg正确拒绝了您的连接尝试。

Since your local unix username is funkdified and you're trying to connect as user goodsounds over a unix domain socket (local) connection where your pg_hba.conf specifies peer authentication, Pg correctly rejects your connection attempt.

这是使用unix套接字时许多安装的默认行为。

This is the default behaviour for many installs when using unix sockets.

您可以：

• 通过在数据库连接设置中指定主机名来通过TCP / IP连接；


• 编辑 pg_hba.conf 使用 md5 密码身份验证代替 peer 身份验证来使用unix套接字（ local 连接类型），因此Pg接受密码身份验证；或


• 使用与您的Unix用户名相同的PostgreSQL用户名进行连接，并在PostgreSQL用户名中创建该用户（如果尚不存在）。

• Connect via TCP/IP by specifying a hostname in your database connection settings;
• edit pg_hba.conf to use md5 password authentication instead of peer authentication for unix sockets (local connection type) so Pg accepts password authentication; or
• Connect with a PostgreSQL username the same as your unix username and create the user in PostgreSQL if it doesn't exist yet.

请参见 <$ c $的文档c> pg_hba.conf 和其余的客户端文档的认证一章。

请注意，对 pg_hba.conf 的更改不会立即生效，必须重新启动或至少重新加载PostgreSQL才能重新读取 pg_hba.conf 。

Note that changes to pg_hba.conf do not take effect immediately, you must restart or at least reload PostgreSQL to get it to reread pg_hba.conf.

同样，如果您安装了多个PostgreSQL版本，则可能有一个版本的libpq和另一版本的服务器。在这种情况下，请确保libpq默认连接到的unix套接字的位置与服务器的 unix_socket_directories 相同，或使用（例如）覆盖它。连接字符串中的host = / tmp 。

Oh, also, if you have multiple PostgreSQL versions installed you might have a libpq from one version and a server from another. In this case make sure the location for the unix socket that libpq connects to by default is the same as the server's unix_socket_directories or override it with (e.g.) host=/tmp in your connection string.

这篇关于PostgreSQL：致命-用户的对等身份验证失败（PG :: ConnectionBad）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！