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

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'
loopraise 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) AWHERE 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；

解锁用户命令如下：

alter user test_user login;

解锁同时需要删除pg_log下csv文件里的对应有改用户登陆失败的日志记录，重制密码登录错误的记录，否则可能会重复计算之前的错误登陆记录。