Table of contents
Preamble
Is PostgreSQL LDAP client authentication easy to implement ? I have already tried it for multiple database flavors and this article will greatly benefit from all the issues I have resolved while implementing LDAP client authentication for MariaDB.
I have tested it with PostgreSQL 15.2 running on Oracle Linux Server release 8.7. The LDAP server I will use is my corporate LDAP server where I have already my short login account. The big picture is:
PostgreSQL LDAP client authentication implementation
I initially had the insecure pg_hba.conf configuration for my test account on my PostgreSQL instance:
host all yjaquier 0.0.0.0/0 trust |
Which I modified for the most secure request password option:
host all yjaquier 0.0.0.0/0 scram-sha-256 |
User created with, of course you must create the user on the instance first:
postgres=# create role yjaquier with superuser login password 'secure_password'; CREATE ROLE |
To test the password request use (first trial I deliberately used a wrong password):
postgres=# SELECT pg_reload_conf(); pg_reload_conf ---------------- t (1 row) postgres=# \q [postgres@server1 ~]$ /usr/pgsql-15/bin/psql --port=5433 --host=server1.domain.com --username=yjaquier --dbname=postgres Password for user yjaquier: psql: error: connection to server at "server1.domain.com" (192.168.56.101), port 5433 failed: FATAL: password authentication failed for user "yjaquier" connection to server at "server1.domain.com" (192.168.56.101), port 5433 failed: FATAL: password authentication failed for user "yjaquier" [postgres@server1 ~]$ /usr/pgsql-15/bin/psql --port=5433 --host=server1.domain.com --username=yjaquier --dbname=postgres Password for user yjaquier: psql (15.2) Type "help" for help. postgres=# |
Unlike MariaDB, with PostgreSQL the LDAP client authentication is directly implemented in the engine so I did only modify my pg_hba.conf file to change the authentication method for my own account with:
host all yjaquier 0.0.0.0/0 ldap ldapserver=ldap-server.domain.com ldapscheme=ldaps ldapbasedn="ou=employee,dc=company,dc=com" ldapsearchattribute=uid |
I tried to authenticate and got the famous certificate error:
2023-03-02 11:24:33.093 CET [30928] LOG: could not perform initial LDAP bind for ldapbinddn "" on server "ldap-server.domain.com": Can't contact LDAP server 2023-03-02 11:24:33.093 CET [30928] DETAIL: LDAP diagnostics: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed (self signed certificate in certificate chain) 2023-03-02 11:24:33.093 CET [30928] FATAL: LDAP authentication failed for user "yjaquier" 2023-03-02 11:24:33.093 CET [30928] DETAIL: Connection matched pg_hba.conf line 94: "host all yjaquier 0.0.0.0/0 ldap ldapserver=ldap-server.domain.com ldapscheme=ldaps ldapbasedn="ou=employee,dc=company,dc=com" ldapsearchattribute=uid" |
Or if you want to test it with the Open LDAP client (install openldap-clients package to be able to use it):
[postgres@server1 ~]$ ldapsearch -H "ldaps://ldap-server.domain.com/" -b "ou=employee,dc=company,dc=com" -s sub "(uid=yjaquier)" ldap_sasl_interactive_bind_s: Can't contact LDAP server (-1) additional info: error:14090086:SSL routines:ssl3_get_server_certificate:certificate verify failed (self signed certificate in certificate chain) |
I have done the exact same certificate setup that we have already seen with MariaDB(link)… First extracted the certification chain of my corporate LDAP server with:
[root@eserver1 ~]# cd /etc/openldap/certs [root@server1 certs]# openssl s_client -showcerts -verify 5 -connect ldap-server.domain.com:636 < /dev/null | awk '/BEGIN/,/END/{ if(/BEGIN/) {a++}; out="ldap-server-cert"a".pem"; print >out}' verify depth is 5 depth=2 C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1 verify error:num=19:self signed certificate in certificate chain verify return:1 depth=2 C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1 verify return:1 depth=1 C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Corporate Server CA1 verify return:1 depth=0 CN = server05.domain.com, OU = DIT, O = STMicroelectronics International NV verify return:1 DONE |
Added my company Certificate Authority files to my PostgreSQL server:
[root@server1 certs]# cd /etc/pki/ca-trust/source/anchors [root@server1 anchors]# ll total 8 -rw-r--r-- 1 root root 2442 Mar 2 17:44 STMicroelectronicsCorporateServerCA1.cer -rw-r--r-- 1 root root 2218 Mar 2 17:44 STMicroelectronicsPrivateCorporateRootCA1.cer [root@server1 anchors]# update-ca-trust extract |
Now my LDAP server certificates can be verified:
[root@server1 anchors]# for cert in /etc/openldap/certs/*.pem; do openssl verify -show_chain $cert ; done /etc/openldap/certs/ldap-server-cert1.pem: OK Chain: depth=0: CN = server05.domain.com, OU = DIT, O = STMicroelectronics International NV (untrusted) depth=1: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Corporate Server CA1 depth=2: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1 /etc/openldap/certs/ldap-server-cert2.pem: OK Chain: depth=0: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Corporate Server CA1 (untrusted) depth=1: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1 /etc/openldap/certs/ldap-server-cert3.pem: OK Chain: depth=0: C = CH, O = STMicroelectronics International N.V., OU = Corporate Services, CN = STMicroelectronics Private Corporate Root CA1 |
And the LDAP search command is working:
[postgres@server1 ~]$ ldapsearch -x -H "ldaps://ldap-server.domain.com/" -b "ou=employee,dc=company,dc=com" -s sub "(uid=yjaquier)" # extended LDIF # # LDAPv3 # base <ou=employee,dc=company,dc=com> with scope subtree # filter: (uid=yjaquier) # requesting: ALL # . . |
PostgreSQL LDAP client authentication testing
The testing is damn simple, with my correct LDAP password:
[postgres@server1 ~]$ /usr/pgsql-15/bin/psql --port=5433 --host=server1.domain.com --username=yjaquier --dbname=postgres Password for user yjaquier: psql (15.2) Type "help" for help. postgres=# |
With the wrong password or even the password I have chosen while creating the account:
[postgres@server1 ~]$ /usr/pgsql-15/bin/psql --port=5433 --host=server1.domain.com --username=yjaquier --dbname=postgres Password for user yjaquier: psql: error: connection to server at "server1.domain.com" (192.168.56.101), port 5433 failed: FATAL: LDAP authentication failed for user "yjaquier" |
I have also tried to create a create a user not present in my central LDAP directory:
postgres=# create role toto with superuser login password 'secure_password'; CREATE ROLE |
I have added in pg_hba.conf file:
host all toto 0.0.0.0/0 ldap ldapserver=ldap-server.domain.com ldapscheme=ldaps ldapbasedn="ou=employee,dc=company,dc=com" ldapsearchattribute=uid |
Of course while connecting I don’t really know what to specify as a password but in PostgreSQL log file the message is interesting:
2023-03-03 00:06:50.635 CET [78738] LOG: LDAP user "toto" does not exist 2023-03-03 00:06:50.635 CET [78738] DETAIL: LDAP search for filter "(uid=toto)" on server "ldap-server.domain.com" returned no entries. 2023-03-03 00:06:50.636 CET [78738] FATAL: LDAP authentication failed for user "toto" 2023-03-03 00:06:50.636 CET [78738] DETAIL: Connection matched pg_hba.conf line 93: "host all toto 0.0.0.0/0 ldap ldapserver=ldap-server.domain.com ldapscheme=ldaps ldapbasedn="ou=employee,dc=company,dc=com" ldapsearchattribute=uid" |
In other words a user that left the company cannot be used anymore in your database which, in real life, is not always true with locally created users…
References
- PostgreSQL Database Security: External Server-Based Authentication
- LDAP Authentication
- The pg_hba.conf File
