postgresql配置session_exec 达到锁定登录失败用户目的

Herb6876

已于 2022-08-11 18:07:57 修改

阅读量4k

点赞数 4

分类专栏： Promote 文章标签： postgresql 数据库服务器

于 2022-07-07 11:53:11 首次发布

本文链接：https://blog.csdn.net/weixin_43557605/article/details/125656032

版权

Promote 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

文章目录

1 说明
2 配置session_exec
3 问题与改进
- 3.1 问题说明
- 3.2 操作步骤

1 说明

由于公司的某SaaS平台要进行等保3.0评测，与之对应的postgresql数据库也要进行相关的安全设置，对其配置锁定用户是必要操作之一。
如下步骤是在postgresql12.8版本上配置session_exec的过程，如下步骤在12.8版本可以完美运行，无坑。

本文涉及的服务和服务器的基本信息

维度	说明
系统版本	Centos 7.9
postgresql版本	12.8
postgresql安装方式	源码安装
postgresql运行用户	postgres

为了避免权限错乱导致服务异常，安装后的操作基本上都是在postgres用户下操作的。

2 配置session_exec

2.1 安装postgresql

如下是我的安装步骤可以参考命令简单粘贴一下

yum -y install gcc gcc-c++ readline-devel zlib-devel libuuid-devel uuid uuid-devel openssl openssl-devel
useradd postgres
mv postgresql-12.8.tar.gz /home/postgres
mkdir /data/app/postgresql /data/appData/postgresql /data/logs/postgresql/postgresql -p
chown -R postgres:postgres /data/app/postgresql /data/appData/postgresql /data/logs/postgresql /home/postgres
su  - postgres
tar -xf postgresql-12.8.tar.gz
cd postgresql-12.8/
./configure --prefix=/data/app/postgresql --with-ossp-uuid --with-openssl --with-zlib
make -j4 && make install
echo -e "export PGHOME=/data/app/postgresql\nexport PATH=\$PATH:\$HOME/.local/bin:\$HOME/bin:\$PGHOME/bin" >> ~/.bashrc
source ~/.bashrc
initdb -D /data/appData/postgresql/

2.2 安装file_fdw扩展

[postgres@localhost postgresql-12.8]$ ls
aclocal.m4  config.log     configure     contrib    doc          GNUmakefile.in  INSTALL   README
config      config.status  configure.in  COPYRIGHT  GNUmakefile  HISTORY         Makefile  src
[postgres@localhost postgresql-12.8]$ cd contrib/
[postgres@localhost contrib]$ file_fdw/
[postgres@localhost file_fdw]$ make
[postgres@localhost file_fdw]$ make install

2.3 安装session_exec

这个需要去github下载然后安装

https://github.com/okbob/session_exec 点击Code 点击Download ZIP

unzip session_exec-master.zip
cd session_exec-master/
make pg_config=/data/app/postgresql/bin/pg_config
make pg_config=/data/app/postgresql/bin/pg_config install

2.4 修改配置文件添加session_exec

logging_collector = on # 有了就不用再添加了
log_destination = 'csvlog' # 有了就不用再添加了
session_preload_libraries='session_exec'
session_exec.login_name='login'

重启让配置生效

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

2.5 创建外部表postgres_log

create extension file_fdw;

CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;

CREATE FOREIGN TABLE public.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 /data/logs/postgresql -type f -name "*.csv" -mtime -1 -exec cat {} \;', format 'csv' );

grant SELECT on postgres_log to PUBLIC;

2.6 创建登录验证函数login

如下设置的是5次 5次登录失败就锁定用户具体的次数可以根据需要进行设置

create or replace function public.login() returns void as $$
declare
res record;
failed_login_times int = 5;
failed_login int = 0;
begin
--获取数据库中所有可连接数据库的用户
for res in select rolname from pg_catalog.pg_roles where rolcanlogin= 't' and rolname !='postgres'
loop
  raise notice 'user: %!',res.rolname;
  --获取当前用户最近连续登录失败次数
  select count(*) 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 WHERE A.error_severity='FATAL' into  failed_login ;
  raise notice 'failed_login_times: %! failed_login: %!',failed_login_times,failed_login;
  --用户最近密码输入错误次数达到5次或以上
  if failed_login >= failed_login_times then
    --锁定用户
    EXECUTE format('alter user %I nologin',res.rolname);
    raise notice 'Account % is locked!',res.rolname;
  end if;
