磐维数据库password_effect_time参数不变，使用户密码永不过期的方案

# 问题描述

需要保证password_effect_time参数值不变（为90天）的情况下保证磐维密码 不过期。

---
# 原理

密码验证的逻辑是：
1. 检查用户名是否正确，无效则输出用户名或密码错误，登录拒绝，有效则继续判断。
2. 检查用户有效性pg_authid.rolvaliduntil（若pg_authid.rolvaliduntil为空则直接登陆成功无输出提示）。无效则输出帐号不在有效期内，有效则继续判断。
3. 从某存储地方取最新设置的密码，比较字符串。无效则输出用户名或密码错误，登录拒绝（达到5次密码错误锁定账户，需要omm用户解锁，但不影响密码有效时间表或用户有效时间表），有效则继续判断。
4. 取pg_auth_history.passwordtime（pg_auth_history表记录历史密码及其有效时间，修改密码会新增该表行）+ password_effect_time中的最大值看是否超过当前日期（若pg_auth_history.passwordtime查询结果为不存在则直接登陆成功但输出注意：距离密码过期还剩0天，请修改密码）。无效则输出密码已经到期，请修改密码（普通用户会登录失败，但omm用户仍会登陆成功），有效则登录成功。

---
# 方案

把用户有效性pg_authid.rolvaliduntil设为infinity无限，用户名对应的pg_auth_history.passwordtime设置成1000年以后，客户就算改密码也不用做额外操作，因为磐维数据库只看最远的有效日期

---
# 测试方案

1. 新建用户testuser

```
CREATE USER testuser WITH PASSWORD 'Test@2024';
CREATE ROLE
```

2. 设置用户有效性pg_authid.rolvaliduntil为infinity（默认未设置时为空，也是无限有效期的效果，可以省略）

```
ALTER USER testuser VALID UNTIL 'infinity';
ALTER ROLE
```

3. 用户名对应的pg_auth_history.passwordtime设置成1000年以后

```
-- 尝试修改 pg_auth_history.passwordtime 为1000年后时间
UPDATE pg_auth_history
SET passwordtime = NOW() + INTERVAL '365000 days'
WHERE roloid = (SELECT oid FROM pg_authid WHERE rolname = 'testuser');
UPDATE 1
```

4. 快进时间到password_effect_time结束（此处为100年）

```
postgres=# SHOW password_effect_time;
password_effect_time
----------------------
36500
(1 行记录)

postgres=# -- 重新检查 pg_authid 和 pg_auth_history
postgres=# SELECT rolname, rolvaliduntil FROM pg_authid WHERE rolname = 'testuser';
rolname | rolvaliduntil
----------+---------------
testuser | infinity
(1 行记录)

postgres=# SELECT roloid, passwordtime FROM pg_auth_history
postgres-# WHERE roloid = (SELECT oid FROM pg_authid WHERE rolname = 'testuser');
roloid | passwordtime
--------+-------------------------------
37111 | 3024-07-01 13:50:47.974046+08
(1 行记录)

[omm@panweidb01 ~]$ date
2025年 02月 28日 星期五 13:56:00 CST
[omm@panweidb01 ~]$ exit
注销
[root@panweidb01 ~]# date -s '2125-03-01 00:00:00'
2125年 03月 01日 星期四 00:00:00 CST
```

5. 测试能否登陆成功（成功，说明普通情况密码时间永久有效）

```
[omm@panweidb01 ~]$ date
2125年 03月 01日 星期四 00:01:01 CST
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2024'
gsql ((PanWeiDB_V2.0-S3.0.1_B01) compiled at 2024-09-29 19:49:44 commit d086caf )
非SSL连接（安全性要求高时，建议使用SSL连接）
输入 "help" 来获取帮助信息。

postgres=>
```

6. 修改用户密码

```
postgres=# ALTER USER testuser WITH PASSWORD 'Test@2025';
ALTER ROLE
```

7. 测试能否登录成功（成功，说明修改密码功能有效）

```
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2025'
gsql ((PanWeiDB_V2.0-S3.0.1_B01) compiled at 2024-09-29 19:49:44 commit d086caf )
非SSL连接（安全性要求高时，建议使用SSL连接）
输入 "help" 来获取帮助信息。

postgres=>
```

8. 再次快进时间到新密码password_effect_time结束（此处为100年）

```
[root@panweidb01 ~]# date
2125年 03月 01日 星期四 00:11:28 CST
[root@panweidb01 ~]# date -s '2225-03-02 00:00:00'
2225年 03月 02日 星期三 00:00:00 CST
```

9. 测试能否登陆成功（成功，说明修改密码后时间依然永久有效）

```
[omm@panweidb01 ~]$ date
2225年 03月 02日 星期三 00:00:33 CST
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2025'
gsql ((PanWeiDB_V2.0-S3.0.1_B01) compiled at 2024-09-29 19:49:44 commit d086caf )
非SSL连接（安全性要求高时，建议使用SSL连接）
输入 "help" 来获取帮助信息。

postgres=>
```

10. 多次输错密码使账户被锁

```
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2026'
gsql: 致命错误: 用户名或密码错误，登录拒绝
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2026'
gsql: 致命错误: 用户名或密码错误，登录拒绝
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2026'
gsql: 致命错误: 用户名或密码错误，登录拒绝
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2026'
gsql: 致命错误: 用户名或密码错误，登录拒绝
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2026'
gsql: 致命错误: 用户名或密码错误，登录拒绝
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2026'
gsql: 致命错误: 账户被锁定
```

11. 用omm用户解锁并查看用户有效期和密码有效期

```
postgres=# ALTER USER testuser ACCOUNT UNLOCK;
ALTER ROLE
postgres=# -- 重新检查 pg_authid 和 pg_auth_history
postgres=# SELECT rolname, rolvaliduntil FROM pg_authid WHERE rolname = 'testuser';
rolname | rolvaliduntil
----------+---------------
testuser | infinity
(1 行记录)

postgres=# SELECT roloid, passwordtime FROM pg_auth_history
postgres-# WHERE roloid = (SELECT oid FROM pg_authid WHERE rolname = 'testuser');
roloid | passwordtime
--------+-------------------------------
37111 | 3024-07-01 13:50:47.974046+08
37111 | 2125-03-01 00:10:03.993114+08
(2 行记录)
```

12. 测试能否登陆成功（成功，说明用户被锁再解锁后时间依然永久有效）

```
[omm@panweidb01 ~]$ gsql -U testuser -W 'Test@2025'
gsql ((PanWeiDB_V2.0-S3.0.1_B01) compiled at 2024-09-29 19:49:44 commit d086caf )
非SSL连接（安全性要求高时，建议使用SSL连接）
输入 "help" 来获取帮助信息。

postgres=>
```