Re: Connection string for Java to connect to PostgreSQL, usingclient 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