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

Greetings,

* Tom Lane (tgl(at)sss(dot)pgh(dot)pa(dot)us) wrote:
> "Wolff, Ken L" <ken(dot)l(dot)wolff(at)lmco(dot)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