Postgres密码验证失败 [英] Postgres password authentication fails
问题描述
我尝试使用Pgadmin从Windows机器上的postgres用户登录到服务器.
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.1 Ubuntu 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.
通过以下方式重置有效性:
Reset the validity with:
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.
编辑:原来是一个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屋！
