Hello, On Thu, 2019-02-21 at 13:10 +0900, s400t@xxxxxxxxxxx wrote: > 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(SSLContextIm > pl.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. > Please read chapter 4 documentation:- https://jdbc.postgresql.org/documentation/head/ssl-client.html I think you are missing some steps. HTH, Robert
