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

psql: FATAL: Peer authentication failed for user "dev"

提问by hsming

when i create a new user, but it cannot login the database.
I do that like this:

当我创建一个新用户时，但它无法登录数据库。
我这样做：

postgres@Aspire:/home/XXX$ createuser dev
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) y
Shall the new role be allowed to create more new roles? (y/n) y

then create a database:

然后创建一个数据库：

postgres@Aspire:/home/XXX$ createdb -O dev test_development

after that, I try psql -U dev -W test_developmentto login, but get the error:

之后，我尝试psql -U dev -W test_development登录，但出现错误：

psql: FATAL:  Peer authentication failed for user "dev"

I tried to solve the problem but failed.

我试图解决这个问题，但失败了。

回答by meyerson

Try:

尝试：

psql -U user_name  -h 127.0.0.1 -d db_name

where

在哪里

• -Uis the database user name
• -his the hostname/IP of the local server, thus avoiding Unix domain sockets
• -dis the database name to connect to

• -U是数据库用户名
• -h是本地服务器的主机名/IP，从而避免 Unix 域套接字
• -d是要连接的数据库名称

This is then evaluated as a "network" connection by Postgresql rather than a Unix domain socket connection, thus not evaluated as a "local" connect as you might see in pg_hba.conf:

这随后被 Postgresql 评估为“网络”连接，而不是 Unix 域套接字连接，因此不会被评估为“本地”连接，如您可能在pg_hba.conf以下内容中看到的：

local   all             all                                     peer

回答by flaviodesousa

Your connection failed because by default psqlconnects over UNIX sockets using peerauthentication, that requires the current UNIX user to have the same user name as psql. So you will have to create the UNIX user devand then login as devor use sudo -u dev psql test_developmentfor accessing the database (and psqlshould notask for a password).

您的连接失败，因为默认情况下psql使用peer身份验证通过 UNIX 套接字进行连接，这要求当前 UNIX 用户与psql. 所以，你必须创建UNIX用户dev，然后登录，dev或用sudo -u dev psql test_development它们来访问数据库（和psql应该不要求输入密码）。

If you cannot or do not want to create the UNIX user, like if you just want to connect to your database for ad hocqueries, forcing a socket connection using psql --host=localhost --dbname=test_development --username=dev(as pointed out by @meyerson answer) will solve your immediate problem.

如果您不能或不想创建 UNIX 用户，就像您只想连接到数据库进行临时查询一样，强制使用套接字连接psql --host=localhost --dbname=test_development --username=dev（如@meyerson 的回答所指出的）将解决您的直接问题。

But if you intend to force password authentication over Unix sockets instead of the peer method, try changing the following pg_hba.conf* line:

但是，如果您打算通过 Unix 套接字而不是 peer 方法强制密码身份验证，请尝试更改以下pg_hba.conf* 行：

from

从

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          peer

to

到

# TYPE DATABASE USER ADDRESS METHOD
local  all      all          md5

• peermeans it will trust the identity (authenticity) of UNIX user. So not asking for a password.

• md5means it will always ask for a password, and validate it after hashing with MD5.

• peer意味着它将信任 UNIX 用户的身份（真实性）。所以不要求密码。

• md5意味着它总是会要求输入密码，并在用 .hash 散列后验证它MD5。

You can, of course, also create more specific rules for a specific database or user, with some users having peerand others requiring passwords.

当然，您也可以为特定数据库或用户创建更具体的规则，某些用户拥有peer密码，而其他用户则需要密码。

After changing pg_hba.confif PostgreSQL is running you'll need to make it re-read the configuration by reloading (pg_ctl reload) or restarting (sudo service postgresql restart).

更改pg_hba.confPostgreSQL 是否正在运行后，您需要通过重新加载 ( pg_ctl reload) 或重新启动 ( sudo service postgresql restart)使其重新读取配置。

^{* The file pg_hba.confwill most likely be at /etc/postgresql/9.x/main/pg_hba.conf}

^{* 该文件pg_hba.conf很可能位于/etc/postgresql/9.x/main/pg_hba.conf}

Edited:Remarks from @Chloe, @JavierEH, @Jonas Eicher, @fccoelho, @Joanis, @Uphill_What comments incorporated into answer.

编辑：@Chloe、@JavierEH、@Jonas Eicher、@fccoelho、@Joanis、@Uphill_What 的评论纳入答案。

回答by stefan.schwetschke

Peer authentication means that postgres asks the operating system for your login name and uses this for authentication. To login as user "dev" using peer authentication on postgres, you must also be the user "dev" on the operating system.

对等身份验证意味着 postgres 向操作系统询问您的登录名并将其用于身份验证。要在 postgres 上使用对等身份验证以用户“dev”身份登录，您还必须是操作系统上的用户“dev”。

You can find details to the authentication methods in the Postgresql documentation.

您可以在Postgresql 文档中找到有关身份验证方法的详细信息。

