1.简单密码策略

PostgreSQL提供了一个插件passwordcheck可以满足简单的密码复杂度测验，防止使用过短，或者与包含用户名的密码。

1.1修改shared_preload_libraries参数
$ vi postgresql.conf
shared_preload_libraries = 'passwordcheck'

1.2重启数据库，使配置生效
$ pg_ctl -D $PGDATA restart

1.3测试SQL
CREATE USER regress_user1;
-- ok
ALTER USER regress_user1 PASSWORD 'a_nice_long_password';
-- error: too short
ALTER USER regress_user1 PASSWORD 'tooshrt';
ERROR:  password is too short
-- error: contains user name
ALTER USER regress_user1 PASSWORD 'xyzregress_user1';
ERROR:  password must not contain user name
-- error: contains only letters
ALTER USER regress_user1 PASSWORD 'alessnicelongpassword';
ERROR:  password must contain both letters and nonletters
-- encrypted ok (password is "secret")
ALTER USER regress_user1 PASSWORD 'md51a44d829a20a23eac686d9f0d258af13';
-- error: password is user name
ALTER USER regress_user1 PASSWORD 'md5e589150ae7d28f93333afae92b36ef48';
ERROR:  password must not equal user name
DROP USER regress_user1;

2.自定义配置复杂策略

2.1修改passwordcheck.c源文件
参考例子
https://github.com/Luckyness/passwordcheck/blob/master/passwordcheck.c

2.2重新编译
$ gmake clean
$ gmake
$ gmake install

2.3配置重启服务
$ vi postgresql.conf
shared_preload_libraries = 'passwordcheck'
passwordcheck.level = 'true'

2.4测试
postgres=# alter role postgres encrypted password 'abcde123';
2020-02-12 16:45:11.210 CST [4763] ERROR:  password must contain both letters and number and specialchar
2020-02-12 16:45:11.210 CST [4763] STATEMENT:  alter role postgres encrypted password 'abcde123';
ERROR:  password must contain both letters and number and specialchar

3.密码验证失败延迟

主要用于防止暴力破解，验证失败后，延迟一个时间窗口才能继续验证。

3.1安装
$ cd /opt/postgresql-12.1/contrib/auth_delay/
$ gmake clean
$ gmake 
$ gmake install

3.2配置
$ vi postgresql.conf
shared_preload_libraries = 'auth_delay,passwordcheck'
auth_delay.milliseconds = 5000

3.3测试
$ /opt/pgsql/bin/psql -h127.0.0.1 -Uregress_user1 postgres
Password for user regress_user1:
密码输入错误后, 需要等待5秒返回认证失败. 防止暴力破解密码.

4.密码有效期

密码更换周期通过设置角色的有效期来强制指定。

4.1	配置
postgres=# alter role postgres valid until '2020-03-01';
4.2	查看用户密码到期时间
postgres=# \du+ postgres
                                          List of roles
 Role name |                         Attributes                         | Member of | Description 
-----------+------------------------------------------------------------+-----------+-------------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS+| {}        | 
           | Password valid until 2020-03-01 00:00:00+08                |           | 

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴进群吹牛唠嗑，交流技术，互赞文章。

如果群二维码失效可以加我微信。