Lock Postgres account after X number of failed logins?

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:

/messages/by-id/OF010D9AFE.7D96A308-ON85257AB6.00746957-85257AB6.0074746B@us.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

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:

/messages/by-id/OF010D9AFE.7D96A308-ON85257AB6.00746957-85257AB6.0074746B@us.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.

Allan.

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

/messages/by-id/OF010D9AFE.7D96A308-ON85257AB6.00746957-85257AB6.0074746B@us.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<mailto: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.
Allan.

"Wolff, Ken L" <ken.l.wolff@lmco.com> writes:

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.

It's been discussed, but it's quite unlikely that we'd add features in
this area. The project position is that if you have such requirements,
you can address them by using external authentication management, like
LDAP or PAM. If we tried to take this on board, first we'd have a bunch
of problems with scope creep (because there are so many different random
requirements that people might have), and second we'd have a bunch of
architectural issues with where to keep the relevant state. It can't
be ordinary database state --- at least not if you'd like to use the
feature on read-only slave servers, and even a single server would
have issues executing a transaction from a not-logged-in session ---
but then where *do* we keep it, and how would an admin see or adjust the
state? It's a can of worms we don't really care to open, especially
when there are perfectly good solutions already available outside PG
proper.

regards, tom lane

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

"Wolff, Ken L" <ken.l.wolff@lmco.com> writes:

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.

It's been discussed, but it's quite unlikely that we'd add features in
this area. The project position is that if you have such requirements,
you can address them by using external authentication management, like
LDAP or PAM.

These kinds of requirements can't be reasonably met using PAM, I know
because I've tried and it's ridiculous and fragile. Saying to do it
with LDAP is basically saying to do it with AD, which the OP
specifically said wasn't an option in some cases, and that's an entirely
reasonable argument to make. GSSAPI/Kerberos would be the same too- if
you can't get an AD account for whatever it is you're doing, then those
options are out the window. If you go out and buy a specific RADIUS
based product or build out such a system, maybe that'd work, but there's
really no good reason that we are lacking such basic capabilities except
that no one has spent the time to improve things in this area- which is
even more unfortunate now that we have a decent password-based auth
system in the form of SCRAM.

I have to say that I also disagree that it's the project's position that
we aren't going to do anything in this area to improve things. What is
needed is careful consideration and thought about how to solve these
problems properly, in core, and we need to stop pushing people away who
have these requirements. They're not unreasonable requirements to have,
and to expect from our built-in password-based system, and we simply
don't have any good solutions, and what we need to do is encourage
people to work on these issues and try to address them as these
requirements are simply not going to go away- they haven't in the past
20 years and they certainly don't seem to be going anywhere anytime
soon.

If we tried to take this on board, first we'd have a bunch
of problems with scope creep (because there are so many different random
requirements that people might have), and second we'd have a bunch of
architectural issues with where to keep the relevant state. It can't
be ordinary database state --- at least not if you'd like to use the
feature on read-only slave servers, and even a single server would
have issues executing a transaction from a not-logged-in session ---
but then where *do* we keep it, and how would an admin see or adjust the
state? It's a can of worms we don't really care to open, especially
when there are perfectly good solutions already available outside PG
proper.

Yes, there's an awful lot that we're missing, in no small part, imv at
least, because of the constant argument against doing anything. The
OP's post is a great example of exactly the use-cases where we are
falling far short of entirely reasonable expectations, and we haven't
got any good solutions to address that.

As it relates to how the feature would be designed and would work,
particularly with things like read replicas, yes, there's certainly
complications to address there but that doesn't make either the feature
bad nor does it make PAM a reasonable solution. Having this only work
for attempts to login against the primary would certainly be a good
first step, in any case, and people who really needed replicas that
users can login to directly and have this requirement could use logical
replication. Perhaps we could feed back failed login attempts to the
primary through the replication protocol, or even consider having the
equivilant of unlogged catalog tables , where each replica has its
own set of data. This isn't the place to hash that out though.

Thanks,

Stephen

Stephen & Tom,

Want to thank you both for taking the time to respond.

