PostGres Config to Authenticate against AD over LDAP
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
Richard Esmonde wrote:
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.
OK. Never tried that myself, but let's see.
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
All good info. Grab yourself a copy of the source from postgresql.org
too when you have time. Always useful to have a copy. Oh and "ack" too
(package is "ack-grep" on Ubuntu I think) - it's an improved version of
grep.
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:
Nothing leaping out at me here. One thing to be aware of is that PG will
try the first authentication method that matches host+db and not try any
further ones.
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
Well, I'd expect LDAP to be mentioned somewhere. Using my source tree,
ack and might powers of C knowledge:
backend/libpq/auth.c
case uaMD5:
case uaCrypt:
case uaPassword:
errstr = gettext_noop("password authentication failed for
user \"%s\"");
Looks to me like we're still using md5/password, and indeed a few lines
down is the error we should be seeing:
#ifdef USE_LDAP
case uaLDAP:
errstr = gettext_noop("LDAP authentication failed for user
\"%s\"");
break;
#endif /* USE_LDAP */
default:
errstr = gettext_noop("authentication failed for user
\"%s\": invalid authentication method");
break;
It also seems that if Ubuntu's installation didn't support ldap we'd see
the last error message.
I think your host must be matching the "password" line in pg_hba.conf
Oh - two more points.
1. I didn't see anything authentication-related in your logs either.
Plenty of connection startup stuff, but no auth.
2. Wireshark is a handy tool for this sort of thing. It's a network
analyser - point it at port 389 and see what it comes up with.
--
Richard Huxton
Archonet Ltd