Configuring PostgreSQL and LDAP Using StartTLS

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:

1. PostgreSQL and LDAP hosts are installed on the same host.


2. Username accounts, but not passwords, are created on the Postgres server.


3. Both username accounts and passwords are configured on the LDAP server.


4. Private/Public keys, sic certificates, are to be created.


5. 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:

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.

Installation LDAP

Running netstat, (netstat -tlnp), returns the following:

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:

Configuration

The following bash script demonstrates configuring OpenLDAP to authenticate three Postgres ROLES, i.e., postgres, user1, and user2:

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:

Working With TLS Encryption

Install Additional Packages

To work with SSL certificates, these packages should be present, i.e., for the Ubuntu distribution:

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.

In this case, the CA certificate is configured to expire in ten years:

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:

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:

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:

Define LDAP certificate attributes:

Sign the LDAP private key using the self-signed Certificate Authority certificate and its private key:

Update access permissions of the private key:

Create and save the LDAP TLS configuration file, certinfo.ldif:

Enable OpenLDAP to use TLS:

Validation test #1

Validation returns the string  “anonymous”:

Validation test #2

This test confirms that the previous behavior without TLS still works:

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”.

Working with PostgreSQL using TLS

Configuration

This is the host-based rule with TLS configured; notice the minor edit in red:

Validation test: TLS

After updating pg_hba.conf with the new argument, ldaptls=1, a server reload is executed, followed by a psql ping:

 

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!