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再进入,再修改生效日期才行
[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)
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=>
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.