PostgreSQL LDAP client authentication implementation

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…

IT World

PostgreSQL LDAP client authentication implementation

Table of contents

Preamble

PostgreSQL LDAP client authentication implementation

PostgreSQL LDAP client authentication testing

References

About Post Author

Yannick Jaquier

Related Posts:

Leave a Reply Cancel Reply