Postgres secure ldap authentication issues

Question

I cannot seem to get my CNPG postgres database to connect to LDAP via secured means; I've setup a bitnami openldap container with a user user01 and password password01 and can bind to it via ldapsearch fine:

❯ ldapsearch    -H ldap://openldap-test.example.org  -D "cn=user01,ou=users,dc=example,dc=org" -w 'password01'  -b 'dc=example,dc=org'
❯ ldapsearch    -H ldaps://openldap-test.example.org -D "cn=user01,ou=users,dc=example,dc=org" -w 'password01'  -b 'dc=example,dc=org'
❯ ldapsearch -Z -H ldap://openldap-test.example.org  -D "cn=user01,ou=users,dc=example,dc=org" -w 'password01'  -b 'dc=example,dc=org'

All return results as expected - ie both ldap, ldaps and STARTTLS are functional.

In my Postgres database I've tried setting CNPN values to

postgresql:
  ldap:
    scheme: ldap[s]
    server: openldap-test.example.org
    tls: false
    port: [389|636]
    bindAsAuth:
      prefix: 'cn='
      suffix: ',ou=users,dc=example,dc=org'

depending upon:

clear-text: port=389, scheme=ldap, tls=false
ldaps:      port=636, scheme=ldaps, tls=false
starttls:   port=389, scheme=ldap, tls=true

which results in pg_hba.conf being populated as such:

host all all 0.0.0.0/0 ldap ldapserver=openldap-test.example.org ldapport=389 ldapscheme=ldap ldapprefix="cn=" ldapsuffix=",ou=users,dc=example,dc=org"
host all all 0.0.0.0/0 ldap ldapserver=openldap-test.example.org ldapport=636 ldapscheme=ldaps ldapprefix="cn=" ldapsuffix=",ou=users,dc=example,dc=org"
host all all 0.0.0.0/0 ldap ldapserver=openldap-test.example.org ldapport=389 ldapscheme=ldap ldaptls=1 ldapprefix="cn=" ldapsuffix=",ou=users,dc=example,dc=org"

respectively.

logging in with:

PGPASSWORD=password01 psql -h postgres-database -U user01 -d mydb

only works for the clear-text (port 389, no STARTTLS). When attempting to use the other two methods, I get this error:

psql: error: connection to server at "postgres-database", port 5432 failed: FATAL: LDAP authentication failed for user "user01"

The output from the postgres container with TLS

{"level":"info","ts":1663799766.7574096,"logger":"postgres","msg":"record","logging_pod":"usdf-butler-ldap-1","record":{"log_time":"2022-09-21 22:36:06.757 UTC","user_name":"user01","database_name":"lsstdb1","process_id":"37730","connection_from":"192.168.252.244:54054","session_id":"632b91d6.9362","session_line_num":"1","command_tag":"authentication","session_start_time":"2022-09-21 22:36:06 UTC","virtual_transaction_id":"3/63120","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"LDAP login failed for user \"cn=user01,ou=users,dc=example,dc=org\" on server \"openldap-test.example.org\": Can't contact LDAP server","detail":"LDAP diagnostics: (unknown error code)","backend_type":"client backend","query_id":"0"}}
{"level":"info","ts":1663799766.7574224,"logger":"postgres","msg":"record","logging_pod":"usdf-butler-ldap-1","record":{"log_time":"2022-09-21 22:36:06.757 UTC","user_name":"user01","database_name":"lsstdb1","process_id":"37730","connection_from":"192.168.252.244:54054","session_id":"632b91d6.9362","session_line_num":"2","command_tag":"authentication","session_start_time":"2022-09-21 22:36:06 UTC","virtual_transaction_id":"3/63120","transaction_id":"0","error_severity":"FATAL","sql_state_code":"28000","message":"LDAP authentication failed for user \"user01\"","detail":"Connection matched pg_hba.conf line 14: \"host all all 0.0.0.0/0 ldap ldapserver=openldap-test.example.org ldapport=636 ldapscheme=ldaps ldapprefix=\"cn=\" ldapsuffix=\",ou=users,dc=example,dc=org\"\"","backend_type":"client backend","query_id":"0"}}

.. and with STARTTS:

{"level":"info","ts":1663799655.9419446,"logger":"postgres","msg":"record","logging_pod":"usdf-butler-ldap-1","record":{"log_time":"2022-09-21 22:34:15.941 UTC","user_name":"user01","database_name":"lsstdb1","process_id":"37528","connection_from":"192.168.252.244:36380","session_id":"632b9167.9298","session_line_num":"1","command_tag":"authentication","session_start_time":"2022-09-21 22:34:15 UTC","virtual_transaction_id":"3/62785","transaction_id":"0","error_severity":"LOG","sql_state_code":"00000","message":"could not start LDAP TLS session: Connect error","detail":"LDAP diagnostics: (unknown error code)","backend_type":"client backend","query_id":"0"}}
{"level":"info","ts":1663799655.9419892,"logger":"postgres","msg":"record","logging_pod":"usdf-butler-ldap-1","record":{"log_time":"2022-09-21 22:34:15.941 UTC","user_name":"user01","database_name":"lsstdb1","process_id":"37528","connection_from":"192.168.252.244:36380","session_id":"632b9167.9298","session_line_num":"2","command_tag":"authentication","session_start_time":"2022-09-21 22:34:15 UTC","virtual_transaction_id":"3/62785","transaction_id":"0","error_severity":"FATAL","sql_state_code":"28000","message":"LDAP authentication failed for user \"user01\"","detail":"Connection matched pg_hba.conf line 14: \"host all all 0.0.0.0/0 ldap ldapserver=openldap-test.example.org ldapport=389 ldapscheme=ldap ldaptls=1 ldapprefix=\"cn=\" ldapsuffix=\",ou=users,dc=example,dc=org\"\"","backend_type":"client backend","query_id":"0"}}

