关于postgresql:PostgreSQL-密码验证功能增强

明码验证介绍

 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;
}

​

取得源码

​

[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