Sometimes tone gets lost in email so please understand I'm saying this with all respect: my company is starting an effort to move away from "commercial" databases (you can probably guess which ones) and Postgres has been identified as the preferred destination. However, if we can't figure out a way to meet this account lock requirement at the database level, plus some other features like enhanced password complexity, its very possible we may end up going in another direction. That's not anyone's problem but our own, but I do suspect other companies will arrive at the same conclusion. From everything else I've seen, Postgres looks like a great product; I've been surprised how robust it is and how easy it's been to learn, so I'd like to see it adopted more widely. But if my company can't figure out a way to meet these requirements, it's doubtful our security organization will allow us to proceed. There's just too much chance of accounts being created in the database that won't be tied to, or otherwise accounted for, in applications. Frankly, it's also not a risk which we can justify to our customers or auditors.

As Stephen states, even some basic functionality in this regard would go a long way. Perhaps something could be built into the postgresql-contrib RPM? Right now the only way I see is to write a hook, which involves changing source code, which then puts us into the situation of (1) maintaining our own code tree and (2) figuring out how to produce a new set of RPMs.

I realize Postgres is a community project and that there are a great number of other valuable feature/enhancement requests in the queue. Just adding my $.02 here.

Thanks again for your time & thoughts.

Ken

-----Original Message-----
From: Stephen Frost <sfrost@snowman.net>
Sent: Tuesday, May 05, 2020 2:46 PM
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Wolff, Ken L (US) <ken.l.wolff@lmco.com>; Allan Kamau <kamauallan@gmail.com>; pgsql-general@lists.postgresql.org; Adrian Klaver <adrian.klaver@aklaver.com>
Subject: EXTERNAL: Re: Lock Postgres account after X number of failed logins?

Greetings,

* Tom Lane (tgl@sss.pgh.pa.us) wrote:

"Wolff, Ken L" <ken.l.wolff@lmco.com> writes:

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.

It's been discussed, but it's quite unlikely that we'd add features in
this area. The project position is that if you have such
requirements, you can address them by using external authentication
management, like LDAP or PAM.

These kinds of requirements can't be reasonably met using PAM, I know because I've tried and it's ridiculous and fragile. Saying to do it with LDAP is basically saying to do it with AD, which the OP specifically said wasn't an option in some cases, and that's an entirely reasonable argument to make. GSSAPI/Kerberos would be the same too- if you can't get an AD account for whatever it is you're doing, then those options are out the window. If you go out and buy a specific RADIUS based product or build out such a system, maybe that'd work, but there's really no good reason that we are lacking such basic capabilities except that no one has spent the time to improve things in this area- which is even more unfortunate now that we have a decent password-based auth system in the form of SCRAM.

I have to say that I also disagree that it's the project's position that we aren't going to do anything in this area to improve things. What is needed is careful consideration and thought about how to solve these problems properly, in core, and we need to stop pushing people away who have these requirements. They're not unreasonable requirements to have, and to expect from our built-in password-based system, and we simply don't have any good solutions, and what we need to do is encourage people to work on these issues and try to address them as these requirements are simply not going to go away- they haven't in the past
20 years and they certainly don't seem to be going anywhere anytime soon.

If we tried to take this on board, first we'd have a bunch of problems
with scope creep (because there are so many different random
requirements that people might have), and second we'd have a bunch of
architectural issues with where to keep the relevant state. It can't
be ordinary database state --- at least not if you'd like to use the
feature on read-only slave servers, and even a single server would
have issues executing a transaction from a not-logged-in session ---
but then where *do* we keep it, and how would an admin see or adjust
the state? It's a can of worms we don't really care to open,
especially when there are perfectly good solutions already available
outside PG proper.

Yes, there's an awful lot that we're missing, in no small part, imv at least, because of the constant argument against doing anything. The OP's post is a great example of exactly the use-cases where we are falling far short of entirely reasonable expectations, and we haven't got any good solutions to address that.

As it relates to how the feature would be designed and would work, particularly with things like read replicas, yes, there's certainly complications to address there but that doesn't make either the feature bad nor does it make PAM a reasonable solution. Having this only work for attempts to login against the primary would certainly be a good first step, in any case, and people who really needed replicas that users can login to directly and have this requirement could use logical replication. Perhaps we could feed back failed login attempts to the primary through the replication protocol, or even consider having the equivilant of unlogged catalog tables , where each replica has its own set of data. This isn't the place to hash that out though.

Thanks,

Stephen

