Thread: 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:

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

"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.  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/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 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
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

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
builtinto 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
newset of RPMs.  
 
>
> 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.
 
>

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>

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

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
respectto 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
exclusivelyon 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'trun 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
throughSSL, 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
withrespect to LDAP/AD, I truly wish it were an option.  I agree with the various sentiments that AD authentication is
moremanageable, scalable, secure, etc.  However, if there were one set of environs where you'd think we could rely
exclusivelyon 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'trun 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!"

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

Greetings,

* Geoff Winkless (pgsqladmin@geoff.dj) wrote:
> On Wed, 6 May 2020, 14:28 Stephen Frost, <sfrost@snowman.net> wrote:
> > * 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?

Seems rather unlikely to be reasonable to set up an LDAP server for
every independent DB, but if you really want to go down that route,
you're very likely to end up in the same situation- users will reuse
their password from their AD account (and/or somewhere else).

> 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.

The OP's comment was actually that they *have* AD in place, but there's
cases where they don't want to use AD for one reason or another.
Suggesting setting up another service like OpenLDAP seems unlikely to
really answer that, but who knows.

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

Except that's the case that was presented here- they have AD but they
don't want to use it for some subset of accounts.

> 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.

Yes, you can set up OpenLDAP with Kerberos auth through SASL, you just
need an appropriate service princ from a KDC (eg: Active Directory, MIT
Kerberos, or Heimdal).  Isn't terribly hard, and is part of what's
called "LDAPv3", which is when SASL support was added (RFC 2251... from
1997).

I have to admit that I've not tried to make PG w/ 'ldap' auth use
Kerberos to connect to an LDAP server.  While it likely could be done,
in such a case you've already got a KDC and presumably would rather be
using Kerberos to auth to all of your services, including PG.

Thanks,

Stephen

On 06.05.20 13:48, Guillaume Lelarge wrote:
> Le mer. 6 mai 2020 à 04:18, Christian Ramseyer <rc@networkz.ch
> <mailto:rc@networkz.ch>> a écrit :
> 
>     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>
> 
>     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.
> 

Thanks for trying it out and the kind words, Guillaume & Ken !

There are some rough corners, I think to make it useful we would need to
do at least:

1. Write reasonable scripts for account locking/unlocking

2. Currently the lockout will also be executed for non-existing user
names and thus make the DOS worse, so we'd need a smart solution for
that (config file with valid users, or cached queries into PG from time
to time to get the existing users, or just being smarter on the log
parsing DETAILS line)

3. Examples how to combine with
https://www.postgresql.org/docs/current/auth-delay.html and/or firewall
drops, so that an attacker gets slowed down. Even if the account is
locked already, the system will still be harmed otherwise.


I'm happy to host this project if it helps enterprise adaption of
Postgres. I've converted the gist into an acutal repository, and you're
all very welcome to become contributors:
https://github.com/rc9000/postgres-fail2ban-lockout

Cheers
Christian


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