密码验证增强功能主要是在原有密码检查模块的基础上，增加了对密码中是否包含至少一个大小写字母，一个数字和一个特殊字符的判断。

密码验证介绍

passwordcheck 模块是在 CREATE ROLE 或者 CREATE USER 期间检查用户密码是否符合指定的规则模块如果密码比较弱，那么在此期间将会拒绝执行密码并返回一个错误。该模块位于 srcpkg/contrib 目录下，安装后位于 $libdir 目录下，使用 shared_preload_libraries加载并重新启动服务器后生效。在该模块中，主要有两个规则判断，一个是用户名自身的判断，一个是密码长度少于8位的判断,一个是对是否包含用户名本身的判断。

密码验证增强功能

实现



/*-------------------------------------------------------------------------
 *
 * passwordcheck_enchance.c
 *
 * Author: Sungsasong
 *
 * IDENTIFICATION
 *      contrib/passwordcheck_enhance/passwordcheck_enhance.c
 *
 *-------------------------------------------------------------------------
 */
#include "postgres.h"
#include <ctype.h>
#ifdef USE_CRACKLIB
#include <crack.h>
#endif
#include "commands/user.h"
#include "catalog/namespace.h"
#include "libpq/crypt.h"
#include "fmgr.h"
#include "utils/guc.h"
#if PG_VERSION_NUM < 100000
#include "libpq/md5.h"
#endif
PG_MODULE_MAGIC;
/* passwords shorter than this will be rejected */
#define MIN_PWD_LENGTH 8
#define MIN_UPPER_LETTER  1
#define MIN_LOWER_LETTER  1
#define MIN_DIGIT_CHAR    1
#define MIN_SPECIAL_CHAR  1
extern void _PG_init(void);
/**********************************************************************
 *Function:passwordcheck_enhance                                      *
 *Verifying the password at least need contains one upper letter,lower* 
 *letter,digit and specital character                                 *
 *********************************************************************/
#if PG_VERSION_NUM >= 100000
 
static void
    check_password(const char *username,
                   const char *shadow_pass,
                   PasswordType password_type,
                   Datum validuntil_time,
                   bool validuntil_null)    
{        
if(password_type != PASSWORD_TYPE_PLAINTEXT)        
{

            /*
             * Unfortunately we cannot perform exhaustive checks on encrypted
             * passwords - we are restricted to guessing. (Alternatively, we could
             * insist on the password being presented non-encrypted, but that has
             * its own security disadvantages.)
             *
             * We only check for username = password.
             */
            char      *logdetail;
            if(plain_crypt_verify(username, shadow_pass, username,&logdetail)== STATUS_OK)
                ereport(ERROR,
                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                         errmsg("password must not contain user name")));
        }else
        {
            /*
             * For unencrypted passwords we can perform better checks
             */
            const char *password = shadow_pass;
            
int            pwdlen = strlen(password);
            
int            i;
           
// bool        pwd_has_letter,
            
//             pwd_has_nonletter;
            
int PWD_UPPER_LETTER_COUNT  = 0;            
int PWD_LOWER_LETTER_COUNT  = 0;
int PWD_SPECIAL_CHAR_COUNT  = 0;            
int PWD_DIGIT_COUNT         = 0;            
int PWD_CONTAINS_LETTER_COUNT = 0;               
//如果满足至少8位密码的条件，那么判断密码中是否包含至少一个大小写字母和特殊字符
for(i = 0; i < pwdlen; i++)
                
{                    
/* enforce minimum length */
                    
if(pwdlen < MIN_PWD_LENGTH)
                    
{
                       ereport(ERROR,
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                                 errmsg("密码长度至少需要 %d 位，并且至少需要包含一个大小写字母和特殊字符 ",MIN_PWD_LENGTH)));
                    
}
                    
//判断是否包含字母
if(isalpha((unsigned char) password[i]))
 {                 
       PWD_CONTAINS_LETTER_COUNT++;                        
       if(islower((unsigned char) password[i]))
       {
                            PWD_LOWER_LETTER_COUNT++;
                        }else if(isupper((unsigned char) password[i]))
                        {
                            PWD_UPPER_LETTER_COUNT++;
                        }
                    }else if(isdigit((unsigned char) password[i]))
                    {
                        PWD_DIGIT_COUNT++;
                    }else
                    {
                        PWD_SPECIAL_CHAR_COUNT++;
                    }
                }
   //判断是否至少包含了一个数字，大小写字母和特殊字符
                if(PWD_LOWER_LETTER_COUNT < MIN_LOWER_LETTER)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个小写字母",
                                   MIN_LOWER_LETTER)));
                }else if(PWD_UPPER_LETTER_COUNT < MIN_UPPER_LETTER)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个大写字母",
                                   MIN_UPPER_LETTER))); 
               }else if(PWD_DIGIT_COUNT < MIN_DIGIT_CHAR)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个数字",
                                   MIN_DIGIT_CHAR)));
                }else if(PWD_SPECIAL_CHAR_COUNT < MIN_SPECIAL_CHAR)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个特殊字符",
                                   MIN_DIGIT_CHAR)));                       
                }



            /* check if the password contains the username */
            if (strstr(password, username))
            {
                ereport(ERROR,
                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                     errmsg("密码不能与用户名同名")));
            }
        }
 /* all checks passed, password is ok */
    }