Wolff, Ken L <ken.l.wolff@lmco.com> writes:

As Stephen states, even some basic functionality in this regard would go a long way. Perhaps something could be built into the postgresql-contrib RPM? Right now the only way I see is to write a hook, which involves changing source code, which then puts us into the situation of (1) maintaining our own code tree and (2) figuring out how to produce a new set of RPMs.

I realize Postgres is a community project and that there are a great number of other valuable feature/enhancement requests in the queue. Just adding my $.02 here.

The problem here is that everyone has valid points.

Tom is quite correct that this sort of security policy really needs to
be implemented in a single central location, such as LDAP, AD or some
other IAM middleware. Having security policies implemented separately in
different systems is where failures creep in and why maintenance
becomes a problem.

Where Tom's solution fails is with smaller companies that cannot afford
this level of infrastructure. They can still fall victim to the same
level of regulatory bureaucracy, but without the necessary level of
technical resources of larger organisations. For these organisations,
basic facilities, like the ability to lock an account after a certain
number of failed login attempts for a period of time is a very useful
feature.

My suggestion would be to develop the basic requirements and contribute
the result to Postgres. This would give back to the community and
eliminate the need to maintain separate code in the long-term. The cost
of paying for extra resources to do this development and maintenance is
still going to be less than the licensing costs for that commercial
competitor. Just requesting the facility is unlikely to result in any
acceptable outcome within any reasonable time frame.

If your security people are really on top of their game, they will be
providing you with a security architecture which fulfils the enterprise
architecture requirements and which centralises IAM management. This is
really the only truly secure solution which guarantees access is removed
from all system in a timely manner, enables effective logging and
auditing of access, ensures consistent application of security policy
and allows consistent response to security incidents and events. While
requiring additional resources to establish, it does tend to result in
reduced maintenance costs in the longer term.

--
Tim Cross

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&gt;

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.

Cheers
Christian

--
Christian Ramseyer, netnea ag
Network Management. Security. OpenSource.
https://www.netnea.com

On Wed, 6 May 2020 at 00:05, Tim Cross <theophilusx@gmail.com> wrote:

Where Tom's solution fails is with smaller companies that cannot afford
this level of infrastructure.

Is there an objection to openldap? It's lightweight (so could
reasonably be run on the same hardware without significant impact),
BSD-ish and mature, and (with the password policy overlay) should
provide exactly the functionality the OP requested.

Geoff

Hi Geoff,

On 06. May, 2020, at 10:33, Geoff Winkless <pgsqladmin@geoff.dj> wrote:

Is there an objection to openldap? It's lightweight (so could
reasonably be run on the same hardware without significant impact),
BSD-ish and mature, and (with the password policy overlay) should
provide exactly the functionality the OP requested.

Geoff

we use openldap in our company and it works as desired.

Cheers,
Paul

Le mer. 6 mai 2020 à 04:18, Christian Ramseyer <rc@networkz.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&gt;

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.

Geoff Winkless <pgsqladmin@geoff.dj> writes:

On Wed, 6 May 2020 at 00:05, Tim Cross <theophilusx@gmail.com> wrote:

Where Tom's solution fails is with smaller companies that cannot afford
this level of infrastructure.

Is there an objection to openldap? It's lightweight (so could
reasonably be run on the same hardware without significant impact),
BSD-ish and mature, and (with the password policy overlay) should
provide exactly the functionality the OP requested.

OpenLDAP is certainly the way I would go. However, for a number of
reasons, smaller companies seem somewhat resistant to that level of
integration. I suspect it is primarily because LDAP skills are less
prevalent amongst admins in these areas. Often, these companies don't
really have a planned architecture - things have grown organically and
got to the point where existing resources are fully allocated just
trying to keep all the bits running. It can be hard to sell the idea,
especially as those making the decisions are not across the issues and
from where they sit, it all looks to be working and your asking for more
resources when it doesn't seem to be broken. The IT guys often fail to
sell the benefits because they focus on the technical aspects rather
than on the business aspects.

One client I helped had admins who had been trying to move everything
over to a centralised LDAP solution for ages and failing. They had
presented great justification for why it was needed, but it focused on
the technical benefits rather than the business continuity, process
improvement and security benefits. Once we put together a new business
case which focused on improved processes for managing access, reduced
security audit costs and improved security controls, they were sold and
made the project a priority.

