Ruby-on-rails PostgreSQL：致命 - 用户的对等身份验证失败 (PG::ConnectionBad)

PostgreSQL: FATAL - Peer authentication failed for user (PG::ConnectionBad)

提问by Abram

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

我正在使用 PostgreSQL，我有一个用户的密码与 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

I get the error

我收到错误

FATAL: Peer authentication failed for user "goodsounds"

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

Here is my pg_hba.conf:

这是我的 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

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

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

Here is my database.yml:

这是我的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

回答by Craig Ringer

"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 套接字并期望连接的 unix 用户具有与 postgresql 用户名相同的 unix 用户名。

Since your local unix username is funkdifiedand you're trying to connect as user goodsoundsover a unix domain socket (local) connection where your pg_hba.confspecifies peerauthentication, Pg correctly rejects your connection attempt.

由于您的本地 unix 用户名是funkdified并且您尝试goodsounds通过local您pg_hba.conf指定peer身份验证的 unix 域套接字 ( ) 连接以用户身份进行连接，因此 Pg 正确拒绝了您的连接尝试。

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

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

You can:

你可以：

• Connect via TCP/IP by specifying a hostname in your database connection settings;
• edit pg_hba.confto use md5password authentication instead of peerauthentication for unix sockets (localconnection 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.

• 通过在数据库连接设置中指定主机名，通过 TCP/IP 进行连接；
• 编辑pg_hba.conf使用md5密码身份验证而不是peerunix 套接字（local连接类型）的身份验证，以便 Pg 接受密码身份验证；或者
• 使用与您的 unix 用户名相同的 PostgreSQL 用户名进行连接，如果该用户尚不存在，则在 PostgreSQL 中创建该用户。

See the docs for pg_hba.confand the rest of the client authentication chapter of the documentation.

请参阅文档pg_hba.conf以及文档的客户端身份验证章节的其余部分。

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

请注意，更改pg_hba.conf不会立即生效，您必须重新启动或至少重新加载 PostgreSQL 以使其重新读取pg_hba.conf。

---

---

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_directoriesor override it with (e.g.) host=/tmpin your connection string.

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

回答by Tarun Garg

I was facing same problem on Ubuntu machine so I removed this error by following some steps. Switch to postgres user

我在 Ubuntu 机器上遇到了同样的问题，所以我按照一些步骤删除了这个错误。切换到 postgres 用户

$ sudo su - postgres

it will ask for password and by default password is postgres

它会要求输入密码，默认密码是 postgres

After switch the user to postgres, open psql console

切换用户到postgres后，打开psql控制台

$ psql

so check the version of postgres if multiple versions are available

因此，如果有多个版本可用，请检查 postgres 的版本

psql=# select VERSION();

PostgreSQL 9.1.13 on x86_64-unk....         # so version is 9.1

Now Open postgres user

现在开放 postgres user

vim /etc/postgresql/9.1/main/pg_hba.conf

9.1is version return form upper command

9.1是版本返回表单上层命令

and replace

并替换

local   all             postgres                                peer

to

到

local   all             postgres                                md5

Restart the service

重启服务

sudo service postgresql restart

I write steps on my blog also

我也在我的博客上写了步骤

http://tarungarg402.blogspot.in/2014/10/set-up-postgresql-on-ubuntu.html

http://tarungarg402.blogspot.in/2014/10/set-up-postgresql-on-ubuntu.html

回答by user3546709

If "peer authentication" does not work, try md5 authenticaion.

如果“对等身份验证”不起作用，请尝试 md5 身份验证。

To specify host try something like this:

要指定主机尝试这样的事情：

 psql -d <dbname> -U <username> -h <hostname>

or this:

或这个：

 psql -d <dbname> -U <username> -h <hostname> -W

回答by Sanjay Salunkhe

edit /etc/postgresql/9.3/main/pg_hba.conf

编辑 /etc/postgresql/9.3/main/pg_hba.conf

# "local" is for Unix domain socket connections only
    local   all             all                                     peer

change to below line and it works for me

更改为下面的行，它对我有用

# "local" is for Unix domain socket connections only
local   all             all                                     md5

回答by Promise Preston

Here's how I solved it;

这是我解决它的方法；

Run the command below to confirm your PostgreSQL version.

运行以下命令以确认您的 PostgreSQL 版本。

psql --version

Navigate to the PostgreSQLconfiguration directory on your server, which is in /etc/postgresql/10/main. Take note that 10is the version of my PostgreSQLinstallation on my server. Your version might be 9.5, 11or 12or any other version.

导航到服务器上的PostgreSQL配置目录，该目录位于/etc/postgresql/10/main. 请注意，这10是我在服务器上安装的PostgreSQL版本。您的版本可能是9.5，11或12或任何其他版本。

cd ~
cd /etc/postgresql/10/main

Once you navigate into the /etc/postgresql/10/maindirectory, open the file pg_hba.confusing the command below. This file controls: which hosts are allowed to connect, how clients are authenticated, which PostgreSQL user names they can use, which databases they can access:

导航到/etc/postgresql/10/main目录后，pg_hba.conf使用以下命令打开文件。此文件控制：允许连接哪些主机、客户端如何进行身份验证、它们可以使用哪些 PostgreSQL 用户名、它们可以访问哪些数据库：

sudo nano pg_hba.conf

Replace the following line below:

替换以下行：

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

with the line below:

与下面的行：

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

Also, replace the following line below:

另外，替换下面的以下行：

# "local" is for Unix domain socket connections only
local       all       all            peer

with the line below:

与下面的行：

# "local" is for Unix domain socket connections only
local       all       all            md5

Additionally, we may want to allow inbound connections to the PostgreSQL database in production (to allow host connections from all databases, all users, all addresses using the md5 method).

此外，我们可能希望在生产中允许到 PostgreSQL 数据库的入站连接（以允许来自所有数据库、所有用户、使用 md5 方法的所有地址的主机连接）。

To achieve this, add the line below at the end of the file, and then save the file:

为此，请在文件末尾添加以下行，然后保存文件：

# remote connections
host      all      all      all      md5

Still within the /etc/postgresql/10/maindirectory, open and edit the file postgresql.confusing the command below:

仍在/etc/postgresql/10/main目录中，postgresql.conf使用以下命令打开并编辑文件：

sudo nano postgresql.conf

Replace the line # listen_address='127.0.0.1'or the line listen_address='127.0.0.1'or the line # listen_address='localhost'or the line listen_address='localhost'with the line below, in order to allow PostgreSQL database to listen to connections from all addresses:

用下面的行替换行# listen_address='127.0.0.1'或行listen_address='127.0.0.1'或行# listen_address='localhost'或行listen_address='localhost'，以允许 PostgreSQL 数据库侦听来自所有地址的连接：

listen_addresses = '*'

Save the file, and navigate to the root directory of your server:

保存文件，然后导航到服务器的根目录：

cd ~

Restart or reload the PostgreSQL server using the command below:

使用以下命令重新启动或重新加载 PostgreSQL 服务器：

sudo systemctl restart postgresql       # To restart
sudo systemctl reload postgresql        # To reload