密码复杂度（passwordcheck插件）
默认密码复杂度规则：
- 密码长度必须大于等于8
- 必须包含字母和非字母
- 密码不能包含用户名
修改密码复杂度规则：
有两种方法。
1.修改passwordcheck.c文件，修改默认的密码复杂度限制
[postgres@mogdb1 passwordcheck]$ cd /u01/soft/pg13source/postgresql-13.3/contrib/passwordcheck/
[postgres@mogdb1 passwordcheck]$ cat passwordcheck.c
/*-------------------------------------------------------------------------
*
* passwordcheck.c
*
*
* Copyright (c) 2009-2020, PostgreSQL Global Development Group
*
* Author: Laurenz Albe <laurenz.albe@wien.gv.at>
*
* IDENTIFICATION
* contrib/passwordcheck/passwordcheck.c
*
*-------------------------------------------------------------------------
*/
#include "postgres.h"
#include <ctype.h>
#ifdef USE_CRACKLIB
#include <crack.h>
#endif
#include "commands/user.h"
#include "fmgr.h"
#include "libpq/crypt.h"
PG_MODULE_MAGIC;
/* Saved hook value in case of unload */
static check_password_hook_type prev_check_password_hook = NULL;
/* passwords shorter than this will be rejected */
#define MIN_PWD_LENGTH 8
extern void _PG_init(void);
extern void _PG_fini(void);
/*
* check_password
*
* performs checks on an encrypted or unencrypted password
* ereport's if not acceptable
*
* username: name of role being created or changed
* password: new password (possibly already encrypted)
* password_type: PASSWORD_TYPE_* code, to indicate if the password is
* in plaintext or encrypted form.
* validuntil_time: password expiration time, as a timestamptz Datum
* validuntil_null: true if password expiration time is NULL
*
* This sample implementation doesn't pay any attention to the password
* expiration time, but you might wish to insist that it be non-null and
* not too far in the future.
*/
static void
check_password(const char *username,
const char *shadow_pass,
PasswordType password_type,
Datum validuntil_time,
bool validuntil_null)
{
if (prev_check_password_hook)
prev_check_password_hook(username, shadow_pass,
password_type, validuntil_time,
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 equal 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;
/* enforce minimum length */--限制密码长度不能太短，默认是8
if (pwdlen < MIN_PWD_LENGTH)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("password is too short")));
/* check if the password contains the username */--限制密码不能包含用户名
if (strstr(password, username))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("password must not contain user name")));
/* check if the password contains both letters and non-letters */--限制密码必须包含字母和非字母
pwd_has_letter = false;
pwd_has_nonletter = false;
for (i = 0; i < pwdlen; i++)
{
/*
* isalpha() does not work for multibyte encodings but let's
* consider non-ASCII characters non-letters
*/
if (isalpha((unsigned char) password[i]))
pwd_has_letter = true;
else
pwd_has_nonletter = true;
}
if (!pwd_has_letter || !pwd_has_nonletter)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("password must contain both letters and nonletters")));
#ifdef USE_CRACKLIB
/* call cracklib to check password */
if (FascistCheck(password, CRACKLIB_DICTPATH))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("password is easily cracked")));
#endif
}
/* all checks passed, password is ok */
}
/*
* Module initialization function
*/
void
_PG_init(void)
{
/* activate password checks when the module is loaded */
prev_check_password_hook = check_password_hook;
check_password_hook = check_password;
}
/*
* Module unload function
*/
void
_PG_fini(void)
{
/* uninstall hook */
check_password_hook = prev_check_password_hook;
}
[postgres@mogdb1 passwordcheck]$
数据库加载passwordcheck插件：
[postgres@mogdb1 ~]$ ll $PGHOME/lib/postgresql/password* -rwxr-xr-x 1 postgres dba 8696 Aug 22 22:41 /u01/pg13/pg/lib/postgresql/passwordcheck.so [postgres@mogdb1 pgdata]$ tail -1 postgresql.conf shared_preload_libraries = 'passwordcheck' [postgres@mogdb1 pgdata]$ pg_ctl restart -D $PGDATA waiting for server to shut down....... done server stopped waiting for server to start....2021-09-03 15:51:59.015 CST [17823] LOG: redirecting log output to logging collector process 2021-09-03 15:51:59.015 CST [17823] HINT: Future log output will appear in directory "log". done server started
密码复杂度测试：
[postgres@mogdb1 pgdata]$ psql psql (13.3) Type "help" for help. postgres=# create user test password 'test'; ERROR: password is too short postgres=# create user test password 'test1234'; ERROR: password must not contain user name postgres=# create user test password 'tesrQWER'; ERROR: password must contain both letters and nonletters postgres=# create user test password '_QQQtred'; CREATE ROLE postgres=# select md5('testtest'); md5 ---------------------------------- 05a671c66aefea124cc08b76ea6d30bb (1 row) postgres=# alter user test password 'md505a671c66aefea124cc08b76ea6d30bb'; ERROR: password must not equal user name postgres=# \q
使用CrackLib字典做复杂的密码校验
这个功能是禁止使用CrackLib字典文件里记录的字符串做密码。
操作系统安装CrackLib以及字典
[root@mogdb1 cracklib]# yum install cracklib*
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
base | 3.6 kB 00:00:00
epel | 4.7 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
(1/3): extras/7/x86_64/primary_db | 243 kB 00:00:00
(2/3): epel/updateinfo | 1.0 MB 00:00:03
(3/3): epel/primary_db | 7.0 MB 00:00:04
Package cracklib-dicts-2.9.0-11.el7.x86_64 already installed and latest version
Package cracklib-2.9.0-11.el7.x86_64 already installed and latest version
Resolving Dependencies
--> Running transaction check
---> Package cracklib-devel.x86_64 0:2.9.0-11.el7 will be installed
---> Package cracklib-python.x86_64 0:2.9.0-11.el7 will be installed
--> Finished Dependency Resolution
Dependencies Resolved
===================================================================================================================================================================================================
Package Arch Version Repository Size
===================================================================================================================================================================================================
Installing:
cracklib-devel x86_64 2.9.0-11.el7 base 18 k
cracklib-python x86_64 2.9.0-11.el7 base 25 k
Transaction Summary
===================================================================================================================================================================================================
Install 2 Packages
Total download size: 43 k
Installed size: 37 k
Is this ok [y/d/N]: y
Downloading packages:
(1/2): cracklib-python-2.9.0-11.el7.x86_64.rpm | 25 kB 00:00:00
(2/2): cracklib-devel-2.9.0-11.el7.x86_64.rpm | 18 kB 00:00:00
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 99 kB/s | 43 kB 00:00:00
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : cracklib-devel-2.9.0-11.el7.x86_64 1/2
Installing : cracklib-python-2.9.0-11.el7.x86_64 2/2
Verifying : cracklib-python-2.9.0-11.el7.x86_64 1/2
Verifying : cracklib-devel-2.9.0-11.el7.x86_64 2/2
Installed:
cracklib-devel.x86_64 0:2.9.0-11.el7 cracklib-python.x86_64 0:2.9.0-11.el7
Complete!
[root@mogdb1 cracklib]#
下载cracklib-words生成字典
字典下载地址, 然后上传到服务器（服务器可以连外网的话，也可以使用wget）
[root@mogdb1 cracklib]# pwd
/u01/cracklib
[root@mogdb1 cracklib]# wget http://downloads.sourceforge.net/project/cracklib/cracklib-words/2008-05-07/cracklib-words-20080507.gz
--2021-09-05 17:38:27-- http://downloads.sourceforge.net/project/cracklib/cracklib-words/2008-05-07/cracklib-words-20080507.gz
Resolving downloads.sourceforge.net (downloads.sourceforge.net)... 204.68.111.105
Connecting to downloads.sourceforge.net (downloads.sourceforge.net)|204.68.111.105|:80... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://pilotfiber.dl.sourceforge.net/project/cracklib/cracklib-words/2008-05-07/cracklib-words-20080507.gz [following]
--2021-09-05 17:38:27-- https://pilotfiber.dl.sourceforge.net/project/cracklib/cracklib-words/2008-05-07/cracklib-words-20080507.gz
Resolving pilotfiber.dl.sourceforge.net (pilotfiber.dl.sourceforge.net)... 69.12.26.12
Connecting to pilotfiber.dl.sourceforge.net (pilotfiber.dl.sourceforge.net)|69.12.26.12|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 4515718 (4.3M) [application/x-gzip]
Saving to: ‘cracklib-words-20080507.gz’
100%[=========================================================================================================================================================>] 4,515,718 716KB/s in 7.8s
2021-09-05 17:38:37 (563 KB/s) - ‘cracklib-words-20080507.gz’ saved [4515718/4515718]
[root@mogdb1 cracklib]# ll
total 4412
-rw-r--r-- 1 root root 4515718 May 8 2008 cracklib-words-20080507.gz
[root@mogdb1 cracklib]# gunzip cracklib-words-20080507.gz
[root@mogdb1 cracklib]# ll
total 16468
-rw-r--r-- 1 root root 16861960 May 8 2008 cracklib-words-20080507
[root@mogdb1 cracklib]# echo "postgresql@1234" >> cracklib-words-20080507
[root@mogdb1 cracklib]# tail -5 cracklib-words-20080507
zzzzzzzz
zzzzzzzzz
zzzzzzzzzz
zzzzzzzzzzzzzzz
postgresql@1234
[root@mogdb1 cracklib]# echo "postgresql@1234" | cracklib-check
postgresql@1234: OK
[root@mogdb1 cracklib]# create-cracklib-dict -o ./cracklib-dict ./cracklib-words-20080507
1671687 1671687
[root@mogdb1 cracklib]# echo "postgresql@1234" | cracklib-check
postgresql@1234: OK
[root@mogdb1 cracklib]# ll
total 24200
-rw-r--r-- 1 root root 1024 Sep 5 17:45 cracklib-dict.hwm
-rw-r--r-- 1 root root 7491216 Sep 5 17:45 cracklib-dict.pwd
-rw-r--r-- 1 root root 417936 Sep 5 17:45 cracklib-dict.pwi
-rw-r--r-- 1 root root 16861976 Sep 5 17:44 cracklib-words-20080507
[root@mogdb1 cracklib]#
重新编译passwordcheck插件：
修改两行代码：
PG_CPPFLAGS = -DUSE_CRACKLIB ‘-DCRACKLIB_DICTPATH="/u01/cracklib/cracklib-dict"’
SHLIB_LINK = -lcrack
[postgres@mogdb1 passwordcheck]$ cd /u01/soft/pg13source/postgresql-13.3/contrib/passwordcheck/
[postgres@mogdb1 passwordcheck]$ ll
total 36
drwxr-xr-x 2 postgres dba 31 May 11 04:52 expected
-rw-r--r-- 1 postgres dba 604 Sep 5 17:56 Makefile
-rw-r--r-- 1 postgres dba 4129 May 11 04:41 passwordcheck.c
-rw-r--r-- 1 postgres dba 4592 Sep 5 18:29 passwordcheck.o
-rwxr-xr-x 1 postgres dba 12832 Sep 5 18:29 passwordcheck.so
drwxr-xr-x 2 postgres dba 31 May 11 04:52 sql
[postgres@mogdb1 passwordcheck]$ vi Makefile
# contrib/passwordcheck/Makefile
MODULE_big = passwordcheck
OBJS = \
$(WIN32RES) \
passwordcheck.o
PGFILEDESC = "passwordcheck - strengthen user password checks"
# uncomment the following two lines to enable cracklib support
PG_CPPFLAGS = -DUSE_CRACKLIB '-DCRACKLIB_DICTPATH="/u01/cracklib/cracklib-dict"'
SHLIB_LINK = -lcrack
REGRESS = passwordcheck
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)
else
subdir = contrib/passwordcheck
top_builddir = ../..
include $(top_builddir)/src/Makefile.global
include $(top_srcdir)/contrib/contrib-global.mk
endif
"Makefile" 24L, 604C written
[postgres@mogdb1 passwordcheck]$ make clean && make && make install
rm -f passwordcheck.so libpasswordcheck.a libpasswordcheck.pc
......
/bin/mkdir -p '/u01/pg13/pg/lib/postgresql'
/bin/install -c -m 755 passwordcheck.so '/u01/pg13/pg/lib/postgresql/passwordcheck.so'
[postgres@mogdb1 pgdata]$ tail -2 postgresql.conf
shared_preload_libraries = 'passwordcheck'
#auth_delay.milliseconds='5000'
[postgres@mogdb1 pgdata]$
修改密码测试：
[postgres@mogdb1 passwordcheck]$ pg_ctl restart waiting for server to shut down.... done server stopped waiting for server to start....2021-09-05 18:32:48.425 CST [27677] LOG: redirecting log output to logging collector process 2021-09-05 18:32:48.425 CST [27677] HINT: Future log output will appear in directory "log". done server started [postgres@mogdb1 passwordcheck]$ psql psql (13.3) Type "help" for help. postgres=# alter user test password 'Ac@153~FDWE#wdsWQds234etfsr'; ALTER ROLE postgres=# alter user test password 'Postgres@12534'; ALTER ROLE postgres=# alter user test password 'Postgres@1234'; ERROR: password is easily cracked postgres=# alter user test password 'Postgres@134'; ALTER ROLE postgres=#
createuser -P命令和\password 命令修改密码时，passwordcheck插件不能正常检测密码的复杂度。
[postgres@mogdb1 pgdata]$ cat postgresql.conf|grep shared_pre
#shared_preload_libraries = '' # (change requires restart)
shared_preload_libraries = 'passwordcheck'
[postgres@mogdb1 pgdata]$ cat postgresql.conf|grep password_encryption
#password_encryption = md5 # md5 or scram-sha-256
password_encryption = scram-sha-256
[postgres@mogdb1 pgdata]$
[postgres@mogdb1 ~]$ createuser -P test1
Enter password for new role:（输入简单密码test）
Enter it again:（输入简单密码test）
[postgres@mogdb1 ~]$
[postgres@mogdb1 log]$ tail -f postgresql-2.csv
[local]",613765a3.139a,2,"authentication",2021-09-07 21:14:11 CST,3/894,0,LOG,00000,"connection authorized: user=postgres database=postgres application_name=createuser",,,,,,,,,"","client backend"
2021-09-07 21:14:11.114 CST,"postgres","postgres",5018,"[local]",613765a3.139a,3,"SELECT",2021-09-07 21:14:11 CST,3/0,0,LOG,00000,"duration: 0.371 ms",,,,,,,,,"createuser","client backend"
2021-09-07 21:14:11.127 CST,"postgres","postgres",5018,"[local]",613765a3.139a,4,"SHOW",2021-09-07 21:14:11 CST,3/0,0,LOG,00000,"duration: 11.905 ms",,,,,,,,,"createuser","client backend"
2021-09-07 21:14:11.132 CST,"postgres","postgres",5018,"[local]",613765a3.139a,5,"idle",2021-09-07 21:14:11 CST,3/898,0,LOG,00000,"statement: CREATE ROLE test1 PASSWORD 'SCRAM-SHA-256$4096:zSUte/llLhqFWJey2KPMkw==$4Xypp4YKuxOP3ukBMlZJ0K3hKuaTxFxUfM6tgx7VSgM=:s3J669j6og3cnWSaIVdvNzkXJJuBCVJ35hRxVUaDTCc=' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;",,,,,,,,,"createuser","client backend"
2021-09-07 21:14:11.144 CST,"postgres","postgres",5018,"[local]",613765a3.139a,6,"CREATE ROLE",2021-09-07 21:14:11 CST,3/0,0,LOG,00000,"duration: 11.755 ms",,,,,,,,,"createuser","client backend"
[postgres@mogdb1 pgdata]$ psql
psql (13.3)
Type "help" for help.
postgres=# \password test1
Enter new password:（输入简单密码test）
Enter it again:（输入简单密码test）
postgres=# \q
[postgres@mogdb1 pgdata]$
[postgres@mogdb1 log]$ tail -f postgresql-2.csv
2021-09-07 21:21:56.888 CST,"postgres","postgres",13500,"[local]",61376767.34bc,4,"idle",2021-09-07 21:21:43 CST,3/915,0,LOG,00000,"statement: ALTER USER test1 PASSWORD 'SCRAM-SHA-256$4096:iGu2df5d9vpDBzR1bZxS9w==$3Sv/TTDtlQ9g2hvtwMYh1sIoUUldOt9A5zvwUKKT6bM=:WkYzu4aqob2FLMTYdzfscvlqGBFOLvhcCfVu9/V9aLs='",,,,,,,,,"psql","client backend"
2021-09-07 21:21:56.905 CST,"postgres","postgres",13500,"[local]",61376767.34bc,5,"ALTER ROLE",2021-09-07 21:21:43 CST,3/0,0,LOG,00000,"duration: 16.851 ms",,,,,,,,,"psql","client backend"
[postgres@mogdb1 pgdata]$ cat postgresql.conf|grep password_encryption
#password_encryption = md5 # md5 or scram-sha-256
password_encryption = 'md5'
[postgres@mogdb1 pgdata]$
[postgres@mogdb1 pgdata]$ createuser -P test2
Enter password for new role:（输入简单密码test）
Enter it again:（输入简单密码test）
[postgres@mogdb1 pgdata]$ psql
psql (13.3)
Type "help" for help.
postgres=# \password test2
Enter new password:（输入简单密码test）
Enter it again:（输入简单密码test）
postgres=# \q
[postgres@mogdb1 pgdata]$
[postgres@mogdb1 log]$ tail -f postgresql-2.csv
2021-09-07 21:26:17.785 CST,,,27677,,61349cd0.6c1d,17,,2021-09-05 18:32:48 CST,,0,LOG,00000,"received SIGHUP, reloading configuration files",,,,,,,,,"","postmaster"
2021-09-07 21:26:17.786 CST,,,27677,,61349cd0.6c1d,18,,2021-09-05 18:32:48 CST,,0,LOG,00000,"parameter ""password_encryption"" changed to ""md5""",,,,,,,,,"","postmaster"
2021-09-07 21:26:27.170 CST,,,18796,"[local]",61376883.496c,1,"",2021-09-07 21:26:27 CST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,"","not initialized"
2021-09-07 21:26:27.171 CST,"postgres","postgres",18796,"[local]",61376883.496c,2,"authentication",2021-09-07 21:26:27 CST,3/926,0,LOG,00000,"connection authorized: user=postgres database=postgres application_name=createuser",,,,,,,,,"","client backend"
2021-09-07 21:26:27.174 CST,"postgres","postgres",18796,"[local]",61376883.496c,3,"SELECT",2021-09-07 21:26:27 CST,3/0,0,LOG,00000,"duration: 0.596 ms",,,,,,,,,"createuser","client backend"
2021-09-07 21:26:27.174 CST,"postgres","postgres",18796,"[local]",61376883.496c,4,"SHOW",2021-09-07 21:26:27 CST,3/0,0,LOG,00000,"duration: 0.105 ms",,,,,,,,,"createuser","client backend"
2021-09-07 21:26:27.174 CST,"postgres","postgres",18796,"[local]",61376883.496c,5,"idle",2021-09-07 21:26:27 CST,3/929,0,LOG,00000,"statement: CREATE ROLE test2 PASSWORD 'md53b46afa69314bf5a2f885a532cfab7c4' NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN;",,,,,,,,,"createuser","client backend"
2021-09-07 21:26:27.180 CST,"postgres","postgres",18796,"[local]",61376883.496c,6,"CREATE ROLE",2021-09-07 21:26:27 CST,3/0,0,LOG,00000,"duration: 5.988 ms",,,,,,,,,"createuser","client backend"
2021-09-07 21:26:43.896 CST,,,19105,"[local]",61376893.4aa1,1,"",2021-09-07 21:26:43 CST,,0,LOG,00000,"connection received: host=[local]",,,,,,,,,"","not initialized"
2021-09-07 21:26:43.897 CST,"postgres","postgres",19105,"[local]",61376893.4aa1,2,"authentication",2021-09-07 21:26:43 CST,3/930,0,LOG,00000,"connection authorized: user=postgres database=postgres application_name=psql",,,,,,,,,"","client backend"
2021-09-07 21:27:00.424 CST,"postgres","postgres",19105,"[local]",61376893.4aa1,6,"SHOW",2021-09-07 21:26:43 CST,3/0,0,LOG,00000,"duration: 0.069 ms",,,,,,,,,"psql","client backend"
2021-09-07 21:27:00.425 CST,"postgres","postgres",19105,"[local]",61376893.4aa1,7,"idle",2021-09-07 21:26:43 CST,3/934,0,LOG,00000,"statement: ALTER USER test2 PASSWORD 'md53b46afa69314bf5a2f885a532cfab7c4'",,,,,,,,,"psql","client backend"
2021-09-07 21:27:00.426 CST,"postgres","postgres",19105,"[local]",61376893.4aa1,8,"ALTER ROLE",2021-09-07 21:26:43 CST,3/0,0,LOG,00000,"duration: 1.178 ms",,,,,,,,,"psql","client backend"
最后修改时间：2021-12-24 22:11:45
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
