Thread: Anyone? Best way to authenticate postgres against active directory?
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.
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:
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 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 ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
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: >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 > >---------------------------(end of broadcast)--------------------------- >TIP 6: explain analyze is your friend > >
* 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
> 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
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 6: explain analyze is your friend
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
Attachment
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:
Magnus Hagander wrote:
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 ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq