Re: Lock Postgres account after X number of failed logins?

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