PostgreSQL 为适配等保测评要求的安全性配置方法汇总（Linux&Windows）

最近公司一个项目进行等保测评过程中，对PostgreSQL数据提出了几点改进要求，如下：

1）未配置密码复杂度策略、口令未定期更换

        修改建议：建议密码至少8位,需大小写字母数字、特殊符号两种及以上组成并且不为常见密码，密码有效期90天

2）未配置配置登陆失败处理功能

        修改建议：建议配置登录错误6次锁定账户10分钟，超时 10分钟自动退出。

3)  默认账户未重命名

        修改建议：建议重命名默认账户

针对以上几条要求，从网上查找资料以及自己的尝试，基本解决了问题。解决的办法记录如下：

---

1、重命名默认账户并设置密码有效期90天（Windows&Linux通用）

（1）登陆navicat，创建查询，建立一个其他超级用户postgres1

CREATE USER postgres1 WITH
LOGIN
SUPERUSER
INHERIT
CREATEDB
CREATEROLE
REPLICATION;
ALTER USER postgres1 WITH PASSWORD '你希望设置的密码';

（2）用postgres1登陆navicat，创建查询，修改postgres名称

ALTER USER postgres RENAME TO admins;

用以下语句生成自动延期90天的修改语句

select $$ alter user admins with password '你希望设置的密码' 
valid until '$$||now()::timestamp(0) + '90 day'||$$';$$;

注意，上面这个语句并没有执行修改操作，只是生成了延期的语句，需要拷贝生成的语句，执行后才会真正修改密码！

（3）删除postgres1

DROP USER postgres1;

---

2、密码至少8位，需大小写字母数字、特殊符号两种及以上组成并且不为常见密码（Windows&Linux通用）

这个分两部分，除了判断不常见密码的其他功能，只需要配置postgresql.conf文件，找到shared_preload_librarie行进行修改，添加passwordcheck库的引用即可

shared_preload_libraries = 'passwordcheck'

注意去掉编码行之前的#

重启数据库，创建账号测试

create user abc password 'abc';
create user abc password 'abc123';
create user abc password 'aaa123456';

增加常见密码库，参见下方链接，需要重新编译passwordcheck，比较麻烦，如有必要再尝试

【数据库】PostgreSQL增加密码复杂度校验_postgresql14数据库密码策略校验-CSDN博客

---

3、超时10分钟自动退出（14版本以下Windows和Linux不通用）

这个需要根据PostgreSQL的版本选择处理方式：

14版本及以上：

可直接配置postgresql.conf, 找到idle_session_timeout行进行修改，注意去掉编码行之前的#，数值单位为毫秒

idle_session_timeout = 600000

14版本以下：

需要安装pg_timeout库，安装方法分windows和linux两种方案：

1）Linux 安装pg_timeout

        拷贝下载好的pg_timeout源代码文件夹到安装路径下，修改makefile中的PG_CONFIG = /postgreSQL安装路径/bin/pg_config， 命令行执行编译并安装 make | make install 即可

2）Windows安装pg_timeout

       步骤一，下载当前使用的PostgreSQL的源代码包，并将pg_timeout源代码文件夹拷贝到PostgreSQL源码的\contrib目录下

        步骤二，打开VS提供的命令行工具（注意，你要编译的是X86还是X64需要选择对应名字的命令行工具，参见下图，要与实际在用的库一致别选错了，否则编译出来的pg_timeout不好用）

从命令行进入src\tools\msvc 文件夹下，命令行执行 build.bat 然后耐心等待PostgreSQL编译完成（需要安装VS环境，以及C、C++、windows SDK啥的，才能正常编译源码，这部分不展开讲了，有问题上网查）

        步骤三，编译成功后，在release目录下能够找到伴随编译的pg_timeout文件夹，从中拷贝出来编译后的dll文件，拷贝到项目中的库中即可。

        另外，如果出现运行插件提示USE_FLOAT8_BYVAL兼容性问题，那么需要修改src\tools\msvc\config_default.pl，增加

float8byval=>1,              # --disable-float8-byval, off by default

这样的配置，然后重新编译，再进行尝试。类似的兼容性问题，可能也可以从编译配置上解决。

3）配置pg_timeout

配置/var/lib/pgsql/12/data中的postgresql.conf，找到shared_preload_libraries行进行修改，注意去掉编码行之前的#，多个引用库之间用“，”分隔即可

shared_preload_libraries = 'pg_timeout'
pg_timeout.naptime = 30
pg_timeout.idle_session_timeout = 600

如果不生效，查看postgresql.auto.conf文件对应的shared_preload_libraries是否有覆盖，如果有则删除

如果日志中看不到pg_timeout的记录，可以看一下postgresql.conf中log_min_messages设置是否正确，pg_timeout的日志等级是LOG，正常应该显示

---

4、登录错误6次锁定账户10分钟（Windows和Linux不通用）

功能逻辑：

