关于连接PostgreSQL时提示 FATAL: password authentication failed for user "连接用户名" 的解决方法

xiayu

今天帮一个同学解决了一个FATAL: password authentication failed for user "连接用户名" 的错误问题，下面说说一下我遇过这个问题的以往排除方法

一、密码忘记了，输入不正确

[postgres @ pgsqldb-master bin]$ psql -h 127.0.0.1 -U postgres
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"

解决方法
1、编辑pg_hba.conf,将md5认证修改成trust认证，编辑后退出保存

[postgres @ pgsqldb-master bin]$ vi ../data/pg_hba.conf

2、执行pg_ctl reload加载生效
[postgres @ pgsqldb-master bin]$ export PGPORT=5432
[postgres @ pgsqldb-master bin]$ export PGDATE=postgres
[postgres @ pgsqldb-master bin]$ export PGDATA=/pgdata/avx/data
[postgres @ pgsqldb-master bin]$ export PGUSER=postgres
[postgres @ pgsqldb-master bin]$ export PATH=/pgdata/avx/bin:$PATH
[postgres @ pgsqldb-master bin]$ pg_ctl reload
server signaled

3、psql连接，用alter role修改密码
[postgres @ pgsqldb-master bin]$ psql
psql (9.2.3)
Type "help" for help.

postgres=# alter role postgres with password '123';
ALTER ROLE
postgres=#

4、退出psql
5、编辑pg_hba.conf,将turst认证修改成md5认证，编辑后退出保存
6、执行pg_ctl reload加载生效

二、密码过期了(今天解决在就是这个),下面来看一下实验

设置用户的密码有效期至2013-01-01

postgres=# alter role postbbs with password '123' valid until '2013-01-01';
ALTER ROLE
postgres=# \pset x on
Expanded display is on.
postgres=# SELECT * FROM pg_roles WHERE rolname='postbbs';
-[ RECORD 1 ]--+-----------------------
rolname | postbbs
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | f
rolcatupdate | f
rolcanlogin | t
rolreplication | f
rolconnlimit | -1
rolpassword | ********
rolvaliduntil | 2013-01-01 00:00:00+08
rolconfig |
oid | 16425

postgres=#

postgres=# \q
[postgres @ pgsqldb-master bin]$ psql -h 127.0.0.1 -U postbbs -d postgres
Password for user postbbs:输入密码123 虽然正确，但不能登录进去
psql: FATAL: password authentication failed for user "postbbs"
[postgres @ pgsqldb-master bin]$

我们现在再修改回来

[postgres @ pgsqldb-master bin]$ psql -h 127.0.0.1 -U postgres
Password for user postgres:
psql (9.2.3)
Type "help" for help.

postgres=# alter role postbbs with valid until 'infinity';
ALTER ROLE
postgres=# \pset x on
Expanded display is on.
postgres=# SELECT * FROM pg_roles WHERE rolname='postbbs';
-[ RECORD 1 ]--+---------
rolname | postbbs
rolsuper | f
rolinherit | t
rolcreaterole | f
rolcreatedb | f
rolcatupdate | f
rolcanlogin | t
rolreplication | f
rolconnlimit | -1
rolpassword | ********
rolvaliduntil | infinity
rolconfig |
oid | 16425

postgres=# \q
[postgres@pgsqldb-master bin]$ psql -h 127.0.0.1 -U postbbs -d postgres
Password for user postbbs:
psql (9.2.3)
Type "help" for help.

postgres=>
postgres=# SELECT rolvaliduntil FROM pg_roles WHERE rolname='postbbs';
rolvaliduntil
---------------
infinity
(1 row)

postgres=#

注
a、alter role postbbs with valid until 'infinity'; 密码永远有效
b、如果所有用户的密码全部过期，这里也需要将认证修改成trust再进入，再修改生效日期才行

另外我发现pgadmin角色管理有一个bug,当密码的有限期设置成空或者 infinity 时，在pgadmin角色管理里面会显示成 1970-1-1 ,如果这时我们用pgadmin角色管理窗口修改其它参数时，则rolvaliduntil 值会变成 1970-1-1，结果退出后就无法进行认证了，所以用pgadmin 角色管理窗口进行参数设置时一定要注意了，例如用窗口设置用户变成管理员，则语句会变成





ALTER ROLE postbbs
SUPERUSER
VALID UNTIL '1970-01-01 00:00:00';



三、采用密码文件认证，但里面的密码是错误的，则会出现如下的提示

