对于用户密码复杂度验证,在Orale中可以通过自定义函数实现。在PG中可以使用passwordcheck.so模块实现同样的功能,不过这个工具的默认要求很低,只可用于简单的密码复杂度校验,默认检查规则如下:
密码长度大于8位
密码不能与用户名相同
密码必须包括字母和数字
如果要增强密码复杂度检查功能,比如密码长度大于15位,必须包括大小写等规则,不象Oracle实现那么简单,但也是可以通过修改源代码实现。本文记录如何通过修改源码passwordcheck.c达到增强复杂度检验的目的,修改后验证规则如下:
密码长度大于15位
密码不能与用户名相同
密码必须包括大小写字母
密码必须包括数字
密码必须包括特殊字符
本文实验环境: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'
**************************
![b0f12d9a308f239d775e22d2975d6fd5.png](https://i-blog.csdnimg.cn/blog_migrate/4b1c36116fd9b2b5cab39127183ad164.jpeg)