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@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=# //---------------------------
