Overview

It has been a while since I have written about the new features in a major pgpool II release. Well pgpool II 4.2 is in the works and the plan is to release it towards the end of this year. As usual every major release of pgpool II is compatible with the parser of latest PG release so pgpool II 4.2 will be compatible with PG-13 parser. There are many nice features that will be part of next major release however the ones that particularly catches my attention is supporting LDAP authentication with pgpool II. I will briefly mention the other 4.2 features in the release but the main purpose of this blog is to discuss and demonstrate the LDAP authentication feature the will be released with pgpool II 4.2.

So before you can test whether an application support LDAP authentication, you need to get an LDAP server setup and have it populated with the database user credentials. I initially started down the part of setting up an LDAP server on my machine but ran into some complexities so decided to use a docker image of LDAP the comes with pre-loaded ldap database.

Before I discuss how the docker LDAP image is deployed, let’s get a quick standard introduction of LDAP to set the context. 

“LDAP, the Lightweight Directory Access Protocol, is a mature, flexible, and well supported standards-based mechanism for interacting with directory servers. It’s often used for authentication and storing information about users, groups, and applications, but an LDAP directory server is a fairly general-purpose data store and can be used in a wide variety of applications.“

There are a couple of commonly-used implementations for the LDAP protocol, including ApacheDS, OpenLDAP, OpenDJ and Active Directory.

LDAP server Configuration

