Re: Lock Postgres account after X number of failed logins? - Mailing list pgsql-general
| From | Allan Kamau |
|---|---|
| Subject | Re: Lock Postgres account after X number of failed logins? |
| Date | |
| Msg-id | CAF3N6oRb-wYL5yb3ZwP_eAJ=EJDOT+KUGS4ervnjjUS4KU0bdQ@mail.gmail.com Whole thread Raw |
| In response to | Re: Lock Postgres account after X number of failed logins? (Adrian Klaver <adrian.klaver@aklaver.com>) |
| List | pgsql-general |
On Tue, May 5, 2020 at 5:28 PM Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.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.
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.
pgsql-general by date: