Bob Johnsen

+ Follow
since Feb 20, 2019
Cows and Likes
Total received
In last 30 days
Total given
Total received
Received in last 30 days
Total given
Given in last 30 days
Forums and Threads
Scavenger Hunt
expand Ranch Hand Scavenger Hunt
expand Greenhorn Scavenger Hunt

Recent posts by Bob Johnsen

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:


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.