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

psql: FATAL: Ident authentication failed for user "postgres"

提问by morpheous

I have installed PostgreSQL and pgAdminIII on my Ubuntu Karmic box.

我已经在我的 Ubuntu Karmic 机器上安装了 PostgreSQL 和 pgAdminIII。

I am able to use pgAdminIII successfully (i.e. connect/log on), however when I try to login to the server using the same username/pwd on the command line (using psql), I get the error:

我能够成功使用 pgAdminIII（即连接/登录），但是当我尝试在命令行上使用相同的用户名/密码（使用 psql）登录到服务器时，出现错误：

psql: FATAL:  Ident authentication failed for user "postgres"

Does anyone now how to resolve this issue?

现在有人如何解决这个问题？

采纳答案by Frank Heikens

Did you set the proper settings in pg_hba.conf?

您是否在 pg_hba.conf 中设置了正确的设置？

See https://help.ubuntu.com/stable/serverguide/postgresql.htmlhow to do it.

请参阅https://help.ubuntu.com/stable/serverguide/postgresql.html如何操作。

回答by Manav

The following steps work for a fresh install of postgres 9.1 on Ubuntu 12.04. (Worked for postgres 9.3.9 on Ubuntu 14.04 too.)

以下步骤适用于在 Ubuntu 12.04 上全新安装 postgres 9.1。（也适用于 Ubuntu 14.04 上的 postgres 9.3.9。）

By default, postgres creates a user named 'postgres'. We log in as her, and give her a password.

默认情况下，postgres 创建一个名为“postgres”的用户。我们以她的身份登录，并给她一个密码。

$ sudo -u postgres psql
\password
Enter password: ...
...

Logout of psqlby typing \qor ctrl+d. Then we connect as 'postgres'. The -h localhostpart is important: it tells the psqlclient that we wish to connect using a TCP connection (which is configured to use password authentication), and not by a PEER connection (which does not care about the password).

psql通过键入\q或注销ctrl+d。然后我们连接为“postgres”。这-h localhost部分很重要：它告诉psql客户端我们希望使用 TCP 连接（配置为使用密码验证）而不是 PEER 连接（不关心密码）进行连接。

$ psql -U postgres -h localhost

回答by Danny Milosavljevic

Edit the file /etc/postgresql/8.4/main/pg_hba.confand replace identor peerby either md5or trust, depending on whether you want it to ask for a password on your own computer or not. Then reload the configuration file with:

编辑文件/etc/postgresql/8.4/main/pg_hba.conf并替换ident或peer通过任一md5或trust，这取决于你是否希望它询问密码你自己的电脑或不上。然后重新加载配置文件：

/etc/init.d/postgresql reload

回答by Leo Bedrosian

You're getting this error because you're failing client authentication. Based on the error message, you probably have the default postgres configuration, which sets client authentication method to "IDENT" for all PostgreSQL connections.

您收到此错误是因为您未通过客户端身份验证。根据错误消息，您可能有默认的 postgres 配置，它将所有 PostgreSQL 连接的客户端身份验证方法设置为“IDENT”。

You should definitely read section 19.1 Client Authenticationin the PostgreSQL manualto better understand the authentication settings available (for each record in pg_hba.conf), but here is the relevant snippet to help with the problem you're having (from the version 9.5 manual):

您绝对应该阅读PostgreSQL 手册中的第19.1节客户端身份验证以更好地了解可用的身份验证设置（对于pg_hba.conf 中的每条记录），但这里是相关的片段，可帮助您解决遇到的问题（来自9.5 版手册)：

trust

Allow the connection unconditionally. This method allows anyone that can connect to the PostgreSQL database server to login as any PostgreSQL user they wish, without the need for a password or any other authentication. See Section 19.3.1 for details.

reject

Reject the connection unconditionally. This is useful for "filtering out" certain hosts from a group, for example a reject line could block a specific host from connecting, while a later line allows the remaining hosts in a specific network to connect.

md5

Require the client to supply a double-MD5-hashed password for authentication. See Section 19.3.2 for details.

password

Require the client to supply an unencrypted password for authentication. Since the password is sent in clear text over the network, this should not be used on untrusted networks. See Section 19.3.2 for details.

gss

Use GSSAPI to authenticate the user. This is only available for TCP/IP connections. See Section 19.3.3 for details.

sspi

Use SSPI to authenticate the user. This is only available on Windows. See Section 19.3.4 for details.

ident

Obtain the operating system user name of the client by contacting the ident server on the client and check if it matches the requested database user name. Ident authentication can only be used on TCP/IP connections. When specified for local connections, peer authentication will be used instead. See Section 19.3.5 for details.

peer

Obtain the client's operating system user name from the operating system and check if it matches the requested database user name. This is only available for local connections. See Section 19.3.6 for details.

ldap

Authenticate using an LDAP server. See Section 19.3.7 for details.

radius

Authenticate using a RADIUS server. See Section 19.3.8 for details.

cert

Authenticate using SSL client certificates. See Section 19.3.9 for details.

pam

Authenticate using the Pluggable Authentication Modules (PAM) service provided by the operating system. See Section 19.3.10 for details.