Based on additional info I saw from the OP and plans to roll out
many databases, I think a centralised directory service approach is
really their only saleable and maintainable solution. In fact, they
probably need to look at their overall identity management architecture.
Failure to get that basic service correct will result in major support
issue blow out as they increase their customer base.

--
Tim Cross

Greetings,

* Geoff Winkless (pgsqladmin@geoff.dj) wrote:

On Wed, 6 May 2020 at 00:05, Tim Cross <theophilusx@gmail.com> wrote:

Where Tom's solution fails is with smaller companies that cannot afford
this level of infrastructure.

Is there an objection to openldap? It's lightweight (so could
reasonably be run on the same hardware without significant impact),
BSD-ish and mature, and (with the password policy overlay) should
provide exactly the functionality the OP requested.

LDAP-based authentication in PG involves passing the user's password to
the database server in the clear (or tunneled through SSL, but that
doesn't help if the DB is compromised), so it's really not a good
solution.

Thanks,

Stephen

Thanks again, everyone, for all the responses and ideas. I'm still getting caught up on the various responses but with respect to LDAP/AD, I truly wish it were an option. I agree with the various sentiments that AD authentication is more manageable, scalable, secure, etc. However, if there were one set of environs where you'd think we could rely exclusively on AD authentication, it would be SQL Server, which by default, relies on Windows & AD for its authentication. However, for our company, even in our SQL Server environments, we almost always have to resort to internal (SQL-authenticated) accounts at times for various reasons: usually because vendor software doesn't support AD authentication, but I've even heard some people mention docker containers can't use it, either. Full disclosure - I haven't run that last one down yet, have only heard it in passing so don't know the details.

Christian's idea of fail2ban looks interesting, so I'm going to be investigating that.

Thanks again, all of you. Really appreciate the feedback and ideas!

Ken

-----Original Message-----
From: Stephen Frost <sfrost@snowman.net>
Sent: Wednesday, May 06, 2020 7:28 AM
To: Geoff Winkless <pgsqladmin@geoff.dj>
Cc: Tim Cross <theophilusx@gmail.com>; pgsql-generallists.postgresql.org <pgsql-general@lists.postgresql.org>
Subject: EXTERNAL: Re: Lock Postgres account after X number of failed logins?

Greetings,

* Geoff Winkless (pgsqladmin@geoff.dj) wrote:

On Wed, 6 May 2020 at 00:05, Tim Cross <theophilusx@gmail.com> wrote:

Where Tom's solution fails is with smaller companies that cannot
afford this level of infrastructure.

Is there an objection to openldap? It's lightweight (so could
reasonably be run on the same hardware without significant impact),
BSD-ish and mature, and (with the password policy overlay) should
provide exactly the functionality the OP requested.

LDAP-based authentication in PG involves passing the user's password to the database server in the clear (or tunneled through SSL, but that doesn't help if the DB is compromised), so it's really not a good solution.

Thanks,

Stephen

Greetings,

* Wolff, Ken L (ken.l.wolff@lmco.com) wrote:

Thanks again, everyone, for all the responses and ideas. I'm still getting caught up on the various responses but with respect to LDAP/AD, I truly wish it were an option. I agree with the various sentiments that AD authentication is more manageable, scalable, secure, etc. However, if there were one set of environs where you'd think we could rely exclusively on AD authentication, it would be SQL Server, which by default, relies on Windows & AD for its authentication. However, for our company, even in our SQL Server environments, we almost always have to resort to internal (SQL-authenticated) accounts at times for various reasons: usually because vendor software doesn't support AD authentication, but I've even heard some people mention docker containers can't use it, either. Full disclosure - I haven't run that last one down yet, have only heard it in passing so don't know the details.

At least as it involves vendor software, most of that is built on top of
libpq or JDBC and you can typically make them work with GSSAPI (which is
basically Kerberos, and is what AD/SQL Server uses), which is what you
want to be using. Don't think the "ldap" auth in PG is like SQL Server
AD auth- it isn't, and "ldap" involves passing user's passwords around
in the clear, it's not secure. So, you might not have as much need for
local accounts as you do for SQL server, but it's certainly possible
you'll end up needing them somewhere.

