cst 中属于flo/emc的功能部分_增强PostgreSQL密码复杂度检查功能

对于用户密码复杂度验证，在Orale中可以通过自定义函数实现。在PG中可以使用passwordcheck.so模块实现同样的功能，不过这个工具的默认要求很低，只可用于简单的密码复杂度校验，默认检查规则如下：

1. 密码长度大于8位

2. 密码不能与用户名相同

3. 密码必须包括字母和数字

如果要增强密码复杂度检查功能，比如密码长度大于15位，必须包括大小写等规则，不象Oracle实现那么简单，但也是可以通过修改源代码实现。本文记录如何通过修改源码passwordcheck.c达到增强复杂度检验的目的，修改后验证规则如下：

1. 密码长度大于15位

2. 密码不能与用户名相同

3. 密码必须包括大小写字母

4. 密码必须包括数字

5. 密码必须包括特殊字符

---

本文实验环境：CentOS7.6 + PG11.8 source code 源码下载地址:  https://www.postgresql.org/ftp/source/v11.8/postgresql-11.8.tar.gz 源码安装文档：https://www.postgresql.org/docs/11/install-short.htm

步骤:

将下载后的源码解压缩, 找到passwordcheck.c源文件，修改后保存退出，如下标黄代码部分为新增或修改，是我们要实现的功能：

tar -xvf postgresql-11.8.tar.gz cd postgresql-11.8/contrib/passwordcheck vim passwordcheck.c PG_MODULE_MAGIC; /* Savedhook value in case of unload */ staticcheck_password_hook_type prev_check_password_hook = NULL; static char*password_special_chars = "!@#$%^&*()_+{}|<>?="; /*passwords shorter than this will be rejected */ #define MIN_PWD_LENGTH 15 #define CONTAINS_LOWER 0x0001 /*Lower-case character */ #define CONTAINS_UPPER 0x0002 /*Upper-case character */ #define CONTAINS_NUMBER 0x0004/* Number */ #define CONTAINS_SPECIAL 0x0008/* Special character */ externvoid _PG_init(void); externvoid _PG_fini(void); ……                 /* check if the passwordcontains both letters and non-letters */                 pwd_has_letter = false;                 pwd_has_nonletter = false;                 for (i = 0; i < pwdlen; i++)                 {                         /*                          * isalpha() does notwork for multibyte encodings but let's                          * consider non-ASCIIcharacters non-letters                          */                         if (isalpha((unsignedchar) password[i]))                                 pwd_has_letter= true;                         else                                pwd_has_nonletter = true;                         if (isupper((unsignedchar) password[i]))                             password_flag |=CONTAINS_UPPER;                         else if(islower((unsigned char) password[i]))                             password_flag |=CONTAINS_LOWER;                         else if(isdigit((unsigned char) password[i]))                             password_flag |=CONTAINS_NUMBER;                         else if(strchr(password_special_chars, (unsigned char) password[i]) != NULL)                             password_flag |=CONTAINS_SPECIAL;                         else                             ereport(ERROR,                                          (errcode(ERRCODE_INVALID_PARAMETER_VALUE),                                          errmsg("password contains invalid characters")));                 }                 if (!pwd_has_letter ||!pwd_has_nonletter)                         ereport(ERROR,                                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),                                         errmsg("password must contain both letters and nonletters")));                 if (!(password_flag &CONTAINS_NUMBER) || !(password_flag & CONTAINS_LOWER) || !(password_flag& CONTAINS_UPPER))                         ereport(ERROR,                                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),                                         errmsg("password must contain both uppercase and lowercase lettersand numbers")));                 if (!(password_flag &CONTAINS_SPECIAL))                         ereport(ERROR,                                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),                                         errmsg("Password must contain at least one specialcharacter.")));

上述修改好源码之后，进行pg server和contrib的编译安装，之后可以在安装目录lib下找到passwordcheck.so。后续可以将这个文件copy到其它同版本的PG中替换，用于实现密码复杂度的要求。

