LDAPS trusted ca support

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
9 messages Options
Reply | Threaded
Open this post in threaded view
|

LDAPS trusted ca support

Marco Cuccato
Hi to all and thanks for the great job you're doing with PGSQL!
May you please check this question?
I can't figure out :(

Best regards,
Marco Cuccato
Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Thomas Munro-5
On Sat, Nov 16, 2019 at 10:50 AM Marco Cuccato <[hidden email]> 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).


Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Marco Cuccato
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 <[hidden email]> ha scritto:
On Sat, Nov 16, 2019 at 10:50 AM Marco Cuccato <[hidden email]> 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).
Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Marco Cuccato
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.


Il giorno lun 25 nov 2019 alle ore 16:07 Marco Cuccato <[hidden email]> ha scritto:
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 <[hidden email]> ha scritto:
On Sat, Nov 16, 2019 at 10:50 AM Marco Cuccato <[hidden email]> 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).
Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Thomas Munro-5
On Tue, Nov 26, 2019 at 4:35 AM Marco Cuccato <[hidden email]> 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)?


Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Marco Cuccato
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 <[hidden email]> ha scritto:
On Tue, Nov 26, 2019 at 4:35 AM Marco Cuccato <[hidden email]> 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)?
Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Marco Cuccato
For the records, to avoid setting more permissions to ldap.conf, it enough to add the following line

Environment=LDAPTLS_CACERT=/var/lib/pgsql/12/data/my-root-ca.pem

To the systemd's postgresql-12.service file!


Thanks again,


Il giorno lun 2 dic 2019 alle ore 14:13 Marco Cuccato <[hidden email]> ha scritto:

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 <[hidden email]> ha scritto:
On Tue, Nov 26, 2019 at 4:35 AM Marco Cuccato <[hidden email]> 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)?
Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Stephen Frost
In reply to this post by Marco Cuccato
Greetings,

* Marco Cuccato ([hidden email]) 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

signature.asc (836 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: LDAPS trusted ca support

Marco Cuccato
Thanks for the tip! 

Il giorno mar 3 dic 2019 alle ore 21:35 Stephen Frost <[hidden email]> ha scritto:
Greetings,

* Marco Cuccato ([hidden email]) 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