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. 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. Cheers Christian -- Christian Ramseyer, netnea ag Network Management. Security. OpenSource. https://www.netnea.com
