Authenticate Postgresql client agains backend ldap

We have a Postgresql server open to many different people at our organization. In order to add a ldap authentication you can add these 3 lines to your current pg_hba.conf file:

host all username       ldap ldapserver="" ldapbasedn="ou=people,dc=uib,dc=no"
host all username    129.177.XXX.YYY/32 ldap ldapserver="" ldapbasedn="ou=people,dc=uib,dc=no"
local all username                      ldap ldapserver="" ldapbasedn="ou=people,dc=uib,dc=no"

where the first one will allow users that access the server from localhost (either from a shell or connections). The second line is allowing a computer from the following IP adresse: 129.177.XXX.YYY.

Finally the last line will use ldap backend for local connections. That means a Linux user that logged in with SSH, will still be validated agains the ldap server, regardless of local password.

Remember this is only authentication, so you have to add the username to Postgresql database anyway (hint: use createuser command).

If you want to allow a user to connect remotely over web, remember to use SSL in the webserver, HTTPS, so that the password is not transmittet un-encrypted.

Note also:  This is also only LDAP, not LDAPS connections. So data going from the Postgresql server to the ldap is not encrypted. In short: Keep the distance between the postgresql server and the LDAP server short, or try to implement LDAPS, which I did, where I failed, so far…

ldapsearch users and places that contains æ, ø and å

We needed to collect “Place” information per user from our LDAP server.
The problem was that the description of the “Place” came out strangely encoded whenever it contained one of the norwegian characters æ,ø or å.

The ldap command:

ldapsearch -x -H ldap:// x121Address=XXXXXX

where XXXXXX is the “place” code, gave a place description that looked like this:

description:: SW5zdGl0dXR0IGZvciBmaWxvc29maSBvZyBmw7hyc3Rlc2VtZXN0ZXJzdHVkaWVy

where the real name of “Place” could be something like: “Institutt for .. and then a word with æ, ø or å”

The solution was to use ldapsearch as follows:
ldapsearch -x -z 1 -t departmentNumber=XXXXXX ou

where XXXXXX is the University of Bergen “placecode” for a “Place”. For instance the number 567123 could be the place code for our IT department.
-z 1 reduces the list of hits to one (1), and ou specifies the “Place” description.

The list of users was already collected in a text file: people.txt on the form:


The bash script that solved the issue for me was:

[code lang=”bash”]

# People collected with:
# ls -al /www/folk/ |awk {‘print $9’}|grep -v unwanted_line|sort > people.txt

PEOPLE=`cat people.txt`

PLACECODE=`ldapsearch -x -H ldap:// uid=$USERNAME | grep departmentNumber | awk {‘print $2’}`

if [ ! -z $PLACECODE ]; then

# Some times name of place is written to screen, other times to a file under /tmp
OUINFO=`ldapsearch -x -z 1 -t departmentNumber=$PLACECODE ou | grep ‘ou:’`

if [ `echo $OUINFO | grep ‘file:’ | wc -l` -eq 0 ];then
PLACE=`echo $OUINFO | sed -e s/"ou:\ "//g`
THEFILE=`echo $OUINFO | grep ‘file:’ | sed -e s/".*file:\/\/"//g`

#echo "The file is: " $THEFILE
#echo "and the place is: " $PLACE