end loop;
end;
$$ language plpgsql strict security definer set search_path to 'public';

2.7 验证登录失败效果

修改pg_hba.conf 添加对测试用户的密码策略然后重启pg

host    all             test1             127.0.0.1/32          md5

创建测试用户

create user test1 encrypted password 'Rootmaster@777';

前5次输入错误密码如下

[postgres@localhost ~]$ psql -Utest1 -h127.0.0.1 -p18126 -d postgres
Password for user test1:
psql: error: FATAL:  password authentication failed for user "test1"
[postgres@localhost ~]$ psql -Utest1 -h127.0.0.1 -p18126 -d postgres
Password for user test1:
psql: error: FATAL:  password authentication failed for user "test1"
[postgres@localhost ~]$ psql -Utest1 -h127.0.0.1 -p18126 -d postgres
Password for user test1:
psql: error: FATAL:  password authentication failed for user "test1"
[postgres@localhost ~]$ psql -Utest1 -h127.0.0.1 -p18126 -d postgres
Password for user test1:
psql: error: FATAL:  password authentication failed for user "test1"
[postgres@localhost ~]$ psql -Utest1 -h127.0.0.1 -p18126 -d postgres
Password for user test1:

查询test1锁定状态如下可以发现已经不可登录

[postgres@localhost session_exec-master]$ psql -Upostgres -h127.0.0.1 -p18126 -d postgres
NOTICE:  user: test1!
NOTICE:  failed_login_times: 5! failed_login: 5!
NOTICE:  Account test1 is locked!
psql (12.8)
Type "help" for help.

postgres=# select rolcanlogin from pg_catalog.pg_roles where rolname='test1';
 rolcanlogin
-------------
 f
(1 row)

test1再次登录提示如下

[postgres@localhost session_exec-master]$ psql -Utest1 -h127.0.0.1 -p18126 -d postgres
Password for user test1:
psql: error: FATAL:  role "test1" is not permitted to log in

具体的用户解锁命令如下

[postgres@localhost ~]$ psql -Upostgres -h127.0.0.1 -p18126 -d testdb1
psql (12.8)
Type "help" for help.

testdb1=# alter user test1 login;
ALTER ROLE

3 问题与改进

3.1 问题说明

如2中的步骤确实可以达到登录失败5次就锁定用户但是每次都是全量查询postgres_log表
举个例子如果你已经登录失败了5次现在给用户解锁下次再登录的时候 sql的逻辑是从postgres_log表按照log_time 倒序
取前5个正常分析那你这次解锁后的第一次登录你的失败次数已经是5了
从功能上来说确实是实现了但是关于已经失败的次数不准确也没有时间限制时间范围是过去的所有的时间

如下的操作通过引入用户登录表来解决上述不准确的问题给登录失败添加了时间 2小时内失败超过5次就锁定用户

这部分的内容是由公司ITSM研发Frank完成的感谢大佬鼎力相助让session_exec的方案更加完善致敬 😄

这部分的具体逻辑都在最后的login函数里读者可以自己研读做如下几点提示如果直接阅读有困难可以参考

login函数先计算2小时之前的时间点作为检查时间段的起始时间
查询postgres_log表里大于起始一时间的登录失败记录选择最后5条将数据插入到用户登录表用以记录登录失败的用户(注意 t_login表里的都是登录失败的记录)
用户登录表里flag字段默认值是0 表示未过期也就是说表示在两小时内
执行update操作更新用户登录表的flag字段把超过2小时的登录请求 flag设置为1 表示已经失效了超过两小时了后续不拿这些数据做分析、计算
从用户登录表里获取用户最后一次登录失败的时间(上边说过用户登录表里都是用户登录失败的记录所以去最后一条就是用户最后登录失败的记录)
取出用户登录失败的次数(flag为0的表示在有效期内的直接从用户登录表count就行)
比较登录失败的此处跟限定的次数达到条件就锁定用户

3.2 操作步骤

如下操作步骤是基于2的操作之上的

重建日志表

