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 am detailing how to enable TLS between PostgreSQL and the OpenLDAP server.

Ironically, the most complicated aspect has nothing to do with either PostgreSQL or OpenLDAP. It has to do with the steps of creating and signing private keys and certificates.

Note: I had seriously considered leaving out much of the OpenLDAP commands, but I figured it might benefit you if, like me, you do not touch this very often.

Summary Steps

The underlying assumptions are:

1. PostgreSQL and LDAP 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, meaning certificates, are to be created.
5. A self-signed Certificate Authority, or 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 with password postgres

Installing PostgreSQL

Apart from the standard steps of installing and configuring Postgres for remote access, edit the host-based authentication file to enable Postgres to refer to the LDAP service for authentication.

Roles and user accounts 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.0.1, 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.

Installing LDAP

Running 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 LDAP installation includes setting the password, which will be admin in this example, you can reset the password at will by executing the following command:

Configuration

The following bash script demonstrates configuring OpenLDAP to authenticate three Postgres roles: 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 on Ubuntu, these packages should be present:

Managing the Certificate Authority

Authentication between the Postgres and LDAP servers includes confirming that the hosts making connection attempts are legitimate. For that reason, the certificates for both servers must be signed, which means a Certificate Authority mechanism is required.

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

An internal system can get away with 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: /usr/local/share/ca-certificates.

Attention: Once signed, the CA certificate is copied onto the Postgres and LDAP servers respectively. In this case, because 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, which in this case is my-ldap.

Generate a private key for the 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 that 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!