Effectively working with LDAP as an authentication mechanism for PostgreSQL typically requires extensive knowledge in both domains. While trying to be as complete yet succinct as possible, I’m detailing how to enable TLS between a PostgreSQL and the OpenLDAP server.
Ironically, the most complicated aspect has nothing to do with either PostgreSQL or OpenLDAP but with the steps of creating and signing private keys and certificates.
Note: I had seriously thought about leaving out much of the OpenLDAP commands, but I figured it might benefit you if you’re like me since I don’t touch this very often.
Summary steps
The underlying assumptions are:
- PostgreSQL and LDAP hosts are installed on the same host.
- Username accounts, but not passwords, are created on the Postgres server.
- Both username accounts and passwords are configured on the LDAP server.
- Private/Public keys, sic certificates, are to be created.
- A self-signed Certificate Authority, CA, exists on the host and has signed the aforementioned Postgres and LDAP certificates.
The environment
The Proof Of Concept described in this document consists of a single stand-alone server:
- Linux OS, Ubuntu 18.04
- Host name: my-ldap
- PostgreSQL version 14
- OpenLDAP version 2.4.45
- Distinguished Name: “cn=admin,dc=nodomain”
- password: admin
- topmost domain: “dn: dc=pg_user,dc=nodomain”
- superuser, postgres: “dn: cn=postgres,dc=pg_user,dc=nodomain”
- user1: “dn: cn=user1,dc=pg_user,dc=nodomain”
- user2: “dn: cn=user2,dc=pg_user,dc=nodomain”
- user accounts:
- postgres (password postgres)
Installation PostgreSQL
Apart from the standard steps of installing and configuring Postgres for remote access, edit the host-based authentication file enabling Postgres to refer to the LDAP service for authentication.
ROLES and useraccounts used in Postgres should be declared. Keep in mind that assigning passwords is NOT required:
1 2 3 | -- Example create role user1 with login password null; create role user2 with login password null; |
pg_hba.conf assumptions:
- The LDAP URL is 127.0.01, localhost, or the hostname URL if desired.
- Rules for both IPv4 and IPv6 are declared.
- NO encryption is enabled between the Postgres and LDAP services.
1 2 3 4 5 | # IPv4 local connections: host all all 0.0.0.0/0 ldap ldapserver=127.0.0.1 ldapprefix="cn=" ldapsuffix=", dc=pg_user, dc=nodomain" # IPv6 local connections: host all all ::0/0 ldap ldapserver=127.0.0.1 ldapprefix="cn=" ldapsuffix=", dc=pg_user, dc=nodomain" |
Installation LDAP
1 | apt-get install -y slapd ldap-utils |
Running netstat, (netstat -tlnp), returns the following:
1 2 3 | Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp 0 0 0.0.0.0:389 0.0.0.0:* LISTEN 7742/slapd tcp6 0 0 :::389 :::* LISTEN 7742/slapd |
Command line utilities
You can control the behavior of OpenLDAP by using these command line utilities:
- ldapmodify
- ldapadd
- ldapdelete
- ldapmodrdn
- ldapsearch
- ldapwhoami
Setting the administrator password
It is understood that administering the LDAP server requires setting the password. Although the installation of LDAP includes setting the password, which will be admin, by executing the following command, one can reset the password at will:
1 2 3 4 5 6 | # Select "No" when asked to configure the database with dbconfig-common. # Set the domain name for your LDAP server, for example, "example.com". # Set the organization name for your LDAP server, for example, "Example Inc". # Set the administrator password for your LDAP server. dpkg-reconfigure slapd |
Configuration
The following bash script demonstrates configuring OpenLDAP to authenticate three Postgres ROLES, i.e., postgres, user1, and user2:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | #!/bin/bash set -e ########################################## # # admin password is "admin" # the top domain is assigned as "nodomain" # ldapadd -v -xD "cn=admin,dc=nodomain" -w admin <<_eof_ # create the topmost domain dn: dc=pg_user,dc=nodomain objectClass: dcObject objectClass: organization dc: pg_user o: Postgres Users description: all postgres users reside here # create SUPERUSER ROLE postgres dn: cn=postgres,dc=pg_user,dc=nodomain objectclass: top objectclass: person cn: postgres sn: postgres userPassword: postgres _eof_ # ########################################## # ADD ROLES ldapadd -v -xD "cn=admin,dc=nodomain" -w admin <<_eof_ # creating other user accounts, down the road # create role user1 dn: cn=user1,dc=pg_user,dc=nodomain objectclass: top objectclass: person cn: user1 sn: user1 userPassword: user1 # create role user2 dn: cn=user2,dc=pg_user,dc=nodomain objectclass: top objectclass: person cn: user2 sn: user2 userPassword: user2 _eof_ |
Test connectivity without TLS
A simple login confirms LDAP and PostgreSQL are working correctly. Even though there is an encrypted session between psql and the Postgres server, there is no encrypted session between Postgres and LDAP as authentication is performed:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off) root@my-ldap:~# psql 'host=my-ldap dbname=postgres user=postgres password=postgres' -c "select 'ping' as test_connectivity" test_connectivity ------------------- ping root@my-ldap:~# psql 'host=my-ldap dbname=postgres user=user1 password=user1' -c "select 'ping from user1' as test_connectivity" test_connectivity ------------------- ping from user1 root@my-ldap:~# psql 'host=my-ldap dbname=postgres user=user2 password=user2' -c "select 'ping from user2' as test_connectivity" test_connectivity ------------------- ping from user2 |
Working With TLS Encryption
Install Additional Packages
To work with SSL certificates, these packages should be present, i.e., for the Ubuntu distribution:
1 | apt install -y gnutls-bin ssl-cert |
Managing the Certificate Authority
Authentication between the Postgres and LDAP servers includes that the hosts making connection attempts are, in fact, legitimate. For that reason, the certificates for both servers must be signed, i.e., a Certificate Authority mechanism is required.
1 2 3 4 | # # Generate private key for self-signed Certificate Authority # certtool --generate-privkey --bits 4096 --outfile /etc/ssl/private/mycakey.pem |
In this case, the CA certificate is configured to expire in ten years:
1 2 3 4 5 6 7 | # # Define CA certificate attributes # echo "cn = my-ldap ca cert_signing_key expiration_days = 3650" > /etc/ssl/ca.info |
An internal system can get away using self-signed CA certificates, otherwise, it is strongly recommended that your certificates be signed by an authorized CA. In this case, the certificate, once signed, is placed in the same directory where the other CA certificates are stored, i.e., /usr/local/share/ca-certificates:
1 2 3 4 5 6 7 8 9 | # # Generate a self-signed CA certificate and # copy the CRT to other trusted CA certificates that includes # both postgres and ldap servers (/usr/local/share/ca-certificates/) # certtool --generate-self-signed --load-privkey /etc/ssl/private/mycakey.pem --template /etc/ssl/ca.info --outfile /usr/local/share/ca-certificates/mycacert.crt |
ATTENTION: Once signed, the CA certificate is copied onto the Postgres and LDAP servers respectively. In this case, as they are on the same host, it is located on host my-ldap. Otherwise, it MUST be copied to all Postgres and LDAP hosts.
Once copied into the correct directory, the list of CA certificates is updated, adding the self-signed CA certificate:
1 2 3 4 5 | # # Update list of CA certificate on both # postgres and LDAP servers # update-ca-certificates |
Generating the LDAP public/private key
From here on, it is important to include the fully qualified domain name of the LDAP certificate hostname, i.e., my-ldap.
Generate a private key for LDAP server:
1 2 3 | certtool --generate-privkey --bits 2048 --outfile /etc/ldap/my-ldap_slapd_key.pem |
Define LDAP certificate attributes:
1 2 3 4 5 6 7 8 9 | # for a certificate request which expires # in one year # echo "organization = mycompany cn = my-ldap tls_www_server encryption_key signing_key expiration_days = 365" > /etc/ssl/my-ldap.info |
Sign the LDAP private key using the self-signed Certificate Authority certificate and its private key:
1 2 3 4 5 6 | certtool --generate-certificate --load-privkey /etc/ldap/my-ldap_slapd_key.pem --load-ca-certificate /etc/ssl/certs/mycacert.pem --load-ca-privkey /etc/ssl/private/mycakey.pem --template /etc/ssl/my-ldap.info --outfile /etc/ldap/my-ldap_slapd_cert.pem |
Update access permissions of the private key:
1 2 | sudo chgrp openldap /etc/ldap/my-ldap_slapd_key.pem sudo chmod 0640 /etc/ldap/my-ldap_slapd_key.pem |
Create and save the LDAP TLS configuration file, certinfo.ldif:
1 2 3 4 5 6 7 8 9 | echo "dn: cn=config add: olcTLSCACertificateFile olcTLSCACertificateFile: /etc/ssl/certs/mycacert.pem - add: olcTLSCertificateFile olcTLSCertificateFile: /etc/ldap/my-ldap_slapd_cert.pem - add: olcTLSCertificateKeyFile olcTLSCertificateKeyFile: /etc/ldap/my-ldap_slapd_key.pem" > /etc/ldap/schema/certinfo.ldif |
Enable OpenLDAP to use TLS:
1 | ldapmodify -Y EXTERNAL -H ldapi:/// -f /etc/ldap/schema/certinfo.ldif |
Validation test #1
Validation returns the string “anonymous”:
1 | ldapwhoami -x -ZZ -H ldap://my-ldap |
Validation test #2
This test confirms that the previous behavior without TLS still works:
1 2 | # without TLS ldapsearch -x -H ldap://my-ldap -b dc=nodomain -D cn=admin,dc=nodomain -w admin |
This test should return the exact output of the previous ldapsearch. Failure is indicated by a short message which is invoked by using the switch “-zz”.
1 2 | # with TLS ldapsearch -x -ZZ -H ldap://my-ldap -b dc=nodomain -D cn=admin,dc=nodomain -w admin |
Working with PostgreSQL using TLS
Configuration
This is the host-based rule with TLS configured; notice the minor edit in red:
1 2 3 4 5 | # IPv4 local connections: hostssl all all 0.0.0.0/0 ldap ldapserver=tmp ldapprefix="cn=" ldapsuffix=", dc=pg_user, dc=nodomain" <span style="color: #ff0000;">ldaptls=1</span> # IPv6 local connections: host all all ::0/0 ldap <span style="color: #000000;">ldapserver=tmp</span> ldapprefix="cn=" ldapsuffix=", dc=pg_user, dc=nodomain" <span style="color: #ff0000;">ldaptls=1</span> |
Validation test: TLS
After updating pg_hba.conf with the new argument, ldaptls=1, a server reload is executed, followed by a psql ping:
1 | systemctl reload postgresql@14-main |
1 2 3 4 | root@my-ldap:~# psql 'host=10.231.38.243 dbname=postgres user=postgres password=postgres' -c "select 'ping from postgres' as test_connectivity" test_connectivity -------------------- ping from postgres |
Percona Distribution for PostgreSQL provides the best and most critical enterprise components from the open-source community, in a single distribution, designed and tested to work together.
Download Percona Distribution for PostgreSQL Today!
