Re: Lock Postgres account after X number of failed logins? - Mailing list pgsql-general

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
effortto move away from "commercial" databases (you can probably guess which ones) and Postgres has been identified as
thepreferred 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'sbeen 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
accountsbeing created in the database that won't be tied to, or otherwise accounted for, in applications.  Frankly,
it'salso 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
intothe postgresql-contrib RPM?  Right now the only way I see is to write a hook, which involves changing source code,
whichthen puts us into the situation of (1) maintaining our own code tree and (2) figuring out how to produce a new set
ofRPMs.  
 

I realize Postgres is a community project and that there are a great number of other valuable feature/enhancement
requestsin 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;
AdrianKlaver <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/applicationscan 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
tosubmit this as a feature request?  Simply edit the PostgreSQL Wiki ToDo page (https://wiki.postgresql.org/wiki/Todo)
oris 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
optionin some cases, and that's an entirely reasonable argument to make.  GSSAPI/Kerberos would be the same too- if you
can'tget an AD account for whatever it is you're doing, then those options are out the window.  If you go out and buy a
specificRADIUS based product or build out such a system, maybe that'd work, but there's really no good reason that we
arelacking such basic capabilities except that no one has spent the time to improve things in this area- which is even
moreunfortunate 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
improvethings.  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
needto do is encourage people to work on these issues and try to address them as these requirements are simply not
goingto 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
doinganything.  The OP's post is a great example of exactly the use-cases where we are falling far short of entirely
reasonableexpectations, 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'scertainly complications to address there but that doesn't make either the feature bad nor does it make PAM a
reasonablesolution.  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
coulduse 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