#else
    static void
    check_password(const char *username,
                   const char *password,
                   int password_type,
                   Datum validuntil_time,
                   bool validuntil_null)
    {
        int            namelen = strlen(username);
        int            pwdlen = strlen(password);
        char           encrypted[MD5_PASSWD_LEN + 1];
        int            i;
        bool        pwd_has_letter,
                    pwd_has_nonletter;
        int PWD_UPPER_LETTER_COUNT  = 0;
        int PWD_LOWER_LETTER_COUNT  = 0;
        int PWD_SPECIAL_CHAR_COUNT  = 0;
        int PWD_DIGIT_COUNT         = 0;
        int PWD_CONTAINS_LETTER_COUNT = 0;

        switch (password_type)
        {
            case PASSWORD_TYPE_MD5:
                /*
                 * Unfortunately we cannot perform exhaustive checks on encrypted
                 * passwords - we are restricted to guessing. (Alternatively, we
                 * could insist on the password being presented non-encrypted, but
                 * that has its own security disadvantages.)
                 *
                 * We only check for username = password.
                 */
  if (!pg_md5_encrypt(username, username, namelen, encrypted))
                {
                    elog(ERROR, "password encryption failed");
                }
                if (strcmp(password, encrypted) == 0)
                {
                    ereport(ERROR,
                            (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                             errmsg("password must not contain user name")));
                }
                break;

            case PASSWORD_TYPE_PLAINTEXT:
                /*
                 * For unencrypted passwords we can perform better checks
                 */

                /* enforce minimum length */
                //如果满足至少8位密码的条件，那么判断密码中是否包含至少一个大小写字母和特殊字符
                for(i = 0; i < pwdlen; i++)
                {
                    /* enforce minimum length */
                    if(pwdlen < MIN_PWD_LENGTH)
                    {
                       ereport(ERROR,
                                (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                                 errmsg("密码长度至少需要 %d 位，并且至少需要包含一个大小写字母和特殊字符 ",MIN_PWD_LENGTH)));
                    }

 //判断是否包含字母
                    if(isalpha((unsigned char) password[i]))
                    {
                        PWD_CONTAINS_LETTER_COUNT++;
                        if(islower((unsigned char) password[i]))
                        {
                            PWD_LOWER_LETTER_COUNT++;
                        }else if(isupper((unsigned char) password[i]))
                        {
                            PWD_UPPER_LETTER_COUNT++;
                        }
                    }else if(isdigit((unsigned char) password[i]))
                    {
                        PWD_DIGIT_COUNT++;
                    }else
                    {
                        PWD_SPECIAL_CHAR_COUNT++;
                    }
                }
 //判断是否至少包含了一个数字，大小写字母和特殊字符
                if(PWD_LOWER_LETTER_COUNT < MIN_LOWER_LETTER)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个小写字母",
                                   MIN_LOWER_LETTER)));
                }else if(PWD_UPPER_LETTER_COUNT < MIN_UPPER_LETTER)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个大写字母",
                                   MIN_UPPER_LETTER))); 
                }else if(PWD_DIGIT_COUNT < MIN_DIGIT_CHAR)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个数字",
                                   MIN_DIGIT_CHAR)));
                }else if(PWD_SPECIAL_CHAR_COUNT < MIN_SPECIAL_CHAR)
                {
                    ereport(ERROR,
                           (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                            errmsg("密码至少需要包含 %d 个特殊字符",
                                   MIN_DIGIT_CHAR)));                       
                }

            /* check if the password contains the username */
            if (strstr(password, username))
            {
                ereport(ERROR,
                        (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
                     errmsg("密码不能与用户名同名")));
            }
            
                break;

            default:
                elog(ERROR, "unrecognized password type: %d", password_type);
                break;
        }

        /* all checks passed, password is ok */
    }

