PG 插件_passwordcheck

PG 密码复杂度检查插件 passwordcheck

• 简介:PG 自带了插件 passordcheck ,用于 简单密码复杂度校验,防止使用郭丹或者包含用户名的密码

• 配置
  需要修改 share_preload_libraries 添加 $libdir/passwordcheck (如果环境变量有设置,可以使用 $libdir)
  然后重启数据库

  extensions=$(grep shared_preload_libraries  /mnt/syncdata/pgsql/data/postgresql.conf | awk -F"'" '{print $2}')
  new_extensions="${extensions},\$libdir/passwordcheck"
  
  sed -i.bak '/shared_preload_libraries/d' /mnt/syncdata/pgsql/data/postgresql.conf
  cat >> /mnt/syncdata/pgsql/data/postgresql.conf <<EOF
  shared_preload_libraries='${new_extensions}'
  EOF
  
  pgsql_ctl restart
  1
  2
  3
  4
  5
  6
  7
  8
  9

• 使用
  只要使用 create role 或者 create user 来创建用户, passwordcheck 就会检查用户的口令:

• 实例

	postgres=# \du
									   List of roles
	 Role name |                         Attributes                         | Member of 
	-----------+------------------------------------------------------------+-----------
	 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

	postgres=# create user test password 'test';
	ERROR:  password is too sh ort

postgres=# 
	postgres=# create user test password 'passwd';
	ERROR:  password is too short
	postgres=# 
	postgres=# create user test password 'admin_123';
	CREATE ROLE
	postgres=# 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

密码校验延迟插件 auth_delay

auth_delay: 报告认证失败之前等待的毫秒数，缺省是0。上述修改表示：则在一次认证失败后，将延迟5秒中才能继续下一次认证。该选项可以增加暴力破解数据库服务器的密码难度，但它并不能防止拒绝服务攻击，甚至可能恶化它们，因为报告验证失败之前等待的过程将损耗连接槽位。

• 修改配置

extensions=$(grep shared_preload_libraries  /mnt/syncdata/pgsql/data/postgresql.conf | awk -F"'" '{print $2}')
new_extensions="${extensions},auth_delay"


sed -i.bak '/shared_preload_libraries/d' /mnt/syncdata/pgsql/data/postgresql.conf
cat >> /mnt/syncdata/pgsql/data/postgresql.conf <<EOF
shared_preload_libraries='${new_extensions}'
auth_delay.milliseconds=5000
EOF
pgsql_ctl restart
1
2
3
4
5
6
7
8
9
10

• 创建测试用户

[root@localhost db12500_Uniview]# /home/postgres/pgsql/bin/psql -U postgres 
Password for user postgres: 
psql (14.2)
Type "help" for help.

postgres=# create user test3 encrypted password 'test3';
ERROR:  password is too short
postgres=# create user test3 encrypted password 'LXMlxm123';
CREATE ROLE
postgres=# \q
1
2
3
4
5
6
7
8
9
10

• 测试用户登录

[root@localhost db12500_Uniview]# time /home/postgres/pgsql/bin/psql -U test3 -d postgres 
Password for user test3: 
psql (14.2)
Type "help" for help.

postgres=> \q

real    0m2.468s
user    0m0.006s
sys     0m0.001s


[root@localhost db12500_Uniview]# time /home/postgres/pgsql/bin/psql -U test3 -d postgres 
Password for user test3: 
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  password authentication failed for user "test3"

real    0m21.122s
user    0m0.006s
sys     0m0.001s
-bash: history: : numeric argument required
-bash: history: : numeric argument required
[root@localhost db12500_Uniview]# 
```**
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23