Re: [JDBC] can't access through SSL - Mailing list pgsql-general

On 02/24/2013 05:54 PM, Maz Mohammadi wrote:
> Hi Adrian,
>
> Thanks for sharing some pointers with me.  You are right, it's not actually an JDBC driver issue.  I posted it on
jdbc,because I'm accessing it from a jdbc client, I thought there might be some security issues with the JDBC driver. 
>
> 1) I'm running postgres-xc v. 9.1.  I'm "pretty" sure that my postgres setup is correct.  Another person from this
distributionlist help me a bit.  This test shows me that the ssl is setup correctly on my server... 

Which server? As I understand it Postgres-XC can have multiple clusters
in use, so I am still not sure which one you are connecting to?

>
> ----------
> postgres-xc@adminuser-VirtualBox:~/datanode2$ psql
> psql (PGXC 1.0.0, based on PG 9.1.4)
> Type "help" for help.
>
> postgres=# \q
> postgres-xc@adminuser-VirtualBox:~/datanode2$ psql -h localhost

To make things easier to debug, use explicit options. The above command
leaves a lot to env variables and hidden configuration. There is a good
chance you are not connecting the way you think you are.


> psql: FATAL:  connection requires a valid client certificate
> FATAL:  no pg_hba.conf entry for host "127.0.0.1", user "postgres-xc", database "postgres", SSL off
> postgres-xc@adminuser-VirtualBox:~/datanode2$

At this point do none of the Tomcat/JDBC process . Until you solve the
above, it just gets you to the same error and it confuses the issue.

The first thing to solve is why you are getting two different error
messages, in particular why it says SSL is off. The second is whether
the user and database specified in the error are who are trying to
connect as and the database you are trying to connect to.

Also have you gone through Table 17-3. SSL Server File Usage at link
below to see if everything is in place:
http://www.postgresql.org/docs/9.2/interactive/ssl-tcp.html

Have you followed Rays suggestion:

'I use openssl to verify the chain, I think that would help you know
what's going on:

openssl verify -CAfile rootca.crt  user.crt


> ------------
>
> 2) My client is a tomcat server.  I've placed JDBC3 drivers (jar file) in the WEB-INF lib directory of my webapp.
>
> 3) MY jdbc url is "jdbc:postgresql://localhost:5432:testdb?ssl=true", and believe me.... username and password are
correct.
>
> 4) Both postgres and tomcat are running on the same machine (an Ubuntu linux virtual box).
>
> 5) when I try to create a JDBC datasource on my tomcat, I enter the JDBC url + user + password, and I'm expecting it
tobe able to connect to it and at least get a "test successful" but I don't.  I get the error that I sent... 
> "Connection attempt failed: FATAL: connection requires a valid client certificate"
>
> 6) I've also specified the following java options..
> -Djavax.net.ssl.trustStore=/home/adminuser/pentaho/keycerts/mazstore -Djavax.net.ssl.trustStorePassword=password
>
> I'll post this on the other distribution list.  BTW, I don't see much in the log files under /var/log directory.
>
> -maz
>

--
Adrian Klaver
adrian.klaver@gmail.com