Thread: Connection string for Java to connect to PostgreSQL, using clientcertificates

Connection string for Java to connect to PostgreSQL, using clientcertificates

From

Date:

21 February 2019, 04:10:50

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
workusing 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
watchinga 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"
directoryseems 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(SSLContextImpl.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.

Thanks for reading a long post.




//Notes from the Youtube:


(1) become a root and setup CA


mkdir /var/lib/CA
cd CA
openssl genrsa -out rootCA.key 2048 (generate CA private key)


openssl req -x509 -new -key rootCA.key -days 10000 -out rootCA.crt (create root cert signed by the CA private key)


(2) Create server key and certificates

mkdir server
cd server
openssl genrsa -out server.key 2048

openssl req -new -key server.key -out server.csr 

openssl x509 -req -in server.csr -CA ../rootCA.crt -CAkey ../rootCA.key -CAcreateserial -out server.crt -days 5000


(3) Client identities

cd ..
mkdir client
cd client
openssl genrsa -out client.key 2048  (private key)

openssl req -new -key client.key -out client.csr  (certificate signing request-- CN MUST be db user name) 

#Create a certificate for database client
openssl x509 -req -in client.csr -CA ../rootCA.crt -CAkey ../rootCA.key -CAcreateserial -out client.crt -days 5000



(4)Copy CA root certificate, server key and certificate into postgresql cluster directory (

.. to /etc/postgresql/9.6... NOT /var/lib..

go to to /etc/postgresql/9.6/main
cp /var/lib/CA/rootCA.crt .
cp /var/lib/CA/server/server.crt .
cp /var/lib/CA/server/server.key .

chmod 600 server.key


(5) edit postgresql.conf, edit pg_hba.conf
postgresql.conf:

listen_addresses = "*"
ssl = true
remove comment out from ssl_ciphers = 'HIGH:MEDIUM..' 
give proper path to ssl_key_file, ssl_cert_file and ssl_ca_file

pg_hba.conf:
comment out: host all all  (some IP) md5 (or trust?)
add: hostssl testdb all 0.0.0.0/0 cert clientcert=1




(6)create .postgresql in client machine's user home directory

mkdir ~/.postgresql
scp root@postgreSERVER:/var/lib/CA/rootCA.crt ~/.postgresql/root.crt
scp root@postgreSERVER:/var/lib/CA/client/client.crt ~/.postgresql/postgresql.crt
scp root@postgreSERVER:/var/lib/CA/client.key ~/.postgresql/postgresql.key

chmod 600 ~/.postgresql/postgresql.key

(7)
user1@192.168.56.101:~$ psql -h 192.168.56.102 -U user1 testdb
psql (9.6.10)
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
Type "help" for help.

user1=# 

//---------------------------

Re: Connection string for Java to connect to PostgreSQL, usingclient certificates

From

rob stone

Date:

21 February 2019, 04:41:12

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

Re: Connection string for Java to connect to PostgreSQL, usingclient certificates

From

Date:

21 February 2019, 22:24:19

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
>