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 определяет уровень родства

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

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

10 октября 2022 СуБД awk

for t in $(echo \\dt | psql $BASE |grep public |awk '{print $3}'); do 
    echo "SELECT setval('$t"_id_seq"', (select max(id)+1 from $t), true);"; 
done | psql $BASE

Суть проблемы в том, что при репиликации таблицы значение последовательности таблицы автоматически не меняется, по этому после отключения репликации значение last_insert_id будет отставать от реального количество записей в таблице

19 июля 2022 СуБД awk

После неудачного эксперимента с бекапами база данных может содержать по несколько копий каждой строки таблицы, это приводит любой ORM в ступор, впрочем и руками удалить такие записи не полуться, так записи имеют одинаковые ID. Ниже решение как легко удалить дубли и оставить только по одной уникальной строке
 

table_name=auth_users

data_base=breys

echo "
begin;

select count(1) from $table_name ;

create  table ttt as select distinct id x,  * from $table_name ;

alter table ttt drop  column x;

truncate table $table_name; 

insert into $table_name (select * from ttt );

drop table ttt ;

select count(1) from $table_name ;

end;
" | psql $data_base

Суть метода такова:

1. создать копию таблицы содержающую только уникальные по ID записи, для этого используется DISTINCT,
2. затем нужно удалить сигнальное поле x
3. почистить целевую таблицу
4. затем перенести данные из временной таблицы в целевую
5. удалить временную таблицу

Если необходимо обработать все таблицы базы данных то скрипт можно использовать в цикле

for table_name in $(echo '\dt' | psql $data_base | awk '{print $3}'| grep -v ^$|sort ); do  

echo "
begin;

select count(1) from $table_name ;

create  table ttt as select distinct id x,  * from $table_name ;

alter table ttt drop  column x;

truncate table $table_name; 

insert into $table_name (select * from ttt );

drop table ttt ;

select count(1) from $table_name ;

end;
" | psql $data_base; 

done

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

28 июня 2018 04 марта 2022 СуБД

Есть таблица dispatch_dispatch_form в дублирующимися строками

чтобы быстро почистить таблицу от дублей необходимо:

delete from dispatch_dispatch_form  a using dispatch_dispatch_form b WHERE a.id < b.id AND a.email = b.email ;

вот так всё просто

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 сервера на место

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 код

30 августа 2021 СуБД select psql concat

Иногда появляется необходимость скопировать некоторые поля из одной базы данных в другую. Как мне кажется, самым простым способом копирования отдельных полей у некоторых строк является генерация SQL скрипта в одной базе и исполнение этого запроса на другой. Например,

select order_num, slug  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num

таким запросом выбираем необходимые строки и поля в исходной базе данных, затем из результатов этого запроса формирует скрипт обновления базы назначений

select concat('update catalog_humanmenu set order_num=',order_num,' where slug=''',slug,''';')  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num;

теперь переключаем вывод результатов запроса не в терминал, а файл на локальном или удалённом диске

\o ordering-guid.txt

-- затем повторяем генератор SQL скрипта и вывод этой команды сохранится в файле ordering-guid.txt

select concat('update catalog_humanmenu set order_num=',order_num,' where slug=''',slug,''';')  from catalog_humanmenu where tree_id = 6 and is_public= true  and order_num > 0 order by order_num;

-- закрываем SQL сессию

\q

в новообразованном файле ordering-guid.txt необходимо удалить первые и последние строки, а затем выполнить его в базе назначения

psql < ordering-guid.txt

Всё, необходимые поля из отобранных строк перенесены в соответствующие поля и строки базы назначения