18 декабря 2023 СуБД fail2ban postgres

Если postgres используется в качестве мастерсервера для репликаций то у него должен быть открыт порт для подключения клиентов извне, соответственно открытый порт начнут сканировать с целью подобрать пароли и тогда в логах появится вот такое записи

postgres@postgres FATAL:  no pg_hba.conf entry for host "115.216.124.164", user "postgres", database "postgres", no encryption
postgres@postgres FATAL:  no pg_hba.conf entry for host "222.90.83.209", user "postgres", database "postgres", no encryption

Причём, подбор паролей идёт активно, десятки тысяч запросов за сутки

for f in /var/log/postgresql/postgresql-14-main.log.*.gz; do 
echo `date -r $f +"%Y-%m-%d"` `zcat $f | grep 'no pg_hba.conf entry for host' |wc -l` ; 
done

2023-10-15 79090
2023-12-10 81664
2023-12-03 33115
2023-11-26 22769
2023-11-19 100753
2023-11-12 70794
2023-11-05 130725
2023-10-29 82514
2023-10-22 119528

Для блокировки адресов источников запроса необходимо добавить фильтр и добавить новое правило в fail2ban

/etc/fail2ban/filter.d/postgres.conf

[Definition]
failregex = FATAL:  no pg_hba.conf entry for host "<HOST>"

Затем созданное имя фильтра необходимо использовать в правиле, добавив в конец файла /etc/fail2ban/jail.conf

[postgresql]
enabled   = true
filter    = postgres
action    = iptables[name=PostgreSQL, port=5432, protocol=tcp, blocktype=DROP]
           sendmail-whois[name=PostgreSQL, dest=root]
logpath   = /var/log/postgresql/postgresql-14-main.log
maxretry  = 3
findtime  = 600
bantime   = 604800

После этого необходимо перезапустить fail2ban: service fail2ban restart 

и проконтролировать перезапуск сервиса просмотрел логи: tail -f /var/log/fail2ban.log

• fail2ban-client status # — покажет список сервисов события которых обслуживает fail2ban
• fail2ban-client status postgresql # — показать статус обработки postgresql
• fail2ban-client set postgresql  unbanip 115.216.124.164 222.90.83.209 34.76.158.233 # — удалить из бана заданные IP

29 сентября 2023 СуБД Postgres RECURSIVE

Рекурсивных запрос к Postgres состоит из двух, объединённых запросов

1. запрос задающий начальные условия рекурсии
2. запросов определяющий следующую порцию данных для выборы

Рекурсия начинает с добавляния в результат данных от первой выборки  и будет продолждать до тех пока второй запрос будет возвращать данные

WITH RECURSIVE family_children as (

select id, 0 as level, fio from family where fio ~ '^Иванов'

union 

select family.id, level+1 as level, family.fio from family join parent on family.id=parent.child join family on family.id=parent.id

) select * from family_children;

Приведённы в примере запрос начинается с поиска в Таблице Имён записей всех Ивановых. В результат подмешивается переменная level, level определяет уровень родства

Затем запрос продолжает поиском записей родителей любых Ивановых. При этом уровень родства увеличился единицу

Запросы будут продолжаться пока будут находиться родители для предыдущей порции детей

22 апреля 2021 26 августа 2022 СуБД Postgresql psql bash awk for drop table cascade | решать тесты

• Знаете ли вы другой способ удаления группы таблиц в Postgres

Скрипт каскадного удаления таблиц в psql
 

for t in $(echo '\dt' | psql $data_base |awk -F\| '{print $2}'); do 
    echo "drop table $t cascade;" ; 
done | psql $data_base

Суть скрипта проста:

• echo '\dt' | psql $database # выводит список таблиц базы данных из переменной $database
• awk -F\| '{print $2}' # вывести второй столбец из списка
• do echo "drop table $t cascade;" ; done # тело цикла формирующее набор SQL команд
• | psql $database # выполнить SQL код

08 января 2018 30 августа 2021 СуБД Postgresql Mysql | решать тесты

• Нужно ли перезапускать сервер Mysql?

Нет простого способа перекинуть данные из разных СУБД, даже в OpenSource. Но всё же можно и сделать это можно через промежуточный формат CSV следующим образом в терминале Postgresql выгружаем нужную таблицу в файл /tmp/test.csv

Copy (Select * from region) To '/tmp/test.csv' With CSV DELIMITER '|';

Затем, необходимо разрешить Mysql загружать данные из внешних файлов, для этого нужно временно добавить в файл /etc/mysql/my.cnf в секцию добавить параметр secure-file-priv

[mysqld]
#
# * Basic Settings
#
secure-file-priv = ""

затем нужно сохранить файл и перезапустить Mysql сервер. После того как сервер перезапущен необходимо переложить файл в специальную директориую которая разрешена для чтения в рамках Mysql

mv /tmp/test.csv /var/lib/mysql-files/

после это заходим в терминал mysql и загружаем данные из нашего файла

LOAD DATA INFILE '/var/lib/mysql-files/test.csv' INTO TABLE region FIELDS TERMINATED BY '|' ;

всё, теперь можно удалить файл и вернуть настройки Mysql сервера на место