• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Connection string using ssl client certificate

 
Greenhorn
Posts: 2
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.

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:




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

(in server machine)
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 /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=#

//---------------------------
 
Bob Johnsen
Greenhorn
Posts: 2
1
  • Likes 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have been rescued by this site:
https://postgresrocks.enterprisedb.com/t5/EDB-Guides/How-to-setup-SSL-authentication/ba-p/1647


#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:

   

Cheers!
 
Saloon Keeper
Posts: 27752
196
Android Eclipse IDE Tomcat Server Redhat Java Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Congratulations on your diligence. And for sharing your new-found knowledge, have a cow!
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic