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

Hi, Allan. Thanks very much for taking the time to respond. Definitely appreciate all your ideas & suggestions. Some may help but for various reasons, at the end of the day, I’m still probably going to be looking for a way to automatically lock accounts after a number of failed logins. I particularly like your idea about using AD authentication but even in our SQL Server environments, we can’t rely on AD completely and end up with a number of SQL/internally-authenticated accounts. I’m sure the same will be true as my organization adopts PostgreSQL. With the potential to eventually deploy hundreds or even thousands of PostgreSQL databases, we’re going to be dealing with a number of different architectures and configurations.

A few people have responded suggesting addressing locking accounts at the application level (i.e.: building something into each application). It may be I’m simply approaching this too much from an Oracle/SQL Server mindset but it seems like configuring this feature at the database level would be more efficient and secure. If implemented at the database level, then during an audit, DBAs could easily confirm the feature is working as intended, instead of having to check with each application. There’s also a concern about accounts being created (for whatever reason) which end up not getting tied to applications. In addition, some of our applications may eventually be 3rd party/vendor/COTS where it won’t be possible to change their code. These are all reasons why I’m trying to figure out a way to handle this internally within PostgreSQL.

I do understand what you described about locking down access through pg_hba.conf, so only authorized users/applications can connect. That makes a lot of sense and I’m going to take it forward to our Information Security organization. However, in case they won’t budge from this requirement, can someone tell me what would be the best way to submit this as a feature request? Simply edit the PostgreSQL Wiki ToDo page (https://wiki.postgresql.org/wiki/Todo) or is there some other method.

Again, thank you very much for taking the time to respond and for all your ideas. I do appreciate it!

Ken

From: Allan Kamau <kamauallan(at)gmail(dot)com>
Sent: Tuesday, May 05, 2020 8:40 AM
To: Wolff, Ken L (US) <ken(dot)l(dot)wolff(at)lmco(dot)com>
Cc: pgsql-general(at)lists(dot)postgresql(dot)org; Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
Subject: EXTERNAL: 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<mailto: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<mailto: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.