PG用户密码有效期设置

用户密码有效期设置

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

逐个用户修改

--查询密码永不过期的所有用户
select * from pg_user 

--查询用户密码永不过期
postgres=# select * from pg_user where valuntil is null;;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |  passwd  | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+----------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | ******** |          |
(1 row)

或
postgres=# select * from pg_shadow where valuntil is null;;
 usename  | usesysid | usecreatedb | usesuper | userepl | usebypassrls |                                                                passwd
                                            | valuntil | useconfig
----------+----------+-------------+----------+---------+--------------+-------------------------------------------------------------------------------------------
--------------------------------------------+----------+-----------
 postgres |       10 | t           | t        | t       | t            | SCRAM-SHA-256$4096:uyBHGflZaZRYe6DtkM2a6w==$a9oBllAGYw7EqsGIaCiAu5f7IvibtFi1HgTlT4CHPrA=:e
2G0PqIsmpmEb+Hs168UYjLXozYdXnjBtJhjFJSNIqA= |          |
(1 row)

--修改用户密码过期时间
alter user user_name with valid until '2024-10-30 00:00:00';

--修改用户密码永不过期
alter user user_name with valid until 'infinity';

批量修改

--查询密码永不过期的所有用户
select * from pg_user where valuntil is null;

--生成密码永不过期的所有用户的拼接sql
select 'alter user '||usename||'with valid until ''2024-10-30 00:00:00'';' from pg_user where valuntil is null;

--执行上面语句输出的所有语句