Postgresql 之 用户密码的有效期设置

作者：瀚高PG实验室 （Highgo PG Lab）- 波罗
测试环境：
服务端 192.168.137.222 HighGoDB v4.1.1 对应PostgreSQL V9.5
客户端 192.168.137.220

1）在主机 192.168.137.222的highgo用户创建测试用户并设置密码过期:

[highgo@hgdb01 ~]$ psql
psql (4.1.1)

PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production

Type "help" for help.
highgo=# create user rep with password '123';' 
highgo=# alter role rep VALID UNTIL '2018-4-11 19:10';
highgo=# \du+ rep
                                   List of roles
 Role name |                 Attributes                  | Member of | Description 
-----------+---------------------------------------------+-----------+-------------
 rep       | Password valid until 2018-04-11 19:10:00+08 | {}        | 

密码到期后, 将无法认证通过，此处必须在客户端去访问数据库服务端验证密码有效期，如果是服务端用户访问不受有效期限制。

在主机192.168.137.220 的highgo用户的psql环境访问另一台的服务端数据库，访问报错。

highgo=#psql -h 192.168.137.222 -U rep highgo
Password for user rep:
psql: FATAL: password authentication failed for user "rep"

2）pg_user或pg_shadow中：
列valuntil值为infinity或空值表示用户密码永不过期；默认为空

在主机 192.168.137.222的highgo用户修改用户密码永不过期:

highgo=# alter user rep with valid until 'infinity';
ALTER ROLE

以下sql 可查看修改后的字段valuntil 值得变化。

highgo=# select * from pg_user;
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 highgo  |       10 | t           | t        | t       | t            | ******** |          | 
 rep     |   131293 | f           | f        | f       | f            | ******** | infinity | 
  
highgo=#  select * from pg_shadow;
 usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls |               passwd                | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+-------------------------------------+--
--------+-----------
 highgo  |       10 | t           | t        | t       | t            | md5ba209e52a1dadc55df77edf498312146 |  
        | 
 rep     |   131293 | f           | f        | f       | f          md5a6f8e01c46849ed88d55e3c347ec318a | infinity | 
(10 rows)

在主机192.168.137.220 的highgo用户的psql环境访问另一台的服务端数据库，正常能访问。

[highgo@hgdb01 ~]$ psql -h 192.168.137.222 -U rep highgo
Password for user rep: 
psql (4.1.1)

PSQL: Release 4.1.1
Connected to:
HighGo Database V4.1 Enterprise Edition Release 4.1.1 - 64-bit Production

Type "help" for help.

highgo=> \dt
         List of relations
 Schema |  Name   | Type  | Owner  
--------+---------+-------+--------
 public | people  | table | highgo
 public | tb1     | table | highgo
 public | toast_t | table | highgo
(3 rows)