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>

hy !! I try to use your script , but what is /usr/local/bin/postgres_set_role_nologin shell script ?

Hi! This was really just some brainstorming we did on the Postgres mailing list, the scripts to ban/unban would still need to be written. As I wrote in the linked post:

The main blanks are in the postgres-action.conf section. The called
scripts in /usr/local/bin would need to be written. It can be as simple
as "psql -c alter role xxx nologin", but you might add some features
like connecting to the primary server if fail2ban triggered on the
standby. Also I'm not sure if setting nologin is the best way to disable
an account, but I'm sure somebody on here could tell you.

The last post in the thread has a summary of the open issue from my point of view:

https://www.postgresql.org/message-id/cec31a7c-b16b-d746-711d-1e2cc0b02135%40networkz.ch

There are some rough corners, I think to make it useful we would need to
do at least:

1. Write reasonable scripts for account locking/unlocking

2. Currently the lockout will also be executed for non-existing user
names and thus make the DOS worse, so we'd need a smart solution for
that (config file with valid users, or cached queries into PG from time
to time to get the existing users, or just being smarter on the log
parsing DETAILS line)

3. Examples how to combine with
https://www.postgresql.org/docs/current/auth-delay.html and/or firewall
drops, so that an attacker gets slowed down. Even if the account is
locked already, the system will still be harmed otherwise._

The mailing list thread didn't go much further after that, so I don't think anybody is using this stuff in production. But I turned the gist into a repository, in case you get it to work and you like to share the parts you had to add, feel free to either fork this for yourself or post your additions as a PR: https://github.com/rc9000/postgres-fail2ban-lockout

Cheers
Christian

thanks for this clever answer