PostgreSQL实现密码输入错误多次锁定账号

原创 tracy 云和恩墨交付战队 2021-09-22

7638

概述：使用session_exec+file_fdw插件，实现当有用户成功连接数据库后，锁定数据库中最近连续输入错误密码5次或以上的用户。（自动锁定用户后，需要手动解锁）

下载session_exec插件

编译session_exec插件

$ unzip session_exec-master.zip
$ cd ./session_exec-master/
$ which pg_config
/u01/pg13/pg/bin/pg_config
$ make pg_config=/u01/pg13/pg/bin/pg_config
$  make pg_config=/u01/pg13/pg/bin/pg_config install
复制

修改数据库配置

设置数据库日志输出格式为CSV，设置日志记录数据库连接信息

$ tail -5 $PGDATA/postgresql.conf
logging_collector=on
log_destination='csvlog'
log_connections = on
session_preload_libraries='session_exec'
session_exec.login_name='public.login'
$ pg_ctl reload
server signaled
复制

注：
session_preload_libraries参数：一个或者多个要在连接开始时预载入的共享库。
shared_preload_libraries参数：一个或者多个要在服务器启动时预载入的共享库。

创建一个外部表：postgres_log

外部表数据为一天内的CSV日志文件内容

postgres=# create extension file_fdw;
CREATE EXTENSION
postgres=# CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE SERVER
postgres=# CREATE FOREIGN TABLE public.postgres_log(
postgres(# log_time timestamp(3) with time zone,
postgres(#   user_name text,
postgres(#   database_name text,
postgres(#   process_id integer,
postgres(#   connection_from text,
postgres(#   session_id text,
postgres(#   session_line_num bigint,
postgres(#   command_tag text,
postgres(#   session_start_time timestamp with time zone,
postgres(#   virtual_transaction_id text,
postgres(#   transaction_id bigint,
postgres(#   error_severity text,
postgres(#   sql_state_code text,
postgres(#   message text,
postgres(#   detail text,
postgres(#   hint text,
postgres(#   internal_query text,
postgres(#   internal_query_pos integer,
postgres(#   context text,
postgres(#   query text,
postgres(#   query_pos integer,
postgres(#   location text,
postgres(#   application_name text,
postgres(#   backend_type text
postgres(# ) SERVER pglog
postgres-# OPTIONS ( program 'find /u01/pg13/pgsource/pgdata/log -type f -name "*.csv" -mtime -1 -exec cat {} \;', format 'csv' );
CREATE FOREIGN TABLE
postgres=# grant SELECT on postgres_log to PUBLIC ;
GRANT
postgres=# select log_time,user_name,error_severity,message,detail from public.postgres_log where command_tag = 'authentication'  order by log_time desc limit 10 offset 0;
          log_time          | user_name | error_severity |                                   message                                    |                                             detail

----------------------------+-----------+----------------+------------------------------------------------------------------------------+--------------------------------------------------------------------------------------
----------
 2021-09-07 05:57:41.24+08  | test      | FATAL          | password authentication failed for user "test"                               | Connection matched pg_hba.conf line 100: "host    all             all 0.0.0.0/0 scram
-sha-256"
 2021-09-07 05:57:23.412+08 | postgres  | LOG            | connection authorized: user=postgres database=postgres application_name=psql |
 2021-09-07 05:55:43.124+08 | test      | FATAL          | password authentication failed for user "test"                               | Connection matched pg_hba.conf line 100: "host    all             all 0.0.0.0/0 md5"
 2021-09-07 05:45:15.064+08 | test5     | FATAL          | password authentication failed for user "test5"                              | Role "test5" does not exist.
         +
                            |           |                |                                                                              | Connection matched pg_hba.conf line 100: "host    all             all 0.0.0.0/0 md5"
 2021-09-07 05:45:12.308+08 | test5     | FATAL          | password authentication failed for user "test5"                              | Role "test5" does not exist.
         +
                            |           |                |                                                                              | Connection matched pg_hba.conf line 100: "host    all             all 0.0.0.0/0 md5"
 2021-09-07 05:43:06.076+08 | test      | LOG            | connection authorized: user=test database=db1 application_name=psql          |
 2021-09-07 05:42:51.308+08 | test      | LOG            | connection authorized: user=test database=postgres application_name=psql     |
 2021-09-07 05:42:42.994+08 | test      | FATAL          | password authentication failed for user "test"                               | Connection matched pg_hba.conf line 100: "host    all             all 0.0.0.0/0 md5"
 2021-09-07 05:42:25.626+08 | postgres  | LOG            | connection authorized: user=postgres database=postgres application_name=psql |
 2021-09-07 05:41:32.74+08  | test      | FATAL          | password authentication failed for user "test"                               | Connection matched pg_hba.conf line 100: "host    all             all 0.0.0.0/0 md5"
(10 rows)                                          +
复制

创建存储过程public.login()

postgres=# create or replace function public.login() returns void as $$
postgres$# declare
postgres$# res record;
postgres$# failed_login_times int = 5;
postgres$# failed_login int = 0;
postgres$# begin
postgres$# --获取数据库中所有可连接数据库的用户
postgres$# for res in select rolname from pg_catalog.pg_roles where rolcanlogin= 't' and rolname !='postgres'
postgres$# loop
postgres$#   raise notice 'user: %!',res.rolname;
postgres$#   --获取当前用户最近连续登录失败次数
postgres$#   select count(*)
postgres$#   from (select log_time,user_name,error_severity,message,detail from public.postgres_log where command_tag = 'authentication' and user_name = res.rolname and (detail is null or detail not like 'Role % does not exist.%') order by log_time desc limit failed_login_times) A
postgres$#   WHERE A.error_severity='FATAL'
postgres$#   into  failed_login ;
postgres$#   raise notice 'failed_login_times: %! failed_login: %!',failed_login_times,failed_login;
postgres$#   --用户最近密码输入错误次数达到5次或以上
postgres$#   if failed_login >= failed_login_times then
postgres$#     --锁定用户
postgres$#     EXECUTE format('alter user %I nologin',res.rolname);
postgres$#     raise notice 'Account % is locked!',res.rolname;
postgres$#   end if;
postgres$# end loop;
postgres$# end;
postgres$# $$ language plpgsql strict security definer set search_path to 'public';
CREATE FUNCTION
复制

密码错误次数测试

$ psql -U test -d postgres -h 192.168.2.154
Password for user test:（输入错误的密码）
psql: error: FATAL:  password authentication failed for user "test"
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:（输入错误的密码）
psql: error: FATAL:  password authentication failed for user "test"
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:
psql: error: FATAL:  password authentication failed for user "test"
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:（输入错误的密码）
psql: error: FATAL:  password authentication failed for user "test"
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:（输入错误的密码）
psql: error: FATAL:  password authentication failed for user "test"
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:（输入错误的密码）
psql: error: FATAL:  password authentication failed for user "test"
$ psql
NOTICE:  user: test1!
NOTICE:  failed_login_times: 5! failed_login: 4!
NOTICE:  user: test2!
NOTICE:  failed_login_times: 5! failed_login: 0!
NOTICE:  user: test!
NOTICE:  failed_login_times: 5! failed_login: 5!
NOTICE:  Account test is locked!
psql (13.3)
Type "help" for help.

postgres=# \q
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:（输入正确的密码）
psql: error: FATAL:  role "test" is not permitted to log in
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:（输入错误的密码）
psql: error: FATAL:  password authentication failed for user "test"
$
$ psql
NOTICE:  user: test1!
NOTICE:  failed_login_times: 5! failed_login: 4!
NOTICE:  user: test2!
NOTICE:  failed_login_times: 5! failed_login: 0!
psql (13.3)
Type "help" for help.

postgres=# alter user test login;
ALTER ROLE
postgres=# \q
$ psql -U test -d postgres -h 192.168.2.154
Password for user test:
NOTICE:  user: test1!
NOTICE:  failed_login_times: 5! failed_login: 4!
NOTICE:  user: test2!
NOTICE:  failed_login_times: 5! failed_login: 0!
NOTICE:  user: test!
NOTICE:  failed_login_times: 5! failed_login: 3!
psql (13.3)
Type "help" for help.

postgres=> \q
复制