磐维数据库用户、密码有效期测试

环境信息

1.用户有效期限

• 创建用户时，可以通过VALID BEGIN，VALID UNTIL设置用户有效期。这个在有效期内，用户可正常使用，超过有效期，用户将无法登陆。
• 1）创建用户并指定有效期

postgres=# CREATE USER u2 WITH PASSWORD 'xxxxxx' VALID BEGIN '2024-11-21 08:00:00' VALID UNTIL '2024-12-11 23:00:00';
CREATE ROLE
postgres=# select rolname,rolpassword,rolvalidbegin,rolvaliduntil from pg_roles where rolname='u2';
 rolname | rolpassword |     rolvalidbegin      |     rolvaliduntil      
---------+-------------+------------------------+------------------------
 u2      | ********    | 2024-11-21 08:00:00+08 | 2024-12-11 23:00:00+08
(1 row)
postgres=# \du u2
                          List of roles
 Role name |               Attributes                | Member of 
-----------+-----------------------------------------+-----------
 u2        | 100 connections                        +| {}
           | Role valid begin 2024-11-21 08:00:00+08+| 
           | Role valid until 2024-12-11 23:00:00+08 | 

• 2）修改主机时间

[root@pwdb2 ~]# date -s '2024-12-11 23:05:00'
Wed Dec 11 23:05:00 CST 2024

• 3）u2 用户登录，提示账户不在有效期内

[omm@pwdb2 ~]$ gsql -U u2
Password for user u2: 
gsql: FATAL:  The account is not within the period of validity.

2.密码有效期限

• 数据库用户的密码都有密码有效期 password_effect_time，当达到密码到期提醒天数 password_notify_time 时，系统会在用户登录数据库时提示用户修改密码

2.1 查看参数

• 1）登录数据库，查看password_effect_time参数配置，该参数单位天

postgres=# show password_effect_time;
 password_effect_time 
----------------------
90
(1 row)

• 2）查看password_notify_time参数配置，该参数单位天

postgres=# show password_notify_time;
 password_notify_time 
----------------------
 7
(1 row)

2.2 password_effect_time过期测试

• 1）修改参数

[omm@pwdb2 ~]$ gs_guc reload -I all -N all -c "password_effect_time=30"

• 2）查看u1用户和omm用户的密码过期时间

postgres=# select b.usename,a.passwordtime,pg_catalog.current_setting('password_effect_time'),a.passwordtime+numtodsinterval(to_number((select setting from pg_settings where name='password_effect_time')),'DAY') as passwordexpiredtime  from 
postgres-# (select roloid,max(passwordtime) as passwordtime from pg_catalog.pg_auth_history group by roloid) a 
postgres-# right join 
postgres-# (select usename,usesysid from pg_user) b 
postgres-# on a.roloid=b.usesysid where b.usename in ('omm','u1');
 usename |         passwordtime          | current_setting |      passwordexpiredtime      
---------+-------------------------------+-----------------+-------------------------------
 omm     | 2024-11-21 17:51:45.752509+08 | 30              | 2024-12-21 17:51:45.752509+08
 u1      | 2024-11-21 18:28:40.728541+08 | 30              | 2024-12-21 18:28:40.728541+08 
(2 rows)

• 3）修改主机时间

[root@pwdb2 ~]# date -s '2024-12-23'
Tue Dec 23 00:00:00 CST 2024

• 4）U1登录数据库，提示密码过期：

[omm@pwdb2 ~]$ gsql -U u1 
Password for user u1: 
gsql: FATAL:  The password has been expired, please change the password.

• 5）omm用户提示密码过期，依然可以登录，且能正常进行数据库操作

[omm@pwdb2 ~]$ gsql -r
NOTICE:  The password has been expired, please change the password.
gsql ((PanWeiDB_V2.0-S3.0.1_B01) compiled at 2024-09-29 19:37:43 commit d086caf )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# select now();
              now              
-------------------------------
 2024-12-23 00:03:33.378791+08
(1 row)

2.3 password_notify_time测试

• 设置了 password_notify_time=7，即密码到期7天内，登录会打印提示：X days left before password expired, please change the password.
• 1）修改主机时间

[root@pwdb2 ~]# date -s "2024-12-16"
Mon Dec 16 00:00:00 CST 2024

• 2）用户登录会提示还有几天密码过期

[omm@pwdb2 ~]$ gsql -U u1 
Password for user u1: 
NOTICE:   6 days left before password expired, please change the password.
gsql ((PanWeiDB_V2.0-S3.0.1_B01) compiled at 2024-09-29 19:37:43 commit d086caf )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=>