PostgreSQL密码安全策略[亲测有效]

postgres=# show password_encryption;
password_encryption 
---------------------
md5
(1 row)
postgres=# select * from pg_shadow where usename="test";
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | usec
onfig 
---------+----------+-------------+----------+---------+--------------+-------------------------------------+------------------------+-----
------
test | 49156 | f | f | f | f | md52d308906cb4ea734a22f76e7927c046b | 2019-04-10 16:58:00+08 |

代码100分

代码100分服务器端设置有效期
postgres=# alter role test valid until "2019-04-10 16:58:00";
ALTER ROLE
postgres=# select * from pg_user where usename="test";
usename | usesysid | usecreatedb | usesuper | userepl | usebypassrls | passwd | valuntil | useconfig 
---------+----------+-------------+----------+---------+--------------+----------+------------------------+-----------
test | 49156 | f | f | f | f | ******** | 2019-04-10 16:58:00+08 | 
(1 row)
客户端连接测试
[postgres@pg2 ~]$ date
Wed Apr 10 17:11:49 CST 2019
[postgres@pg2 ~]$ psql -h 192.168.6.12 -U test -d postgres -p 5432
Password for user test: 
psql: FATAL: password authentication failed for user "test"

[pg@pg ~]$ ls -atl $LD_LIBRARY_PATH/passwordcheck*
-rwxr-xr-x 1 pg pg 8640 Feb 1 14:23 /opt/postgres/lib/passwordcheck.so
postgres=# select name,setting from pg_settings where name like "%dynamic%";
name | setting 
----------------------------+---------
dynamic_library_path | $libdir
dynamic_shared_memory_type | posix
(2 rows)
postgres=# alter system set shared_preload_libraries=pg_pathman,pg_stat_statements,passwordcheck;
ALTER SYSTEM
postgres=# 
重启生效

代码100分postgres=# alter role test with password "test";
ERROR: password is too short
postgres=# alter role test password "12345678";
ERROR: password must contain both letters and nonletters
postgres=# alter role test with password "test1234";
ERROR: password must not contain user name
postgres=# alter role test with password "tttt1234";
ALTER ROLE

so文件存储在$libdir下
[pg@pg lib]$ ls -atl $LD_LIBRARY_PATH/auth_delay*
-rwxr-xr-x 1 pg pg 8432 Feb 1 14:23 /opt/postgres/lib/auth_delay.so
参数修改
shared_preload_libraries --预加载模块
auth_delay.milliseconds (int) --指定延迟时间
postgres=# alter system set shared_preload_libraries=pg_pathman, pg_stat_statements, passwordcheck,auth_delay;
ALTER SYSTEM
重启生效
postgres=# alter system set auth_delay.milliseconds=5000;
ALTER SYSTEM
reload生效

[pg@pg ~]$ psql -h 192.168.6.12 -U test -p 5432 -d postgres
Password for user test: 
--5s
psql: FATAL: password authentication failed for user "test"
[pg@pg ~]$
输入密码后，如果密码不正确，会等待5s，然后返回密码失败提示
[pg@pg ~]$ psql -h 192.168.6.12 -U test -p 5432 -d postgres
Password for user test: 
psql (10.4)
Type "help" for help.
postgres=> 
输入密码后，如果密码正确，没有等待。

HISTFILE
The file name that will be used to store the history list. If unset, the file name is taken from the PSQL_HISTORY environment variable. If that is not set either, the default is ~/.psql_history, or %APPDATA%postgresqlpsql_history on Windows. For example, putting:
set HISTFILE ~/.psql_history- :DBNAME
in ~/.psqlrc will cause psql to maintain a separate history for each database.
Note
This feature was shamelessly plagiarized from Bash. --？？！！
csvlog 
数据库错误日志

如以下命令，会记录到HISTFILE和csvlog日志中
postgres=# alter role test with password "tttt1234";
ALTER ROLE
history file记录
[pg@pg ~]$ cat ~/.psql_history |grep tttt1234
alter role test with password "tttt1234";
[pg@pg ~]$ 
csvlog记录
[pg@pg ~]$ cat $PGDATA/postgresql.conf |grep log_statement
#log_statement = "none"         # none, ddl, mod, all
log_statement = "ddl"
#log_statement_stats = off
[pg@pg ~]$ 
[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_092557.csv |grep tttt1234
2019-04-12 09:33:23.036 CST,"pg","postgres",1309,"[local]",5cafeadb.51d,3,"idle",2019-04-12 09:33:15 CST,3/21,0,LOG,00000,"statement: alter role test with password "tttt1234";",,,,,,,,,"psql"

[pg@pg ~]$ createuser -l -h 127.0.0.1 -p 5432 -U pg -W tuser
Password: 
[pg@pg ~]$ 
[pg@pg ~]$ cat $PGDATA/pg_log/postgresql-2019-04-12_092557.csv |grep tuser
2019-04-12 11:17:48.348 CST,"pg","postgres",1574,"localhost:42560",5cb0035c.626,3,"idle",2019-04-12 11:17:48 CST,3/236,0,LOG,00000,"statement: CREATE ROLE tuser NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;",,,,,,,,,"createuser"