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