Сразу оговорюсь что в бесплатной версии PostgreSQL нет встроенного функционала, который бы это обеспечивал. Поэтому все варианты решения данной задачи в той или иной степени можно будет назвать костылями. Одну статью с использованием fail2ban я уже написал, теперь дело за вторым вариантом.
Суть работы
В PostgreSQL используются хуки, которые если просто выражаться без подробностей этакие api модули, написанные на C. Так вот благодаря этим hook вы можете обращаться к уже запущенным сессиям в PostgreSQL, загружая эти модули при старте PostgreSQL.
Мы будем использовать кастомный hook под названием session_exec. Суть в том, что благодаря session_exec мы можем запускать любую функцию в PostgreSQL, но только если аутентификация прошла успешна, т.е. сессия открыта. Я думаю вы сразу поняли почему этот метод я бы назвал костылём, а если нет, то ответ будет ниже. В любом случае я советую дочитать до конца, может вы не так привередливы, как я.
В итоге схема будет следующая:
- Пользователь удачно авторизуется на сервере PostgreSQL
- Дальше запускается функция, которая читает файлы логов PostgreSQL. Для того чтобы понять, что кто-то неудачно пытался авторизоваться
- Дальше эта функция записывает эти попытки в отдельную таблицу
- Вытаскивает значения для каждого пользователя и если в сумме больше предела банит пользователя
Недостатки
Сразу распишу недостатки этого метода чтобы вы сразу для себя решили нужно ли вам это.
- Нужно руками отменять бан пользователя
- Нужно для каждого пользователя вручную внести в таблицу запись о том, что он удачно залогинился
- Бан пользователя произойдёт только после того как кто-либо удачно авторизуется в PostgreSQL, потому что функция выполнится только при удачной авторизации, но блокируемый пользователь всё равно не сможет войти.
Установка session_exec
- Клонируем репозиторий
sudo git clone https://github.com/okbob/session_exec.git cd session_exec - Компилируем и ставим session_exec
sudo dnf install redhat-rpm-config -y sudo find / -name pg_config sudo make PG_CONFIG=/usr/pgsql-14/bin/pg_config sudo make PG_CONFIG=/usr/pgsql-14/bin/pg_config install
Подготовка таблиц
Как я уже говорил выше нам понадобиться 2 таблицы:
auth_table- в которой будем хранить информацию об авторизацииpostgres_log- будет FOREIGN TABLE для логов
Поэтому создаём эти 2 таблицы, в таблице postgres_log обратите внимание на команду где я делаю парсинг логов PostgreSQL. Если у вас в postgresql.conf значение переменной log_line_prefix не '%m [%p] %q%u@%d@%h ' парнсинг будет отличаться.
sudo su - postgres
vim get_logs.sh
find /var/lib/pgsql/14/data/log/ -type f -name "*.log" -mtime -1 -exec cat {} \; | grep "password authentication failed " | awk -F " " '{print $1,$2,$3"@",$4"@",$5"@",$6"@",$7" "$8" "$9" "$10" "$11" "$12;}' | awk '{ gsub("@ ","@"); print }'
chmod u+x get_logs.sh
psql
create table auth_table
(
login_time timestamp(3),
user_name text ,
auth int4
);
CREATE EXTENSION file_fdw;
CREATE SERVER pglog FOREIGN DATA WRAPPER file_fdw;
CREATE FOREIGN TABLE postgres_log(
log_time timestamp(3),
session_id text,
user_name text,
database_name text,
connection_from text,
error_severity text,
message text
) SERVER pglog
OPTIONS ( program '/var/lib/pgsql/get_logs.sh', format 'csv', delimiter '@');
Создание пользователя для тестов
Для того чтобы проверить можем ли мы получить доступ к логам через таблицу postgres_log создадим пользователя и попробуем специально неудачно залогинится.
sudo vim /var/lib/pgsql/14/data/pg_hba.conf
host all admin 127.0.0.1/32 md5
local all admin md5
sudo systemctl reload postgresql-14
sudo su - postgres
psql
CREATE USER admin WITH PASSWORD '123456';
А я напоминаю, что в проде не стоит писать в команде открытый пароль.
psql -U admin -d postgres
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: password authentication failed for user "admin"
Теперь сделаем select к таблице postgres_log и проверим что доступ к логам PostgreSQL присутствует.
select * from postgres_log;
log_time | session_id | user_name | database_name | connection_from | error_severity | message
-----------------------------+------------+-----------+---------------+-----------------+----------------+---------------------------------------
---------
2022-10-11 11:01:55.938 +06 | [206815] | admin | postgres | [local] | FATAL: | password authentication failed for user admin
Первичная запись
Тут вылезает еще один недостаток этого метода - это то, что нужно вручную добавить запись о удачной аутентификации пользователя.
psql
insert into auth_table select log_time,user_name,0
from postgres_log
where message LIKE '%authentication failed%'
and error_severity= 'FATAL:';
Создание функции
Мы уже почти на финишной прямой, сейчас нам осталось только создать функцию, которая будет вызваться при удачной аутентификации и добавить модуль в сам PostgreSQL.
psql
create or replace function custom_login() returns void as $$
declare
res text;
c1 timestamp(3) with time zone;
begin
-- Get the latest time in the current log
select login_time
from public.auth_table
where auth = 0
order by login_time
desc limit 1
into c1;
-- Insert the latest data into auth_table surface
insert into public.auth_table
select log_time,user_name
from public.postgres_log
where message LIKE '%authentication failed%'
and error_severity= 'FATAL:'
and log_time > c1;
update public.auth_table set auth = 1 where login_time > c1;
-- Check if the number of login failures is greater than 3, If more than 3 Then lock the user
for res in select user_name from public.auth_table where auth = 1 group by user_name having count(*) >=3
loop
-- Lock the user
EXECUTE format('alter user %I nologin',res);
-- Disconnect the currently locked user session
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';
Вызов библиотеки session_exec
Для того, чтобы наша функция вызывалась при удачной аутентификации нам нужно подключить библиотеку session_exec и передать имя функции для вызова.
sudo vim /var/lib/pgsql/14/data/postgresql.conf
session_preload_libraries = 'session_exec'
session_exec.login_name='custom_login'
sudo systemctl restart postgresql-14
Теперь если неудачно залогинится и проверить таблицу auth_table то у нас должна появится запись о неудачной аутентификации от пользователя admin.
psql
select * from auth_table;
2022-10-11 11:01:55.938 | admin | 0
2022-10-11 11:43:28.614 | admin | 1
Тестируем
Теперь нам нужно произвести 3 или более неудачных аутентификаций под пользователем admin. Но заблокируется он только после того как мы удачно авторизуемся на сервере PostgreSQL.
psql -U admin -d postgres
psql -U admin -d postgres
psql -U admin -d postgres
psql
NOTICE: Account admin is locked!
\du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Разблокировка пользователя
А вот разблокировать пользователя придётся вручную, выполнив команды ниже:
update auth_table set auth = 0 where user_name='admin' and auth=1;
alter user admin login ;
\du
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
Итог
Самый большой минус этого решения что нужно удачно залогинится чтобы пользователь всё-таки заблокировался, при этом не важно под каким пользователем вы залогинились (superuser и т.д.). При этом в целом сам пользователь, который неудачно логинился 3 раза после тоже не зайдёт, но вот ошибка будет psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: terminating connection due to administrator command. Но если у вас большой проект и там авторизация происходит часто, то это небольшая проблема.
Второе это то что при каждом логине будет считывается информация с лог файлов PostgreSQL что будет немного притормаживать саму систему. Насколько сильно будет страдать производительность я не знаю, так как тестировал на редко используем сервере. Если уж этот процесс действительно долгий то как решение можно получать логи только сегодняшнего дня используя следующий запрос: find /var/lib/pgsql/14/data/log/ -type f -name "*.log" -mtime -1 -exec cat {} \; | grep "$asd .* password authentication failed ".
В целом вариант конечно же рабочий, но со своими недостатками. Использовать такой способ или нет уже на ваше усмотрение. Иногда бывают безвыходные ситуации и даже такие способы будут полезны для решения задач.
Комментарии