相信

无条件允许连接。这种方法允许任何可以连接到 PostgreSQL 数据库服务器的人以他们希望的任何 PostgreSQL 用户身份登录，而无需密码或任何其他身份验证。有关详细信息，请参阅第 19.3.1 节。

拒绝

无条件拒绝连接。这对于从组中“过滤掉”某些主机很有用，例如拒绝行可以阻止特定主机连接，而后面的行允许特定网络中的其余主机连接。

MD5

要求客户端提供双重 MD5 哈希密码进行身份验证。有关详细信息，请参阅第 19.3.2 节。

密码

要求客户端提供未加密的密码进行身份验证。由于密码是通过网络以明文形式发送的，因此不应在不受信任的网络上使用。有关详细信息，请参阅第 19.3.2 节。

总站

使用 GSSAPI 对用户进行身份验证。这仅适用于 TCP/IP 连接。有关详细信息，请参阅第 19.3.3 节。

sspi

使用 SSPI 对用户进行身份验证。这仅在 Windows 上可用。有关详细信息，请参阅第 19.3.4 节。

身份

通过联系客户端上的身份服务器获取客户端的操作系统用户名，并检查它是否与请求的数据库用户名匹配。身份验证只能用于 TCP/IP 连接。当为本地连接指定时，将使用对等身份验证。有关详细信息，请参阅第 19.3.5 节。

同行

从操作系统获取客户端的操作系统用户名，并检查是否与请求的数据库用户名匹配。这仅适用于本地连接。有关详细信息，请参阅第 19.3.6 节。

LDAP

使用 LDAP 服务器进行身份验证。有关详细信息，请参阅第 19.3.7 节。

半径

使用 RADIUS 服务器进行身份验证。有关详细信息，请参阅第 19.3.8 节。

证书

使用 SSL 客户端证书进行身份验证。有关详细信息，请参阅第 19.3.9 节。

帕姆

使用操作系统提供的可插入身份验证模块 (PAM) 服务进行身份验证。有关详细信息，请参阅第 19.3.10 节。

So ... to solve the problem you're experiencing, you could do one of the following:

所以......要解决您遇到的问题，您可以执行以下操作之一：

1. Change the authentication method(s) defined in your pg_hba.conffile to trust, md5, or password(depending on your security and simplicity needs) for the local connection records you have defined in there.

2. Update pg_ident.confto map your operating system users to PostgreSQL users and grant them the corresponding access privileges, depending on your needs.

3. Leave the IDENT settings alone and create users in your database for each operating system user that you want to grant access to. If a user is already authenticated by the OS and logged in, PostgreSQL won't require further authentication and will grant access to that user based on whatever privileges (roles) are assigned to it in the database. This is the default configuration.

1. 将pg_hba.conf文件中定义的身份验证方法更改为trust、md5、 或password（取决于您的安全性和简单性需求），用于您在其中定义的本地连接记录。

2. 更新pg_ident.conf以将您的操作系统用户映射到 PostgreSQL 用户并根据您的需要授予他们相应的访问权限。

3. 保留 IDENT 设置并在您的数据库中为您要授予访问权限的每个操作系统用户创建用户。如果用户已经通过操作系统的身份验证并登录，PostgreSQL 将不需要进一步的身份验证，并且将根据数据库中分配给该用户的任何权限（角色）授予对该用户的访问权限。这是默认配置。

Note: The location of pg_hba.confand pg_ident.confis OS dependent.

注：的位置pg_hba.conf和pg_ident.conf与操作系统相关。

回答by boulder_ruby

Simply adding the -h localhostbit was all mine required to work

简单地添加-h localhost位是我工作所需的全部

回答by Ivan Trechyokas

You can set the environment variable PGHOST=localhost:

您可以设置环境变量PGHOST=localhost：

$ psql -U db_user db_name
psql: FATAL:  Peer authentication failed for user "db_user"

$ export PGHOST=localhost
$ psql -U db_user db_name

Password for user mfonline:

回答by Ethan Brown

In case none of the above works for you:

I've done quite a few Postgres installations, but was flummoxed today on a RedHat 6.5 system (installing Postgres 9.3). My typical hba.conf configuration that Aron shows above didn't work. It turned out that my system was using IPV6, and ignoring the IPV4 configuration. Adding the line:

如果以上都不适合您：

我已经安装了很多 Postgres，但今天在 RedHat 6.5 系统（安装 Postgres 9.3）上感到困惑。Aron 上面显示的我的典型 hba.conf 配置不起作用。原来我的系统使用的是 IPV6，而忽略了 IPV4 配置。添加行：

host    all             all             ::1/128                 password

allowed me to login successfully.

让我登录成功。

回答by Joseph Persie

Out of all the answers above nothing worked for me. I had to manually change the users password in the database and it suddenly worked.

在上面的所有答案中，没有什么对我有用。我不得不手动更改数据库中的用户密码，它突然起作用了。

psql -U postgres -d postgres -c "alter user produser with password 'produser';"

I used the following settings:

我使用了以下设置：

pg_hba.conf

pg_hba.conf

