Postgres 密码认证失败 [英] Postgres password authentication fails
问题描述
我尝试使用 postgres 用户从我的 Windows 机器登录到我的服务器，并使用 Pgadmin.
I tried to login with the postgres user from my windows machine to my server with Pgadmin.
但它一直给我这个错误:
But it keeps giving me this error:
psql: FATAL: password authentication failed for user "postgres"
然后我尝试使用 psql 从命令行登录，这给了我同样的错误.然后，在将 pg_hba.conf 中的本地条目置于信任之后，我使用 psql 将密码重置为test".然后我将条目放回 md5，并尝试使用密码test"登录.
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 中我使用了这些命令:
In psql I have used these commands:
ALTER ROLE postgres WITH PASSWORD 'test';
ALTER ROLE postgres PASSWORD 'test';
ALTER USER postgres WITH PASSWORD 'test';
ALTER USER postgres PASSWORD 'test';
还有这个特殊的 psql 命令
And this special psql command
password
每次都把pg_hba.conf本地入口返回给md5，尝试用psql登录:
Every time, I returned the pg_hba.conf local entry to md5, and tried to login with psql:
psql -U postgres
然后我被要求输入密码.输入'test'后，psql给了我之前提到的同样的错误.
And then I am asked for a password. After entering 'test', psql gives me the same error as I mentioned earlier.
当然，每次更改 pg_hba 文件后，我都重新启动了 postgresql.我将 psql 与su postgres"一起使用.
And of course, I restarted postgresql after each and every change to the pg_hba file. And I'm using psql with '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.
一些信息:
PostgreSQL 9.1Ubuntu 12.04
Postgresql 9.1 Ubuntu 12.04
Pg_hba 文件(按要求)
Pg_hba file (as requested)
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
当我想修改密码时，我把最上面的md5改成了trust.我想提一下，这个配置以前运行没有问题.
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.
结果
sudo -u postgres psql -x -c "select * from pg_user where usename='postgres'"
是:
usename | postgres
usesysid | 10
usecreatedb | t
usesuper | t
usecatupd | t
userepl | t
passwd | ********
valuntil | 1970-01-01 00:00:00+01
useconfig |
推荐答案
如最新编辑所示，密码有效期到 1970 年，这意味着它目前无效.这解释了错误消息，就像密码不正确一样.
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.
重置有效性:
ALTER USER postgres VALID UNTIL 'infinity';
在最近的一个问题中，另一个用户在用户帐户和 PG-9.2 上遇到了同样的问题:
In a recent question, another user had the same problem with user accounts and PG-9.2:
显然，有一种方法可以无意中为 Unix 纪元(1970 年 1 月 1 日，abstime 类型的最小可能值)设置虚假密码有效性.可能是 PG 本身或某些客户端工具中的错误会导致这种情况.
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 abstime type). Possibly, there's a bug in PG itself or in some client tool that would create this situation.
EDIT:结果证明是 pgadmin 错误.请参阅 https://dba.stackexchange.com/questions/36137/
EDIT: it turns out to be a pgadmin bug. See https://dba.stackexchange.com/questions/36137/
这篇关于Postgres 密码认证失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
