Anyone? Best way to authenticate postgres against active directory?

On Mon, 2006-12-18 at 20:23 -0600, Derrick wrote:

Subject:
[GENERAL] Anyone? Best way to
authenticate postgres against
active directory?

I know very little about AD, its kerberos implementation, and
interoperability limitations. I have been using kerberos with postgresql
for years and think it's nearly the holy grail of authentication. I know
that linux can authenticate against AD domains , so I presume that one
could also get postgresql to do so.

Here are two bookmarks that I've saved and that you might find useful:
http://chrisp.de/en/rsrc/kerberos.html
http://www.windowsnetworking.com/articles_tutorials/Authenticating-Linux-Active-Directory.html

If you can make that work, the community might appreciate a summary of
how you did it.

-Reece

--
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0
./universe -G 6.672e-11 -e 1.602e-19 -protonmass 1.673e-27 -uspres bush
kernel warning: universe consuming too many resources. Killing.
universe killed due to catastrophic leadership. Try -uspres carter.

On Mon, Dec 18, 2006 at 08:23:08PM -0600, Derrick wrote:

I've been struggling with pam_ldap and a windows 2003 active directory
server, trying to get postgres to authenticate against it. I'm
wondering what the best way to get postgres to authenticate against
windows active directory would be? I've posted the problem on the
general mail list that I was having with pam_ldap but no response. Any
ideas, how to's, or good links would be much appreciated, and I'm under
a time constraint. Thanks a million in advance.

You can do this three different ways:
1) You can do it with Kerberos. This is a bit tricky to get working, but
it can be done. This will give you single-sign-on, and not just the same
password. A large downside is that this is not supported by all
interfaces, for examlpe it's not supported with JDBC or .NET.

2) You can use the native LDAP authentication that's available in 8.2. I
specifically created it to be used in one of my own Active Directory
installations, so I know it works there (thuogh it of course works with
other LDAP installs as well).

3) You can use pam_ldap. Never done that myself, but I've heard of
others having some problems with it before, so I would definitly advise
you to use option 2 for simple LDAP auth.

//Magnus

Thanks I've been trying to use pam_ldap but I keep getting this error
from postgres. I think it may have something to do with postgres
running as the postgres user and not having permissions to something,
but I have no idea what.

LOG: pam_authenticate failed: Conversation error
FATAL: PAM authentication failed for user "bkelly"
LOG: could not send data to client: Broken pipe
LOG: pam_authenticate failed: User not known to the underlying
authentication module
FATAL: PAM authentication failed for user "bkelly"

this is my pam.d/postgresql file:
#####
auth required /lib/security/pam_ldap.so debug
account required /lib/security/pam_ldap.so debug
#####

Magnus Hagander wrote:

On Tue, Dec 19, 2006 at 09:52:58AM -0600, Derrick Stensrud wrote:

Thanks I've been trying to use pam_ldap but I keep getting this error
from postgres. I think it may have something to do with postgres
running as the postgres user and not having permissions to something,
but I have no idea what.

Probably. Can you try the native LDAP authentication? If not, I'll have
to defer to someone who knows PAM.

//Magnus

(I am working on this project with Derrick.) We have to use the Active
Directory to authenticate not only users from our client-side app (We're
attempting to use PostgreSQL essentially as a proxy authentication
mechanism), but also for connections to the SFTP server, and finally our
web app. Rather than doing three separate binding mechanisms, we wanted
to do the PAM/AD work once, and then have everything else defer to PAM
for authentication.

Magnus Hagander wrote:

* John McCawley (nospam@hardgeus.com) wrote:

(I am working on this project with Derrick.) We have to use the Active
Directory to authenticate not only users from our client-side app (We're
attempting to use PostgreSQL essentially as a proxy authentication
mechanism), but also for connections to the SFTP server, and finally our
web app. Rather than doing three separate binding mechanisms, we wanted
to do the PAM/AD work once, and then have everything else defer to PAM
for authentication.

Have you considered using Kerberos to auth against AD instead of trying
to use LDAP binding? If you still want to use PAM then you might check
out libpam-krb5, which from a bit of googling appears to work w/ AD
Kerberos. Of course, an alternative might be to try using the native
Kerberos support in Postgres which I've heard may work w/ the Postgres
ODBC driver...

Personally, I've gotten the Postgres ODBC driver working under windows
with MIT Kerberos and I've gotten Firefox under Windows working w/ MIT
Kerberos and using negotiate with Apache2 to authenticate users of
PhpPgAdmin to Postgres. I'm pretty sure all of this is possible with AD
instead of MIT Kerberos, or possibly even through a cross-realm setup.

Thanks,

Stephen

Stephen Frost wrote:

* John McCawley (nospam@hardgeus.com) wrote:

(I am working on this project with Derrick.) We have to use the Active
Directory to authenticate not only users from our client-side app (We're
attempting to use PostgreSQL essentially as a proxy authentication
mechanism), but also for connections to the SFTP server, and finally our
web app. Rather than doing three separate binding mechanisms, we wanted
to do the PAM/AD work once, and then have everything else defer to PAM
for authentication.

Ok. That certainly makes sense. Just that I can't help you then :-)

Have you considered using Kerberos to auth against AD instead of trying
to use LDAP binding? If you still want to use PAM then you might check
out libpam-krb5, which from a bit of googling appears to work w/ AD
Kerberos. Of course, an alternative might be to try using the native
Kerberos support in Postgres which I've heard may work w/ the Postgres
ODBC driver...

The native one works very well with the ODBC driver, and should work
with anything based off libpq. Which means anything that's not Java or
.NET, I think.

Personally, I've gotten the Postgres ODBC driver working under windows
with MIT Kerberos and I've gotten Firefox under Windows working w/ MIT
Kerberos and using negotiate with Apache2 to authenticate users of
PhpPgAdmin to Postgres. I'm pretty sure all of this is possible with AD
instead of MIT Kerberos, or possibly even through a cross-realm setup.

It works with AD on the server side, you still need to install MIT
Kerberos on the client.

//Magnus

Is anyone successfully using pam_ldap with postgres to authenticate
database users? I've read a million how to docs but I've hit a road block.

Magnus Hagander wrote: