PostgreSQL用户登录失败自动锁定的处理方案

$ unzip session_exec-master.zip

$ cd session_exec-master/

$ make pg_config= /opt/pgsql/bin/pg_config

$ make pg_config= /opt/pgsql/bin/pg_config install

session_preload_libraries= 'session_exec'

session_exec.login_name= 'login'

$ sudo systemctl restart postgresql-12

create table t_login

(

login_time timestamp (3) with time zone --插入时间,

user_name text --数据库登录用户,

flag int4 --标志位，0代表过期数据，1代表正常状态数据

);

$ cd /opt/postgresql-12 .5 /contrib/file_fdw

$ make && make install

create extension file_fdw;

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

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 /opt/pg_log_5432 -type f -name "*.csv" -mtime -1 -exec cat {} \;' , format 'csv' );

$ psql -Upostgres

WARNING: function "login()" does not exist

psql (12.5)

Type "help" for help.

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 test1 encrypted password 'XXX' ;

$ psql -h192.168.137.11 -Utest1 postgres

Password for user test1:

psql: error: FATAL: password authentication failed for user "test1"

select * from postgres_log where command_tag= 'authentication' and error_severity= 'FATAL' ;

insert into t_login select log_time,user_name,0

  from postgres_log

  where command_tag= 'authentication'

  and error_severity= 'FATAL' ;

postgres=# select * from t_login;

   login_time  | user_name | flag

-------------------------+-----------+------

  2021-02-08 06:24:47.101 | test1  | 0

  2021-02-08 06:25:16.581 | test1  | 1

  2021-02-08 06:25:18.429 | test1  | 1

(3 rows )

[postgres@node11 ~]$ psql

NOTICE: Account test1 is locked!

psql (12.5)

Type "help" for help.

postgres=# select * from t_login;

   login_time  | user_name | flag

-------------------------+-----------+------

  2021-02-08 06:45:38.017 | test1  | 0

  2021-02-08 06:45:58.809 | test1  | 1

  2021-02-08 06:45:58.809 | test1  | 1

  2021-02-08 06:46:08.116 | test1  | 1

  2021-02-08 06:46:11.986 | test1  | 1

(5 rows )

update t_login set flag = 0 where user_name= 'test1' and flag=1;