On 06.05.20 13:48, Guillaume Lelarge wrote: > Le mer. 6 mai 2020 à 04:18, Christian Ramseyer <rc@xxxxxxxxxxx > <mailto:rc@xxxxxxxxxxx>> 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
