Re: Connection string for Java to connect to PostgreSQL, usingclient certificates - Mailing list pgsql-general

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
minedidn't work. 
For now, immediate problem has been solved.

Cheers!









----- Original Message -----
> From: rob stone <floriparob@gmail.com>
> To: s400t@yahoo.co.jp; "pgsql-general@lists.postgresql.org" <pgsql-general@lists.postgresql.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@yahoo.co.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@192.168.56.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@192.168.56.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
>