当前操作环境pg位14 如果版本低于14 执行login函数的时候可能会报错如果报错去看下一行日志的字段个数跟这个表的字段格式是否相等 extra_parama和extra_paramb是笔者自己添加的 14版本的pg 不增加两个字段会出错


DROP FOREIGN TABLE public.postgres_log;

CREATE FOREIGN TABLE public.postgres_log (
        log_time timestamp(3) NULL,
        user_name text NULL,
        database_name text NULL,
        process_id int4 NULL,
        connection_from text NULL,
        session_id text NULL,
        session_line_num int8 NULL,
        command_tag text NULL,
        session_start_time timestamptz NULL,
        virtual_transaction_id text NULL,
        transaction_id int8 NULL,
        error_severity text NULL,
        sql_state_code text NULL,
        message text NULL,
        detail text NULL,
        hint text NULL,
        internal_query text NULL,
        internal_query_pos int4 NULL,
        context text NULL,
        query text NULL,
        query_pos int4 NULL,
        "location" text NULL,
        application_name text NULL,
        process_name text NULL,
        extra_parama text NULL,
        extra_paramb int8 NULL
)
SERVER pglog
OPTIONS (program 'find /data/logs/pg -type f -name "*.csv" -mtime -1 -exec cat {} \;', format 'csv');

创建用户登录表

CREATE TABLE public.t_login (
        login_time timestamp with time zone NOT NULL,
        user_name text NOT NULL,
        flag int4 NULL DEFAULT 0
);
CREATE INDEX t_login_time_idx ON public.t_login (login_time);
CREATE INDEX t_login_user_name_flag_idx ON public.t_login (user_name,flag);
COMMENT ON TABLE public.t_login IS '用户登录记录表';

-- Column comments

COMMENT ON COLUMN public.t_login.login_time IS '登录时间';
COMMENT ON COLUMN public.t_login.user_name IS '登录用户';
COMMENT ON COLUMN public.t_login.flag IS '过期表示：0未过期，1过期';

重建login

create or replace function public.login() returns void as $$
declare
res record;
failed_login_times int = 5;
failed_login int = 0;
-- 用户登录最近时间
failed_login_last_time timestamp(3);
-- 过期时间
expiration_time timestamp(3);
begin
        
-- 设置登录失败检查时段的起始时间
SELECT now()::timestamp + '-2 hour' into expiration_time;
raise notice 'expiration_start_time: %!', expiration_time;

-- 获取数据库中所有可连接数据库的用户
for res in select rolname from pg_catalog.pg_roles where rolcanlogin= 't' and rolname !='postgres'
loop
  raise notice 'user: %!', res.rolname;

  -- 获取用户上次登录过期时间
  select login_time from public.t_login where user_name=res.rolname
     order by login_time desc limit 1 into failed_login_last_time;
  if failed_login_last_time is null then 
    failed_login_last_time = expiration_time;
  end if;
  raise notice 'failed_login_last_time: %!', failed_login_last_time;
 
  -- 将最新的数据插入t_login表
  insert into public.t_login(login_time,user_name)
  select log_time,user_name from public.postgres_log
      where command_tag='authentication' and error_severity= 'FATAL'
        and user_name = res.rolname and log_time > failed_login_last_time
        and (detail is null or detail not like 'Role % does not exist.%')
        order by log_time desc limit failed_login_times;
 
  -- 距离现在超过2小时的设置为过期数据
  update public.t_login set flag=1 where user_name=res.rolname and flag=0 and login_time < expiration_time;
 
  -- 获取当前用户最近连续登录失败次数
  select count(1) from public.t_login WHERE user_name=res.rolname and flag=0 into failed_login ;
     
  raise notice 'failed_login_times: %! failed_login: %!',failed_login_times,failed_login;
  --用户最近密码输入错误次数达到5次或以上
  if failed_login >= failed_login_times then
    --锁定用户
    EXECUTE format('alter user %I nologin',res.rolname);
    raise notice 'Account % is locked!',res.rolname;
    -- 设置当前表中数据全部过期，重新计算过期次数
    update public.t_login set flag=1 where user_name=res.rolname and flag=0;
  end if;
end loop;
end;
$$ language plpgsql strict security definer set search_path to 'public';