Thread: LDAP Configuration for Postgres authenticating against AD

On Tuesday 04 August 2009 19:41:57 Richard Esmonde wrote:
> Hi,
>
>
>
> I'm new to PostGres (so go easy on my naivety).  I am trying to configure
> the postgres host based configuration file to permit users to authenticate
> against our Active Directory.
>
> Needless to say both Ubuntu server and AD are in the same Domain.
>
>
>
> .         I am running PostGRESQL v8.3.7 on a 64-Bit Ubuntu Hardy Heron
> Dell server with Apache 2.
>
> .         I am not running SSL.
>
> .         This work is happening on a LAN.  My AD server=master1 and the
> LAN=belfry.lan
>
> .         I installed Postgres as follow:
>
> o   # sudo apt-get install postgresql-8.3 postgresql-client-8.3
> postgresql-client-common postgresql-common
>
>
>
> It runs just fine and I can create databases users and tables with no
> problems.
>
>
>
> Currently, the end of my pg_hba.conf file looks like:
>
> ============================================
>
> # IPv4 local connections:
>
> host    all         all         127.0.0.1/32          md5
>
> host    all     all     10.5.5.0 255.255.255.0  password
>
>
>
> # IPv6 local connections:
>
> host    all         all         ::1/128               md5
>
>
>
> # Remote TCP/IP connection
>
> #host   all     postgres        127.0.0.1/32    password
>
> # host  all     all             10.5.5.0/16    ldap
> "ldap://master1:389/dc=belfry,dc=lan;BELFRY\"
>
> # host  all     all             10.5.5.0 255.255.255.0  ldap
> "ldap://master1:389/dc=belfry,dc=lan;BELFRY\"
>
>
>
> host  all     all             10.5.5.0 255.255.255.0   ldap
> "ldap://master1. belfry.lan:389/ou=Belfry
> Users,ou=programmers;dc=belfry,dc=lan;cn=*;BELFRY\"
>
>
>
>
>
> =============================================
>
>
>
> Each time I change it I stop and start PostGres.
>
>
>
> I created a testuser and a test database.  The user, testuser exists in my
> Active directory with a different password.  I can connect as testuser to
> the DB via command line or via pgAdmin111 with the postgres password for
> testuser.  When I try to connect using the users LDAP password I always
> get:
>
>
>
> .         psql: FATAL:  password authentication failed for user testuser
>
>
>
> Three days into this I am none the wiser - I'm exhausting Google servers.
> Can anyone tell me what I have forgotten to do or have overlooked in
> getting this setup correctly?  To my mind it's behaving as though it's not
> honoring anything I have put in the pg_hba.conf for Remote TCP/IP
> connections.  I have to be missing something super simple...  a
> postgres-ldap add-on for Postgres on Ubuntu perhaps?
>
>
>
> I set connections to debug2 in the logs.  Debug5 was giving me hundreds of
> lines of "blah".  Tail  of logs now looks like:
>
> =============================================
>
>
>
> 2009-08-04 16:49:15 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:15 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:15 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:15 PDT DEBUG:  server process (PID 8637) exited with exit
> code 0
>
> 2009-08-04 16:49:24 PDT LOG:  incomplete startup packet
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  forked new backend, pid=8646 socket=9
>
> 2009-08-04 16:49:24 PDT DEBUG:  server process (PID 8646) exited with exit
> code 0
>
> 2009-08-04 16:49:24 PDT DEBUG:  postmaster received signal 2
>
> 2009-08-04 16:49:24 PDT LOG:  received fast shutdown request
>
> 2009-08-04 16:49:24 PDT LOG:  aborting any active transactions
>
> 2009-08-04 16:49:24 PDT LOG:  autovacuum launcher shutting down
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT LOG:  shutting down
>
> 2009-08-04 16:49:24 PDT LOG:  database system is shut down
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  proc_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  shmem_exit(0)
>
> 2009-08-04 16:49:24 PDT DEBUG:  exit(0)
>
> 2009-08-04 23:53:23 GMT DEBUG:  postgres: PostmasterMain: initial environ
> dump:
>
> 2009-08-04 23:53:23 GMT DEBUG:  -----------------------------------------
>
> 2009-08-04 23:53:23 GMT DEBUG:          LC_CTYPE=en_US.UTF-8
>
> 2009-08-04 23:53:23 GMT DEBUG:          PGSYSCONFDIR=/etc/postgresql-common
>
> 2009-08-04 23:53:23 GMT DEBUG:          PGLOCALEDIR=/usr/share/locale
>
> 2009-08-04 23:53:23 GMT DEBUG:          PWD=/var/lib/postgresql
>
> 2009-08-04 23:53:23 GMT DEBUG:          PGDATA=/var/lib/postgresql/8.3/main
>
> 2009-08-04 23:53:23 GMT DEBUG:          LC_COLLATE=C
>
> 2009-08-04 23:53:23 GMT DEBUG:          LC_MESSAGES=en_US.UTF-8
>
> 2009-08-04 23:53:23 GMT DEBUG:          LC_MONETARY=C
>
> 2009-08-04 23:53:23 GMT DEBUG:          LC_NUMERIC=C
>
> 2009-08-04 23:53:23 GMT DEBUG:          LC_TIME=C
>
> 2009-08-04 23:53:23 GMT DEBUG:  -----------------------------------------
>
> 2009-08-04 16:53:23 PDT LOG:  could not load root certificate file
> "root.crt": no SSL error reported
>
> 2009-08-04 16:53:23 PDT DETAIL:  Will not verify client certificates.
>
> 2009-08-04 16:53:23 PDT DEBUG:  invoking IpcMemoryCreate(size=30384128)
>
> 2009-08-04 16:53:23 PDT DEBUG:  max_safe_fds = 981, usable_fds = 1000,
> already_open = 9
>
> 2009-08-04 17:01:09 PDT LOG:  could not load root certificate file
> "root.crt": no SSL error reported
>
> 2009-08-04 17:01:09 PDT DETAIL:  Will not verify client certificates.
>
> 2009-08-04 17:01:09 PDT DEBUG:  max_safe_fds = 981, usable_fds = 1000,
> already_open = 9
>
> =============================================
>
>
>
>
>
> Thanks in advance to any and all who have a clue more than I,
>
>
>
> Rich



