目录
四、复制passwordcheck.so到数据库的$libdir下
概述:
PostgreSQL自带了一个passwordcheck扩展插件(需下载PostgreSQL数据库的源码安装包)可以满足简单的密码复杂度测验, 防止使用过短, 或者与包含用户名的密码,只需要把"$libdir/passwordcheck"加入到postgresql.conf的shared_preload_libraries参数中,然后重启服务器即可。
以下测试使用的是PostgreSQL 13.7,原数据库安装位置/var/lib/pgsql/13/
一、安装cracklib以及字典
下载安装cracklib
[root@localhost ~]# yum install -y cracklib-devel cracklib-dicts cracklib
检查安装
[root@localhost ~]# rpm -ql cracklib-dicts
/usr/lib64/cracklib_dict.hwm
/usr/lib64/cracklib_dict.pwd
/usr/lib64/cracklib_dict.pwi
/usr/sbin/mkdict
/usr/sbin/packer
/usr/share/cracklib/cracklib-small.hwm
/usr/share/cracklib/cracklib-small.pwd
/usr/share/cracklib/cracklib-small.pwi
/usr/share/cracklib/pw_dict.hwm
/usr/share/cracklib/pw_dict.pwd
/usr/share/cracklib/pw_dict.pwi
二、下载PostgreSQL数据库的源码安装包
下载与自己数据库版本一致的PostgreSQL源码,其中包含passwordcheck扩展插件。
本次下载到/opt/src下
#创建新文件夹src
mkdir /opt/src
#进入src文件夹下
cd /opt/src
#下载源码包,在此替换成自己的数据库版本一致的源码包
wget https://ftp.postgresql.org/pub/source/v13.7/postgresql-13.7.tar.bz2
#解压
tar xjvf postgresql-13.7.tar.bz2
#查看文件夹下
[root@localhost src]# ls
postgresql-13.7 postgresql-13.7.tar.bz2
二、配置passwordcheck
#查找cracklib_dict路径
[root@localhost src]# find / -name cracklib_dict*
/usr/lib64/cracklib_dict.hwm
/usr/lib64/cracklib_dict.pwd
/usr/lib64/cracklib_dict.pwi
#进入passwordcheck文件夹下
[root@localhost src]# cd /opt/src/postgresql-13.7/contrib/passwordcheck
[root@localhost passwordcheck]#
#修改Makefile, 把注释去掉, 并修改字典文件(不要带.pwd后缀).
[root@localhost passwordcheck]# vi Makefile
#把下面两行注释去掉
#修改字典文件/usr/lib/cracklib_dict为步骤一安装的cracklib
PG_CPPFLAGS = -DUSE_CRACKLIB '-DCRACKLIB_DICTPATH="/usr/lib64/cracklib_dict"'
SHLIB_LINK = -lcrack
三、编译passwordcheck
#进入/opt/src/postgresql-13.7下应该有configure
[root@localhost passwordcheck]# cd /opt/src/postgresql-13.7
[root@localhost postgresql-13.7]# ls
aclocal.m4 config.status contrib GNUmakefile INSTALL src
config configure COPYRIGHT GNUmakefile.in Makefile
config.log configure.in doc HISTORY README
[root@localhost postgresql-13.7]#
#全部重新编译,大概需要等待5分钟
[root@localhost postgresql-13.7]#./configure --prefix=/opt/pgsql
[root@localhost postgresql-13.7]#gmake world
在此可能出现没有安装readline库和zlib库,若不存在这些库,下载安装库后再次编译即可。
#安装readline库
yum install readline-devel
#安装zlib库
yum install zlib-devel
四、复制passwordcheck.so到数据库的$libdir下
#查询$libdir位置。$libdir是一个环境变量,它用于指定PostgreSQL安装目录中存放库文件的路径。
#在Linux系统中,$libdir通常指定为/usr/lib/postgresql/{version}/lib/
#在命令行中输入以下命令查找$libdir路径:
[root@localhost postgresql-13.7]# pg_config --libdir
/usr/pgsql-13/lib
[root@localhost postgresql-13.7]# cd /usr/pgsql-13/lib
#若pg_config工具未安装,可配置
#查询pg_config文件位置
[root@localhost postgresql-13.7]# find / -name pg_config
find: ‘/proc/32474’: No such file or directory
/usr/pgsql-13/bin/pg_config
/opt/src/postgresql-13.7/src/bin/pg_config
/opt/src/postgresql-13.7/src/bin/pg_config/pg_config
#将/usr/pgsql-13/bin替换为你的pg_config实际所在的路径
export PATH=$PATH:/usr/pgsql-13/bin
#生效
source ~/.bashrc
#测试pg_config是否生效,输入pg_config后有信息显示
pg_config
#复制passwordcheck.so到原数据库的$libdir下
cp /opt/src/postgresql-13.7/contrib/passwordcheck/passwordcheck.so /usr/pgsql-13/lib
[root@localhost lib]# ls
bitcode libecpg.so utf8_and_euc2004.so
cyrillic_and_mic.so libecpg.so.6 utf8_and_euc_cn.so
dict_int.so libecpg.so.6.13 utf8_and_euc_jp.so
dict_snowball.so libpgfeutils.a utf8_and_euc_kr.so
dict_xsyn.so libpgtypes.so.3 utf8_and_euc_tw.so
euc2004_sjis2004.so libpgtypes.so.3.13 utf8_and_gb18030.so
euc_cn_and_mic.so libpq.so.5 utf8_and_gbk.so
euc_jp_and_sjis.so libpq.so.5.13 utf8_and_iso8859_1.so
euc_kr_and_mic.so libpqwalreceiver.so utf8_and_iso8859.so
euc_tw_and_big5.so passwordcheck.so utf8_and_johab.so
latin2_and_win1250.so pgoutput.so utf8_and_sjis2004.so
latin_and_mic.so plpgsql.so utf8_and_sjis.so
libecpg_compat.so.3 utf8_and_big5.so utf8_and_uhc.so
libecpg_compat.so.3.13 utf8_and_cyrillic.so utf8_and_win.so
五、添加passwordcheck扩展,重启数据库
#查看shared_preload_libraries
postgres=# show shared_preload_libraries;
shared_preload_libraries
--------------------------
(1 row)
#配置shared_preload_libraries
postgres=# alter system set shared_preload_libraries='passwordcheck';
ALTER SYSTEM
#重启数据库
[root@localhost passwordcheck]# systemctl restart postgresql-13
[root@localhost passwordcheck]# systemctl status postgresql-13
#查看配置是否生效
postgres=# show shared_preload_libraries;
shared_preload_libraries
--------------------------
passwordcheck
(1 row)
六、新建账户AAA,测试密码复杂度是否生效
postgres=# CREATE USER AAA WITH PASSWORD '123';
ERROR: password is too short
postgres=# CREATE USER AAA WITH PASSWORD '123456';
ERROR: password is too short
postgres=# CREATE USER AAA WITH PASSWORD '12345678';
ERROR: password must contain both letters and nonletters
postgres=# CREATE USER AAA WITH PASSWORD '1234567a';
ERROR: password is easily cracked
postgres=# CREATE USER AAA WITH PASSWORD '123456Aa';
ERROR: password is easily cracked
postgres=# CREATE USER AAA WITH PASSWORD '12345$Aa';
ERROR: password is easily cracked
postgres=# CREATE USER AAA WITH PASSWORD 'D3mNj76F';
CREATE ROLE
七、报错问题
1、编译passwordcheck时,出现
"configure:12156: error: readline library not found If you have readline already installed, see config.log for details on the Use --without-readline to disable readline support. pgac_cv_check_readline=no with_readline='yes'"
"checking for inflate in -lz... no configure: error: zlib library not found If you have zlib already installed, see config.log for details on the failure. It is possible the compiler isn't looking in the proper directory. Use --without-zlib to disable zlib support."
由于没有安装readline库和zlib库,下载安装库后再次编译即可。
#安装readline库
yum install readline-devel
#安装zlib库
yum install zlib-devel
参考:
PostgreSQL扩展之passwordcheck - 墨天轮