#endif
/*
 * Module initialization function
 */

void
_PG_init(void)
{
    /* activate password checks when the module is loaded */
    check_password_hook = check_password;
}

PostgreSQL 密码验证功能增强

获得源码



[passwordcheck_enhance](https://github.com/DeveloperHonor/passwordkcheck-enhance-for-postgresql.git "passwordcheck_enhance module")

安装



 *下载源码文件并传入到 PostgreSQL 源码包 contrib 目录下*
    *解压下载的源码包文件*
    `[postgres@sungsasong contrib]$ unzip passwordkcheck-enhance-for-postgresql-main`
    *切换到解压目录*
    *执行 make && make install*
    *在 $PGDATA/postgresql.auto.conf或者 $PGDATA/postgresql.conf文件中加入如下*
    `shared_preload_libraries = 'passwordcheck_enhance`
    *重新启动 PostgreSQL 服务器*

验证



postgres=# CREATE USER user_test WITH PASSWORD 'user';
ERROR:  密码长度至少需要 8 位，并且至少需要包含一个大小写字母和特殊字符 
postgres=# CREATE USER user_test WITH PASSWORD 'useruser';
ERROR:  密码至少需要包含 1 个大写字母
postgres=# CREATE USER user_test WITH PASSWORD 'useruseA';
ERROR:  密码至少需要包含 1 个数字
postgres=# CREATE USER user_test WITH PASSWORD 'useruseA1';
ERROR:  密码至少需要包含 1 个特殊字符
postgres=# CREATE USER user_test WITH PASSWORD 'useruseA1!';
CREATE ROLE

PostgreSQL 密码验证功能增强

密码验证介绍

密码验证增强功能

实现

PostgreSQL 密码验证功能增强

获得源码

安装

验证

继续阅读

postgresql windows下修改帐号密码 (图文)

黑马PostgreSQL，为何一黑到底

pgAdmin III的界面简介

ubuntu系统下如何运行.sh文件ubuntu系统下如何运行.sh文件

【转】PostgreSQL Checkpoint DemystifiedPostgreSQL Checkpoint DemystifiedWhat is a checkpoint?

PostgreSql 数据库杂记

PostgreSQL数据库中匿名块的写法

PG10的函数比PG9.6变化不少

PostgreSQL中的GENERATED COLUMN什么是 GENERATED COLUMN

PostgreSQL HLL插件介绍—晟数学院

PG Cluster 用非5432端口时的登录方式

数据表的基本操作之创建数据表

Centos7下Postgresql源码编译安装

CentOS7离线编译安装PostgreSQL11

Performance best practices for using Azure Database for PostgreSQL

在PostgreSQL中使用PostGIS来创建Geodatabase模型