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

Le mer. 6 mai 2020 à 04:18, Christian Ramseyer <rc(at)networkz(dot)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.
>
> 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.

--
Guillaume.