PostgreSQL使用session_exec和file_fdw实现失败次数锁定用户策略

使用session_exec 、file_fdw以及自定义函数实现该功能。

缺陷：实测发现锁用户后，进去解锁特定用户。只能允许一次登陆，应该再次登陆的时候，触发函数，把之前的日志里的错误登陆的信息也计算到登录次数里了。而且foreign table不能在数据库里清理。需要删除对应的pg_log，才能使foreign table信息清理掉，来重制该用户的密码错误记录。

https://github.com/okbob/session_exec

unzip session_exec-master.zip
cd session_exec-master/
make pg_config=$PGHOME/bin/pg_config
make pg_config=$PGHOME/bin/pg_config install

更改配置文件

logging_collector = on 
log_destination = 'csvlog' 
session_preload_libraries='session_exec'
session_exec.login_name='login'

更改完之后需要重启数据库

pg_ctl restart

安装file_fdw扩展

cd contrib/file_fdw/
# 安装命令
make && make install

创建外部表postgres_log

create extension file_fdw;

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE public.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 /home/pg13/data/log/ -type f -name "*.csv" -mtime -1 -exec cat {} \;', format 'csv' );

grant SELECT on postgres_log to PUBLIC;

如下设置的是5次 5次登录失败就锁定用户

create or replace function public.login() returns void as $$
declare
res record;
failed_login_times int = 5;
failed_login int = 0;
begin
--获取数据库中所有可连接数据库的用户
for res in select rolname from pg_catalog.pg_roles where rolcanlogin= 't' and rolname !='postgres'
loop
  raise notice 'user: %!',res.rolname;
  --获取当前用户最近连续登录失败次数
  select count(*)
  from (select log_time,user_name,error_severity,message,detail from public.postgres_log where command_tag = 'authentication' and user_name = res.rolname and (detail is null or detail not like 'Role % does not exist.%') order by log_time desc limit failed_login_times) A
  WHERE A.error_severity='FATAL'
  into  failed_login ;
  raise notice 'failed_login_times: %! failed_login: %!',failed_login_times,failed_login;
  --用户最近密码输入错误次数达到5次或以上
  if failed_login >= failed_login_times then
    --锁定用户
    EXECUTE format('alter user %I nologin',res.rolname);
    raise notice 'Account % is locked!',res.rolname;
  end if;
end loop;
end;
$$ language plpgsql strict security definer set search_path to 'public';

用户是否被锁/是否允许登陆，可以查看pg_roles系统视图里的rolcanlogin字段。

验证密码失败：

使用如下语句，可以查询登陆失败的记录。

select log_time,user_name,error_severity,message,detail from public.postgres_log where command_tag = 'authentication'  order by log_time desc limit 10 offset 0；