< Back to blog home PostgreSQL Zone

Secure LDAP implementation for Fujitsu Enterprise Postgres

Vincent O'Dea | November 1, 2023

Background

LDAP does not encrypt packages sent between the client and the server. In contrast, LDAPS encrypts all LDAP attributes, including user credentials. Packet encryption safeguards the data from credential theft and makes packet sniffing attacks harder to perform.

LDAPS uses TLS to encrypt LDAP packets

TLS (Transport Layer Security) is a cryptographic protocol that establishes a secure connection between client and server using certificates. LDAPS makes the communication between the client and the server secure so that it cannot ldapseabe hacked by a third party. It ensures that both parties can be certain who they are talking to and messages in transit cannot be modified.

Note that using ldapscheme or ldaptls only encrypts the traffic between the PostgreSQL server and the LDAP server. The connection between the PostgreSQL server and the PostgreSQL client will still be unencrypted unless SSL is used there as well.

Steps to implement LDAPS

Implementing LDAPS is simple, but requires a few steps.

The procedure to configure your Fujitsu Enterprise Postgres database with LDAP on Linux is described in our blog post Connecting Fujitsu Enterprise Postgres to Active Directory for Authentication using LDAP. However, unfortunately I found very little online material for how to get LDAPS working on a standard VM installation.

The below is what was done with a recent customer to get LDAPS working for them.

Customer story

Customer security officers asked all LDAP consumers to migrate to LDAPS as part of an internal security review. The customer was able to connect with LDAP, but could not when attempting to use LDAPS.

Just to reiterate, using unsecure LDAP to connect to their Fujitsu Enterprise Postgres instance worked fine.

The customer pg_hba.conf file is using a key file where they store most of the METHOD attributes:

# TYPE  DATABASE  USER   ADDRESS      METHOD
hostssl db        user   ip-address   ldap@ldap_binduser.key

where the file @ldap_binduser.key is stored in the data directory and protected via chmod 0400.

The @ldap_binduser.key was using:

ldapurl="ldap://xxxx:389" ldaptls=0 ldapbasedn="xxxx" ldapsearchattribute=sAMAccountName 
ldapbinddn="xxxxx" ldapbindpasswd="????"

Or

ldapserver=xxxx ldapbasedn="yyyy" ldapsearchattribute=sAMAccountName 
ldapbinddn="zzzz" ldapbindpasswd="????"

If they used it in the unsecure manner (which was to be switched off soon) it worked fine, and they could establish the connection via psql. However, if they added the secure LDAP parameters, authentication failed.

ldapserver=xxxx ldapport=636 ldapscheme=ldaps  ldapbasedn="yyyy" ldapsearchattribute=sAMAccountName 
ldapbinddn="zzzz" ldapbindpasswd=????"

Note that the customer already had SSL set to on in the postgresql.conf. Their pg_hba.conf entry was as follows:

# TYPE   DATABASE   USER    ADDRESS          METHOD
hostssl  all        abc123  xx.xx.xx.xx/32   ldap @ldap_binduser_new9.key clientcert=verify-full

Where the LDAP config key file resembled

ldapserver=xxxx ldaptls=1 ldapport=636 ldapscheme=ldaps ldapbasedn="xxxx" 
ldapsearchattribute=sAMAccountName ldapbinddn="xxxxx" ldapbindpasswd="???"

postgres@test > psql -dpostgres -p 21001 -udj640_t2 -h azsetipsqltest02.cs.test.ams.azu.dbgcloud.io
Password for user dj640_t2:
psql: error: FATAL:  LDAP authentication failed for user "dj640_t2" (10684)
FATAL:  LDAP authentication failed for user "dj640_t2" (10684)
postgres@test >

The error message in the database log when using LDAPS was:

…[351453]: LOG: received SIGHUP, reloading configuration files (11195)
…[350752]: LOG: disconnection: session time: 0:02:03.115 user=dj640_t2 database=postgres host=10.120.102.17 port=39254 (11111)
…[351453]: LOG: connection received: host=10.120.102.17 port=32910 (11212)
…[351487]: LOG: connection received: host=10.120.102.17 port=45146 (11212)
…[351487]: LOG: could not start LDAP TLS session: Can't contact LDAP server (10733)
…[351487]: DETAIL: LDAP diagnostics: error:1416F086: SSL routines:tls_process_server_certificate:certificate verify failed (unable to get local issuer certificate)
…[351487]: FATAL: LDAP authentication failed for user "dj640_t2" (10684)
…[351487]: DETAIL: connection matched pg_hba.conf line 249
…[351488]: LOG: connection received: host=10.120.102.17 port=45154 (11272)
…[351488]: LOG: could not start LDAP TLS session: Can't contact LDAP server (10733)
…[351488]: DETAIL: LDAP diagnostics: error:1416F086:SSL routine:ssl_process_server_certificate:certificate verify failed (unable to get local issuer certificate) 
…[351488]: FATAL: LDAP authentication failed for user "dj640_t2" (10684)
…[351488]: DETAIL: Connection matched pg_hba.conf line 249

