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

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(at)snowman(dot)net>
Sent: Tuesday, May 05, 2020 2:46 PM
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Wolff, Ken L (US) <ken(dot)l(dot)wolff(at)lmco(dot)com>; Allan Kamau <kamauallan(at)gmail(dot)com>; 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?

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