Hint:If no authentication method works anymore, disconnect the server from the network and use method "trust" for "localhost" (and double check that your server is not reachable through the network while method "trust" is enabled).

提示：如果身份验证方法不再有效，请断开服务器与网络的连接，并为“localhost”使用方法“trust”（并在启用方法“trust”时仔细检查您的服务器是否无法通过网络访问）。

回答by tymik

When you specify:

当您指定：

psql -U user

it connects via UNIX Socket, which by default uses peerauthentication, unless specified in pg_hba.confotherwise.

它通过 UNIX 套接字连接，默认情况下使用peer身份验证，除非pg_hba.conf另有说明。

You can specify:

您可以指定：

host    database             user             127.0.0.1/32       md5
host    database             user             ::1/128            md5

to get TCP/IP connection on loopback interface (both IPv4 and IPv6) for specified databaseand user.

在环回接口（IPv4 和 IPv6）上获取指定database和的TCP/IP 连接user。

After changes you have to restart postgres or reload it's configuration. Restart that should work in modern RHEL/Debian based distros:

更改后，您必须重新启动 postgres 或重新加载它的配置。重新启动应该在基于现代 RHEL/Debian 的发行版中工作：

service postgresql restart

Reload should work in following way:

重新加载应按以下方式工作：

pg_ctl reload

but the command may differ depending of PATH configuration - you may have to specify absolute path, which may be different, depending on way the postgres was installed.

但是命令可能会因 PATH 配置而异 - 您可能必须指定绝对路径，这可能会有所不同，具体取决于 postgres 的安装方式。

Then you can use:

然后你可以使用：

psql -h localhost -U user -d database

to login with that userto specified databaseover TCP/IP. md5stands for encrypted password, while you can also specify passwordfor plain text passwords during authorisation. These 2 options shouldn't be of a great matter as long as database server is only locally accessible, with no network access.

使用user指定database的 TCP/IP登录。 md5代表加密密码，同时您也可以password在授权期间指定明文密码。只要数据库服务器只能在本地访问，而不能访问网络，这两个选项应该不是什么大问题。

Important note:Definition order in pg_hba.confmatters - rules are read from top to bottom, like iptables, so you probably want to add proposed rules above the rule:

重要说明：pg_hba.conf事项中的 定义顺序- 规则从上到下阅读，如 iptables，因此您可能希望在规则上方添加建议的规则：

host    all             all             127.0.0.1/32            ident

回答by mgoldwasser

While @flaviodesousa's answer would work, it also makes it mandatory for all users (everyone else) to enter a password.

虽然@flaviodesousa 的答案可行，但它也强制要求所有用户（其他所有人）输入密码。

Sometime it makes sense to keep peer authentication for everyone else, but make an exception for a service user. In that case you would want to add a line to the pg_hba.conf that looks like:

有时，为其他所有人保留对等身份验证是有意义的，但对服务用户进行例外处理。在这种情况下，您需要在 pg_hba.conf 中添加一行，如下所示：

local   all             some_batch_user                         md5

I would recommend that you add this line right below the commented header line:

我建议您在注释标题行的正下方添加此行：

# TYPE  DATABASE        USER            ADDRESS                 METHOD
local   all             some_batch_user                         md5

You will need to restart PostgreSQL using

您需要使用以下命令重新启动 PostgreSQL

sudo service postgresql restart

If you're using 9.3, your pg_hba.conf would most likely be:

如果您使用的是 9.3，您的 pg_hba.conf 很可能是：

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

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

回答by alangrah

This works for me when I run into it:

当我遇到它时，这对我有用：

sudo -u username psql

回答by Abel

The easiest solution:

最简单的解决方案：

CREATE USER dev WITH PASSWORD 'dev';
CREATE DATABASE test_development;
GRANT ALL PRIVILEGES ON DATABASE test_development to dev;
ALTER ROLE dev CREATEROLE CREATEDB;

回答by VPaul

I simply had to add -h localhost

我只需要添加 -h localhost

回答by Ikrom

In my case I was using different port. Default is 5432. I was using 5433. This worked for me:

就我而言，我使用的是不同的端口。默认值为 5432。我使用的是 5433。这对我有用：

$ psql -f update_table.sql -d db_name -U db_user_name -h 127.0.0.1 -p 5433

回答by Tyler Curtis Jowers

For people in the future seeing this, postgresis in the /usr/lib/postgresql/10/binon my Ubuntu server.

对于人们在将来看到这个，postgres在/usr/lib/postgresql/10/bin我的Ubuntu服务器上。

I added it to the PATH in my .bashrc file, and add this line at the end

我将它添加到我的 .bashrc 文件中的 PATH，并在最后添加这一行

PATH=$PATH:/usr/lib/postgresql/10/bin

then on the command line

然后在命令行

$> source ./.bashrc

I refreshed my bash environment. Now I can use postgres -D /whereverfrom any directory

我刷新了我的 bash 环境。现在我可以postgres -D /wherever从任何目录使用