Postgresqlのユーザーログインエラーの回数を制限するサンプルコード

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE pglog ( 
 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,
 backend_type text 
) SERVER pglog 
OPTIONS ( program 'find $PGDATA/log -type f -name "*.csv" -mtime -1 -exec cat {} \;', format 'csv' ); 

create table t_login
(
login_time timestamp(3) with time zone --insert time,
user_name text,
flag int -- flag bit, 0 for expired data
);

bill=# insert into t_login select log_time,user_name from pglog where command_tag='authentication' and error_severity= 'FATAL'
bill-# ;
INSERT 0 4

create or replace function lock_user() returns void as $$
declare
res text;
c1 timestamp(3) with time zone;
begin
select login_time from t_login where flag = 0 order by login_time desc limit 1 into c1; --get the latest time in the current log
insert into t_login select log_time,user_name from pglog where command_tag='authentication' and error_severity= 'FATAL' and log_time > c1; --insert insert the latest data into the t_login table
update t_login set flag = 1 where login_time > c1; 
for res in select user_name from t_login where flag = 1 group by user_name having count(*) >=3 --check if the number of failed login attempts is greater than 3, if greater than 3 then lock the user
loop
EXECUTE format('alter user %I nologin',res); -- lock the user
EXECUTE 'select pg_terminate_backend(pid) from pg_stat_activity where usename=$1' using res; -- break the current locked user session
raise notice 'Account % is locked!',res;
end loop;
end;
$$ language plpgsql strict;

session_preload_libraries='session_exec'
session_exec.login_name='lock_user'

bill=# select * from t_login;
     login_time | user_name | flag 
----------------------------+-----------+------
 2020-08-26 07:26:45.42+08 | test1 | 1
 2020-08-26 07:26:50.179+08 | test1 | 1
 2020-08-26 07:26:52.487+08 | test1 | 1
 2020-08-26 07:26:54.537+08 | test1 | 1
(4 rows)

pg13@cnndr4pptliot-> psql bill test1
Password for user test1: 
NOTICE: c1 = <NULL>
psql: error: could not connect to server: FATAL: terminating connection due to administrator command
CONTEXT: SQL statement "select pg_terminate_backend(pid) from pg_stat_activity where usename=$1"
PL/pgSQL function lock_user() line 13 at EXECUTE

pg13@cnndr4pptliot-> psql bill test1
Password for user test1: 
psql: error: could not connect to server: FATAL: role "test1" is not permitted to log in

bill=# alter user test1 login;
ALTER ROLE

bill=# update t_login set flag = 0;
UPDATE 4