postgresql Postgres 密码认证失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14564644/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Postgres password authentication fails
提问by milosa
I tried to login with the postgres user from my windows machine to my server with Pgadmin.
我尝试使用 pgadmin 使用 postgres 用户从我的 Windows 机器登录到我的服务器。
But it keeps giving me this error:
但它不断给我这个错误：
psql: FATAL: password authentication failed for user "postgres"
So then I tried to login from the command line with psql, which gave me the same error. I then resetted the password to 'test' using psql, after putting the local entry in pg_hba.conf to trust. And then I placed the entry back to md5, and tried to login with the password 'test'.
然后我尝试使用 psql 从命令行登录，这给了我同样的错误。然后，在将 pg_hba.conf 中的本地条目置于信任之后，我使用 psql 将密码重置为“test”。然后我将条目放回 md5，并尝试使用密码“test”登录。
In psql I have used these commands:
在 psql 中，我使用了这些命令：
ALTER ROLE postgres WITH PASSWORD 'test';
ALTER ROLE postgres PASSWORD 'test';
ALTER USER postgres WITH PASSWORD 'test';
ALTER USER postgres PASSWORD 'test';
And this special psql command
还有这个特殊的 psql 命令
\password
Every time, I returned the pg_hba.conf local entry to md5, and tried to login with psql:
每次都把pg_hba.conf本地入口返回给md5，尝试用psql登录：
psql -U postgres
And then I am asked for a password. After entering 'test', psql gives me the same error as I mentioned earlier.
然后我被要求输入密码。输入 'test' 后，psql 给出了与我之前提到的相同的错误。
And of course, I restarted postgresql after each and every change to the pg_hba file. And I'm using psql with 'su postgres'.
当然，每次更改 pg_hba 文件后，我都重新启动了 postgresql。我将 psql 与 'su postgres' 一起使用。
So, even though I am able to change the password the usual way, it isn't accepted as the password.
因此，即使我能够以通常的方式更改密码，它也不会被接受为密码。
I hope somebody is able to help me with this.
我希望有人能够帮助我解决这个问题。
Some info:
一些信息：
Postgresql 9.1 Ubuntu 12.04
PostgreSQL 9.1 Ubuntu 12.04
Pg_hba file (as requested)
Pg_hba 文件（根据要求）
local all postgres md5
local all all md5
# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5
host all all <my-ip-address>/32 md5
When I wanted to modify the password, I changed the top md5 to trust. I want to mention that this configuration has worked without problems before.
当我想修改密码时，我把最上面的md5改成了trust。我想提一下，这个配置以前没有问题。
The results of
结果
sudo -u postgres psql -x -c "select * from pg_user where usename='postgres'"
Are:
是：
usename | postgres
usesysid | 10
usecreatedb | t
usesuper | t
usecatupd | t
userepl | t
passwd | ********
valuntil | 1970-01-01 00:00:00+01
useconfig |
回答by Daniel Vérité
As shown in the latest edit, the password is valid until 1970, which means it's currently invalid. This explains the error message which is the same as if the password was incorrect.
如最新编辑所示，密码有效期至 1970 年，这意味着它目前无效。这解释了错误消息，就像密码不正确一样。
Reset the validity with:
重置有效性：
ALTER USER postgres VALID UNTIL 'infinity';
In a recent question, another user had the same problem with user accounts and PG-9.2:
在最近的一个问题中，另一个用户在用户帐户和 PG-9.2 上遇到了同样的问题：
PostgreSQL - Password authentication fail after adding group roles
So apparently there is a way to unintentionally set a bogus password validity to the Unix epoch (1st Jan, 1970, the minimum possible value for the abstimetype). Possibly, there's a bug in PG itself or in some client tool that would create this situation.
显然，有一种方法可以无意中为 Unix 时代（1970 年 1 月 1 日，该abstime类型的最小可能值）设置虚假密码有效性。可能是 PG 本身或某些客户端工具中的错误会导致这种情况。
EDIT: it turns out to be a pgadmin bug. See https://dba.stackexchange.com/questions/36137/
编辑：原来是 pgadmin 错误。见https://dba.stackexchange.com/questions/36137/
回答by Pablo Santa Cruz
pg_hba.confentry define login methods by IP addresses. You need to show the relevant portion of pg_hba.confin order to get proper help.
pg_hba.confentry 通过 IP 地址定义登录方法。您需要显示 的相关部分pg_hba.conf才能获得适当的帮助。
Change this line:
改变这一行：
host all all <my-ip-address>/32 md5
To reflect your local network settings. So, if your IP is 192.168.16.78(class C) with a mask of 255.255.255.0, then put this:
反映您的本地网络设置。因此，如果您的 IP 是192.168.16.78（C 类），掩码为255.255.255.0，则输入：
host all all 192.168.16.0/24 md5
Make sure your WINDOWS MACHINE is in that network 192.168.16.0and try again.
确保您的 WINDOWS MACHINE 位于该网络中，192.168.16.0然后重试。
回答by Marcovannoord
I came across this question, and the answers here didn't work for me; i couldn't figure out why i can't login and got the above error.
我遇到了这个问题，这里的答案对我不起作用；我无法弄清楚为什么我无法登录并出现上述错误。
It turns out that postgresql saves usernames lowercase, but during authentication it uses both upper- and lowercase.
事实证明，postgresql 将用户名保存为小写，但在身份验证期间它同时使用大写和小写。
CREATE USER myNewUser WITH PASSWORD 'passWord';
will create a user with the username 'mynewuser' and password 'passWord'.
将使用用户名“mynewuser”和密码“passWord”创建一个用户。
This means you have to authenticate with 'mynewuser', and not with 'myNewUser'. For a newbie in pgsql like me, this was confusing. I hope it helps others who run into this problem.
这意味着您必须使用“mynewuser”而不是“myNewUser”进行身份验证。对于像我这样的 pgsql 新手来说，这很令人困惑。我希望它可以帮助遇到此问题的其他人。
回答by tayfun K?l??
Assuming, that you have root access on the box you can do:
假设您在盒子上拥有 root 访问权限，您可以执行以下操作：
sudo -u postgres psql
If that fails with a database "postgres" does not exists this block.
如果数据库“postgres”失败，则此块不存在。
sudo -u postgres psql template1
Then sudo nano /etc/postgresql/11/main/pg_hba.conf file
然后 sudo nano /etc/postgresql/11/main/pg_hba.conf 文件
local all postgres ident
For newer versions of PostgreSQL ident actually might be peer.
对于较新版本的 PostgreSQL，ident 实际上可能是对等的。
Inside the psql shell you can give the DB user postgres a password:
在 psql shell 中，您可以给 DB 用户 postgres 一个密码：
ALTER USER postgres PASSWORD 'newPassword';
