Connection string for Java to connect to PostgreSQL, using client certificates

From: <s400t(at)yahoo(dot)co(dot)jp>
To: "pgsql-general(at)lists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org>
Subject: Connection string for Java to connect to PostgreSQL, using client certificates
Date: 2019-02-21 04:10:50
Message-ID: 1321198867.134127.1550722250138.JavaMail.yahoo@mail.yahoo.co.jp
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(SSLContextImpl.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.

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(at)postgreSERVER:/var/lib/CA/rootCA.crt ~/.postgresql/root.crt
scp root(at)postgreSERVER:/var/lib/CA/client/client.crt ~/.postgresql/postgresql.crt
scp root(at)postgreSERVER:/var/lib/CA/client.key ~/.postgresql/postgresql.key

chmod 600 ~/.postgresql/postgresql.key

(7)
user1(at)192(dot)168(dot)56(dot)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=# 

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Jiří Fejfar 2019-02-21 04:14:40 Re: Copy entire schema A to a different schema B
Previous Message Vincent Predoehl 2019-02-21 02:49:23 Re: Plpythonu extension