PostgreSQL创建用户设置密码策略
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=#
免责声明：
1、本站资源由自动抓取工具收集整理于网络。
2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。
3、电子书、小说等仅供网友预览使用，书籍版权归作者或出版社所有。
4、如作者、出版社认为资源涉及侵权，请联系本站，本站将在收到通知书后尽快删除您认为侵权的作品。
5、如果您喜欢本资源，请您支持作者，购买正版内容。
6、资源失效，请下方留言，欢迎分享资源链接
文章评论