1. The customer could successfully verify the certificate using openssl.

   $  openssl s_client -showcerts -verify_return_error  -connect xxxxxxxxxx:636 </dev/null
   CONNECTED(00000003)
   depth=2 C = DE, O = Deutsche Boerse Group, CN = Deutsche Boerse Group Root CA
   verify return:1
   depth=1 DC = net, DC = …
   verify return:1
   depth=0
   verify return:1
   ---
   Certificate chain
    0 s:
    …
   Server certificate
   subject=…
   issuer=…
   ---
   No client certificate CA names sent
   Requested Signature Algorithms: RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:RSA+SHA1:ECDSA+SHA256:ECDSA+SHA384:ECDSA+SHA1:DSA+SHA1:RSA+SHA512:ECDSA+SHA512
   Shared Requested Signature Algorithms: RSA-PSS+SHA256:RSA-PSS+SHA384:RSA-PSS+SHA512:RSA+SHA256:RSA+SHA384:ECDSA+SHA256:ECDSA+SHA384:RSA+SHA512:ECDSA+SHA512
   Peer signing digest: SHA256
   Peer signature type: RSA-PSS
   Server Temp Key: ECDH, P-384, 384 bits
   ---
   SSL handshake has read 4328 bytes and written 813 bytes
   Verification: OK
   ---
   New, TLSv1.3, Cipher is TLS_AES_256_GCM_SHA384
   Server public key is 2048 bit
   Secure Renegotiation IS NOT supported
   Compression: NONE
   Expansion: NONE
   No ALPN negotiated
   Early data was not sent
   Verify return code: 0 (ok)
   ---
   DONE

2. The customer had the below entry in /etc/openldap/ldap.conf:

   TLS_CACERT /etc/openldap/certs/ldapca.pem

   In the command above, the PEM file contains the certificate that signs the LDAP server certificate.

3. We advised the customer to amend the file /etc/openldap/ldap.conf to include the entries below:

   TLS_CACERTDIR  /etc/openldap/certs
   TLS_CERTFILE   ldapca.pem
   TLS_CACERT     /etc/openldap/certs/ldapca.pem

4. Check the customer's postgres OS user can read the configuration file /etc/openldap/ldap.conf.
   • If it cannot, then add the necessary permissions so it can read /etc/openldap/ldap.conf and re-test.
   • If it can, then set the environment variable LDAPTLS_CACERT as below, restart the Fujitsu Enterprise Postgres instance, and re-test.

     export LDAPTLS_CACERT=/etc/openldap/certs/ldapca.pem
     pg_ctl -D $PGDATA restart

5. If this works, add to the postgres OS account profile and the system file if auto-start is configured.

   Edit .bash_profile

   export LDAPTLS_CACERT=/etc/openldap/certs/ldapca.pem

   Modify the Fujitsu Enterprise Postgres service as defined in /etc/systemd/system and add:

   Environment=LDAPTLS_CACERT=/etc/openldap/certs/ldapca.pem

6. If this fails, add trace (strace or truss) to an ldapsearch and get more details.

   Example:

   strace -e trace=open,close,read,write ldapsearch .. > /tmp/Results.output

   You can check if your ldapsearch is correct by:

   ldapsearch -x -LLL -h add.testldap.com -D lookup@testldap.com -w 'Sep@2023' -b "dc=testldap,dc=com"

   where:

   • -x specifies simple authentication
   • -LLL prints responses in LDIF format without comments and version
   • -h specifies the LDAP host to connect to
   • -D indicates the user that will bind to AD
   • lookup@testldap.com is the LDAP bind user and NETBIOS name of the domain
   • -w specifies the password for the bind user
   • -b is the base dn for the search

   The above resolution worked! It solved the problem, and the psql connections were now able to use LDAPS.

   Results:

   > export LDAPTLS_CACERT=/etc/openldap/certs/ldapca.pem
   >  psql "port=12345 dbname=postgres host=… ……… sslcert=… sslkey=… sslrootcert=… sslmode=verify-full"
   Password for user abc12345:
   psql: error: FATAL:  LDAP authentication failed for user "abc12345" (10684)
   >
   > export LD_LIBRARY_PATH=/usr/lib64:/opt/fsepv13server64/lib
   >
   >  psql "port=12345 dbname=postgres host=… ……… sslcert=… sslkey=… sslrootcert=… sslmode=verify-full"
   Password for user abc12345:
   psql: error: FATAL:  LDAP authentication failed for user "abc12345" (10684)
   >
   >
   > pg_ctl -D <DATA> restart
   waiting for server to shut down.... done
   server stopped
   waiting for server to start....2023-10-04 12:58:16.812 CEST [2297957] LOG:  redirecting log output to logging collector process
   2023-10-04 12:58:16.812 CEST [2297957] HINT:  Future log output will appear in directory "/var/lib/xxx/postgres_log/13.1/pg_log".
   done
   server started
   >  psql "port=12345 dbname=postgres host=… ……… sslcert=… sslkey=… sslrootcert=… sslmode=verify-full"
   Password for user abc12345:
   psql (13.4)
   SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
   Type "help" for help.
   
   postgres=> \l
                                       List of databases
      Name    |  Owner   | Encoding |   Collate   |    Ctype    |     Access privileges
   -----------+----------+----------+-------------+-------------+---------------------------

To use secure client certificates to Fujitsu Enterprise Postgres refer to the PostgreSQL documentation Server Setup and Operation > Secure TCP/IP Connections with SSLs