postgresql 中用户的对等身份验证失败

Peer authentication failed for user in postgresql

提问by user1919

I am trying to run some postgresql commands through a fabric script. When I execute the script I get:

我正在尝试通过结构脚本运行一些 postgresql 命令。当我执行脚本时，我得到：

out: psql: FATAL:  Peer authentication failed for user "sparc2"

This is how my pg_hba.conf file looks like:

这是我的 pg_hba.conf 文件的样子：

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

I have also modified the postgresql.conf file with adding this line:

我还通过添加以下行修改了 postgresql.conf 文件：

listen_addresses = '*'

After applying the changes I restarted postgresql. But the error is still the same.

应用更改后，我重新启动了 postgresql。但错误仍然相同。

回答by filiprem

PostgreSQL has 2 connection entry points:

PostgreSQL 有 2 个连接入口点：

1. TCP/IP (hostin pg_hba.conf)
2. Unix sockets (localin pg_hba.conf)

1. TCP/IP（host在 pg_hba.conf 中）
2. Unix 套接字（local在 pg_hba.conf 中）

Your server is configured to use peerauth which works only for Unix sockets, and means - ask the kernel if the OS username matches DB username.

您的服务器配置为使用peer仅适用于 Unix 套接字的 auth，这意味着 - 询问内核操作系统用户名是否与数据库用户名匹配。

You have following options:

您有以下选择：

• change pg_hba.confto use md5auth for localsocket connections, or
• change connection settings in your script to use IP connection (127.0.0.1 should work) instead of socket connection. [ This may not require editing the files - sometimes setting PGHOSTvariable is enough ], or
• make your script to run from OS user sparc2, not postgres.

• 更改pg_hba.conf为使用md5auth 进行local套接字连接，或
• 更改脚本中的连接设置以使用 IP 连接（127.0.0.1 应该可以工作）而不是套接字连接。[这可能不需要编辑文件 - 有时设置PGHOST变量就足够了]，或
• 使您的脚本从操作系统用户运行sparc2，而不是postgres.

---

---

Risks / drawbacks

风险/缺点

• if you change peer to md5, some automation scripts that run from "postgres" OS user, and rely on "peer" auth, will stop working. They will start asking for password
• if you change peer to md5, and forget database superuser password, you may have to re-enable peer auth to reset it.

• 如果您将 peer 更改为 md5，一些从“postgres”操作系统用户运行并依赖“peer”身份验证的自动化脚本将停止工作。他们会开始要求输入密码
• 如果您将 peer 更改为 md5，并且忘记了数据库超级用户密码，您可能需要重新启用 peer auth 以重置它。

In general, the "peer" auth is OK. Ease and security of kernel-based local auth is the reason why many distributions choose it for local admin connections. It is useful especially on multi-user shell servers. You can disable it for selected accounts only:

一般来说，“对等”身份验证是可以的。基于内核的本地身份验证的易用性和安全性是许多发行版选择它进行本地管理员连接的原因。它在多用户 shell 服务器上尤其有用。您只能为选定的帐户禁用它：

#CHANNEL  DB    USER     METHOD
local     all   sparc2   md5
local     all   all      peer

More details: hereand here.

更多细节：这里和这里。