PostgreSQL用户登录失败自动锁定的解决办法

素颜马尾好姑娘i 2022-11-11 05:10 1270阅读 0赞

> 墨墨导读：PostgreSQL使用session\_exec插件实现用户密码验证失败几次后自动锁定，本文介绍一种处理方案。

# 一、插件session\_exec安装配置篇 #

下载插件并编译安装。  
https://github.com/okbob/session\_exec

$ 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

配置postgresql.conf。

session_preload_libraries='session_exec'
    session_exec.login_name='login'

注意：上面第一个变量是设置session\_preload\_libraries而不是通常设置的shared\_preload\_libraries。  
第二个变量是需要自定义实现的登录函数。

重启数据库服务。

$ sudo systemctl restart postgresql-12

# 二、自定义登录函数篇 #

创建t\_login表用于存储提取自数据库日志中登录失败的信息。

create table t_login
    (
    login_time timestamp(3) with time zone --插入时间,
    user_name text --数据库登录用户,
    flag int4 --标志位，0代表过期数据，1代表正常状态数据
    );

使用file\_fdw外部表记录数据库日志信息。  
file\_fdw如果未配置过，参见下面步骤。

$ cd /opt/postgresql-12.5/contrib/file_fdw
    $ make && make install
    
    create extension file_fdw;
    CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

建立外部表postgres\_log，关联数据库日志中登录失败的信息。

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

注意：  
1./opt/pg\_log\_5432需要修改为实际环境日志目录。  
2. 不同PG版本csv日志格式可能有所差异，参考PG官网文档runtime-config-logging章节（http://postgres.cn/docs/12/runtime-config-logging.html）。

此时连接数据库因未创建登录函数会出现下面的警告信息。

$ psql -Upostgres
    WARNING:  function "login()" does not exist
    psql (12.5)
    Type "help" for help.

创建登录函数login。

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

模拟test1用户登录失败，输入错误密码。

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

可以看到1条数据，手工插入一条登录失败的信息到t\_login表。

insert into t_login  select log_time,user_name,0
        from postgres_log 
        where command_tag='authentication' 
        and error_severity= 'FATAL';

参考上面登录失败测试，接着再测试2次。

然后使用postgres用户登录数据库，观察t\_login表数据。

再测试两次失败登录，然后使用postgres用户登录数据库，看到提示该用户被锁定。

解锁用户。

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

总结

1.  session\_exec通过用户登录成功后调用login函数去实现锁定登录失败次数过多的用户。
2.  此种方式有点繁琐且会造成数据库连接变慢。
3.  不支持自动解锁，需要管理用户手工处理。

参考链接：  
https://blog.csdn.net/weixin\_39540651/article/details/108227188

作者

彭冲，云和恩墨PG技术顾问（网名：多米爸比），PG社区认证专家，中国首批PostgreSQL ACE伙伴 多年从事基于PostgreSQL数据库的软件研发，擅长于PL/PGsql业务迁移及优化，Oracle到PostgreSQL的迁移升级，异构数据库整合；作为墨天轮PostgreSQL专栏作者，热衷于PostgreSQL实践技术分享，在自己的岗位积极推广PostgreSQL，致力为PG社区多做奉献。

墨天轮原文链接：https://www.modb.pro/db/45279（复制到浏览器中打开或者点击“阅读原文”立即查看）