PostgreSQL使用passwordcheck扩展通过CrackLib来检查口令 PostgreSQL自带了一个插件passwordcheck可以满足简单的密码复杂度测验, 防止使用过短, 或者与包含用户名的密码，只需要把$libdir/passwordcheck加入到postgresql.conf的shared_preload_libraries参数中，然后重启服务器即可，只要通过CREATE ROLE或ALTER ROLE设置用户，passwordcheck模块就会检查用户的口令。

1 passwordcheck

推荐使用的密码:大小写字母，数字，特殊字符，长度不少于8位

1.1 配置shared_preload_libraries

alter system set shared_preload_libraries='passwordcheck';

[postgres@PGserver2 contrib]$ ls -l pass*
total 28
-rw-r--r--. 1 postgres postgres  570 Aug 10 04:54 Makefile
-rw-r--r--. 1 postgres postgres 4131 Aug 10 04:54 passwordcheck.c
-rw-rw-r--. 1 postgres postgres 3696 Aug 29 00:13 passwordcheck.o
-rwxrwxr-x. 1 postgres postgres 8616 Aug 29 00:13 passwordcheck.so
[postgres@PGserver2 contrib]$ pwd
/home/postgres/postgresql-10.18/contrib

1.2 重启PGserver

[postgres@PGserver2 ~]$ pg_ctl restart -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@PGserver2 ~]$

1.3 测试检查

postgres@[local]:5432=#ALTER USER postgres WITH PASSWORD 'postgres';
ERROR:  password must not contain user name
postgres@[local]:5432=#


postgres@[local]:5432=#ALTER USER postgres WITH PASSWORD 'postgres';
ERROR:  password must not contain user name
postgres@[local]:5432=#ALTER USER postgres WITH PASSWORD 'postgres';
ERROR:  password must not contain user name
postgres@[local]:5432=#ALTER USER postgres WITH PASSWORD 'post';
ERROR:  password is too short
postgres@[local]:5432=#ALTER USER postgres WITH PASSWORD 'yanwei122';
ALTER ROLE
postgres@[loc

1.4 取消之后测试

postgres@[local]:5432=#alter system reset shared_preload_libraries ;
ALTER SYSTEM

[postgres@PGserver2 ~]$ pg_ctl restart -l /tmp/logfile
waiting for server to shut down.... done
server stopped
waiting for server to start.... done
server started
[postgres@PGserver2 ~]$


postgres@[local]:5432=#ALTER USER postgres WITH PASSWORD 'postgres';
ALTER ROLE
postgres@[local]:5432=#

说明可以随意修改了

1.5 源码修改规则

源码修改：需要三种字符的密码规则

本节内容参考：

https://www.cnblogs.com/Luckyness/p/11996834.html

https://github.com/Luckyness/passwordcheck

1.5.1.参考pg_cron的源码在配置文件内增加一个参数

/* 引入扩展 */
#include "utils/guc.h"
……
……
/*
* 配置文件内passwordcheck.level='true' 为需要特殊字符
* passwordcheck.level='false' 为只需要英文和数字
*/
static  bool passwordcheck_level =  false;
……
……
void
_PG_init(void)
{
    /* 定义密码级别参数 */
    DefineCustomBoolVariable(
        "passwordcheck.level",
        gettext_noop("passwordcheck_level true: Password must contain leter, number, special characters;false : Password must contain leter, special characters"),
        NULL,
        &passwordcheck_level,
        false,
        PGC_POSTMASTER,
        GUC_SUPERUSER_ONLY,
        NULL, NULL, NULL);

    /* activate password checks when the module is loaded */
    check_password_hook = check_password;
}

1.5.2.修改源码配置校验数字

        if(passwordcheck_level)
        {
            /* check if the password contains both letters and number and specialchar */
            pwd_has_number =  false;
            pwd_has_special =  false;
            pwd_has_letter =  false;
            for (i =  0; i < pwdlen; i++)
            {
                if (isalpha((unsigned  char) password[i]))
                    pwd_has_letter =  true;
                else  if (isdigit((unsigned  char) password[i]))
                    pwd_has_number =  true;
                else
                    pwd_has_special =  true;
            }
            if (!pwd_has_number ||  !pwd_has_letter ||  !pwd_has_special)
                ereport(ERROR,
                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                        errmsg("password must contain both letters and number and specialchar")));
        }
        else
        {
            /* check if the password contains both letters and non-letters */
            pwd_has_letter =  false;
            pwd_has_number =  false;
            for (i =  0; i < pwdlen; i++)
            {
                if (isalpha((unsigned  char) password[i]))
                    pwd_has_letter =  true;
                else
                    pwd_has_number =  true;
            }
            if (!pwd_has_letter ||  !pwd_has_number)
                ereport(ERROR,
                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                        errmsg("password must contain both letters and nonletters")));
        }

1.5.3 使用方式

替换目录 ../postgresql-11.4/contrib/passwordcheck 下的 passwordcheck.c
编译安装 make && make install
postgresql配置文件内修改 (postgresql.conf)
shared_preload_libraries = 'passwordcheck'
passwordcheck.level = 'true'