Postgresql限制用戶登錄錯誤次數(shù)的實例代碼
在oracle中我們可以通過設(shè)置FAILED_LOGIN_ATTEMPTS來限制用戶密碼登錄錯誤的次數(shù)，但是在postgresql中是不支持這個功能的。盡管PostgreSQL支持event trigger，可是event局限于DDL，對于登錄登出事件是沒辦法使用event trigger的。
不過像登錄新建會話觸發(fā)某個事件這個需求可以通過hook實現(xiàn)，不過該方法比較復雜，需要修改內(nèi)核代碼，在客戶端認證中添加邏輯，判斷輸入密碼次數(shù)統(tǒng)計。這里推薦一種比較簡單的方法實現(xiàn)類似的功能。
這里我們要使用到session_exec這個插件，使用該插件會在登錄時執(zhí)行一個指定的function。
下載地址：
https://github.com/okbob/session_exec
下載解壓之后需要進行以下配置：
- set session_preload_libraries to session_execset
- session_exec.login_name to name of your login function
該插件有以下特點：
- 如果函數(shù)不存在則會進行警告；
- 函數(shù)執(zhí)行失敗則不允許連接。
利用該插件我們可以寫一個簡單的函數(shù)來實現(xiàn)限制用戶登錄錯誤次數(shù)的功能。
例子：
1、建立外部表記錄數(shù)據(jù)庫日志信息。
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ù)庫日志中的登錄信息。
create table t_login ( login_time timestamp(3) with time zone --插入時間, user_name text, flag int --標志位，0代表過期數(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; --獲取當前日志中最新時間
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; --斷開當前被鎖定用戶會話
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、測試
模擬test1用戶登錄錯誤超過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)
當我們在使用test1用戶登錄時則無法連接
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、解鎖用戶
此時想要解鎖該用戶則需要執(zhí)行：
bill=# alter user test1 login; ALTER ROLE
然后需要注意還要將t_login中過期的數(shù)據(jù)修改。
bill=# update t_login set flag = 0; UPDATE 4
參考鏈接：
https://github.com/okbob/session_exec
到此這篇關(guān)于Postgresql限制用戶登錄錯誤次數(shù)的文章就介紹到這了,更多相關(guān)Postgresql限制用戶登錄錯誤次數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
相關(guān)文章
詳解如何在Ubuntu?18.04上安裝和使用PostgreSQL
關(guān)系數(shù)據(jù)庫管理系統(tǒng)是許多網(wǎng)站和應(yīng)用程序的關(guān)鍵組件,它們提供了一種結(jié)構(gòu)化的方式來存儲、組織和訪問信息,本文演示了如何在?Ubuntu?18.04?VPS?實例上安裝?Postgres,并提供了基本數(shù)據(jù)庫管理的說明,需要的朋友可以參考下2024-07-07
PostgreSQL被稱為是“最高級的開源數(shù)據(jù)庫”，它的數(shù)據(jù)類型非常豐富，用它來解決一些比較偏門的需求非常適合。由于PgSQL國內(nèi)的資料較少，所有本文將詳細介紹此類問題，以便后來的同學能順利使用PgSQL。2021-05-05
PostgresSql 多表關(guān)聯(lián)刪除語句的操作
這篇文章主要介紹了PostgresSql 多表關(guān)聯(lián)刪除語句的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
PostgreSQL創(chuàng)建新用戶所遇見的權(quán)限問題以及解決辦法
這篇文章主要給大家介紹了關(guān)于PostgreSQL創(chuàng)建新用戶所遇見的權(quán)限問題以及解決辦法, 在PostgreSQL中創(chuàng)建一個新用戶非常簡單,但可能會遇到權(quán)限問題,需要的朋友可以參考下2023-09-09
Linux 上 定時備份postgresql 數(shù)據(jù)庫的方法
這篇文章主要介紹了Linux 上 定時備份postgresql 數(shù)據(jù)庫的方法,本文給大家介紹的非常詳細，對大家的學習或工作具有一定的參考借鑒價值，需要的朋友可以參考下2021-02-02
postgresql 實現(xiàn)啟動、狀態(tài)查看、關(guān)閉
這篇文章主要介紹了postgresql 實現(xiàn)啟動、狀態(tài)查看、關(guān)閉的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
這篇文章主要介紹了PostgreSQL 慢查詢SQL跟蹤操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
postgresql 循環(huán)函數(shù)的簡單實現(xiàn)操作
這篇文章主要介紹了postgresql 循環(huán)函數(shù)的簡單實現(xiàn)操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01
Postgresql?REGEXP開頭的正則函數(shù)用法圖文詳解
正則表達式是指一個用來描述或者匹配一系列符合某個句法規(guī)則的字符串的單個字符串,下面這篇文章主要給大家介紹了關(guān)于Postgresql?REGEXP開頭的正則函數(shù)用法的相關(guān)資料,需要的朋友可以參考下2024-02-02
在postgresql中運行sql文件并導出結(jié)果的操作
這篇文章主要介紹了在postgresql中運行sql文件并導出結(jié)果的操作，具有很好的參考價值，希望對大家有所幫助。一起跟隨小編過來看看吧2021-01-01