Re: Connection string for Java to connect to PostgreSQL, using client certificates

Hello again Rob,

Thank you for pointing that.

Now what I did:

1. Copied the server.crt created on the postgresqlSERVER's /var/lib/CA/server directory to client side.

2. Ran this script:|

openssl x509 -in server.crt -out server.crt.der -outform der

3. keytool -keystore $JAVA_HOME/jre/lib/security/cacerts -alias postgresql -import -file server.crt.der

--- some message---
Trust this certificate? [no]:  yes
Certificate was added to keystore

4. In my connection string, I added these lines, with hints from that site you mentioned.

props.setProperty("trustStore", "/usr/lib/jvm/java-8-openjdk-amd64/jre/lib/security/cacerts");
props.setProperty("trustStorePassword", "changeit"); (I entered that password when I ran script #3)

... and I still got

org.postgresql.util.PSQLException: Could not read SSL key file /home/user1/cert/postgresql.key.

for bonus I had one extra error..
Caused by: java.io.IOException: extra data given to DerValue constructor

After spending better part of the morning, and going through different errors, I came to this site:
https://postgresrocks.enterprisedb.com/t5/EDB-Guides/How-to-setup-SSL-authentication/ba-p/1647

This one (#5.5) helped:
5.5 convert the client key in DER format:

openssl pkcs8 -topk8 -outform DER -in postgresql.key -out postgresql.key.pk8 -nocrypt

Yes, instead of
props.setProperty("sslkey","/home/user1/cert/postgresql.key");

I used  
props.setProperty("sslkey","/home/user1/cert/postgresql.key.pk8"); 

and it worked!

My final connection string:

    String url = "jdbc:postgresql://192.168.56.102:5432/testdb";

    Properties props = new Properties();
    props.setProperty("user","user1");
    props.setProperty("ssl","true");
    props.setProperty("sslmode","verify-ca");
    props.setProperty("sslrootcert","/home/user1/cert/root.crt");
    props.setProperty("sslkey","/home/user1/cert/postgresql.key.pk8");
    props.setProperty("sslcert","/home/user1/cert/postgresql.crt");
        
    c = DriverManager.getConnection(url,props);

I did find many sites mentioning the need for making the server certificate available to Java, but I don't know why mine didn't work.
For now, immediate problem has been solved.

Cheers!

----- Original Message -----
> From: rob stone <floriparob(at)gmail(dot)com>
> To: s400t(at)yahoo(dot)co(dot)jp; "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
> Cc:
> Date: 2019/2/20, Wed 21:41
> Subject: Re: Connection string for Java to connect to PostgreSQL, using client certificates
>
> Hello,
>
> On Thu, 2019-02-21 at 13:10 +0900, s400t(at)yahoo(dot)co(dot)jp wrote:
>> 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:
>> psql (9.6.10)
>> SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-
>> SHA384, bits: 256, compression: off)
>> Type "help" for help.
>>
>> user1=#
>> //-------------------------------
>>
>> However, I have been unable to connect using a Java connection
>> string.
>>
>> This is more like ssl/certificate issue, and only tangentially
>> related to the postgreSQL, but since I want to make it work using
>> Java (running a test program from Eclipse), I am trying my luck here.
>>
>> I started by creating a CA, server side key and certificate, and
>> client side key and certificate. This I learnt by watching a Youtube
>> video (https://www.youtube.com/watch?v=FWK3lR6bSn8 ).
>>
>> 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:
>> postgresql.crt (1)
>> postgresql.key (2)
>> root.crt (3)
>>
>>
>> (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:
>>
>> Connection c = null;
>> Statement st = null;
>>
>>
>> try {
>>           Class.forName("org.postgresql.Driver");
>>          
>>           //credit: https://github.com/pgjdbc/pgjdbc/issues/1364
>>           String url =
> "jdbc:postgresql://192.168.56.102:5432/testdb";
>>
>>           Properties props = new Properties();
>>           props.setProperty("user","user1");
>>           props.setProperty("password","");
>>           props.setProperty("sslmode","verify-ca");
>>        
>>  
> props.setProperty("sslrootcert","/home/user1/cert/root.crt");
>>        
>>  
> props.setProperty("sslcert","/home/user1/cert/postgresql.crt");
>>        
>>  
> props.setProperty("sslkey","/home/user1/cert/postgresql.key");
>>           props.setProperty("loggerLevel","TRACE");
>>        
>>           c = DriverManager.getConnection(url,props);
>>           Statement st = c.createStatement();
>>          
>>           c.setAutoCommit(false);
>>           System.out.println("Opened database successfully");
>>
>>           ResultSet rs = stmt.executeQuery( "SELECT * FROM " +
>> someTableName );
>>           while ( rs.next() ) {
>>           ......
>> ......
>> }
>> ....
>> ....
>>
>>
>> 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.
>> at
>> org.postgresql.ssl.jdbc4.LazyKeyManager.getPrivateKey(LazyKeyManager.
>> java:250)
>> at
>> sun.security.ssl.AbstractKeyManagerWrapper.getPrivateKey(SSLContextIm
>> pl.java:1250)
>>
>> I googled, and someone suggested I convert the key file to a
> "der"
>> format.
>>
>> I tried this:
>> user1(at)192(dot)168(dot)56(dot)101:~/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
>> user1(at)192(dot)168(dot)56(dot)101:~/cert$
>>
>> I guess it expects PEM format.
>>
>> I am stuck. Please help.
>>
>
>
> Please read chapter 4 documentation:-
>
> https://jdbc.postgresql.org/documentation/head/ssl-client.html
>
> I think you are missing some steps.
>
> HTH,
>
> Robert
>