Postgresql怎么限制用戶登錄錯(cuò)誤次數(shù)

小編給大家分享一下Postgresql怎么限制用戶登錄錯(cuò)誤次數(shù)，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

在oracle中我們可以通過(guò)設(shè)置FAILED_LOGIN_ATTEMPTS來(lái)限制用戶密碼登錄錯(cuò)誤的次數(shù)，但是在postgresql中是不支持這個(gè)功能的。盡管PostgreSQL支持event trigger，可是event局限于DDL，對(duì)于登錄登出事件是沒(méi)辦法使用event trigger的。

不過(guò)像登錄新建會(huì)話觸發(fā)某個(gè)事件這個(gè)需求可以通過(guò)hook實(shí)現(xiàn)，不過(guò)該方法比較復(fù)雜，需要修改內(nèi)核代碼，在客戶端認(rèn)證中添加邏輯，判斷輸入密碼次數(shù)統(tǒng)計(jì)。這里推薦一種比較簡(jiǎn)單的方法實(shí)現(xiàn)類似的功能。

這里我們要使用到session_exec這個(gè)插件，使用該插件會(huì)在登錄時(shí)執(zhí)行一個(gè)指定的function。

下載地址：

https://github.com/okbob/session_exec

下載解壓之后需要進(jìn)行以下配置：

1. set session_preload_libraries to session_execset

2. session_exec.login_name to name of your login function

該插件有以下特點(diǎn)：

1. 如果函數(shù)不存在則會(huì)進(jìn)行警告；

2. 函數(shù)執(zhí)行失敗則不允許連接。

利用該插件我們可以寫(xiě)一個(gè)簡(jiǎn)單的函數(shù)來(lái)實(shí)現(xiàn)限制用戶登錄錯(cuò)誤次數(shù)的功能。

例子：

1、建立外部表記錄數(shù)據(jù)庫(kù)日志信息。

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' );

2、創(chuàng)建表t_login提取數(shù)據(jù)庫(kù)日志中的登錄信息。

create table t_login
(
login_time timestamp(3) with time zone --插入時(shí)間,
user_name text,
flag int --標(biāo)志位，0代表過(guò)期數(shù)據(jù)
);

插入登錄信息：

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

3、創(chuàng)建登錄執(zhí)行的function

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; --獲取當(dāng)前日志中最新時(shí)間
insert into t_login select log_time,user_name from pglog where command_tag='authentication' and error_severity= 'FATAL' and log_time > c1; --將最新的數(shù)據(jù)插入t_login表
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 --檢查登錄失敗次數(shù)是否大于3，若大于3則鎖定用戶
loop
EXECUTE format('alter user %I nologin',res); --鎖定用戶
EXECUTE 'select pg_terminate_backend(pid) from pg_stat_activity where usename=$1' using res; --斷開(kāi)當(dāng)前被鎖定用戶會(huì)話
raise notice 'Account % is locked!',res;
end loop;
end;
$$ language plpgsql strict;

4、編輯postgresql.conf文件，配置登錄函數(shù)

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

5、測(cè)試
模擬test1用戶登錄錯(cuò)誤超過(guò)3次：

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)

當(dāng)我們?cè)谑褂胻est1用戶登錄時(shí)則無(wú)法連接

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

6、解鎖用戶
此時(shí)想要解鎖該用戶則需要執(zhí)行：

bill=# alter user test1 login;
ALTER ROLE

然后需要注意還要將t_login中過(guò)期的數(shù)據(jù)修改。

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

以上是“Postgresql怎么限制用戶登錄錯(cuò)誤次數(shù)”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注億速云行業(yè)資訊頻道！