osixia/openldap (https://github.com/osixia/docker-openldap) provides a docker image to run OpenLDAP, it is very simple to build and deploy OpenLDAP server with pre-existing ldap data. While the GitHub page provides all the details about how to build and run the docker image, I will provide the commands that I ran to get this up and running. I ran into few glitches while setting up the docker image for OpenLDAP and using the phpLDAPadmin docker image that provides a GUI interface for adding entires in the LDAP database. I will discuss the GUI phpLDAPadmin later, just mentioned it here in parsing.

You can clone the osixia/openldap using : 

git clone https://github.com/osixia/docker-openldap.git

and run

make build

this will build the latest version of  osixia/openldap docker image, the latest version is 1.4.0. If you are missing any needed packages, you will be prompted to install those using the package manager for your OS. I am doing this on mac so I have used “brew” to install any missing packages.

Once the build is successful you are ready to run the docker image, the docker command for doing that is the following :

docker run -p 389:389 -p 636:636 --name my-openldap-container --detach osixia/openldap:1.4.0

The port mapping for 389 and 636 is required if you need to access the ldap server from another machine. The —name argument is name of the name of the container, the —detach is the version of the docker image; 1.4.0 in this case.

Once you have executed the above, you can run the following docker command to see if the container is running.

docker ps -a
CONTAINER ID        IMAGE                       COMMAND                 CREATED             STATUS              PORTS                                        NAMES
d1dab7fbfcd6        osixia/phpldapadmin:0.9.0   "/container/tool/run"   7 days ago          Up 25 hours         80/tcp, 0.0.0.0:6443->443/tcp                phpldapadmin
8a7c49860615        osixia/openldap:1.4.0       "/container/tool/run"   8 days ago          Up 25 hours         0.0.0.0:389->389/tcp, 0.0.0.0:636->636/tcp   my-openldap-container

The next step is to do some LDAP server configuration, run the following command and it will take you the ldap container shell. Don’t forget to use your container ID, you can get the ID using the above command.

docker exec -it 8a7c49860615 /bin/bash

Next, we will edit the ldap configuration by issuing the following command, please note that you need to update your package manager in this shell and install vim using the package manager.

vim /etc/ldap/ldap.conf	

Change the following parameters in ldap.conf file

BASE		dc=example,dc=com
URI		ldap://192.168.18.13

We are only interested in updating the above parameters in ldap.conf file. I am using the default “example” domain name but you are free to set it to something else. The URI contains the IP of the machine where you are running the ldap server.

After updating the ldap.conf file, the next step is to configure the slapd (stand alone ldap daemon) using the command below in the same shell.

dpkg-reconfigure slapd

Follow the instructions below for doing the slapd configuration :

Say no to omitting openldap server configuration

Omit OpenLDAP server configuration? [yes/no] no

Specify “example.com” in domain name, 	you can write something else here but it needs to same as what you specified in the ldap.conf file.
  
DNS domain name: example.com
        
Organization name: example
Do you want the database to be removed when slapd is purged? [yes/no] yes
Move old database? [yes/no] yes

After completing the above, we are done with ldap configuration, the following command can be used to search the ldap database.

docker exec my-openldap-container ldapsearch -x 

This should return all the entries made in the ldap database so far, here is one from my ldap database.

# postgres, users, example.com
dn: cn=postgres,cn=users,dc=example,dc=com
givenName: postgres
sn: postgres
cn: postgres
uid: postgres
uidNumber: 1001
gidNumber: 500
homeDirectory: /home/postgres
objectClass: inetOrgPerson
objectClass: posixAccount
objectClass: top

The next step is to build the docker image for phpLDAPadmin, it is available at 

https://github.com/osixia/docker-phpLDAPadmin   

Follow the same steps to clone the docker image and do “make build” to build the image. Run the following command to run the phpLDAPadmin container after successfully building the image :

docker run -p 6443:443 --env PHPLDAPADMIN_LDAP_HOSTS=192.168.18.13 —- name phpldapadmin --detach osixia/phpldapadmin:0.9.0

The ldap_host needs to be IP of the machine when you are running the ldap server. The GUI for phpLDAPadmin can be accessed using the following URL :

https://127.0.0.1:6443/

Login as username cn=admin,dc=example,dc=com  
Password : (password that was setup at slapd configuration)

The ldap database users can be created using the GUI interface.

Click on “create new entry” “Generic: Posix Group”

Once you have created a group, click on the group in the the left pane of the GUI interface and click on “Create new entry” under the group. Click on “Generic: User Account” and follow the instructions to create the required database users.  

Here is a look of the GUI interface..

Run the ldapsearch command again to list the database users created using the GUI interface.

docker exec my-openldap-container ldapsearch -x

Test LDAP authentication with pgpool II

Now we have the ldap server setup and populated with user information, we are ready for testing LDAP authentication with pgpool II.

Before testing ldap authentication with pgpool, I configured the PG server with ldap in order to test server with LDAP authentication without pgpool II in the middle. The PG server needs to be configured with the “—with-ldap” switch in order to support the ldap server configuration.

PostgreSQL pg_hba.conf settings

The PG pg_hba.conf needs to have the following entries for LDAP authentication, the other entries needs to be disabled.

host    all          all         127.0.0.1/32          ldap ldapserver=192.168.18.13 ldapprefix="cn=" ldapsuffix=",cn=users,dc=example,dc=com"
host    all          all         192.168.18.13/32      ldap ldapserver=192.168.18.13 ldapprefix="cn=" ldapsuffix=",cn=users,dc=example,dc=com"

The server needs to be restarted after making the above entires.

Next, we need to create the database users mapping to ones that we have created in the ldap database. Please create the database users using the “createuser” utility and connect with PSQL using the database user, it will get authenticated by the ldap server.

./psql postgres -h 192.168.18.13 -U ahsanhadi
Password for user ahsanhadi: 
psql (14devel)
Type "help" for help.

postgres=# \q

pgpool II pool.hba.conf settings

If building pgpool II from the source, use the “—with-ldap” switch with the configuration command. We need make changes to pgpool II configuration files as follows :

Enable pool_hba in pgpool configuration file :

enable_pool_hba = on
allow_clear_text_frontend_auth = on

I have also enabled clear text authentication but you can always set it to off and use the pool_passwd file for authentication as-well. Please use the pg_md5 utility provided by pgpool II installation for generating the md5 value for the password. You can also provide the password in pool_passwd in clear text. The format for adding username and password in the file is “username:password”

pool_passwd = 'pool_passwd'

The following entries need to added to pool_hba.conf file while the other entires needs to be disabled. Please configure the IP address of your ldap server and pgpool II server accordingly.

host    all          all         127.0.0.1/32          ldap ldapserver=192.168.18.13 ldapprefix="cn=" ldapsuffix=",cn=users,dc=example,dc=com"
host    all          all         192.168.18.13/32      ldap ldapserver=192.168.18.13 ldapprefix="cn=" ldapsuffix=",cn=users,dc=example,dc=com"

After doing the above, run the following command to run pgpool II

pgpool -f ~/pgpool.conf.sample -a ~/pool_hba.conf.sample -n

Pass the pgpool configuration file and pool_hba file using the -f and -a switches respectively.

Connect to PG server using the pgpool II port and it will be done using LDAP authentication.

./psql postgres -h 192.168.18.13 -U ahsanhadi -p 9999 
Password for user ahsanhadi: 
psql (14devel)
Type "help" for help.

postgres=# \q

Please note that in this blog, we are using LDAP authentication all the way from pgpool II to PostgreSQL, both pgpool and PG server are using LDAP authentication. It is possible that we only use pgpool II with ldap and server can be using trust or any other authentication.

Conclusion

pgpool II has come a long way in the last several major releases in terms of new functionality, performance and making the middle tier application stable, robust and user friendly. The pgpool II HA feature i.e. watchdog has been totally reengineered to handle complex HA scenarios like split brain etc. The pgpool II performance for both extended and simple queries has been greatly improved.

Another focus of pgpool II 4.2 release is to make the pgpool II configuration and setup simpler for the users. The community is working on simplifying watchdog configuration as-well working on the next generation GUI tool for configuration, administration, management and monitoring of pgpool.

In this blog, the LDAP authentication feature in pgpool II 4.2 is described in detail. The blog should help the user in configuring PG and pgpool II with LDAP authentication server.