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@xxxxxxxxx>
> To: s400t@xxxxxxxxxxx; "pgsql-general@xxxxxxxxxxxxxxxxxxxx" <pgsql-general@xxxxxxxxxxxxxxxxxxxx>
> 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@xxxxxxxxxxx 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
>
