LDAPS trusted ca support

On Sat, Nov 16, 2019 at 10:50 AM Marco Cuccato <mcuccato.vts@gmail.com>
wrote:

Hi to all and thanks for the great job you're doing with PGSQL!
May you please check this question?

https://stackoverflow.com/questions/58747680/postgresql-ldap-authentication-with-ssl-self-signed-certificate

I can't figure out :(

Hi,

There are a bunch of files with names like ldap.conf that are searched
for configuration by libldap.so (depending how it was built).
https://www.openldap.org/software/man.cgi?query=ldap.conf describes
the options.

For example, in the automated regression tests we just put the
following into a file we point to with $LDAPCONF:

TLS_REQCERT never

Without that, our simple LDAPS test fails with the same error you
showed. Of course you probably want to actually verify your real
server's certificate, so perhaps you need to put the self-signed cert
into TLS_CACERT (so it's trusted as a CA to sign stuff, including
itself).

I'm not sure why command line ldapsearch is working for you. I'd try
using strace/truss to see what files it's opening to get that stuff,
and compare with PostgreSQL (trace the main postmaster process using
-f to follow children, and then try to log in).

Hi,
unfortunately I cannot modify the company's LDAP server configuration.
The only way is to configure my PGSQL instance which is a client of LDAP
server.
As the server, at the connection time, presents it's certificate, I need a
way to tell PGSQL to trust it, adding somewhere the root CA certificate
that's used to sign the LDAP certificate.
At system level (a Red Hat 7.6 server), the root CA self-signed
certificate is already added as CA to be trusted, but seems PGSQL ignore
that.
What can I do?
Thanks

Il giorno mar 19 nov 2019 alle ore 11:34 Thomas Munro <
thomas.munro@gmail.com> ha scritto:

On Sat, Nov 16, 2019 at 10:50 AM Marco Cuccato <mcuccato.vts@gmail.com>
wrote:

Hi to all and thanks for the great job you're doing with PGSQL!
May you please check this question?

https://stackoverflow.com/questions/58747680/postgresql-ldap-authentication-with-ssl-self-signed-certificate

I can't figure out :(

Hi,

There are a bunch of files with names like ldap.conf that are searched
for configuration by libldap.so (depending how it was built).
https://www.openldap.org/software/man.cgi?query=ldap.conf describes
the options.

For example, in the automated regression tests we just put the
following into a file we point to with $LDAPCONF:

TLS_REQCERT never

Without that, our simple LDAPS test fails with the same error you
showed. Of course you probably want to actually verify your real
server's certificate, so perhaps you need to put the self-signed cert
into TLS_CACERT (so it's trusted as a CA to sign stuff, including
itself).

I'm not sure why command line ldapsearch is working for you. I'd try
using strace/truss to see what files it's opening to get that stuff,
and compare with PostgreSQL (trace the main postmaster process using
-f to follow children, and then try to log in).

On Tue, Nov 26, 2019 at 4:35 AM Marco Cuccato <mcuccato.vts@gmail.com>
wrote:

Ok sorry for the mail before I misunderstood your suggestion.
I verified the ldap.conf file and the TLS_CACERT parameter points to a

PEM file which already contains the certificate that signs the LDAP server
certificate.

Here are some things I'd check: When you used the ldapsearch command,
did you use -ZZ? (Just -Z means something like try to use SSL but
don't worry if it doesn't work; -ZZ requires it to work). Does the
"postgres" user (assuming the RHEL packages use that to run
PostgreSQL) have permissions to read the files it needs to read? If
you become that user with su - postgres, can you use the "ldapsearch"
command successfully? If you do strace -f -p [postmaster], and then
try to log in with your LDAP-authenticated user, does it give you a
clue about what files it is accessing or failing to access, and then
if you compare "strace ldapsearch ...", does that give you a clue
about what is different? If you do ldd /path/to/postgres and ldd
/path/to/ldapsearch can you see that they're both using the same
libldap-XXX.so.Y (if they were using different OpenLDAP client
libraries they might have different .conf paths compiled into them)?

Thanks Thomas,
your suggestions put me on the right way.
I was performing the ldapsearch as root and not as the postgresql user,
that is the user that run postgres service.
Thanks to ldapsearch debug, I found that this user was not able to read
the /etc/openldap/ldap.conf file, which contains the TLS configuration
properties such as TLS_CACERT, TLS_CACERTDIR and TLS_CACERTFILE that points
to the needed self-signed certificate.
After letting postgres user to read this file, the ldap authentication
works.
Just a precisation: ldapscheme=ldap with ldaptls=1 works, any other
combination does not work.
Thank you very much,
Marco

Il giorno lun 25 nov 2019 alle ore 22:33 Thomas Munro <
thomas.munro@gmail.com> ha scritto:

On Tue, Nov 26, 2019 at 4:35 AM Marco Cuccato <mcuccato.vts@gmail.com>
wrote:

Ok sorry for the mail before I misunderstood your suggestion.
I verified the ldap.conf file and the TLS_CACERT parameter points to a

PEM file which already contains the certificate that signs the LDAP server
certificate.

Here are some things I'd check: When you used the ldapsearch command,
did you use -ZZ? (Just -Z means something like try to use SSL but
don't worry if it doesn't work; -ZZ requires it to work). Does the
"postgres" user (assuming the RHEL packages use that to run
PostgreSQL) have permissions to read the files it needs to read? If
you become that user with su - postgres, can you use the "ldapsearch"
command successfully? If you do strace -f -p [postmaster], and then
try to log in with your LDAP-authenticated user, does it give you a
clue about what files it is accessing or failing to access, and then
if you compare "strace ldapsearch ...", does that give you a clue
about what is different? If you do ldd /path/to/postgres and ldd
/path/to/ldapsearch can you see that they're both using the same
libldap-XXX.so.Y (if they were using different OpenLDAP client
libraries they might have different .conf paths compiled into them)?

Greetings,

* Marco Cuccato (mcuccato.vts@gmail.com) wrote:

unfortunately I cannot modify the company's LDAP server configuration.

Note that if you're working in an Active Directory environment, you
should really be considering Kerberos/GSSAPI instead of LDAP for your
authentication. Using PostgreSQL's "ldap" auth method means that the
user's password is sent to, and read by, the PostgreSQL server, which
isn't really very secure.

You'll definitely also want to be using SSL/TLS between the PostgreSQL
client system and the PostgreSQL server, but that doesn't help you if
the PostgreSQL server itself is compromised.

Thanks,

Stephen