openGauss维护管理之密码有效期

一、概述

opengauss用户可以创建的时候设置有效期，也可以通过配置设置有效期

二、试验

1、创建有效期的用户

1、创建用户并设置有效期
CREATE USER joe WITH PASSWORD 'Bigdata@123' VALID BEGIN '2015-10-10 08:00:00' VALID UNTIL '2016- 10-10 08:00:00';
2、更改用户有效期
ALTER USER joe WITH VALID BEGIN '2016-11-10 08:00:00' VALID UNTIL '2017-11-10 08:00:00';

 注意：若在“CREATE ROLE”或“ALTER ROLE”语法中不指定“VALID BEGIN”，表示不对 用户的开始操作时间做限定；
 若不指定“VALID UNTIL”，表示不对用户的结束操作时间做限 定；若两者均不指定，表示该用户一直有效。

3、使用过期账户登录会咋样
[omm@gsdb01 dn01]$ gsql -p 26000 -r -U joe1 -W Bigdata@123
gsql: FATAL:  The account is not within the period of validity. #账户不在有效期内

2、探讨配置参数影响

opengauss配置文件中还有个参数影响密码有效期
vi /opt/huawei/install/data/dn01/postgresql.conf
#password_effect_time = 90d              #The password effect time(0-999)
#password_notify_time = 7d               #The password notify time(0-999)

1、默认情况下会咋样
show password_effect_time; #结果也是90，也就是默认注释的情况下，密码有效期依旧是90
2、更改linux虚拟机的时间
首先把时间同步停掉
systemctl stop ntpd
date -s '20230628' #时间调整到三个月以后
su - omm
gs_om -t restart #重启数据库
使用用户登录
[omm@gsdb01 ~]$ gsql -d test_db -p 26000 -r -U test -W test@123
gsql ((openGauss 3.1.1 build 70980198) compiled at 2023-01-06 09:27:09 commit 0 last mr  )
NOTICE : The password has been expired, please change the password.
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
#确实有提醒密码已经过期，请修改密码，但是数据库依旧可以增删改查不受影响

3、更改参数
vi /opt/huawei/install/data/dn01/postgresql.conf
password_effect_time = 0 #The password effect time(0-999)
重启数据库
gs_om -t restart
[omm@gsdb01 ~]$ gsql -d test_db -p 26000 -r -U test -W test@123
gsql ((openGauss 3.1.1 build 70980198) compiled at 2023-01-06 09:27:09 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help
#可以正常登陆，没有任何提醒了

3、补充配置参数修改大坑

1、默认配置参数
vi /opt/huawei/install/data/dn01/postgresql.conf
#password_effect_time = 90d              #The password effect time(0-999)
#password_notify_time = 7d               #The password notify time(0-999)
2、将注释取消掉并重启
password_effect_time = 90d              #The password effect time(0-999)
重启服务
gs_om -t restart
直接报错：
[GAUSS-53600]: Can not start the database, the cmd is source /home/omm/.bashrc; python3 '/opt/huawei/install/om/script/local/StartInstance.py' -U omm -R /opt/huawei/install/app -t 300 --security-mode=off,  Error:
[FAILURE] gsdb01:
[GAUSS-51607] : Failed to start instance. Error: Please check the gs_ctl log for failure details.
[2023-03-28 18:23:54.557][2977990][][gs_ctl]: gs_ctl started,datadir is /opt/huawei/install/data/dn01
[2023-03-28 18:23:54.581][2977990][][gs_ctl]: waiting for server to start...
.0 LOG:  [Alarm Module]can not read GAUSS_WARNING_TYPE env.
0 LOG:  [Alarm Module]Host Name: gsdb01
0 LOG:  [Alarm Module]Host IP: gsdb01. Copy hostname directly in case of taking 10s to use 'gethostbyname' when /etc/hosts does not contain <HOST IP>
0 LOG:  [Alarm Module]Cluster Name: dbCluster
0 LOG:  [Alarm Module]Invalid data in AlarmItem file! Read alarm English name failed! line: 57
0 WARNING:  failed to open feature control file, please check whether it exists: FileName=gaussdb.version, Errno=2, Errmessage=No such file or directory.
0 WARNING:  failed to parse feature control file: gaussdb.version.
0 WARNING:  Failed to load the product control file, so gaussdb cannot distinguish product version.
0 LOG:  bbox_dump_path is set to /opt/huawei/corefile/
 0 [BACKEND] LOG:  parameter "password_effect_time" requires a numeric value
 0 [BACKEND] LOG:  invalid value for parameter "password_notify_time": "7d"
2023-03-28 18:23:54.632 6422c03a.10000 [unknown] 140338669405760 [unknown] 0 dn_6001 F0000  0 [BACKEND] FATAL:  configuration file "/opt/huawei/install/data/dn01/postgresql.conf" contains errors
[2023-03-28 18:23:55.582][2977990][][gs_ctl]: waitpid 2977993 failed, exitstatus is 256, ret is 2
[2023-03-28 18:23:55.582][2977990][][gs_ctl]: stopped waiting
[2023-03-28 18:23:55.582][2977990][][gs_ctl]: could not start server
Examine the log output..

3、报错原因
password_effect_time = 90 #千万不要加d，加了就上面错误
去掉d可以正常重启
gs_om -t restart