SSL certificates issue
Recently I have been working on implementation of mutual SSL authentication between our application and PostgreSQL database.
I managed to make it work wih "ssl=true" connection option and "clientcert=1" flags in pg_hba.conf. Moreover I managed to make it work with C++ application using libpq and Java application using postgresql JDBC driver.
The only concern I have is some discrepancy between the way libpq and JDBC works. It seems that libpq is less restrictive than JDBC with standard
built-in SSLSocketFactory.
The following schema describes some information about my certs:
Client Server
postgresql.crt server.crt
postgresql.key server.key
root.crt root.crt
where postgresql.crt is singed by Intermediate CA 1
server.crt is signed by Intermediate CA 2
Intermediate CA 1 and Intermediate CA 2 are chain certs both singed by the same root certificate
root.crt - root certificate that signed Intermediate CA1 and Intermediate CA 2 (which was enough for libpq but not enough for JDBC)
Now the issue is then when using libpq it was enough to have only root certificate in server's root.crt and it worked fine.
But when I tried using the same with JDBC it turned out that I need to put whole chain (2 certs) of Intermediate CA 1 in server's root.crt.
All I need is the confirmation whether this is working as designed or this is possibly libpq bug?
Kind Regards,
Joanna
Asia <asia123321@op.pl> writes:
Now the issue is then when using libpq it was enough to have only root certificate in server's root.crt and it worked fine.
But when I tried using the same with JDBC it turned out that I need to put whole chain (2 certs) of Intermediate CA 1 in server's root.crt.
This is poor configuration, because every certificate listed in root.crt
is considered fully trusted for every purpose. It's best to keep only
top-level root certs in root.crt. Instead, put the full chain of
certificates into the client's postgresql.crt, as per the manual:
: In some cases, the client certificate might be signed by an
: "intermediate" certificate authority, rather than one that is directly
: trusted by the server. To use such a certificate, append the certificate
: of the signing authority to the postgresql.crt file, then its parent
: authority's certificate, and so on up to a "root" authority that is
: trusted by the server. The root certificate should be included in every
: case where postgresql.crt contains more than one certificate.
In the JDBC case you'd need to put all those certs into the client's
keystore, which I'm afraid I don't know the details of doing. Possibly
somebody on pgsql-jdbc could help you with that.
regards, tom lane
Thank you for your reply. I agree that this configuration could be better and this is why I sent my post.
There is still one concern remaining. As I said I have working configuration with libpq and jdbc. For jdbc I created keystore, that is properly used with connection ssl=on parameter and clientcert=1 in og_hba.conf, everything works fine.
The issue is why for libpq it is enough to have only one lowest level root certificate matched besides the fact that certificate postgresql.crt that is presented to server contains actually 3 certs (2 from Intermediate authority with lowest level root).
For JDBC it was not enough, I had to put whole CA chain to be able to create succesfull connection. It seems lipqg does not support chained CA's.
I need to confirm that this is working as designed (I will be able to adjust my solution depending on the answer).
Thank you.
Kind regards,
Joanna
W dniu 2011-08-22 15:37:28 użytkownik Tom Lane <tgl@sss.pgh.pa.us> napisał:
Show quoted text
Asia <asia123321@op.pl> writes:
Now the issue is then when using libpq it was enough to have only root certificate in server's root.crt and it worked fine.
But when I tried using the same with JDBC it turned out that I need to put whole chain (2 certs) of Intermediate CA 1 in server's root.crt.This is poor configuration, because every certificate listed in root.crt
is considered fully trusted for every purpose. It's best to keep only
top-level root certs in root.crt. Instead, put the full chain of
certificates into the client's postgresql.crt, as per the manual:: In some cases, the client certificate might be signed by an
: "intermediate" certificate authority, rather than one that is directly
: trusted by the server. To use such a certificate, append the certificate
: of the signing authority to the postgresql.crt file, then its parent
: authority's certificate, and so on up to a "root" authority that is
: trusted by the server. The root certificate should be included in every
: case where postgresql.crt contains more than one certificate.In the JDBC case you'd need to put all those certs into the client's
keystore, which I'm afraid I don't know the details of doing. Possibly
somebody on pgsql-jdbc could help you with that.regards, tom lane
Import Notes
Resolved by subject fallback
Il giorno lun, 22/08/2011 alle 09.37 -0400, Tom Lane ha scritto:
Asia <asia123321@op.pl> writes:
Now the issue is then when using libpq it was enough to have only root certificate in server's root.crt and it worked fine.
But when I tried using the same with JDBC it turned out that I need to put whole chain (2 certs) of Intermediate CA 1 in server's root.crt.
[...]
In the JDBC case you'd need to put all those certs into the client's
keystore, which I'm afraid I don't know the details of doing. Possibly
somebody on pgsql-jdbc could help you with that.
you should import CA certificate in your JRE ca certstore with commands:
cd $JAVA_HOME/jre/lib/security
keytool -import -trustcacerts -alias $YOURCAALIAS \
-file $YOURCACERTFILE -keystore cacerts
I usually store in client and server certificates the whole chain from
primary CA.
Bye,
Giuseppe