Login函数实现锁定和解锁逻辑，会在每次用户登录到“函数所在的库”时，被调用（session_exec触发），调用后会从外部表postgres_log读取日志中的“密码验证失败”记录，插入到t_login表中，然后搜索t_login表中是否某个用户的失败次数大于等于6次，则执行锁定用户操作；如果锁定时间已超过10分钟，则执行解锁操作。

 Linux  

先说Linux的方案，参考了以下文章PostgreSQL用户登录失败自动锁定的解决办法_postgresql the account has been locked-CSDN博客

步骤一 安装session_exec

拷贝下载好的session_exec-master源代码文件夹到安装路径下，修改makefile中的PG_CONFIG = /pg安装目录/bin/pg_config
   命令行执行编译并安装

make
make install

如安装失败可能是由于缺少postgresql12-devel，参考https://blog.csdn.net/bendywu/article/details/127745169安装

步骤二 配置postgresql.conf，使用session_exec并生成CSV格式日志

配置postgresql.conf，找到以下几个行进行修改，注意去掉编码行之前的#

    session_preload_libraries='session_exec' #找到并修改
    session_exec.login_name='login' 
    #添加到session_preload_libraries行下，后面会写login函数，给这里调用

    log_destination = 'csvlog' #找到并修改，修改日志类型为csv,以配合外部表查询
    log_filename = 'postgresql-%a.log' #找到并修改，按星期几生成日志
    log_rotation_age = 1d #找到并修改，按天滚动


    lc_messages = 'en_US.UTF-8' 
    #找到并修改，生产环境如果安装的是中文版，需要改变日志为英文，避免字符编码错误

步骤三 重启数据库以启用新配置

步骤四 进入需要管理的数据库进行后续操作，创建t_login表用于存储提取自数据库日志中登录失败的信息

执行以下语句

create table t_login
(
	login_time timestamp(3),
	user_name text,
	flag int4
);

步骤五 使用file_fdw外部表记录数据库日志信息
执行以下语句

create extension file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

步骤六 建立外部表postgres_log，关联数据库日志中登录失败的信息。

    DROP FOREIGN TABLE postgres_log;
    CREATE FOREIGN TABLE postgres_log(  
      log_time timestamp(3),  
      user_name text,  
      database_name text,  
      process_id integer,
      connection_from text,
      session_id text,  
      session_line_num bigint,  
      command_tag text,  
      session_start_time timestamp,  
      virtual_transaction_id text,  
      transaction_id bigint,  
      error_severity text,  
      sql_state_code text,  
      message text,  
      detail text,  
      hint text,  
      internal_query text,  
      internal_query_pos integer,  
      context text,  
      query text,  
      query_pos integer,  
      location text,  
      application_name text
    ) SERVER pglog  
    OPTIONS ( program 'find pg安装目录/data/log -type f -name "*.csv" -mtime -7 -exec cat {} \;', format 'csv' );

注意，这里的表字段不是随便写的，是受postgresql版本影响的，具体有那些字段，可以从这个连接里找PostgreSQL: Documentation: 15: 20.8. Error Reporting and Logging，这个网页最上面可以选版本查看

步骤七 创建登录函数

以下函数需要在希望管理的库中创建

create or replace function login() returns void as $$
	declare
	lastFail timestamp(3) with time zone;
	uname text;
	locktime timestamp(3) with time zone;
	minuteInv int4;
	begin
	--获取当前日志中最新时间，作为基础从日志中取登录失败记录
	select login_time from public.t_login order by login_time desc limit 1 into lastFail; 
	if lastFail IS NULL then
		lastFail = CURRENT_DATE;
	end if;
	raise notice '检测开始时间 %',lastFail;
	 --将新的登录失败记录数据插入t_login表
	insert into public.t_login select log_time,user_name from public.postgres_log 
	where command_tag='authentication' and error_severity= 'FATAL' and log_time > lastFail;
	update public.t_login set flag = 0 where login_time > lastFail; 
	--检查登录失败次数是否大于6，若大于6则锁定用户
	for uname, locktime in select user_name,MAX(login_time) from public.t_login where flag = 0 group by user_name having count(*) >= 6 
	loop
	raise notice '%最后登录失败时间 %',uname,locktime;
	SELECT round(cast(date_part('epoch', CURRENT_TIMESTAMP - locktime)/60 as numeric ),1) into minuteInv;
	raise notice 'minuteInv %',minuteInv;
	if minuteInv > 10 then
		--解锁用户
		raise notice '锁定超过10分钟，解锁用户%',uname;
		EXECUTE format('alter user %I login',uname); 
		update public.t_login set flag = 1 where user_name = uname;
	else
		--锁定用户
		EXECUTE format('alter user %I nologin',uname); 
		--断开当前被锁定用户会话
		EXECUTE 'select pg_catalog.pg_terminate_backend(pid) from pg_catalog.pg_stat_activity where usename=$1' using uname; 
		raise notice '累计6次输入错误密码，用户%锁定，10分钟后再尝试!',uname;
	end if;
	end loop;
	end;
	$$ language plpgsql strict security definer set search_path to 'public';

