postgres fail2ban configuration with user locking

pg-fail2ban.md

Postgres fail2ban configuration, custom actions

Needs fail2ban 0.10+. See https://github.com/fail2ban/fail2ban/wiki/How-to-ban-something-other-as-host-(IP-address),-like-user-or-mail,-etc. for more information.

Motivated by this discussion on pgsql-general: https://www.postgresql.org/message-id/61463e206b7c4c0ca17b03a59e890b78%40lmco.com

/etc/fail2ban/jail.d/postgres.conf

[postgres-lockuser]

enabled = true
filter = postgres-lockuser
action = postgres-action
logpath = /var/lib/postgresql/data/log/postgresql*.log
maxretry = 3
bantime = 3600
usedns = raw
ignoreip =
ignorecommand =

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

[Definition]
failregex = FATAL:  password authentication failed for user "<F-ID/>"

/etc/fail2ban/action.d/postgres-action.conf

#
# based on the example action.d/dummy.conf 

[Definition]

# Option:  actionstart
# Notes.:  command executed on demand at the first ban (or at the start of Fail2Ban if actionstart_on_demand is set to false).
# Values:  CMD
#
actionstart = if [ ! -z '<target>' ]; then touch <target>; fi;
              printf %%b "<init>\n" <to_target>
              echo "%(debug)s started"

# Option:  actionflush
# Notes.:  command executed once to flush (clear) all IPS, by shutdown (resp. by stop of the jail or this action)
# Values:  CMD
#
actionflush = 
              

# Option:  actionstop
# Notes.:  command executed at the stop of jail (or at the end of Fail2Ban)
# Values:  CMD
#
actionstop = 
             

# Option:  actioncheck
# Notes.:  command executed once before each actionban command
# Values:  CMD
#
actioncheck = 

# Option:  actionban
# Notes.:  command executed when banning an IP. Take care that the
#          command is executed with Fail2Ban user rights.
# Tags:    See jail.conf(5) man page
# Values:  CMD
#
actionban = printf %%b "+<ip> <matches> <fid>\n" <to_target>
            echo "%(debug)s banned <ip> (family: <family>) m <matches> fid <fid>"
	        /usr/local/bin/postgres_set_role_nologin <fid>

# Option:  actionunban
# Notes.:  command executed when unbanning an IP. Take care that the
#          command is executed with Fail2Ban user rights.
# Tags:    See jail.conf(5) man page
# Values:  CMD
#
actionunban = /usr/local/bin/postgres_set_role_login <fid> 
              

debug = [<name>] <actname> <target> <ip> <to_target> --

[Init]

init = 123

target = /var/log/f2b/fail2ban.dummy
to_target = >> <target>

thanks for this clever answer