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

On Tue, May 5, 2020 at 5:28 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> On 5/5/20 7:13 AM, 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). I’ve been investigating this for a while and the
> > only reference I’ve found is to write a hook:
> > https://wiki.postgresql.org/images/e/e3/Hooks_in_postgresql.pdf , which
> > is a little more involved than I’d hoped. Was hoping there was
> > something native available within PostgreSQL.
>
> There is not.
>
> You might want to take a look at this thread:
>
>
> https://www.postgresql.org/message-id/OF010D9AFE.7D96A308-ON85257AB6.00746957-85257AB6.0074746B%40us.ibm.com
>
>
> >
> > 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.
> >
> > This is my first time using this mail list so apologies in advance if
> > I’m not following etiquette or doing something incorrectly.
> >
> > Thanks in advance.
> >
> > Ken W
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com

You can configure PostgreSQL to authenticate via your organisations LDAP or
Active directory then benefit from your organisation's user login account
locking mechanism.

Just in case it proves difficult to find or implement login locking as you
have requested here is a plan B different from what you have requested.

Ideally access to PostgreSQL could be via some authorized applications
which may be developed inhouse, unless your users are required to connect
use psql issue SQL queries directly.
In this case you would only need to secure the application by restricting
access to their use by the use of logins authenticated against your
institutional account manager such as Active Directory.

If your applications are webapplications or are deployed from a small pool
of known computers that would host the applications that need to access the
database, you can restrict access to the port PostgreSQL is listening to by
configuring pg_hba.conf. In this file you would permit only connections
originating from the IP address of the computer(s) that host your
applications as well as the user name and the databases they can connect to.
Then you can also configure firewall rules at the OS of the server hosting
PostgreSQL to disallow connections from all IP addresses except those that
are for the hosts on which your database accessing applications are running
from.

If this fails to meet your requirement, depending the size of your
workload, you can deploy all your database accessing applications on the
same computer on which PostgreSQL is deployed.
Then disallow all TCP/IP connections other than localhost to your database.
This way the only way to anyone to log into PostgreSQL would be from a ssh
or interactive session at the computer hosting PostgreSQL.
Here you would only need to enforce account locking at the OS level for
failed attempts, this would be seamless if log ins to your nodes are via or
organisation's user authentication machinery which would already have the
account locking mechanisms for N number of failed attempts.

Allan.