And yes, you can certainly get GSSAPI/Kerberos to work in docker and in
Kube, it's just more complicated due to sorting through DNS/rDNS and
such, but it's been done (and I've done it :).

Thanks!

Stephen

On 2020-05-06 09:28:28 -0400, Stephen Frost wrote:

LDAP-based authentication in PG involves passing the user's password to
the database server in the clear (or tunneled through SSL, but that
doesn't help if the DB is compromised), so it's really not a good
solution.

Still a lot better than PostgreSQL's md5 scheme, which stores
password-equivalent hashes: If the database is compromised the attacker
has all hashes immediately and can use them to login. Intercepting
encrypted traffic even at the endpoint is much harder and can only
uncover passwords actually used.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

On Wed, May 6, 2020 at 5:26 PM Peter J. Holzer <hjp-pgsql@hjp.at> wrote:

On 2020-05-06 09:28:28 -0400, Stephen Frost wrote:

LDAP-based authentication in PG involves passing the user's password to
the database server in the clear (or tunneled through SSL, but that
doesn't help if the DB is compromised), so it's really not a good
solution.

Still a lot better than PostgreSQL's md5 scheme, which stores
password-equivalent hashes: If the database is compromised the attacker
has all hashes immediately and can use them to login. Intercepting
encrypted traffic even at the endpoint is much harder and can only
uncover passwords actually used.

If the database is compromised the attacker already has the data, though,
so not as many needs to log in anymore.

But more to the point -- one should not use md5 in PostgreSQL these days,
one should be using scram-sha-256 which does not have this problem (and has
been around for a few years by now)., if using local database logins.

--
Magnus Hagander
Me: https://www.hagander.net/ <http://www.hagander.net/&gt;
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/&gt;

Greetings,

* Peter J. Holzer (hjp-pgsql@hjp.at) wrote:

On 2020-05-06 09:28:28 -0400, Stephen Frost wrote:

LDAP-based authentication in PG involves passing the user's password to
the database server in the clear (or tunneled through SSL, but that
doesn't help if the DB is compromised), so it's really not a good
solution.

Still a lot better than PostgreSQL's md5 scheme, which stores
password-equivalent hashes: If the database is compromised the attacker
has all hashes immediately and can use them to login. Intercepting
encrypted traffic even at the endpoint is much harder and can only
uncover passwords actually used.

No, it's really not better because when you're talking about LDAP it's
usually in reference to AD or similar centralized data store- so now you
get a user's credentials not *just* for access to the particular PG
database that you've compromised but across the *entire* AD environment.

If you just compromise the md5 store (which you shouldn't really be
using anyway, but whatever) then, sure, you can use that PW equivilant
to get access into the DB that you've already compromised, and if they
use the same username for other PG databases then maybe those too, but
you don't get access to their VPN credentials, or the ability to RDP to
any server they're allowed to log in to, or to the SQL server databases
they have access to, or, or, or ...

(at least, not without cracking the md5 hash, which requires at least a
little bit of additional effort and we do "salt" it with the username so
it's not completely trivial... still, please, please, please use SCRAM
for local logins, at least..)

Thanks,

Stephen

On Wed, 6 May 2020, 14:28 Stephen Frost, <sfrost@snowman.net> wrote:

Greetings,

* Geoff Winkless (pgsqladmin@geoff.dj) wrote:

On Wed, 6 May 2020 at 00:05, Tim Cross <theophilusx@gmail.com> wrote:

Where Tom's solution fails is with smaller companies that cannot afford
this level of infrastructure.

Is there an objection to openldap?

LDAP-based authentication in PG involves passing the user's password to
the database server in the clear (or tunneled through SSL, but that
doesn't help if the DB is compromised), so it's really not a good
solution

If your DB is compromised then (if the LDAP server is only used for the db)
what difference does it make to lose the passwords?

I was (as per the thread) suggesting a simple way for small companies to
achieve the OP's requirements without a large infrastructure investment and
without involving the pg team undertaking the rediscovery of novel circular
transportation-assisting devices.

Any large company will have an AD or similar setup already, clearly I'm not
suggesting using it in that situation.

AIUI you can configure kerberos with openldap if that's more your thing,
fwiw, but then IME the learning curve (and thus setup cost) increases
exponentially.

Geoff