PostgreSQL创建用户设置密码策略
PostgreSQL默认对创建用户的密码是没有限制。为了安全管控，使用密码策略来增强用户密码的强度。本文以PostgreSQL14的版本为例子介绍两种插件来测试一下。
passwordcheck插件
只要使用 CREATE ROLE 或 ALTER ROLE (CREATE USER ALTER USER)设置用户密码，passwordcheck 模块就会检查用户的密码。如果密码被认为太弱，它将被拒绝并且命令将终止并出现错误。要启用此模块，请将“$libdir/passwordcheck”添加到 postgresql.conf 中的shared_preload_libraries，然后重新启动服务器。(注：使用createuser -P和\password修改密码命令时候是无法检测的)，下面介绍3种验证密码的策略。
默认密码策略
默认密码复杂程度限制：密码长度必须大于8位、必须包含字母和非字母密码、不能包含用户名
简单测试加载passwordcheck插件
cat >> $PGDATA/postgresql.conf <<EOF
shared_preload_libraries = 'passwordcheck'
EOF
pg_ctl restart -D $PGDATA
复制[postgres@centos7 ~]$ cat >> $PGDATA/postgresql.conf <<EOF
shared_preload_libraries = 'passwordcheck'
EOF
[postgres@centos7 ~]$ pg_ctl restart -D $PGDATA
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-08-30 13:18:26.123 CST [74629] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-08-30 13:18:26.124 CST [74629] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-08-30 13:18:26.125 CST [74629] LOG: could not create IPv6 socket for address "::1": Address family not supported by protocol
2023-08-30 13:18:26.126 CST [74629] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-30 13:18:26.128 CST [74636] LOG: database system was shut down at 2023-08-30 13:18:26 CST
2023-08-30 13:18:26.129 CST [74629] LOG: database system is ready to accept connections
done
server started
[postgres@centos7 ~]$
复制创建用户测试，分别用小于8位、只包含字母、包含用户名的密码，最后测试字母加数字大于8位。
psql create user user1 password '123'; create user user1 password 'helloworld'; create user user1 password 'user1user1'; create user user1 password 'abcd1234';复制
[postgres@centos7 ~]$ psql psql (14.9) Type "help" for help. postgres=# create user user1 password '123'; 2023-08-30 13:24:19.837 CST [74708] ERROR: password is too short 2023-08-30 13:24:19.837 CST [74708] STATEMENT: create user user1 password '123'; ERROR: password is too short postgres=# create user user1 password 'helloworld'; 2023-08-30 13:25:01.733 CST [74708] ERROR: password must contain both letters and nonletters 2023-08-30 13:25:01.733 CST [74708] STATEMENT: create user user1 password 'helloworld'; ERROR: password must contain both letters and nonletters postgres=# create user user1 password 'user1user1'; 2023-08-30 13:25:22.737 CST [74708] ERROR: password must not contain user name 2023-08-30 13:25:22.737 CST [74708] STATEMENT: create user user1 password 'user1user1'; ERROR: password must not contain user name postgres=# create user user1 password 'abcd1234'; CREATE ROLE postgres=#复制
设置自定义密码策略
可以通过修改passwordcheck.c源码来实现。密码策略为必须大于8个字符，而且包含大小写字母数字和特殊字符。
源码中主要把原来校验的部分做了修改。分别在26行、92-93行、110-128行。如下：
/*-------------------------------------------------------------------------
*
* passwordcheck.c
*
*
* Copyright (c) 2009-2021, 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"
/* add */
#include "utils/guc.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;
/* modify */
bool pwd_has_upperletter,pwd_has_lowerletter,pwd_has_number,pwd_has_special;
#ifdef USE_CRACKLIB
const char *reason;
#endif
/* enforce minimum length */
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")));
/* modify */
/* check if the password contains upper lower letters and number and specialchar */
pwd_has_upperletter = false;
pwd_has_lowerletter = false;
pwd_has_number = false;
pwd_has_special = false;
for (i = 0; i < pwdlen; i++)
{
/*
* isalpha() does not work for multibyte encodings but let's
* consider non-ASCII characters non-letters
*/
/* modify */
if (isupper((unsigned char) password[i]))
pwd_has_upperletter = true;
else if (islower((unsigned char) password[i]))
pwd_has_lowerletter = true;
else if (isdigit((unsigned char) password[i]))
pwd_has_number = true;
else
pwd_has_special = true;
}
/* modify */
if (!pwd_has_upperletter || !pwd_has_lowerletter || !pwd_has_number || !pwd_has_special)
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("password must contain upper lower letters and number and specialchar")));
#ifdef USE_CRACKLIB
/* call cracklib to check password */
if ((reason = FascistCheck(password, CRACKLIB_DICTPATH)))
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("password is easily cracked"),
errdetail_log("cracklib diagnostic: %s", reason)));
#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;
}
复制到修改后的源码目录，重新编译安装
cd /home/postgres/postgresql-14.9/contrib/passwordcheck
make && make install
pg_ctl restart
复制[postgres@centos7 passwordcheck]$ cd /home/postgres/postgresql-14.9/contrib/passwordcheck
[postgres@centos7 passwordcheck]$ make && make install
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/postgresql-14.9/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[1]: Leaving directory `/home/postgres/postgresql-14.9/src/backend'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I. -I../../src/include -D_GNU_SOURCE -c -o passwordcheck.o passwordcheck.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o passwordcheck.so passwordcheck.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/postgresql-14.9/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[1]: Leaving directory `/home/postgres/postgresql-14.9/src/backend'
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/install -c -m 755 passwordcheck.so '/opt/pg14/lib/postgresql/passwordcheck.so'
[postgres@centos7 passwordcheck]$ pg_ctl restart
waiting for server to shut down....2023-08-30 14:44:14.544 CST [75633] LOG: received fast shutdown request
2023-08-30 14:44:14.544 CST [75633] LOG: aborting any active transactions
2023-08-30 14:44:14.545 CST [75633] LOG: background worker "logical replication launcher" (PID 75646) exited with exit code 1
2023-08-30 14:44:14.545 CST [75641] LOG: shutting down
2023-08-30 14:44:14.547 CST [75633] LOG: database system is shut down
done
server stopped
waiting for server to start....2023-08-30 14:44:14.651 CST [76057] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-08-30 14:44:14.652 CST [76057] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-08-30 14:44:14.653 CST [76057] LOG: could not create IPv6 socket for address "::1": Address family not supported by protocol
2023-08-30 14:44:14.653 CST [76057] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-30 14:44:14.655 CST [76064] LOG: database system was shut down at 2023-08-30 14:44:14 CST
2023-08-30 14:44:14.656 CST [76057] LOG: database system is ready to accept connections
done
server started
[postgres@centos7 passwordcheck]$
复制测试创建用户名，验证有效性
psql create user user2 password 'H1a_'; create user user2 password 'abcd1234'; create user user2 password 'Abcd1234'; create user user2 password 'abcd_1234'; create user user2 password 'user2user2'; create user user2 password 'Abcd_1234';复制
[postgres@centos7 passwordcheck]$ psql psql (14.9) Type "help" for help. postgres=# create user user2 password 'H1a_'; 2023-08-30 14:47:44.507 CST [76099] ERROR: password is too short 2023-08-30 14:47:44.507 CST [76099] STATEMENT: create user user2 password 'H1a_'; ERROR: password is too short postgres=# create user user2 password 'abcd1234'; 2023-08-30 14:48:11.653 CST [76099] ERROR: password must contain upper lower letters and number and specialchar 2023-08-30 14:48:11.653 CST [76099] STATEMENT: create user user2 password 'abcd1234'; ERROR: password must contain upper lower letters and number and specialchar postgres=# create user user2 password 'Abcd1234'; 2023-08-30 14:48:27.469 CST [76099] ERROR: password must contain upper lower letters and number and specialchar 2023-08-30 14:48:27.469 CST [76099] STATEMENT: create user user2 password 'Abcd1234'; ERROR: password must contain upper lower letters and number and specialchar postgres=# create user user2 password 'abcd_1234'; 2023-08-30 14:49:16.935 CST [76099] ERROR: password must contain upper lower letters and number and specialchar 2023-08-30 14:49:16.935 CST [76099] STATEMENT: create user user2 password 'abcd_1234'; ERROR: password must contain upper lower letters and number and specialchar postgres=# create user user2 password 'user2user2'; 2023-08-30 14:49:43.608 CST [76099] ERROR: password must not contain user name 2023-08-30 14:49:43.608 CST [76099] STATEMENT: create user user2 password 'user2user2'; ERROR: password must not contain user name postgres=# create user user2 password 'Abcd_1234'; CREATE ROLE postgres=#复制
配合使用cracklib字典做验证
安装cracklib下载字典网站 点击cracklib-words-20080507.gz上传文件到PostgreSQL服务器，create-cracklib-dict创建字典
yum install -y cracklib*
gunzip cracklib-words-20080507.gz
mv cracklib-words-20080507 /opt/pgdata/
su - postgres
cd /opt/pgdata/
create-cracklib-dict -o cracklib-dict cracklib-words-20080507
ls -l crack*
复制[root@centos7 ~]# yum install -y cracklib*
Loaded plugins: fastestmirror, langpacks
Loading mirror speeds from cached hostfile
base | 3.6 kB 00:00:00
extras | 2.9 kB 00:00:00
updates | 2.9 kB 00:00:00
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
Downloading packages:
(1/2): cracklib-devel-2.9.0-11.el7.x86_64.rpm | 18 kB 00:00:00
(2/2): cracklib-python-2.9.0-11.el7.x86_64.rpm | 25 kB 00:00:00
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Total 147 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@centos7 ~]# pwd
/root
[root@centos7 ~]# ll
total 4420
-rw-------. 1 root root 1836 Mar 7 15:19 anaconda-ks.cfg
-rw-r--r-- 1 root root 4515718 Aug 30 15:52 cracklib-words-20080507.gz
drwxr-xr-x 2 root root 6 Aug 29 22:17 Desktop
drwxr-xr-x 2 root root 6 Aug 29 22:17 Documents
drwxr-xr-x 2 root root 6 Aug 29 22:17 Downloads
-rw-r--r--. 1 root root 1884 Mar 7 15:20 initial-setup-ks.cfg
drwxr-xr-x 2 root root 6 Aug 29 22:17 Music
drwxr-xr-x 2 root root 6 Aug 29 22:17 Pictures
drwxr-xr-x 2 root root 6 Aug 29 22:17 Public
drwxr-xr-x 2 root root 6 Aug 29 22:17 Templates
drwxr-xr-x 2 root root 6 Aug 29 22:17 Videos
[root@centos7 ~]# gunzip cracklib-words-20080507.gz
[root@centos7 ~]# ll
total 16476
-rw-------. 1 root root 1836 Mar 7 15:19 anaconda-ks.cfg
-rw-r--r-- 1 root root 16861960 Aug 30 15:52 cracklib-words-20080507
drwxr-xr-x 2 root root 6 Aug 29 22:17 Desktop
drwxr-xr-x 2 root root 6 Aug 29 22:17 Documents
drwxr-xr-x 2 root root 6 Aug 29 22:17 Downloads
-rw-r--r--. 1 root root 1884 Mar 7 15:20 initial-setup-ks.cfg
drwxr-xr-x 2 root root 6 Aug 29 22:17 Music
drwxr-xr-x 2 root root 6 Aug 29 22:17 Pictures
drwxr-xr-x 2 root root 6 Aug 29 22:17 Public
drwxr-xr-x 2 root root 6 Aug 29 22:17 Templates
drwxr-xr-x 2 root root 6 Aug 29 22:17 Videos
[root@centos7 ~]# mv cracklib-words-20080507 /opt/pgdata/
[root@centos7 ~]# su - postgres
Last login: Wed Aug 30 15:38:03 CST 2023 on pts/0
[postgres@centos7 ~]$ cd /opt/pgdata/
[postgres@centos7 pgdata]$ create-cracklib-dict -o cracklib-dict cracklib-words-20080507
1671686 1671686
[postgres@centos7 pgdata]$ ls -l crack*
-rw-r--r-- 1 postgres dba 1024 Aug 30 15:56 cracklib-dict.hwm
-rw-r--r-- 1 postgres dba 7491003 Aug 30 15:56 cracklib-dict.pwd
-rw-r--r-- 1 postgres dba 417936 Aug 30 15:56 cracklib-dict.pwi
-rw-r--r-- 1 root root 16861960 Aug 30 15:52 cracklib-words-20080507
[postgres@centos7 pgdata]$
复制重新编译passwordcheck插件，修改Markfile第10-11行代码注意修改字典路径
cd /home/postgres/postgresql-14.9/contrib/passwordcheck
vim Makefile
cat Makefile
make clean && make && make install
cd /opt/pgdata/
vim postgresql.conf
tail -10 postgresql.conf
pg_ctl restart
复制[postgres@centos7 passwordcheck]$ vim Makefile
[postgres@centos7 passwordcheck]$ cat 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="/opt/pgdata/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
[postgres@centos7 passwordcheck]$ make clean && make && make install
rm -f passwordcheck.so libpasswordcheck.a libpasswordcheck.pc
rm -f passwordcheck.o passwordcheck.bc
rm -rf results/ regression.diffs regression.out tmp_check/ tmp_check_iso/ log/ output_iso/
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/postgresql-14.9/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[1]: Leaving directory `/home/postgres/postgresql-14.9/src/backend'
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -DUSE_CRACKLIB '-DCRACKLIB_DICTPATH="/opt/pgdata/cracklib_dict"' -I. -I. -I../../src/include -D_GNU_SOURCE -c -o passwordcheck.o passwordcheck.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o passwordcheck.so passwordcheck.o -L../../src/port -L../../src/common -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags -lcrack
make -C ../../src/backend generated-headers
make[1]: Entering directory `/home/postgres/postgresql-14.9/src/backend'
make -C catalog distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/catalog'
make -C utils distprep generated-header-symlinks
make[2]: Entering directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[2]: Nothing to be done for `distprep'.
make[2]: Nothing to be done for `generated-header-symlinks'.
make[2]: Leaving directory `/home/postgres/postgresql-14.9/src/backend/utils'
make[1]: Leaving directory `/home/postgres/postgresql-14.9/src/backend'
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/install -c -m 755 passwordcheck.so '/opt/pg14/lib/postgresql/passwordcheck.so'
[postgres@centos7 passwordcheck]$ cd /opt/pgdata/
[postgres@centos7 pgdata]$ vim postgresql.conf
[postgres@centos7 pgdata]$ tail -10 postgresql.conf
#------------------------------------------------------------------------------
# Add settings for extensions here
shared_preload_libraries = 'passwordcheck'
[postgres@centos7 pgdata]$ pg_ctl restart
waiting for server to shut down....2023-08-30 16:09:25.451 CST [76640] LOG: received fast shutdown request
2023-08-30 16:09:25.452 CST [76640] LOG: aborting any active transactions
2023-08-30 16:09:25.452 CST [76640] LOG: background worker "logical replication launcher" (PID 76653) exited with exit code 1
2023-08-30 16:09:25.452 CST [76648] LOG: shutting down
2023-08-30 16:09:25.456 CST [76640] LOG: database system is shut down
done
server stopped
waiting for server to start....2023-08-30 16:09:25.560 CST [77515] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-08-30 16:09:25.561 CST [77515] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-08-30 16:09:25.562 CST [77515] LOG: could not create IPv6 socket for address "::1": Address family not supported by protocol
2023-08-30 16:09:25.563 CST [77515] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-30 16:09:25.565 CST [77522] LOG: database system was shut down at 2023-08-30 16:09:25 CST
2023-08-30 16:09:25.566 CST [77515] LOG: database system is ready to accept connections
done
server started
[postgres@centos7 pgdata]$
复制测试创建用户名,验证有效性
psql create user user5 password '123'; create user user5 password 'helloworld'; create user user5 password 'user5user5'; create user user5 password 'abcd1234'; create user user5 password 'Abcd1234'; create user user5 password 'qf8hQmSs!9';复制
[postgres@centos7 pgdata]$ psql psql (14.9) Type "help" for help. postgres=# drop user user5; ERROR: role "user5" does not exist postgres=# create user user5 password '123'; ERROR: password is too short postgres=# create user user5 password 'helloworld'; ERROR: password must contain both letters and nonletters postgres=# create user user5 password 'user5user5'; ERROR: password must not contain user name postgres=# create user user5 password 'abcd1234'; ERROR: password is easily cracked postgres=# create user user5 password 'Abcd1234'; ERROR: password is easily cracked postgres=# create user user5 password 'qf8hQmSs!9'; CREATE ROLE postgres=#复制
credcheck插件
credcheck PostgreSQL 扩展提供了一些常规的凭据检查，这些检查将在用户创建、密码更改和用户重命名期间进行评估。通过使用这个扩展，我们可以定义一组规则。更详细的内容可以去作者官网查阅
注意安装这个扩展必须是10以上的版本，最好是12以上版本不然有些功能无法实现
源码安装
git clone https://github.com/MigOpsRepos/credcheck
cd credcheck/
make install
复制[postgres@centos7 ~]$ git clone https://github.com/MigOpsRepos/credcheck
Cloning into 'credcheck'...
remote: Enumerating objects: 352, done.
remote: Counting objects: 100% (91/91), done.
remote: Compressing objects: 100% (63/63), done.
remote: Total 352 (delta 48), reused 62 (delta 28), pack-reused 261
Receiving objects: 100% (352/352), 106.66 KiB | 0 bytes/s, done.
Resolving deltas: 100% (214/214), done.
[postgres@centos7 ~]$ cd credcheck/
[postgres@centos7 credcheck]$ make install
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -I. -I./ -I/opt/pg14/include/postgresql/server -I/opt/pg14/include/postgresql/internal -D_GNU_SOURCE -c -o credcheck.o credcheck.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -fPIC -shared -o credcheck.so credcheck.o -L/opt/pg14/lib -Wl,--as-needed -Wl,-rpath,'/opt/pg14/lib',--enable-new-dtags
/bin/mkdir -p '/opt/pg14/lib/postgresql'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/mkdir -p '/opt/pg14/share/postgresql/extension'
/bin/install -c -m 755 credcheck.so '/opt/pg14/lib/postgresql/credcheck.so'
/bin/install -c -m 644 .//credcheck.control '/opt/pg14/share/postgresql/extension/'
/bin/install -c -m 644 .//updates/credcheck--1.2.0--2.0.0.sql .//updates/credcheck--2.0.0--2.1.0.sql .//updates/credcheck--0.2.0--1.0.0.sql .//updates/credcheck--1.1.0--1.2.0.sql .//updates/credcheck--0.1.0--0.1.1.sql .//updates/credcheck--1.0.0--1.1.0.sql .//updates/credcheck--0.1.1--0.2.0.sql .//credcheck--2.1.0.sql '/opt/pg14/share/postgresql/extension/'
[postgres@centos7 credcheck]$
复制修改postgresql.conf文件，重启数据库
cd /opt/pgdata/
vim postgresql.conf
tail -15 postgresql.conf
pg_ctl restart
复制[postgres@centos7 credcheck]$ cd /opt/pgdata/
[postgres@centos7 pgdata]$ vim postgresql.conf
[postgres@centos7 pgdata]$ tail -15 postgresql.conf
#------------------------------------------------------------------------------
# Add settings for extensions here
shared_preload_libraries = 'credcheck'
credcheck.password_min_length = 8
credcheck.password_min_special = 1
credcheck.password_min_digit = 1
credcheck.password_min_upper = 1
credcheck.password_min_lower = 1
[postgres@centos7 pgdata]$ pg_ctl restart
waiting for server to shut down.... done
server stopped
waiting for server to start....2023-08-30 15:10:00.178 CST [76470] LOG: starting PostgreSQL 14.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
2023-08-30 15:10:00.179 CST [76470] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-08-30 15:10:00.180 CST [76470] LOG: could not create IPv6 socket for address "::1": Address family not supported by protocol
2023-08-30 15:10:00.181 CST [76470] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2023-08-30 15:10:00.183 CST [76477] LOG: database system was shut down at 2023-08-30 15:10:00 CST
2023-08-30 15:10:00.184 CST [76470] LOG: database system is ready to accept connections
done
server started
[postgres@centos7 pgdata]$
复制添加credcheck插件
CREATE EXTENSION credcheck; SHOW shared_preload_libraries; \dx SHOW credcheck.password_min_length;复制
[postgres@centos7 pgdata]$ psql psql (14.9) Type "help" for help. postgres=# CREATE EXTENSION credcheck; CREATE EXTENSION postgres=# SHOW shared_preload_libraries; shared_preload_libraries -------------------------- credcheck (1 row) postgres=# \dx List of installed extensions Name | Version | Schema | Description -----------+---------+------------+------------------------------------------------------ credcheck | 2.1.0 | public | credcheck - postgresql plain text credential checker plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language (2 rows) postgres=# SHOW credcheck.password_min_length postgres-# ; credcheck.password_min_length ------------------------------- 8 (1 row) postgres=#复制
测试创建用户名，验证有效性
psql create user user3 password 'H1a_'; create user user3 password 'abcd1234'; create user user3 password 'Abcd1234'; create user user3 password 'abcd_1234'; create user user3 password 'Abcd_1234';复制
postgres=# create user user3 password 'H1a_'; 2023-08-30 15:25:57.770 CST [76677] ERROR: password length should match the configured credcheck.password_min_length ERROR: password length should match the configured credcheck.password_min_length postgres=# create user user3 password 'abcd1234'; 2023-08-30 15:26:06.064 CST [76677] ERROR: password does not contain the configured credcheck.password_min_upper characters ERROR: password does not contain the configured credcheck.password_min_upper characters postgres=# create user user3 password 'Abcd1234'; 2023-08-30 15:26:15.795 CST [76677] ERROR: password does not contain the configured credcheck.password_min_special characters ERROR: password does not contain the configured credcheck.password_min_special characters postgres=# create user user3 password 'abcd_1234'; 2023-08-30 15:26:25.276 CST [76677] ERROR: password does not contain the configured credcheck.password_min_upper characters ERROR: password does not contain the configured credcheck.password_min_upper characters postgres=# create user user3 password 'Abcd_1234'; CREATE ROLE postgres=#复制
最后修改时间：2023-08-31 10:22:23
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
评论
目录
