Образовательный проект «SnakeProject» Михаила Козлова

⇒ FreeBSD and Nix ⇐

CISCO

Voice(Asterisk\Cisco)

Microsoft

Powershell

Python

SQL\T-SQL

1С

Общая

WEB Разработка

ORACLE SQL \ JAVA

Мото

Стрельба, пневматика, оружие

Саморазвитие и психология

Postgresql ban password authentication failed ip

Postgresql ban password authentication failed ip

/etc/postgresql/17/main/postgresql.conf
log_connections = on log_line_prefix = '%m {%h} [%p] %q%u@%d '

systemctl restart postgresql

who badman:

for f in /var/log/postgresql/postgresql-17-main.log.*.gz; do echo `date -r $f +"%Y-%m-%d"` `zcat $f | grep 'FATAL: password authentication failed for user' |wc -l` ; done test@test 1.1.1.1 FATAL: password authentication failed for user "test" cat /var/log/postgresql/postgresql-17-main.log | grep 'FATAL: password authentication failed for user' | grep -oE "\b([0-9]{1,3}\.){3}[0-9]{1,3}\b" | uniq

/root/psotgresql_ban.sh
#!/bin/bash iptables -F INPUT cat /var/log/postgresql/postgresql-17-main.log | grep 'FATAL: password authentication failed for user' | grep -oE "\b([0-9]{1,3}\.){3}[0-9]{1,3}\b" | uniq | xargs echo {} > /tmp/ban cat /var/log/postgresql/postgresql-17-main.log | grep 'Role ".*" does not exist' | grep -oE "\b([0-9]{1,3}\.){3}[0-9]{1,3}\b" | uniq | xargs echo {} >> /tmp/ban for line in $(cat /tmp/ban); do iptables -A INPUT -s $line -j DROP -m comment --comment "ban postgresql" ; done

debug:

sh -x /root/psotgresql_ban.sh

list rules:

iptables -L INPUT --line-numbers

drop rule by num^
iptables -D INPUT 2
iptables -D INPUT 1

cron:

crontab -e
*/15 * * * * /root/psotgresql_ban.sh

Комментарии пользователей