##编译安装pg server ##进入源码解压目录, 执行 cd  build_dir/postgresql-11.8 ./configure--prefix=/u01/pgsql11.8--without-zlib--without-readline   -->>>>指定安装目录为/u01/pgsql11.8, 处于演示目的,所以没有安装zlib和readline包。 make makeinstall ##编译安装contrib,是一些第三方组织贡献出来的工具代码。 ##进入源码解压目录执行 cd  build_dir/postgresql-11.8/contrib make makeinstall ##后续步骤可根据实际需要决定是否执行 adduserpostgres mkdir/usr/local/pgsql/data chownpostgres /usr/local/pgsql/data su -postgres /usr/local/pgsql/bin/initdb-D /usr/local/pgsql/data /usr/local/pgsql/bin/pg_ctl-D /usr/local/pgsql/data -l logfile start /usr/local/pgsql/bin/createdbtest /usr/local/pgsql/bin/psqltest

测试:

首先开启passwordcheck验证, 修改参数文件postgresql.conf

#修改如下

shared_preload_libraries= 'pg_stat_statements,passwordcheck' passwordcheck.level='true

 重启实例生效

pg_ctl restart

从测试结果可以看到满足我们的要求，如下：

postgres=# create role bert with login password 'abc1234567890';              ----->>>>提示密码长度不够2020-07-15 15:06:07.035 CST [36623] ERROR:  password is too short2020-07-15 15:06:07.035 CST [36623] STATEMENT:  create role bert with login password 'abc1234567890';ERROR:  password is too shortpostgres=# create role bert with login password '12345678900000000';       ----->>>>提示密码必须同时包括字母和数字2020-07-15 15:06:18.363 CST [36623] ERROR:  password must contain both letters and nonletters2020-07-15 15:06:18.363 CST [36623] STATEMENT:  create role bert with login password '12345678900000000';ERROR:  password must contain both letters and nonletterspostgres=# create role bert with login password '12345678900000000abc';2020-07-15 15:06:25.526 CST [36623] ERROR:  password must contain both uppercase and lowercase letters and numbers2020-07-15 15:06:25.526 CST [36623] STATEMENT:  create role bert with login password '12345678900000000abc';ERROR:  password must contain both uppercase and lowercase letters and numberspostgres=# alter role bert password 'abcd1234567890000';    ----->>>>>提示密码必须包括大小写2020-07-15 15:11:52.509 CST [36623] ERROR:  password must contain both uppercase and lowercase letters and numbers2020-07-15 15:11:52.509 CST [36623] STATEMENT:  alter role bert password 'abcd1234567890000';ERROR:  password must contain both uppercase and lowercase letters and numberspostgres=# alter user bert password 'Abcd123456789000';     ------>>>>>>提示密码必须包括至少一个特殊字符2020-07-15 15:45:02.200 CST [39324] ERROR:  Password must contain at least one special character.2020-07-15 15:45:02.200 CST [39324] STATEMENT:  alter user bert password 'Abcd123456789000';ERROR:  Password must contain at least one special character.postgres=# alter user bert password 'Abcd>123456789000';ALTER ROLE

延伸：

这里还存在个问题，就是通过\password命令修改的话，可以输入不满足长度的密码，原因是使用\password时passwordcheck检查的是加密后的口令，官方文档提到过，检查md5加密后的口令是很困难的，所以当passwordcheck检查加密的口令时，只检查密码是否与用户名相同这一项，实际上是将用户名通过md5加密后与数据库中的md5密码做比较，如果相同，则报错口令不能与用户名相同。

postgres=#\password bert

Enter new password:   ---->>>>>可以输入小于15位的口令, 而不被阻. 但是输入的是与用户名相同的话可以被检测出来。

Enter itagain:

postgres=#

postgres=# \setECHO_HIDDEN ON

postgres=#\password bert

Enter newpassword:

Enter itagain:

*********QUERY **********

ALTERUSER bert PASSWORD 'md5efabd7549b98ddce9b14ba5e2e83eae1'     

**************************

---

扫码关注更多哦!