References: 
 <CAHnVB4C9tSJkD4Ysd645HJmJjWzNYKJgUumE74YY8FRjUFJE4g@mail.gmail.com>
 <30655.1536069964@sss.pgh.pa.us> <20180905174832.GO4184@tamriel.snowman.net>
User-agent: mu4e 0.9.18; emacs 26.1
From: Tim Cross <theophilusx@gmail.com>
To: Stephen Frost <sfrost@snowman.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
 Praneel Devisetty <devisettypraneel@gmail.com>, pgsql-admin@postgresql.org
Subject: Re: User to get locked after three wrong login attempts.
In-reply-to: <20180905174832.GO4184@tamriel.snowman.net>
Date: Thu, 06 Sep 2018 08:09:49 +1000
Message-ID: <87va7j61g2.fsf@gmail.com>
MIME-Version: 1.0
Content-Type: text/plain
Precedence: bulk


Stephen Frost <sfrost@snowman.net> writes:

> Greetings,
>
> * Tom Lane (tgl@sss.pgh.pa.us) wrote:
>> Praneel Devisetty <devisettypraneel@gmail.com> writes:
>> > We have a requirement , where we require a user to get locked after three
>> > wrong login attempts.
>> 
>> The usual recommendation is to configure Postgres to use PAM
>> authentication; then you can set up any weird requirements like
>> this one in the PAM configuration.
>
> Unfortunately, it's a pain to set up PAM and there's a lot of things in
> the PAM stack which can't be used because PostgreSQL doesn't run as
> root.  We should really have a better solution to this pretty commonly
> asked for capability; I'm hoping to find time soon to hack on that.
>
> Thanks!
>
> Stephen

These days, I think the better solution is to have this functionality in
a central system. Putting aside that it is an 'outdated' auditor
requirement, what the auditor really wants to see is that access to ALL
systems is locked after 3 failed authentication attempts (for a period
e.g. 5 minutes). Having a centralised system also has the benefit of
'same login', so your users have the same username and password across
all services in the organisation and 1 central and consistent place for
password management.

I would suggest looking at what can be achieved with oepnLDAP and/or
Active Directory/Kerberos.

Note that the tricky part with this approach in the era of multiple
devices is getting the parameters tweaked correctly. It is not as easy
as just saying 'after 3 failed logins, lock the account'. You need to
consider what happens when someone changes their password and has
multiple devices logged into different services (e.g. mail). As soon as
the password has changed, some of these devices will begin to fail and
this will happen before the user can open each device and change the
password. If the policy is to restrictive, by the time they do this,
their account is locked and they cannot change the password - now they
are caught in a vicious cycle. Most lockout mechanisms have parameters
you can set to avoid this issue.

Tim
-- 
Tim Cross