CODEWITHSUNDEEP
postgresqlauthenticationsslldapstarttls
yee379
yee379

Reputation: 6752

Postgres secure ldap authentication issues

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?

Upvotes: 0

Views: 3101

Answers (2)

Brad
Brad

Reputation: 2185

I FINALLY got this working with ldaps. Some notes for anyone else struggling with this in the future:

  1. You must mount your CA certificate and create an environment variable. You do not need your intermediate certificate (if you have one)
...
  projectedVolumeTemplate:
    sources:
      - secret:
          name: root-ca
          items:
            - key: root.crt
              path: root.crt
  env:
    - name: LDAPTLS_CACERT
      value: /projected/root.crt
...
----
apiVersion: v1
data:
  root.crt: 
# replace with your base64 encoded CA certificate)
kind: Secret
metadata:
  name: root-ca
type: Opaque

However, this does NOT work:

  postgresql:
    ldap:
      server: '10.45.30.28'
      port: 3269
      scheme: 'ldaps'
      bindSearchAuth:
        baseDN: 'DC=mydomain,DC=local'
        bindDN: 'CN=postgres,OU=Special Accounts,OU=145 King St W,DC=mydomain,DC=local'
        bindPassword:
          name: 'ldapbindpassword'
          key: 'bindpassword'
        searchAttribute: "sAMAccountName"

This DOES work:

  postgresql:
    ldap:
      server: 'mydomain.local'
      port: 3269
      scheme: 'ldaps'
      bindSearchAuth:
        baseDN: 'DC=mydomain,DC=local'
        bindDN: 'CN=postgres,OU=Special Accounts,OU=145 King St W,DC=mydomain,DC=local'
        bindPassword:
          name: 'ldapbindpassword'
          key: 'bindpassword'
        searchAttribute: "sAMAccountName"

Couple notes:

I had to use port 3269 (LDAPS Global Catalog Server) instead of 636 (the standard LDAP port) to get this to work. Your mileage may vary. I'm unclear as to why using GC LDAPS is necessary.

The only difference between these configs is the server value. In the first, it's an IP address, and in the second, it's a DNS name. The TLS certificate we have installed from our domain controller has both values in the SAN (subject alternative name); however, openldap/openssl/Postgres seems not to like the IP address for whatever reason.

And what's infuriating about this is that the only error returned by openldap search with the IP address is this:

ldap_sasl_bind(SIMPLE): Can't contact LDAP server (-1)

The only thing that shows up in the Postgres logs is:

    "message": "could not perform initial LDAP bind for ldapbinddn \"CN=postgres,OU=Special Accounts,OU=145 King St W,DC=mydomain,DC=local\" on server \"10.45.30.28\": Can't contact LDAP server",
    "detail": "LDAP diagnostics: (unknown error code)",

Even when cranking logging all the way up. This is all I could get - super helpful OpenLDAP/Postgres! :angry:

The way we were able to figure this out was to create a custom version of the CloudNative postgres image using this: https://cloudnative-pg.io/blog/creating-container-images/.

Then, in the custom image, I installed the ldaputils so we could run ldapsearch queries and find a query that worked. Here's the docker file we used:

FROM ghcr.io/cloudnative-pg/postgresql:17-bullseye

LABEL maintainer="[email protected]"

# To install any package we need to be root
USER root

RUN apt-get update && \
    apt-get install -y wget curl ldap-utils 

# Change the uid of postgres to 26
RUN usermod -u 26 postgres
USER 26

Hopefully, my notes above save someone else from the same headache I went through. :exploding_head:

Upvotes: -2

larsks
larsks

Reputation: 311606

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.

Upvotes: 0

Related Questions