I am having hard time to connect to PostgreSQL server using client certificate from within a Java program.
Any insight would be helpful.
I can connect to the server using psql command line from a client machine(192.168.56.101) (psql -h 192.168.56.102 -U user1 -d testdb) [192.168.56.102 is "postgreSERVER" machine)
successful outcome looks like this:
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.
However, I have been unable to connect using a Java connection string.
For my own memo, I am reproducing the steps to create certificates and keys below, copied directly from that Youtube:
After creating those files, I copied the server side files to /etc/postgresql/9.6/main/) (I am using Debian, and "data" directory seems to be "/etc/postgresql/9.6/main/").
and the client side files to /home/user1/.postgresql folder. (had to created ".postgresql" folder)
The files were chmodded to 600.
And when I used psql from a client machine (Debian), I can connect happily as I mentioned above.
Now for the Java test:
I copied the "client side" files to /home/user1/cert/ (created "cert" folder)
The files are:
(1)originally created as "client.crt" in 192.168.56.102:/var/lib/CA/client/, and copied as postgresql.crt to the client side
(2)originally created as "client.key" in 192.168.56.102:/var/lib/CA/client/, and copied as postgresql.key
(3)originally created as "rootCA.crt" in 192.168.56.102:/var/lib/CA/, and copied as "root.crt"
My connection string is:
When I run the code (in Eclipse, in client machine/Debian), I get this error:
org.postgresql.util.PSQLException: Could not read SSL key file /home/user1/cert/postgresql.key.
I googled, and someone suggested I convert the key file to a "der" format.
I tried this:
firstname.lastname@example.org:~/cert$ openssl x509 -outform der -in postgresql.key -out postgresql.der
but then it says,
unable to load certificate
140663292355968:error:0906D06C:PEM routines:PEM_read_bio:no start line:../crypto/pem/pem_lib.c:686:Expecting: TRUSTED CERTIFICATE
I guess it expects PEM format.
I am stuck. Please help.
Thanks for reading a long post.
//Notes from the Youtube:
(1) become a root and setup CA
(in server machine)
openssl genrsa -out rootCA.key 2048 (generate CA private key)
openssl req -x509 -new -key rootCA.key -days 10000 -out rootCA.crt (create root cert signed by the CA private key)
(2) Create server key and certificates
openssl genrsa -out server.key 2048