PostgreSQL扩展之credcheck

概述

现在国家对数据和系统的安全越来越重视，不定期的对系统进行等保测评和攻防演练已经成为了常态，PostgreSQL强大之处就是有丰富的扩展，credcheck是PostgreSQL的一个扩展，提供了对数据库用户名和密码的一般性的规则检查，这些检查将在创建、修改和重命名用户等操作时评估。通过使用这个扩展，我们可以定义一组规则来允许一组特定的凭证，以及一组规则来拒绝某种类型的凭证。这个扩展是基于PostgreSQL的check_password_hook钩子开发的。PostgreSQL也自带了一个简单密码的检查扩展passwordcheck，但是灵活性不够。credcheck不仅能对密码进行检查，同时还能对用户名检查，此扩展提供所有检查都可以灵活配置。默认配置不会强制执行任何复杂的检查。通过使用SET credcheck.<check-name> TO <some value>;命令，强制执行新的凭据检查设置，也可以在参数文件中做全局配置，例如：

• credcheck.username_min_length = ‘6’
• credcheck.username_min_special = ‘1’
• credcheck.username_min_digit = ‘1’
• credcheck.username_min_upper = ‘2’
• credcheck.username_min_lower = ‘1’
• credcheck.username_min_repeat = ‘2’
• credcheck.username_contain_password = ‘on’
• credcheck.username_contain = ‘a,b,c’
• credcheck.username_not_contain = ‘x,y,z’
• credcheck.username_ignore_case = ‘on’
• credcheck.password_min_length = ‘10’
• credcheck.password_min_special = ‘1’
• credcheck.password_min_digit = ‘1’
• credcheck.password_min_upper = ‘1’
• credcheck.password_min_lower = ‘1’
• credcheck.password_min_repeat = ‘2’
• credcheck.password_contain_username = ‘on’
• credcheck.password_contain = ‘a,b,c’
• credcheck.password_not_contain = ‘x,y,z’
• credcheck.password_ignore_case = ‘on’

安装

• 安装限制：PostgreSQL 10版本及以上；
• 设置pg_config二进制文件在当前的PATH中；
• 下载到目录中，然后运行安装命令：make install；
• 追加credcheck到shared_preload_libraries参数中；
• 然后重启PostgreSQL数据库实例；
• 创建扩展：CREATE EXTENSION credcheck;

参数

例子

--用户名长度至少4位
postgres=# SET credcheck.username_min_length = '4';
SET
postgres=# CREATE USER abc WITH PASSWORD '123456';
ERROR:  username length should match the configured credcheck.username_min_length
postgres=# CREATE USER abcd WITH PASSWORD '123456';
CREATE ROLE

--用户名中至少包括1位特殊字符
postgres=# SET credcheck.username_min_special = '1';
SET
postgres=# DROP USER IF EXISTS abcd;
DROP ROLE
postgres=# CREATE USER abcd WITH PASSWORD '123456';
ERROR:  username does not contain the configured credcheck.username_min_special characters
postgres=# CREATE USER ab_cd WITH PASSWORD '123456';
CREATE ROLE

--用户名中至少包括1位数字
postgres=# SET credcheck.username_min_digit = '1';
SET
postgres=# DROP USER IF EXISTS ab_cd;
DROP ROLE
postgres=# CREATE USER ab_cd WITH PASSWORD '123456';
ERROR:  username does not contain the configured credcheck.username_min_digit characters
postgres=# CREATE USER ab_cd1 WITH PASSWORD '123456';
CREATE ROLE

--用户名中至少包括1位大写字母
postgres=# SET credcheck.username_min_upper = '1';
SET
postgres=# DROP USER IF EXISTS ab_cd1;
DROP ROLE
postgres=# CREATE USER ab_cd1 WITH PASSWORD '123456';
ERROR:  username does not contain the configured credcheck.username_min_upper characters
postgres=# CREATE USER "ab_cd1A" WITH PASSWORD '123456';
CREATE ROLE

--用户名中至少包括1位小写字母
postgres=# SET credcheck.username_min_lower = '1';
SET
postgres=# DROP USER IF EXISTS "ab_cd1A";
DROP ROLE
postgres=# CREATE USER "AB_CD1A" WITH PASSWORD '123456';
ERROR:  username does not contain the configured credcheck.username_min_lower characters
postgres=# CREATE USER "ab_CD1A" WITH PASSWORD '123456';
CREATE ROLE

