PostgreSQL用户登录失败自动锁定

插件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='public.login'

注意：上面第一个变量是设置session_preload_libraries而不是通常设置的shared_preload_libraries。

第二个变量是需要自定义实现的登录函数

重启数据库服务

$ sudo systemctl restart postgresql-12

自定义登录函数篇

使用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，并赋予表的select权限给public角色。

CREATE FOREIGN TABLE public.postgres_log(  
  log_time timestamp without 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' );

grant SELECT on postgres_log to PUBLIC ;

注意：1./opt/pg_log_5432需要修改为实际环境日志目录。
2.不同PG版本csv日志格式可能有所差异，参考修改runtime-config-logging

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

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

创建登录函数login，文件下载连接<<PostgreSQL用户登录失败自动锁定函数>>

测试使用篇

创建测试用户

create user test1 encrypted password 'XXX';
create user test2 encrypted password 'XXX';
create user test3 encrypted password 'XXX';

test1和test2连续三次输入错误密码，然后test3登录数据库可查看到test1和test2已经不能登录。

$ psql -Utest3 postgres
psql (12.7)
Type "help" for help.

postgres=> \du test*
            List of roles
 Role name |  Attributes  | Member of 
-----------+--------------+-----------
 test1     | Cannot login | {}
 test2     | Cannot login | {}
 test3     |              | {}

使用管理用户解锁用户

alter user test1 login ;
alter user test2 login ;

如果是当天登录失败的记录，需要清除日志文件失败的记录，可通过外部表postgres_log或者直接处理日志文件。

总结

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

参考链接：
https://blog.csdn.net/weixin_39540651/article/details/108227188

推荐阅读<<PostgreSQL安全加固>>

保持联系

从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴进群吹牛唠嗑，交流技术，互赞文章。

如果群二维码失效可以加我微信。