did you reference this when you set this up?

http://wiki.postgresql.org/wiki/LDAP_Authentication_against_AD

If not maybe there's some help here...

On Tue, 2009-08-04 at 21:41 -0400, Richard Esmonde wrote:

> Three days into this I am none the wiser - I’m exhausting Google
> servers.  Can anyone tell me what I have forgotten to do or have
> overlooked in getting this setup correctly?  To my mind it’s behaving
> as though it’s not honoring anything I have put in the pg_hba.conf for
> Remote TCP/IP connections.  I have to be missing something super
> simple…..  a postgres-ldap add-on for Postgres on Ubuntu perhaps?

On my Ubuntu 9.04 system `postmaster' links directly to libldap, and
appears to support LDAP authentication. I don't see any additional auth
modules in the respository. So, no, I doubt you need anything extra.

Are you sure your posted pg_hba.conf was right? You had:

host  all     all     10.5.5.0 255.255.255.0  password
host  all     all     10.5.5.0 255.255.255.0   ldap "ldap://master1.
belfry.lan:389/ou=Belfry
Users,ou=programmers;dc=belfry,dc=lan;cn=*;BELFRY\"


... so you wouldn't even be trying LDAP authentication. Any user trying
to connect to any DB from the 10.5.5.0/24 range would be expected to use
password auth. The LDAP auth line can never match.

Maybe instead of "all" users for password auth you wanted to (eg) only
require password auth for the "postgres" user?


If that's not the issue: Try watching for LDAP traffic with wireshark;
see if there's any communication. Also, use `ldapsearch' from the host
running Pg to query AD, make sure that works.

--
Craig Ringer

On Aug 4, 2009, at 6:41 PM, Richard Esmonde wrote:

> Hi,
>
> I’m new to PostGres (so go easy on my naivety).  I am trying to
> configure the postgres host based configuration file to permit users
> to authenticate against our Active Directory.
> Needless to say both Ubuntu server and AD are in the same Domain.
>
> ·         I am running PostGRESQL v8.3.7 on a 64-Bit Ubuntu Hardy
> Heron Dell server with Apache 2.
> ·         I am not running SSL.
> ·         This work is happening on a LAN.  My AD server=master1 and
> the LAN=belfry.lan
> ·         I installed Postgres as follow:
> o   # sudo apt-get install postgresql-8.3 postgresql-client-8.3
> postgresql-client-common postgresql-common
>
> It runs just fine and I can create databases users and tables with
> no problems.
>
> Currently, the end of my pg_hba.conf file looks like:
> ============================================
> # IPv4 local connections:
> host    all         all         127.0.0.1/32          md5
> host    all     all     10.5.5.0 255.255.255.0  password

This is the line that will take effect for any connection from
10.5.5.0/24.

>
> # IPv6 local connections:
> host    all         all         ::1/128               md5
>
> # Remote TCP/IP connection
> #host   all     postgres        127.0.0.1/32    password
> # host  all     all             10.5.5.0/16    ldap "ldap://master1:389/dc=belfry,dc=lan;BELFRY
> \"
> # host  all     all             10.5.5.0 255.255.255.0  ldap "ldap://master1:389/dc=belfry,dc=lan;BELFRY
> \"
>
> host  all     all             10.5.5.0 255.255.255.0   ldap "ldap://master1
> . belfry.lan:389/ou=Belfry
> Users,ou=programmers;dc=belfry,dc=lan;cn=*;BELFRY\"

Anything that might match this will already have matched the line
above (and had a password challenge), so this line will never be used.

Cheers,
   Steve

On 5 Aug 2009, at 3:41, Richard Esmonde wrote:
> Currently, the end of my pg_hba.conf file looks like:
> ============================================

> host  all     all             10.5.5.0 255.255.255.0   ldap "ldap://
> master1. belfry.lan:389/ou=Belfry
> Users,ou=programmers;dc=belfry,dc=lan;cn=*;BELFRY\"


Others already commented on that fact that this line is never matched,
but is that space between "master1." and "belfry.lan" intentional?
(The re-wrapping caused by indenting it for reply didn't make it more
obvious to see unfortunately)

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.


!DSPAM:737,4a7ab0d510131737011379!