On 06.05.20 13:48, Guillaume Lelarge wrote:
> Le mer. 6 mai 2020 à 04:18, Christian Ramseyer <rc@networkz.ch
> <mailto:rc@networkz.ch>> a écrit :
>
> Here is a quick, rough example with still some blanks to fill in - I put
> it on github for readability:
> <https://gist.github.com/rc9000/fd1be13b5c8820f63d982d0bf8154db1>
>
> 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.
>
>
> I already knew about fail2ban, but didn't know it could be set up this
> way. That's pretty impressive. I've just finished testing your config
> files, and it works really well (well, when you finally get rid of the
> selinux permission errors :) ). Anyway, thanks a lot for sharing this.
>
Thanks for trying it out and the kind words, Guillaume & Ken !
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.
I'm happy to host this project if it helps enterprise adaption of
Postgres. I've converted the gist into an acutal repository, and you're
all very welcome to become contributors:
https://github.com/rc9000/postgres-fail2ban-lockout
Cheers
Christian
--
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com