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

最近公司一个项目进行等保测评过程中，对PostgreSQL数据提出了几点改进要求，如下：
  1）未配置密码复杂度策略、口令未定期更换
          修改发起：发起密码至少8位,需巨细写字母数字、特殊符号两种及以上组成并且不为常见密码，密码有效期90天
  2）未配置配置登岸失败处理功能
          修改发起：发起配置登录错误6次锁定账户10分钟，超时 10分钟自动退出。
  3)  默认账户未重命名
          修改发起：发起重命名默认账户
  针对以上几条要求，从网上查找资料以及自己的实验，基本办理了问题。办理的办法记录如下：
  

---

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

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

1. CREATE USER postgres1 WITH
2. LOGIN
3. SUPERUSER
4. INHERIT
5. CREATEDB
6. CREATEROLE
7. REPLICATION;
8. ALTER USER postgres1 WITH PASSWORD '你希望设置的密码';

复制代码

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

1. ALTER USER postgres RENAME TO admins;

复制代码

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

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

复制代码

注意，上面这个语句并没有实行修改操作，只是生成了延期的语句，须要拷贝生成的语句，实行后才会真正修改密码！
（3）删除postgres1

1. DROP USER postgres1;

复制代码

---

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

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

1. shared_preload_libraries = 'passwordcheck'
3. 注意去掉编码行之前的#

复制代码

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

1. create user abc password 'abc';
2. create user abc password 'abc123';
3. create user abc password 'aaa123456';

复制代码

增长常见密码库，参见下方链接，须要重新编译passwordcheck，比力贫苦，如有须要再实验
【数据库】PostgreSQL增长密码复杂度校验_postgresql14数据库密码策略校验-CSDN博客

---

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

这个须要根据PostgreSQL的版本选择处理方式：
14版本及以上：
可直接配置postgresql.conf, 找到idle_session_timeout行进行修改，注意去掉编码行之前的#，数值单位为毫秒

1. 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，增长

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

复制代码

这样的配置，然后重新编译，再进行实验。类似的兼容性问题，可能也可以从编译配置上办理。
3）配置pg_timeout
配置/var/lib/pgsql/12/data中的postgresql.conf，找到shared_preload_libraries行进行修改，注意去掉编码行之前的#，多个引用库之间用“，”分隔即可

1. shared_preload_libraries = 'pg_timeout'
2. pg_timeout.naptime = 30
3. 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
   下令行实行编译并安装

1. make
2. make install

复制代码

如安装失败可能是由于缺少postgresql12-devel，参考https://blog.csdn.net/bendywu/article/details/127745169安装
步骤二 配置postgresql.conf，利用session_exec并生成CSV格式日记
配置postgresql.conf，找到以下几个行进行修改，注意去掉编码行之前的#

1.     session_preload_libraries='session_exec' #找到并修改
2.     session_exec.login_name='login'
3.     #添加到session_preload_libraries行下，后面会写login函数，给这里调用
5.     log_destination = 'csvlog' #找到并修改，修改日志类型为csv,以配合外部表查询
6.     log_filename = 'postgresql-%a.log' #找到并修改，按星期几生成日志
7.     log_rotation_age = 1d #找到并修改，按天滚动
10.     lc_messages = 'en_US.UTF-8'
11.     #找到并修改，生产环境如果安装的是中文版，需要改变日志为英文，避免字符编码错误

复制代码

步骤三 重启数据库以启用新配置
步骤四 进入须要管理的数据库进行后续操作，创建t_login表用于存储提取自数据库日记中登录失败的信息
实行以下语句

1. create table t_login
2. (
3.         login_time timestamp(3),
4.         user_name text,
5.         flag int4
6. );

复制代码

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

1. create extension file_fdw;
2. CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

复制代码

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

1.     DROP FOREIGN TABLE postgres_log;
2.     CREATE FOREIGN TABLE postgres_log(  
3.       log_time timestamp(3),  
4.       user_name text,  
5.       database_name text,  
6.       process_id integer,
7.       connection_from text,
8.       session_id text,  
9.       session_line_num bigint,  
10.       command_tag text,  
11.       session_start_time timestamp,  
12.       virtual_transaction_id text,  
13.       transaction_id bigint,  
14.       error_severity text,  
15.       sql_state_code text,  
16.       message text,  
17.       detail text,  
18.       hint text,  
19.       internal_query text,  
20.       internal_query_pos integer,  
21.       context text,  
22.       query text,  
23.       query_pos integer,  
24.       location text,  
25.       application_name text
26.     ) SERVER pglog  
27.     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，这个网页最上面可以选版本检察

