PostgreSQL 用户登录失败账号锁定

PostgreSQL 用户登录失败账号锁定
下载地址 https://github.com/okbob/session_exec.git

[root@localhost src]# unzip session_exec-master.zip
[root@localhost src]# cd session_exec-master/
[root@localhost session_exec-master]# make pg_config=/usr/local/pgsql-12.8/bin/pg_config
[root@localhost session_exec-master]# make pg_config=/usr/local/pgsql-12.8/bin/pg_config install

配置 postgresql
[root@localhost session_exec-master]# su - postgres
[postgres@localhost session_exec-master]$ cd /usr/local/pgsql-12.8/data/
[postgres@localhost data]$ vim postgresql.conf
session_preload_libraries='session_exec'
session_exec.login_name='login'

重启数据库
[postgres@localhost data]$ pg_ctl restart -D /usr/local/pgsql-12.8/data/

创建 t_login 表用于存储提取自数据库日志中登录失败的信息
CREATE TABLE t_login (
login_time timestamp(3) with time zone,--插入时间
user_name text,--数据库登录用户
flag int4 --标志位,0 代表过期数据,1 代表正常状态数据
);

使用 file_fdw 外部表记录数据库日志信息
如果 file_fdw 如果未配置过，参见下面步骤
CREATE extension file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

建立外部表 postgres_log，关联数据库日志中登录失败的信息
CREATE FOREIGN TABLE postgres_log(  
  log_time timestamp(3) with time zone,  
  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 with time zone,  
  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 /usr/local/pgsql-12.8/data/log/ -type f -name 'postgresql*.csv' -mtime -1 -exec cat {} \;', format 'csv' );

创建登录函数 login
create or replace function login() returns void as $$
declare
res text;
c1 timestamp(3) with time zone;
begin

--获取当前日志中最新时间
select login_time
from public.t_login
where flag = 0
order by login_time
desc limit 1
into c1;

--将最新的数据插入 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 > c1;

update public.t_login set flag = 1 where login_time > c1;

--检查登录失败次数是否大于 3，若大于 3 则锁定用户
for res in select user_name from public.t_login where flag = 1 group by user_name having count(*) >=3
loop
--锁定用户
EXECUTE format('alter user %I nologin',res);
--断开当前被锁定用户会话
EXECUTE 'select pg_catalog.pg_terminate_backend(pid) from pg_catalog.pg_stat_activity where usename=$1' using res;
raise notice 'Account % is locked!',res;
end loop;
end;
$$ language plpgsql strict security definer set search_path to 'public';

测试
create user test_user encrypted password 'test@20220526';

模拟 test_user 用户登录失败，输入错误密码
$ psql -h 192.168.30.140 -U test_user cloud_test
Password for user test_user:
psql: error: FATAL:  password authentication failed for user 'test_user'

通过外部表查看登录失败的日志
select * from postgres_log where command_tag='authentication' and error_severity= 'FATAL';

解锁用户
alter user test_user login ;

同时清空登录失败的标记位
update t_login set flag = 0 where user_name='test_user' and flag=1;