--用户名中一个字符连续重复的上限
postgres=# SET credcheck.username_min_repeat = '2';
SET
postgres=# DROP USER IF EXISTS "ab_CD1A";
DROP ROLE
postgres=# CREATE USER "ab_CD1AAA" WITH PASSWORD '123456';
ERROR:  username characters are repeated more than the configured credcheck.username_min_repeat times
postgres=# CREATE USER "ab_CD1AA" WITH PASSWORD '123456';
CREATE ROLE

--用户名中不能包含密码
postgres=# SET credcheck.username_contain_password = 'on';
SET
postgres=# DROP USER IF EXISTS "ab_CD1AA";
DROP ROLE
postgres=# CREATE USER "ab_CD123456AA" WITH PASSWORD '123456';
ERROR:  username should not contain password
postgres=# CREATE USER "ab_CD12345AA" WITH PASSWORD '123456';
CREATE ROLE

--用户名中应包含这些字符之一
postgres=# SET credcheck.username_contain = 'a,b,c';
SET
postgres=# DROP USER IF EXISTS "ab_CD12345AA";
DROP ROLE
postgres=# CREATE USER "zx_CD1AA" WITH PASSWORD '123456';
ERROR:  username does not contain the configured credcheck.username_contain characters
postgres=# CREATE USER "ax_CD1AA" WITH PASSWORD '123456';
CREATE ROLE

--用户名中不应包含这些字符
postgres=# SET credcheck.username_not_contain = 'x,y,z';
SET
postgres=# DROP USER IF EXISTS "ax_CD1AA";
DROP ROLE
postgres=# CREATE USER "ax_CD1AA" WITH PASSWORD '123456';
ERROR:  username contains the configured credcheck.username_not_contain unauthorized characters
postgres=# CREATE USER "a_CD1AA" WITH PASSWORD '123456';
CREATE ROLE

--以上例子中的各种判断都是区分字母大小写的，如果想忽略大小写，可以设置以下参数
postgres=# SET credcheck.username_ignore_case = 'on';
SET
postgres=# DROP USER IF EXISTS "a_CD1AA";
DROP ROLE
postgres=# CREATE USER "a_CD1AAa" WITH PASSWORD '123456';
ERROR:  username characters are repeated more than the configured credcheck.username_min_repeat times
postgres=# CREATE USER "aX_CD1AA" WITH PASSWORD '123456';
ERROR:  username contains the configured credcheck.username_not_contain unauthorized characters
postgres=# CREATE USER "X_CD1AA" WITH PASSWORD '123456';
ERROR:  username contains the configured credcheck.username_not_contain unauthorized characters
postgres=# CREATE USER "B_rD1AA" WITH PASSWORD '123456';
CREATE ROLE

--在实际的应用中一般很少对用户名进行一些规则限制，但是对密码的规则限制却很有必要
--重置用户名的相关参数
postgres=# SET credcheck.username_min_length TO DEFAULT;
SET
postgres=# SET credcheck.username_min_special TO DEFAULT;
SET
postgres=# SET credcheck.username_min_upper TO DEFAULT;
SET
postgres=# SET credcheck.username_min_lower TO DEFAULT;
SET
postgres=# SET credcheck.username_min_digit TO DEFAULT;
SET
postgres=# SET credcheck.username_contain_password TO DEFAULT;
SET
postgres=# SET credcheck.username_ignore_case TO DEFAULT;
SET
postgres=# SET credcheck.username_contain TO DEFAULT;
SET
postgres=# SET credcheck.username_not_contain TO DEFAULT;
SET
postgres=# SET credcheck.username_min_repeat TO DEFAULT;
SET

--设置密码最小长度
postgres=# SET credcheck.password_min_length = '6';
SET
postgres=# DROP USER IF EXISTS "B_rD1AA";
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD '12345';
ERROR:  password length should match the configured credcheck.password_min_length
postgres=# CREATE USER test WITH PASSWORD '123456';
CREATE ROLE

--密码必须包括至少1位特殊字符
postgres=# SET credcheck.password_min_special = '1';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD '123456';
ERROR:  password does not contain the configured credcheck.password_min_special characters
postgres=# CREATE USER test WITH PASSWORD '123456@';
CREATE ROLE

