This week's book giveaway is in the Cloud/Virtualization forum.
We're giving away four copies of Grokking Bitcoin and have Kalle Rosenbaum on-line!
See this thread for details.
Win a copy of Grokking Bitcoin this week in the Cloud/Virtualization forum!
  • 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 all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Tim Cooke
  • Junilu Lacar
  • Paul Clapham
  • Devaka Cooray
  • Knute Snortum
Saloon Keepers:
  • Ron McLeod
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Frits Walraven
  • Carey Brown
  • salvin francis
  • Claude Moore

Connection string using ssl client certificate  RSS feed

Posts: 2
  • Mark post as helpful
  • send pies
  • 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( (psql -h -U user1 -d testdb) [ 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.


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 (

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, and copied as postgresql.crt to the client side
(2)originally created as "client.key" in, and copied as postgresql.key
(3)originally created as "rootCA.crt" in, 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(

I googled, and someone suggested I convert the key file to a "der" format.

I tried this:
user1@$ 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

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

comment out: host all all  (some IP) md5 (or trust?)
add: hostssl testdb all 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

user1@$ psql -h -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.


Bob Johnsen
Posts: 2
  • Likes 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have been rescued by this site:

#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

I used  

and it worked!

My final connection string:


Saloon Keeper
Posts: 20643
Android Eclipse IDE Java Linux Redhat Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Congratulations on your diligence. And for sharing your new-found knowledge, have a cow!
I just had the craziest dream. This tiny ad was in it.
Create Edit Print & Convert PDF Using Free API with Java
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!