Re: LDAP authentication slow

On Wed, May 30, 2018 at 6:31 PM, Tim Cross <theophilusx(at)gmail(dot)com> wrote:

>
> C GG <cgg0007(at)gmail(dot)com> writes:
>
> > On Wed, May 30, 2018 at 2:50 PM, Stephen Frost <sfrost(at)snowman(dot)net>
> wrote:
> >
> >> Greetings,
> >>
> >> * C GG (cgg0007(at)gmail(dot)com) wrote:
> >> > On Wed, May 30, 2018 at 12:04 PM, Stephen Frost <sfrost(at)snowman(dot)net>
> >> wrote:
> >> > > What's the reason for wishing for them to "be able to type in a
> >> > > password"? With GSSAPI/Kerberos, users get true single-sign-on, so
> >> they
> >> > > would log into the Windows system with a password and then have a
> TGT
> >> > > which can be used to authenticate to other services without having
> to
> >> > > type in their password over and over again.
> >> >
> >> > By and large, we're using pre-existing tools that would have to be
> >> heavily
> >> > modified to co-opt GSSAPI as an authentication method. For some
> >> > tools/applications, it's just not practical to use a ticket. But the
> >> > username/password paradigm is well supported. Most of these tools
> aren't
> >> > being used on Windows machines. That's not to say that Linux and macOS
> >> > don't have robust Kerberos tools available for use, but thinking that
> >> > Kerberos tickets are just floating out there in login-space waiting
> to be
> >> > used by psql and other tools isn't really the case in our environment.
> >>
> >> This argument doesn't really hold much weight. Anything using libpq is
> >> likely to work well with GSSAPI and most languages base their access to
> >> PG on libpq. Ensuring that a ticket is available also isn't hard with
> >> k5start or similar. Even proxying tickets with mod_auth_kerb or similar
> >> isn't all that difficult to get going to leverage SPNEGO.
> >>
> >
> > Sounds complicated.
> >
> >
> >>
> >> > The main reason for moving to LDAP(S) is the ability to enforce
> password
> >> > quality and rotation without the risk of disclosure (
> >> > https://www.postgresql.org/docs/10/static/passwordcheck.html) ...
> >> Allowing
> >> > pre-hashed passwords to be sent to the back-end circumvents any
> >> protections
> >> > passwordcheck might give. Plus, passwordcheck isn't available in all
> >> > PostgreSQL environments (I'm specifically thinking of AWS RDS).
> >>
> >> This seems entirely out-of-place and not related to GSSAPI (pre-hashed
> >> passwords..?). Further, password quality and rotation would be able to
> >> be handled by AD instead of trying to do it in PG, though this also
> >> seems to be conflating different things (are you talking about access
> >> from an application, whose password should be randomly generated to
> >> begin with, or users..?).
> >>
> >>
> > Correct. Password quality and rotation needs to be handled in AD instead
> of
> > trying to do it in PG.
> >
> > I'm feeling attacked for trying to work with the tools I have available
> to
> > me and the constraints I have been given.
> >
> >
> >> > Unless I've missed something GSSAPI is still out of consideration if
> >> we're
> >> > having to supply username/password combinations in connection strings.
> >>
> >> There continues to be some confusion here as with GSSAPI you
> >> specifically wouldn't need to include passwords in connection strings.
> >>
> >> > I am still wondering what can we do to speed LDAP(S) up? Is there a
> >> > speedier authentication delegation paradigm that utilizes
> >> username/password
> >> > from the client?
> >>
> >> Passing passwords around between different systems for authentication is
> >> likely to be expensive and insecure. Using SCRAM on PG would at least
> >> avoid the call out from the PG server to the LDAP server but then you
> >> would have different passwords on the different systems potentially.
> >>
> > The solution to these issues is to move away from passing passwords
> >> around, as Active Directory did.
> >>
> >> Thanks!
> >>
> >> Stephen
> >>
> >
> > Please let me be clear, this is not a question about whether or not to
> use
> > passwords. This is a question of how to determine the cause of and
> remedy a
> > slowdown retrieving data from PostgreSQL when using LDAP(S) to
> authenticate
> > PostgreSQL users. One of the sideline questions would be how to achieve
> the
> > same effect by using a different scheme. I should further clarify that a
> > major requirement would be that the scheme would need to work in our
> > current environment without having to re-engineer the client
> applications.
> > That would entail the need to pass a username and password as we have
> > traditionally done.
> >
>
> You could see if using openLDAP performs better.
>
> In our environment, we have both AD and openLDAP. We use the AD password
> sync service to synchronise passwords across AD and LDAP. Attributes in
> both systems are managed by MS Forefront. Our environment has a large
> mix of technologies - servers are roughly evenly split between Linux and
> MS - still probably more Linux, though MS has been increasing in recent
> years. Databases are a mix of Oracle and Postgres plus a smattering of
> MySQL. Staff numbers are around 3k with about 60% on MS, 35% OSX and 5%
> Linux. Client base is about 80k.
>
> The reason we use both openLDAP and AD is because there are differences
> between the two which are important for some of our applications (for
> example, attributes which are single valued under LDAP standards but can
> be multi-valued under AD) and because we need additional schemas which
> are easy to implement in standards compliant LDAP, but difficult in
> AD. We also found that when just requiring LDAP functionality, openLDAP
> out performed AD.
>
> How easily this can be done in your environment will depend on your
> identity management solution. Depending on what that is, it may be as
> easy as just adding another downstream target and a few mapping rules.
> In this case, it would probably be an overall win. However, if your IAM
> system cannot manage things easily, this is probably not practical.
>
> There has been another thread regarding LDAP performance where the issue
> looks like it could be a DNS related problem. It seems establishing
> connections is close when LDAP address is specified using name and
> faster when just an IP address is used. This could be something else you
> should look at. We had an issue a while back where our central IT
> provider made changes to DNS to improve security and enabling better
> handling of misbehaving clients - essentially, it was a DNS throttling
> configuration which would temporarily block requests from an IP if
> the number of requests being made was above a specified threshold. This
> caused some initial problems for us as we found some application
> libraries did not perform proper DNS caching and would regularly exceed
> the threshold. It also took some trial and error to get the right
> watermark for the throttling. A simple test like using IP address rather
> than name
> would likely help to identify if DNS related issues could be the cause
> or whether it is just an AD specific issue.
>
> Definitely check AD logs as well - the issue could be simply that adding
> a new system has increased demand sufficiently to degrade performance of
> AD (though I would expect there would be complaints from others outside
> the DB area if this was the case).
>
> The GSSAPI approach is not as complicated as it sounds, but it can be
> affected by environment/infrastructure architecture and it will be
> critical to ensure you have good time synchronisation. This can be
> somewhat challenging in hybrid environments where you have a mix of
> local and remote services. When it all works, it is great, but when you
> do have a problem, diagnosis can be challenging.
>
> The overall approach of having one identity with one password per entity
> is IMO the right approach and your only hope for good password policy
> application. However, getting to that point can be very challenging.
>
> --
> Tim Cross
>

Thanks Tim. This is amazing insight. We had some difficulties synchronizing
AD to openLDAP, so we scrapped that and put our efforts toward adopting AD
as the identity management for the whole company. I would love to take the
openLDAP sync discussion offline if you're willing to briefly share what
you've learned through the process. ;)

In the meantime, I did what I promised Adrian Klaver I would do and I added
the AD servers to the /etc/hosts file. That had an immediate and dramatic
effect on the performance. That confirms (at least to me) that DNS
resolution was playing a large part in the slowdown. I'm going to
experiment with running a local DNS caching server to see if that will give
the same effect.

The day is still young, and I'm a skeptic at heart, but I'm going to put a
pencil mark in the win column for this workaround and keep my eye on it to
make sure it sticks.

Thanks all!