Thread: authenticating using Active Directory?

A new federal related project has asked me if PostgreSQL can authenticate a user using Active Directory or LDAP. I've
neverused either of these and therefore have no real idea. 
Hence, my question. Is there a way to use either of these technologies to authenticate a user?
--
Jay

Jay,,

* John Scalia (jayknowsunix@gmail.com) wrote:
> A new federal related project has asked me if PostgreSQL can authenticate a user using Active Directory or LDAP. I've
neverused either of these and therefore have no real idea. 
> Hence, my question. Is there a way to use either of these technologies to authenticate a user?

The short answer is yes.  Active Directory uses Kerberos for
authentication, which PostgreSQL supports through the GSS authentication
mechanism.

LDAP authentication is also supported but is strongly discouraged in an
Active Directory environment (by Microsoft) as Kerberos should be used
instead since it's a much more secure solution.  LDAP-based
authentication requires sending the password to PG as cleartext.

    Thanks!

        Stephen

* Jeffrey Shaw (shawjef3@gmail.com) wrote:
> I spent a significant amount of time trying to make Windows users able to
> authenticate to PostgreSQL using Kerberos. I found that it was only
> possible if the server was running on Windows. If I ran the server on
> Linux, only Linux clients were able to authenticate. I asked EnterpriseDB
> for help, and they confirmed that PostgreSQL on Linux doesn't support
> Windows clients with Active Directory.

That's not correct and I'm sorry to hear that there has been confusion
and misinformation about that.  PostgreSQL on Linux will absolutely work
with Windows clients and I've run it both with a keytab created by a
Windows KDC and by having a cross-realm trust to a MIT KDC.  Note that,
on Linux, you use the GSS authentication mechanism, not SSPI, but those
are (more-or-less, see below) wireline compatible.

> If someone has been able to make it work, I'd love to hear how.

One of the issues which can trip someone up in such a configuration is
that Windows and Linux differ in the principal requested/used- Windows
will default to POSTGRES/f.q.d.n@REALM.COM while Linux is
postgres/f.q.d.n@REALM.COM and, yes, case matters.  You can also get
into a bit of trouble with the actual principals (Windows is a bit dumb
when it comes to how it deals with case- in particular, I've seen it
happily accept whatever case the user logged into the box with and stick
that into the TGT, so you may have to deal with that in the pg_ident
mapping file).

For my 2c, unless you've got just one PG server or one service in your
environment, it's easier to have a different realm for the Unix-based
resources with an MIT KDC and a cross-realm (which can be one-way) trust
from the AD environment where the users log in to the Unix realm which
PG, SSH, Apache w/ mod_auth_krb, etc, exist.  Setting up an MIT KDC is
pretty straight-forward and it's definitely easier to test PG
kerberos-based auth when everything is on Linux first, get it all
working, and then adding the cross-realm trust is pretty easy, as is
supporting the Windows users in the other realm.  Getting basic
Kerberos going is usually the more difficult piece because it's a bit
involved and not something many people are used to doing these days.

Thanks!

    Stephen

> On Sat, Mar 7, 2015 at 8:57 AM, Stephen Frost <sfrost@snowman.net> wrote:
>
> > Jay,,
> >
> > * John Scalia (jayknowsunix@gmail.com) wrote:
> > > A new federal related project has asked me if PostgreSQL can
> > authenticate a user using Active Directory or LDAP. I've never used either
> > of these and therefore have no real idea.
> > > Hence, my question. Is there a way to use either of these technologies
> > to authenticate a user?
> >
> > The short answer is yes.  Active Directory uses Kerberos for
> > authentication, which PostgreSQL supports through the GSS authentication
> > mechanism.
> >
> > LDAP authentication is also supported but is strongly discouraged in an
> > Active Directory environment (by Microsoft) as Kerberos should be used
> > instead since it's a much more secure solution.  LDAP-based
> > authentication requires sending the password to PG as cleartext.
> >
> >         Thanks!
> >
> >                 Stephen
> >