用户密码有效期设置
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;
--执行上面语句输出的所有语句