密碼驗證增強功能主要是在原有密碼檢查子產品的基礎上，增加了對密碼中是否包含至少一個大小寫字母，一個數字和一個特殊字元的判斷。

密碼驗證介紹

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模型