[postgres @ pgsqldb-master bin]$ cat /home/postgres/.pgpass
127.0.0.1:5432:*:postgres:1234
[postgres @ pgsqldb-master bin]$ psql -h 127.0.0.1 -U postgres -d postgres
psql: FATAL: password authentication failed for user "postgres"
password retrieved from file "/home/postgres/.pgpass"

解决方法
1、修改.pgpass文件，将密码修改正确，注意，linux下.pgpass的访问权限要设置成 0600

2、如果用户无法修改密码文件，则连接时加-W,强制输入密码

[postgres@pgsqldb-master bin]$ psql -h 127.0.0.1 -U postgres -d postgres -W
Password for user postgres:
psql (9.2.3)
Type "help" for help.

postgres=#

四、低级错误，用户名不存在

[postgres @ pgsqldb-master bin]$ psql -h 127.0.0.1 -U posgres -d postgres -W
Password for user posgres:
psql: FATAL: password authentication failed for user "posgres"
[postgres@pgsqldb-master bin]$ psql -h 127.0.0.1 -U postGres -d postgres -W
Password for user postGres:
psql: FATAL: password authentication failed for user "postGres"

这样的大头虾错误不看清楚往往更难发现，注意用户名也是区分大小写的

五、修改了用户密码采用md5加密的用户名

[postgres@pgsqldb-master bin]$ psql -h 127.0.0.1 -U postgres -d postgres -W
Password for user postgres:
psql (9.2.3)
Type "help" for help.

postgres=# alter role postbbs with ENCRYPTED password '123';
ALTER ROLE
postgres=# alter role postgres with ENCRYPTED password '123';
ALTER ROLE
postgres=# SELECT rolname,rolpassword FROM pg_authid;
rolname | rolpassword
----------+-------------------------------------
postbbs | md5514d208ad0f8842c176b4836992f1cbb
postgres | md59df270eb52907fff723d9b8b7436113a
(2 rows)

密码一样都是 "123" 不同用户名，生成的md5编码也是不样的

postgres=# alter role postbbs rename to postbbs_1;
NOTICE: MD5 password cleared because of role rename
ALTER ROLE
postgres=# SELECT rolname,rolpassword FROM pg_authid;
rolname | rolpassword
-----------+-------------------------------------
postgres | md59df270eb52907fff723d9b8b7436113a
postbbs_1 |
(2 rows)

因为MD5加密的口令使用角色名字作为加密的盐粒，所以， 如果口令是MD5加密的，那么给一个用户改名会清空其口令

更详细的说明 http://www.postgresql.org/docs/9.2/static/sql-alterrole.html

postgres=# \c postgres postbbs_1
Password for user postbbs_1:
FATAL: password authentication failed for user "postbbs_1"
Previous connection kept

由于密码清空，所以也就无法认证

postgres=# alter role postbbs_1 with UNENCRYPTED password '123';
ALTER ROLE
postgres=# SELECT rolname,rolpassword FROM pg_authid;
rolname | rolpassword
-----------+-------------------------------------
postgres | md59df270eb52907fff723d9b8b7436113a
postbbs_1 | 123
(2 rows)

postgres=# alter role postbbs_1 rename to postbbs;
ALTER ROLE
postgres=# SELECT rolname,rolpassword FROM pg_authid;
rolname | rolpassword
----------+-------------------------------------
postgres | md59df270eb52907fff723d9b8b7436113a
postbbs | 123
(2 rows)

postgres=# \c postgres postbbs
Password for user postbbs:
You are now connected to database "postgres" as user "postbbs".
postgres=>

采用明码密码存储的话，修改前后密码保持不变，所以可以认证

所以修改MD5加密认证用户名时应采用这样的做法,这一点本人认为有些不友好，因为原来md5密码我们是不知道明文的

postgres=> \c postgres postgres
Password for user postgres:
You are now connected to database "postgres" as user "postgres".
postgres=# alter role postbbs rename to postbbs_1;
ALTER ROLE
postgres=# alter role postbbs_1 with password '123';
ALTER ROLE
postgres=#

六、一台主机上将了多个不同port的Postgresql服务，连接时指向的port不正确

[postgres @ pgsqldb-master bin]$ psql -h 192.168.1.100 -U postgres -d postgres
Password for user postgres:
psql: FATAL: password authentication failed for user "postgres"

默认的5432 port里面的用户postgres密码不是123

[postgres@pgsqldb-master bin]$ psql -h 192.168.1.100 -U postgres -d postgres -p 9240
Password for user postgres:
psql (9.2.3, server 9.2.4)
Type "help" for help.

postgres=#

port 为9240的用户postgres 密码才是123

这样的错误也是属于比较大头虾的错误