Showing results for 
Search instead for 
Did you mean: 

Setting up Postgres LDAP authentication with TLS

EDB Team Member




Setting up Postgres LDAP authentication with TLS

Setting up LDAP authentication with TLS is not as difficult as it looks, provided you have right steps to accomplish it. If the steps are not followed correctly, then troubleshooting any error message can become a daunting task. The error messages returned are not very informative and there are several explanations available online for the same error, which makes troubleshooting even more difficult.

In order to make it easy for anyone wanting to setup LDAP with TLS authentication in Postgres, I have come up with following steps. I found steps given in following article easy to follow: “How To Encrypt OpenLDAP Connections Using STARTTLS
So, my steps are based on above article available on DigitalOcean.

Step 1: Setup Postgres LDAP authentication

Using the steps given in following postgresrocks KB article: EDB Postgres LDAP authentication without TLS/SSL,
setup LDAP authentication.

Make sure LDAP authentication is working without TLS.

Step 2: Setup hostname for your LDAP server

Make sure hostname is setup correctly on your LDAP server. For my testing, I have set it up like this:

# hostnamectl set-hostname ldaphost

Added following entry in /etc/hosts:   ldaphost

Step 3: Make sure GnuTLS package is installed on LDAP server and DB server:

# yum list installed GnuTLS

if it’s not installed, install it using yum:

# yum install gnutls

Step 4: On your LDAP server, create the CA and LDAP Service templates:

# mkdir /etc/ssl/templates

# vi /etc/ssl/templates/ca_server.conf

Enter following and save it:

cn = LDAP Server CA

# vi /etc/ssl/templates/ldap_server.conf

Enter following (change organization to your company, cn to FQDN of your LDAP server):

organization = "EDB"
cn = ldaphost
expiration_days = 3652

Step 5: Create CA Key and Certificate

# mkdir /etc/ssl/private
# certtool -p --outfile /etc/ssl/private/ca_server.key
# certtool -s --load-privkey /etc/ssl/private/ca_server.key --template /etc/ssl/templates/ca_server.conf --outfile /etc/ssl/certs/ca_server.pem

Step 6: Create LDAP Service Key and Certificate

# certtool -p --sec-param high --outfile /etc/ssl/private/ldap_server.key
# certtool -c --load-privkey /etc/ssl/private/ldap_server.key --load-ca-certificate /etc/ssl/certs/ca_server.pem --load-ca-privkey /etc/ssl/private/ca_server.key --template /etc/ssl/templates/ldap_server.conf --outfile /etc/ssl/certs/ldap_server.pem

Step 7: Give OpenLDAP access to LDAP Server Key

# chgrp -R ldap /etc/ssl/private
# chmod 640 /etc/ssl/private/ldap_server.key

Step 8: Configure OpenLDAP to use certs and keys

# vi addcerts.ldif

Enter following and save:

dn: cn=config
changetype: modify
replace: olcTLSCACertificateFile
olcTLSCACertificateFile: /etc/ssl/certs/ca_server.pem

replace: olcTLSCertificateFile

olcTLSCertificateFile: /etc/ssl/certs/ldap_server.pem

replace: olcTLSCertificateKeyFile

olcTLSCertificateKeyFile: /etc/ssl/private/ldap_server.key
# ldapmodify -H ldapi:// -Y EXTERNAL -f addcerts.ldif

Reload OpenLDAP service:

# service slapd force-reload

Step 9: Setup client on LDAP server

On the LDAP server:

# cp /etc/ssl/certs/ca_server.pem /etc/openldap/certs/ca_certs.pem

Adjust TLS_CACERT value in /etc/openldap/ldap.conf:

TLS_CACERTDIR   /etc/openldap/certs
TLS_CACERT     /etc/openldap/certs/ca_certs.pem

Save and close the file.

If everything is fine, then following should run without an error and return “anonymous”:

# ldapwhoami -H ldap:// -x -ZZ

Step 10: Setup LDAP client on EDB Postgres db server:

Copy CA certificate from LDAP server:

On DB Server:

# cd /etc/openldap/certs
# scp root@ldaphost:/etc/ssl/certs/ca_server.pem .

# cat ./ca_server.pem | tee –a ca_certs.pem

Adjust TLS_CACERT in /etc/openldap/ldap.conf:

TLS_CACERTDIR   /etc/openldap/certs
TLS_CACERT     /etc/openldap/certs/ca_certs.pem

Save the file.

If everything is fine, then following command will return anonymous:

# ldapwhoami -H ldap://ldaphost -x -ZZ

Step 11: Setup LDAP+TLS on EDB Postgres server:

# su – enterprisedb

enterprisedb@trn-vm ~]$ cd /var/lib/edb/as11/data
vi pg_hba.conf

add following line (make sure to change IP range, ldapbasedn, ldapbinddn, and ldapbindpasswd values for your environment) :

host   all             all            ldap ldapserver=ldaphost ldaptls=1 ldapport=389 ldapbasedn="ou=EDBUsers,dc=edb,dc=com" ldapbinddn="cn=enterprisedb,ou=EDBUsers,dc=edb,dc=com" ldapsearchattribute=uid ldapbindpasswd=edb

Save and close pg_hba.conf

Reload pg_hba.conf file:

/usr/edb/as11/bin/pg_ctl reload –D /var/lib/edb/as11/data

Test DB connection using psql from a remote server.

[root@ldaphost ~]# /opt/edb/as10/bin/psql -h <db host> -p <db port> edb -U enterprisedb
Password for user enterprisedb: 
psql.bin (10.3.8, server 11.1.7)
WARNING: psql.bin major version 10, server major version 11.
         Some psql features might not work.
Type "help" for help.

edb=# \q

In the /var/log/slapd/slapd.log on LDAP server, you should see and entry like below to ensure it’s using TLS for connection:

Mar 27 01:55:50 ldaphost slapd[4268]: do_extended: oid=
Mar 27 01:55:50 ldaphost slapd[4268]: conn=1003 op=0 STARTTLS
Mar 27 01:55:50 ldaphost slapd[4268]: send_ldap_extended: err=0 oid= len=0