--密码必须包括至少1位数字
postgres=# SET credcheck.password_min_digit = '1';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'abcde@';
ERROR:  password does not contain the configured credcheck.password_min_digit characters
postgres=# CREATE USER test WITH PASSWORD 'abcde@1';
CREATE ROLE

--密码必须包括至少1位大写字母
postgres=# SET credcheck.password_min_upper = '1';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'abcde@1';
ERROR:  password does not contain the configured credcheck.password_min_upper characters
postgres=# CREATE USER test WITH PASSWORD 'abcdE@1';
CREATE ROLE

--密码必须包括至少1位小写字母
postgres=# SET credcheck.password_min_lower = '1';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'ABCE@1';
ERROR:  password does not contain the configured credcheck.password_min_lower characters
postgres=# CREATE USER test WITH PASSWORD 'aBCE@1';
CREATE ROLE

--密码中一个字符连续重复的上限
postgres=# SET credcheck.password_min_repeat = '2';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'aaaCE@1';
ERROR:  password characters are repeated more than the configured credcheck.password_min_repeat times
postgres=# CREATE USER test WITH PASSWORD 'aaCE@1';
CREATE ROLE

--密码中不能包含用户名
postgres=# SET credcheck.password_contain_username = 'on';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'aaCE@1test';
ERROR:  password should not contain username
postgres=# CREATE USER test WITH PASSWORD 'aaCE@1tes';
CREATE ROLE

--密码中应包含这些字符之一
postgres=# SET credcheck.password_contain = 'a,b,c';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'xyZ@1tes';
ERROR:  password does not contain the configured credcheck.password_contain characters
postgres=# CREATE USER test WITH PASSWORD 'ayZ@1tes';
CREATE ROLE

--密码中不应包含这些字符
postgres=# SET credcheck.password_not_contain = 'x,y,z';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'ayN@1tes';
ERROR:  password contains the configured credcheck.password_not_contain unauthorized characters
postgres=# CREATE USER test WITH PASSWORD 'abN@1tes';
CREATE ROLE

--以上例子中的密码的各种判断都是区分字母大小写的，如果想忽略大小写，可以设置以下参数
postgres=# SET credcheck.password_ignore_case = 'on';
SET
postgres=# DROP USER IF EXISTS test;
DROP ROLE
postgres=# CREATE USER test WITH PASSWORD 'aYN@1tes';
ERROR:  password contains the configured credcheck.password_not_contain unauthorized characters
postgres=# CREATE USER test WITH PASSWORD 'aaAN@1tes';
ERROR:  password characters are repeated more than the configured credcheck.password_min_repeat times
postgres=# CREATE USER test WITH PASSWORD 'aAN@1tes';
CREATE ROLE
复制

限制

• PostgreSQL 10版本及以上；
• 密码仅适用于纯文本密码，如果任何用户尝试使用ENCRYPTED密码创建用户，则会报告错误；

postgres=# SHOW password_encryption;
 password_encryption 
---------------------
 scram-sha-256
(1 row)
postgres=# CREATE USER test WITH PASSWORD 'SCRAM-SHA-256$4096:41/Qm4fFL+EDbj8ixRlwEg==$U7YbMlWNHrbBIKYTF2d+UJNCHTdHjcaN2l2H43YtSQM=:aYLEFgseI7hmmCf81m6KlMNnHy9eSfTPlToEE4RylHs=';
ERROR:  password type is not a plain text
复制

• 如果创建的用户没有设置密码，用户在创建和重命名时都将不会检查规则

postgres=# SET credcheck.username_min_length = '4';
SET
postgres=# CREATE USER te;  --规则要求用户名至少4位，此处由于创建用户名没有密码，检查规则会忽略
CREATE ROLE
postgres=# ALTER USER te RENAME to abc;  --检查规则也被忽略
ALTER ROLE
postgres=# CREATE USER te WITH PASSWORD '12345678';  --有密码时会检查规则
ERROR:  username length should match the configured credcheck.username_min_length
postgres=# CREATE USER test WITH PASSWORD '12345678';
CREATE ROLE
postgres=# ALTER USER test RENAME TO abc;  --有密码时会检查规则
ERROR:  username length should match the configured credcheck.username_min_length
复制

参考：
https://github.com/MigOpsRepos/credcheck