PostgreSQL用户登录失败自动锁定函数

彭冲

1047

1页

16次

2021-07-25

免费下载

create or replace function public.login() returns void as $$

declare

rec_user_name record;

cnt_log_fails int = 3;

cnt_log_fail int;

begin

-- 判断当天其它用户是否有登录失败的记录

perform 1

from public.postgres_log

where user_name != current_user

and command_tag='authentication'

and error_severity= 'FATAL'

and log_time::date=current_date;

--其它用户没有登录失败的记录，流程结束

if not found then

raise notice 'user % do nothing.',current_user;

return;

end if;

for rec_user_name in select distinct user_name from public.postgres_log where

user_name != current_user and command_tag='authentication' and error_severity=

'FATAL' and log_time::date=current_date

loop

--查询用户当天连续登录失败的次数

execute 'select count(1)'

' from( select log_time,error_severity'

' from public.postgres_log'

' where user_name=$1'

' and command_tag=$2 '

' and log_time::date=current_date'

' order by log_time desc limit $3'

' ) foo where error_severity = $4;'

into cnt_log_fail using rec_user_name.user_name,'authentication',

cnt_log_fail,'FATAL';

if cnt_log_fail<cnt_log_fails then

continue;

end if;

perform public.lock_user(rec_user_name.user_name);

end loop;

end;

$$ language plpgsql security invoker;

create or replace function public.lock_user(lock_user character varying) returns

void as $$

begin

if(select rolcanlogin from pg_roles where rolname=lock_user) then

execute format('alter user %I nologin',lock_user);

end if;

end;

$$ language plpgsql strict security definer;

of 1

免费下载

postgresql 自动锁定用户

关注

评论