以上实现了登录库时的锁定和解锁操作，但是用户必须得登录login函数所在的库，才能触发login函数，否则login函数不会执行。

不能在多个库中添加login表和t_login表，因为相互之间数据是隔离的，会导致重复锁定。

解决办法是通过使用dblink，在多个库之间共享login函数，参见步骤八

步骤八 其他库关联login方法

在其他库中，执行以下语句，增加dblink扩展。

create extension dblink;

添加login函数，远程调用已有的login方法

create or replace function login() returns void as $$
	declare
	lname text;
	v_log text;
	a int;
	begin
	lname = CURRENT_TIMESTAMP;
	perform dblink_connect(lname,'dbname=库名 host=localhost port=端口 user=账号 password=密码');
	SELECT * from dblink(lname,'select public.login()') AS T(id TEXT) into v_log;
	perform dblink_disconnect(lname);
	end;
	$$ language plpgsql strict security definer set search_path to 'public';

这样，在用户登录别的库的时候，也会触发锁定和解锁了。

 Windows  

对于windows版的postgresql，与Linux的不同主要有以下几个步骤：

步骤一 安装session_exec，这里需要参照 《3、超时10分钟自动退出》中的windows编译方式，生成dll文件，放到postgresql安装目录下使用

步骤二 配置postgresql.conf，使用session_exec并生成CSV格式日志

除了Linux段落中提到的修改，还需要修改

log_filename = 'postgresql.log' #找到并修改
log_rotation_age = 30d #找到并修改，按月滚动

#整个日志都生成到一个文件内，一个月清理一次，为了配合外部表关联的windows的命令行指令
#如果你比较熟悉windows命令行，可以进行优化

步骤三、四、五，参见Linux部分

步骤六 建立外部表postgres_log，关联数据库日志中登录失败的信息

这里主要是options中的指令与linux不同，需要注意

	CREATE FOREIGN TABLE postgres_log(  
	  log_time timestamp(3),  
	  user_name text,  
	  database_name text,  
	  process_id integer,
	  connection_from text,
	  session_id text,  
	  session_line_num bigint,  
	  command_tag text,  
	  session_start_time timestamp,  
	  virtual_transaction_id text,  
	  transaction_id bigint,  
	  error_severity text,  
	  sql_state_code text,  
	  message text,  
	  detail text,  
	  hint text,  
	  internal_query text,  
	  internal_query_pos integer,  
	  context text,  
	  query text,  
	  query_pos integer,  
	  location text,  
	  application_name text,
		  backend_type text,
  leader_pid integer,
  query_id bigint
	) SERVER pglog  
	OPTIONS ( filename 'pg安装目录\data\log\postgresql.csv', format 'csv' );

步骤七 创建登录函数

create or replace function login() returns void as $$
	declare
	lastFail timestamp(3) with time zone;
	uname text;
	locktime timestamp(3) with time zone;
	minuteInv int4;
	begin
	--获取当前日志中最新时间，作为基础从日志中取登录失败记录
	select login_time from public.t_login order by login_time desc limit 1 into lastFail; 
	if lastFail IS NULL then
		lastFail = CURRENT_DATE;
	end if;
	raise notice '检测开始时间 %',lastFail;
	 --将新的登录失败记录数据插入t_login表
	insert into public.t_login select log_time,user_name from public.postgres_log 
	where sql_state_code='28P01' and error_severity= 'FATAL' and log_time > lastFail;
	update public.t_login set flag = 0 where login_time > lastFail; 
	--检查登录失败次数是否大于6，若大于6则锁定用户
	for uname, locktime in select user_name,MAX(login_time) from public.t_login where flag = 0 group by user_name having count(*) >= 6 
	loop
	raise notice '%最后登录失败时间 %',uname,locktime;
	SELECT round(cast(date_part('epoch', CURRENT_TIMESTAMP - locktime)/60 as numeric ),1) into minuteInv;
	raise notice 'minuteInv %',minuteInv;
	if minuteInv > 10 then
		--解锁用户
		raise notice '锁定超过10分钟，解锁用户%',uname;
		EXECUTE format('alter user %I login',uname); 
		update public.t_login set flag = 1 where user_name = uname;
	else
		--锁定用户
		EXECUTE format('alter user %I nologin',uname); 
		--断开当前被锁定用户会话
		EXECUTE 'select pg_catalog.pg_terminate_backend(pid) from pg_catalog.pg_stat_activity where usename=$1' using uname; 
		raise notice '累计6次输入错误密码，用户%锁定，10分钟后再尝试!',uname;
	end if;
	end loop;
	end;
	$$ language plpgsql strict security definer set search_path to 'public';

其中where sql_state_code='28P01' and error_severity= 'FATAL' 这部分判断条件与linux下不同，可能是postgreSQL版本不一致（linux是pg12，windows是pg14）造成的。

步骤八参见Linux部分