local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            password  
# IPv6 local connections:
host    all             all             ::1/128                 password

Connection is successful finally for the following command:

最后通过以下命令连接成功：

psql -U produser -d dbname -h localhost -W 

回答by Kenny Evitt

Hmmm ...

嗯...

If you can connect with the username and password in pgAdminIII but you can't connect with psqlthen those two programs are probably connecting to the database differently.

如果您可以使用 pgAdminIII 中的用户名和密码连接但无法连接，psql那么这两个程序可能以不同的方式连接到数据库。

[If you're connecting to different databases, first try connecting to the same database. See below.]

[如果您要连接到不同的数据库，请先尝试连接到同一个数据库。见下文。]

From PostgreSQL: Documentation: 9.3: psql:

来自PostgreSQL：文档：9.3：psql：

If you omit the host name, psql will connect via a Unix-domain socket to a server on the local host, or via TCP/IP to localhost on machines that don't have Unix-domain sockets.

如果省略主机名，psql 将通过 Unix 域套接字连接到本地主机上的服务器，或通过 TCP/IP 连接到没有 Unix 域套接字的机器上的 localhost。

If you're not running something like psql ... -h host_name ..., and you're running Ubuntu, psqlshould be connecting via a Unix-domain socket, so PostgreSQL probably isn't configured to allow one of the password authentication methods for the postgresuser.

如果您没有运行类似psql ... -h host_name ...，并且您正在运行 Ubuntu，则psql应该通过 Unix 域套接字进行连接，因此 PostgreSQL 可能未配置为允许postgres用户的密码身份验证方法之一。

You can test this by running:

您可以通过运行来测试：

sudo -u postgres psql

sudo -u postgres psql

If the above works, your server is probably configured to use peerauthentication for local connections by the postgresuser, i.e. asking the OS for your user name to confirm that you're postgres.

如果上述方法有效，则您的服务器可能配置为对postgres用户的本地连接使用对等身份验证，即向操作系统询问您的用户名以确认您是postgres。

So It's Probably Your pg_hba.confFile

所以它可能是你的pg_hba.conf文件

The full path of the file will be something like/etc/postgresql/9.3/main/pg_hba.conf. You can view it by, e.g. sudo cat /etc/postgresql/9.3/main/pg_hba.conf | more.

文件的完整路径类似于/etc/postgresql/9.3/main/pg_hba.conf。您可以通过例如查看它sudo cat /etc/postgresql/9.3/main/pg_hba.conf | more。

If you're omitting the host name in your psqlcommand, you should be able to connect if you add the following entry to your pg_hba.conffile:

如果您在psql命令中省略了主机名，则如果将以下条目添加到pg_hba.conf文件中，您应该能够连接：

# Connection type   Database   User       IP addresses   Method
local               all        postgres                  md5

[Commented lines in the pg_hba.conffile start with #.]

[ pg_hba.conf文件中的注释行以#.开头]

If you areincluding the host name in your psqlcommand, add this entry instead:

如果您在命令中包含主机名，请psql改为添加以下条目：

# Connection type   Database   User       IP addresses   Method
host                all        postgres   127.0.0.1/32   md5

You need to put the entry before any other entries are matched for your connection via psql. If in doubt about where to put it, just put it before the first un-commented line.

您需要在任何其他条目通过psql. 如果不确定将它放在哪里，只需将它放在第一个未注释的行之前。

More about pg_hba.conf

更多关于pg_hba.conf

From PostgreSQL: Documentation: 9.3: The pg_hba.conf File[bold emphasis mine]:

来自PostgreSQL：文档：9.3：pg_hba.conf 文件[粗体强调我的]：

The first record with a matching connection type, client address, requested database, and user nameis used to perform authentication. There is no "fall-through" or "backup": if one record is chosen and the authentication fails, subsequent records are not considered. If no record matches, access is denied.

具有匹配连接类型、客户端地址、请求的数据库和用户名的第一条记录用于执行身份验证。没有“失败”或“备份”：如果选择了一条记录并且身份验证失败，则不考虑后续记录。如果没有记录匹配，则拒绝访问。

Note that records are notmatched on authentication method. So, if your pg_hba.conffile contains the following entry:

请注意，记录与身份验证方法不匹配。因此，如果您的pg_hba.conf文件包含以下条目：

# Connection type   Database   User       IP addresses   Method
local               all        postgres                  peer

Then you won'tbe able to connect via:

那么您将无法通过以下方式连接：

psql -u postgres

psql -u postgres

Unless one of these entries is in your pg_hba.conffile abovethe former entry:

除非这些条目之一在前一个条目上方的pg_hba.conf文件中：

# Connection type   Database   User       IP addresses   Method
local               all        postgres                  md5
local               all        postgres                  password   # Unencrypted!
local               all        all                       md5
local               all        all                       password   # Unencrypted!

回答by Dustin Kirkland

I found that I had to install an identity server, that listens on port 113.

我发现我必须安装一个身份服务器，它侦听端口 113。

sudo apt-get install pidentd
sudo service postgresql restart

And then ident worked.

然后 ident 起作用了。