步骤七 创建登录函数
以下函数须要在希望管理的库中创建

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

复制代码

以上实现了登录库时的锁定和解锁操作，但是用户必须得登录login函数所在的库，才气触发login函数，否则login函数不会实行。
不能在多个库中添加login表和t_login表，由于相互之间数据是隔离的，会导致重复锁定。
办理办法是通过利用dblink，在多个库之间共享login函数，参见步骤八
步骤八 其他库关联login方法
在其他库中，实行以下语句，增长dblink扩展。

1. create extension dblink;

复制代码

添加login函数，长途调用已有的login方法

1. create or replace function login() returns void as $$
2.         declare
3.         lname text;
4.         v_log text;
5.         a int;
6.         begin
7.         lname = CURRENT_TIMESTAMP;
8.         perform dblink_connect(lname,'dbname=库名 host=localhost port=端口 user=账号 password=密码');
9.         SELECT * from dblink(lname,'select public.login()') AS T(id TEXT) into v_log;
10.         perform dblink_disconnect(lname);
11.         end;
12.         $$ 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段落中提到的修改，还须要修改

1. log_filename = 'postgresql.log' #找到并修改
2. log_rotation_age = 30d #找到并修改，按月滚动
4. #整个日志都生成到一个文件内，一个月清理一次，为了配合外部表关联的windows的命令行指令
5. #如果你比较熟悉windows命令行，可以进行优化

复制代码

步骤三、四、五，参见Linux部分
步骤六 建立外部表postgres_log，关联数据库日记中登录失败的信息
这里主要是options中的指令与linux不同，须要注意

1.         CREATE FOREIGN TABLE postgres_log(  
2.           log_time timestamp(3),  
3.           user_name text,  
4.           database_name text,  
5.           process_id integer,
6.           connection_from text,
7.           session_id text,  
8.           session_line_num bigint,  
9.           command_tag text,  
10.           session_start_time timestamp,  
11.           virtual_transaction_id text,  
12.           transaction_id bigint,  
13.           error_severity text,  
14.           sql_state_code text,  
15.           message text,  
16.           detail text,  
17.           hint text,  
18.           internal_query text,  
19.           internal_query_pos integer,  
20.           context text,  
21.           query text,  
22.           query_pos integer,  
23.           location text,  
24.           application_name text,
25.                   backend_type text,
26.   leader_pid integer,
27.   query_id bigint
28.         ) SERVER pglog  
29.         OPTIONS ( filename 'pg安装目录\data\log\postgresql.csv', format 'csv' );

复制代码

步骤七 创建登录函数

1. create or replace function login() returns void as $$
2.         declare
3.         lastFail timestamp(3) with time zone;
4.         uname text;
5.         locktime timestamp(3) with time zone;
6.         minuteInv int4;
7.         begin
8.         --获取当前日志中最新时间，作为基础从日志中取登录失败记录
9.         select login_time from public.t_login order by login_time desc limit 1 into lastFail;
10.         if lastFail IS NULL then
11.                 lastFail = CURRENT_DATE;
12.         end if;
13.         raise notice '检测开始时间 %',lastFail;
14.          --将新的登录失败记录数据插入t_login表
15.         insert into public.t_login select log_time,user_name from public.postgres_log
16.         where sql_state_code='28P01' and error_severity= 'FATAL' and log_time > lastFail;
17.         update public.t_login set flag = 0 where login_time > lastFail;
18.         --检查登录失败次数是否大于6，若大于6则锁定用户
19.         for uname, locktime in select user_name,MAX(login_time) from public.t_login where flag = 0 group by user_name having count(*) >= 6
20.         loop
21.         raise notice '%最后登录失败时间 %',uname,locktime;
22.         SELECT round(cast(date_part('epoch', CURRENT_TIMESTAMP - locktime)/60 as numeric ),1) into minuteInv;
23.         raise notice 'minuteInv %',minuteInv;
24.         if minuteInv > 10 then
25.                 --解锁用户
26.                 raise notice '锁定超过10分钟，解锁用户%',uname;
27.                 EXECUTE format('alter user %I login',uname);
28.                 update public.t_login set flag = 1 where user_name = uname;
29.         else
30.                 --锁定用户
31.                 EXECUTE format('alter user %I nologin',uname);
32.                 --断开当前被锁定用户会话
33.                 EXECUTE 'select pg_catalog.pg_terminate_backend(pid) from pg_catalog.pg_stat_activity where usename=$1' using uname;
34.                 raise notice '累计6次输入错误密码，用户%锁定，10分钟后再尝试!',uname;
35.         end if;
36.         end loop;
37.         end;
38.         $$ 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部分


免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。