the logs from openldap:

632b9167.376217e9 0x7f784ae33700 conn=1005 fd=14 ACCEPT from IP=172.24.4.42:25550 (IP=0.0.0.0:389)
632b9167.376345a0 0x7f784a632700 conn=1005 op=0 EXT oid=1.3.6.1.4.1.1466.20037
632b9167.376425eb 0x7f784a632700 conn=1005 op=0 STARTTLS
632b9167.3765614e 0x7f784a632700 conn=1005 op=0 RESULT oid= err=0 qtime=0.000012 etime=0.000153 text=
632b9167.3792906f 0x7f784a632700 conn=1005 fd=14 TLS established tls_ssf=256 ssf=256 tls_proto=TLSv1.3 tls_cipher=TLS_AES_256_GCM_SHA384
632b9167.379e8703 0x7f784ae33700 conn=1005 fd=14 closed (connection lost)
632b9167.37ef19fe 0x7f784a632700 conn=1006 fd=14 ACCEPT from IP=172.24.4.42:58264 (IP=0.0.0.0:389)
632b9167.37efd555 0x7f784ae33700 conn=1006 op=0 EXT oid=1.3.6.1.4.1.1466.20037
632b9167.37f01047 0x7f784ae33700 conn=1006 op=0 STARTTLS
632b9167.37f09797 0x7f784ae33700 conn=1006 op=0 RESULT oid= err=0 qtime=0.000006 etime=0.000055 text=
632b9167.381a6e2a 0x7f784ae33700 conn=1006 fd=14 TLS established tls_ssf=256 ssf=256 tls_proto=TLSv1.3 tls_cipher=TLS_AES_256_GCM_SHA384
632b9167.382591e3 0x7f784a632700 conn=1006 fd=14 closed (connection lost)
632b91d6.2c73ec6d 0x7f784ae33700 conn=1007 fd=14 ACCEPT from IP=172.24.4.42:27509 (IP=0.0.0.0:636)
632b91d6.2c9ede29 0x7f784ae33700 conn=1007 fd=14 TLS established tls_ssf=256 ssf=256 tls_proto=TLSv1.3 tls_cipher=TLS_AES_256_GCM_SHA384
632b91d6.2ca8d57f 0x7f784a632700 conn=1007 fd=14 closed (connection lost)
632b91d6.2cf610c8 0x7f784ae33700 conn=1008 fd=14 ACCEPT from IP=172.24.4.42:40216 (IP=0.0.0.0:636)
632b91d6.2d1b084d 0x7f784ae33700 conn=1008 fd=14 TLS established tls_ssf=256 ssf=256 tls_proto=TLSv1.3 tls_cipher=TLS_AES_256_GCM_SHA384
632b91d6.2d24bdaa 0x7f784a632700 conn=1008 fd=14 closed (connection lost)

what is interesting is that each single login attempt with postgres attempts to query ldap twice; each apparently succeeding with the TLS handshake, and then immediately closing. No binding is even attempted. both report sql_state_code of 28000 in postgres.

thinking that this might be a cert issue, from the postgres container i run:

$ openssl s_client -showcerts -verify 5 -connect openldap-test.examp:636  < /dev/null | awk '/BEGIN/,/END/{ if(/BEGIN/)    {a++}; out="cert"a".pem"; print >out}'
$ for cert in *.pem; do openssl verify -show_chain $cert ; done
C = US, ST = **redacted**, O = **redacted**, CN = openldap-test.example.org
error 20 at 0 depth lookup: unable to get local issuer certificate
error cert1.pem: verification failed
cert2.pem: OK
Chain:
depth=0: C = US, ST = MI, L = Ann Arbor, O = Internet2, OU = InCommon, CN = InCommon RSA Server CA (untrusted)
depth=1: C = US, ST = New Jersey, L = Jersey City, O = The USERTRUST Network, CN = USERTrust RSA Certification Authority
cert3.pem: OK
Chain:
depth=0: C = US, ST = New Jersey, L = Jersey City, O = The USERTRUST Network, CN = USERTrust RSA Certification Authority (untrusted)
depth=1: C = GB, ST = Greater Manchester, L = Salford, O = Comodo CA Limited, CN = AAA Certificate Services
cert4.pem: OK
Chain:
depth=0: C = GB, ST = Greater Manchester, L = Salford, O = Comodo CA Limited, CN = AAA Certificate Services

any ideas upon why postgres is disconnecting and not even attempting to bind?

Answer 1

I think you have a certificate verification problem. The certificate presented by your LDAP server is not trusted by your postgres server. There are various ways of addressing this problem.

The right way: Install the correct CA certificate

1. Place the appropriate CA certificate (the one that signed your ldap server certiticate) somewhere on your postgres server (e.g, /etc/ssl/certs/my-ca-certificate.crt).

2. Configure libldap to trust certificates signed by that CA. Add the following to /etc/ldap/ldap.conf (create the file if it does not exist):

   TLS_CACERT /etc/ssl/certs/my-ca-certificate.crt
   

The wrong way: disable certificate verification

1. Add the following to /etc/ldap/ldap.conf:

   TLS_REQCERT never
   

If you are unable to write to /etc/ldap/ldap.conf, you can place your configuration in a different file and set the LDAPCONF environment variable to point to that file.

If you're interested, I've set up a simple docker-compose stack for testing this; you can find it here.