Le mer. 6 mai 2020 à 04:18, Christian Ramseyer <rc@networkz.ch> a écrit :
On 05.05.20 16:13, Wolff, Ken L wrote: > Hi, everyone. Wondering if there’s a way in PostgreSQL to automatically > lock accounts after a number of failed logins (a security requirement > for my organization). > > Locking accounts after X number of failed logins is an excellent way to > defeat brute force attacks, so I’m just wondering if there’s a way to do > this, other than the aforementioned hook. > >
Hi Ken
This doesn't seem mentioned in other replies so far: a very "unixy" approach to bolt this feature onto almost any Linux server process is the fail2ban (https://github.com/fail2ban/fail2ban) utility. This is a daemon that reads arbitrary logfiles, and then triggers an action if some failure condition is seen a number of times.
Typically this will scan the logfile for an IP and on failure add a temporary firewall rule to block the source, but all of this is configurable. So in your case you can lock the account instead, and then decide if you want automatic unlocking after a while, if you want to drop the IP that tried to login additionally on the firewall as well, etc.
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.
