Thread: Re: [JDBC] can't access through SSL
I still can’t access my SSL enabled server!!!
Is root.crt supposed to be an exact copy of server.crt file which I use in my client’s keystore?
I have another observation. As I start the coordinator node, I don’t see any file access to the server.key or server.crt file? Aren’t these files supposed to be read at start up time or at least when I try to make a connection from my java application?
Everything I try create a datasource on tomcat I get the follow error on client and server’s console…
FATAL: connection requires a valid client certificate.
Am I missing something?
-maz
From: pgsql-jdbc-owner@postgresql.org [mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Maz Mohammadi
Sent: Friday, February 22, 2013 4:33 PM
To: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] can't access through SSL
Correction…
After double checking the path to java’s keystore file, and correcting it…this is the new error.
FATAL: connection requires a valid client certificate.
Any idea would be greatly appreciated.
-maz
From: Maz Mohammadi
Sent: Friday, February 22, 2013 3:51 PM
To: 'pgsql-jdbc@postgresql.org'
Subject: RE: [JDBC] can't access through SSL
Hello,
I regenerated some new keys for my postgres server. I’ve placed them under /var/lib…./coord and shared them with the datanodes as well.
After adding the certificates to the keystore for my tomcat java application, I get the following error on my server.
LOG: could not accept SSL connection: sslv3 alert certificate unkown.
I thought I had to use JDBC 3 for this.
Any ideas?
-maz
From: Maz Mohammadi
Sent: Friday, February 22, 2013 3:45 PM
To: pgsql-jdbc@postgresql.org
Subject: RE: [JDBC] can't access through SSL
Thx, one step closer.
From: Vitalii Tymchyshyn [mailto:tivv00@gmail.com]
Sent: Friday, February 22, 2013 12:56 PM
To: Maz Mohammadi
Cc: pgsql-jdbc@postgresql.org
Subject: Re: [JDBC] can't access through SSL
Try jdbc:postgresql://localhost:5432/testdb?ssl=true
2013/2/22 Maz Mohammadi <mmohammadi@pentaho.com>
Hello all,
I’m trying to access a postgres database through a java application (tomcat). This is the only entry I have in pg_hba.conf
# TYPE DATABASE USER ADDRESS METHOD
hostssl all all 127.0.0.1/32 cert
and put the certicate (from /var/lib/postre…../coord/server.crt) in the cacerts under $JAVA_HOME/…….
This is my jdbc URL….
jdbc:postgresql://localhost:5432/testdb&ssl=true
But When I try to create a datasource on tomcat, I get the following error…
“Connection attempt failed: FATAL: no pg_hba.conf entry for host "127.0.0.1", user "progres-xc", database "testdb&ssl=true", SSL off”
Any help is greatly appreciated.
-maz
--
Best regards,
Vitalii Tymchyshyn
On Feb 23, 2013, at 11:05 PM, Maz Mohammadi wrote: > =20 > FATAL: connection requires a valid client certificate.=20 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
On 02/23/2013 08:05 PM, Maz Mohammadi wrote: > I still can’t access my SSL enabled server!!! > > Is root.crt supposed to be an exact copy of server.crt file which I use > in my client’s keystore? > > I have another observation. As I start the coordinator node, I don’t > see any file access to the server.key or server.crt file? Aren’t these > files supposed to be read at start up time or at least when I try to > make a connection from my java application? > > Everything I try create a datasource on tomcat I get the follow error on > client and server’s console… > > FATAL: connection requires a valid client certificate. > > Am I missing something? It would seem that from this thread you are working with Postgres-XC not Postgres, is that correct? > > -maz > > -- Adrian Klaver adrian.klaver@gmail.com
Correct! I'm new postgresql and I need to figure this out for a client. I installed a bunch packages on my Ubuntu linux and hereI am. I've learned a lot. I have 2 datanodes, coordinator + gtm. -maz -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com] Sent: Sunday, February 24, 2013 4:37 PM To: Maz Mohammadi Cc: pgsql-jdbc@postgresql.org; pgsql-general@postgresql.org Subject: Re: [GENERAL] [JDBC] can't access through SSL On 02/23/2013 08:05 PM, Maz Mohammadi wrote: > I still can't access my SSL enabled server!!! > > Is root.crt supposed to be an exact copy of server.crt file which I > use in my client's keystore? > > I have another observation. As I start the coordinator node, I don't > see any file access to the server.key or server.crt file? Aren't > these files supposed to be read at start up time or at least when I > try to make a connection from my java application? > > Everything I try create a datasource on tomcat I get the follow error > on client and server's console... > > FATAL: connection requires a valid client certificate. > > Am I missing something? It would seem that from this thread you are working with Postgres-XC not Postgres, is that correct? > > -maz > > -- Adrian Klaver adrian.klaver@gmail.com
On 02/24/2013 02:35 PM, Maz Mohammadi wrote: > Correct! > > I'm new postgresql and I need to figure this out for a client. I installed a bunch packages on my Ubuntu linux and hereI am. I've learned a lot. I have 2 datanodes, coordinator + gtm. Some general pointers on helping to figure this out: 1) Postgres-XC != Postgres. It shares a code base but adds more moving parts. Along that line, you will need to be more specific about how you have setup Postgres-XC and exactly which part is failing? I for one do not use it, so I am not really sure what datanodes, coordinator and gmt signify. On a related note XC has its own mailing list(https://lists.sourceforge.net/lists/listinfo/postgres-xc-general), it may turn out there are people there that can answer the question sooner. 2) JDBC. It would seem from this thread and the other that covered this topic that JDBC is not really the issue. To make your life simpler I would test your setup using psql until you get it running properly, then pull in JDBC to see if it adds any problems. Also, it is generally considered not good protocol to cross post the same issue to different lists. 3) Simple with more detail is better. Create a minimum use case and then provide maximum detail of how it was set up and run. For instance: a) What are the versions of the software? b) Where is the client being run from? c) Where is the server? d) How are both setup? e) What is being done between the client and the server? f) What do you expect to happen? g) What is actually happening? i) The actual error message(s)? > > -maz > -- Adrian Klaver adrian.klaver@gmail.com
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 setu= p is correct. Another person from this distribution list help me a bit. T= his test shows me that the ssl is setup correctly on my server... ---------- postgres-xc@adminuser-VirtualBox:~/datanode2$ psql=20 psql (PGXC 1.0.0, based on PG 9.1.4) Type "help" for help. postgres=3D# \q postgres-xc@adminuser-VirtualBox:~/datanode2$ psql -h localhost psql: FATAL: connection requires a valid client certificate FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres-xc", data= base "postgres", SSL off postgres-xc@adminuser-VirtualBox:~/datanode2$ ------------ 2) My client is a tomcat server. I've placed JDBC3 drivers (jar file) in t= he WEB-INF lib directory of my webapp.=20 3) MY jdbc url is "jdbc:postgresql://localhost:5432:testdb?ssl=3Dtrue", and= believe me.... username and password are correct. 4) Both postgres and tomcat are running on the same machine (an Ubuntu linu= x virtual box). =20 5) when I try to create a JDBC datasource on my tomcat, I enter the JDBC ur= l + user + password, and I'm expecting it to be able to connect to it and a= t 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 certi= ficate" 6) I've also specified the following java options.. -Djavax.net.ssl.trustStore=3D/home/adminuser/pentaho/keycerts/mazstore -Dja= vax.net.ssl.trustStorePassword=3Dpassword I'll post this on the other distribution list. BTW, I don't see much in th= e log files under /var/log directory. -maz -----Original Message----- From: Adrian Klaver [mailto:adrian.klaver@gmail.com]=20 Sent: Sunday, February 24, 2013 8:08 PM To: Maz Mohammadi Cc: pgsql-jdbc@postgresql.org; pgsql-general@postgresql.org Subject: Re: [GENERAL] [JDBC] can't access through SSL On 02/24/2013 02:35 PM, Maz Mohammadi wrote: > Correct! > > I'm new postgresql and I need to figure this out for a client. I install= ed a bunch packages on my Ubuntu linux and here I am. I've learned a lot. = I have 2 datanodes, coordinator + gtm. Some general pointers on helping to figure this out: 1) Postgres-XC !=3D Postgres. It shares a code base but adds more moving pa= rts. Along that line, you will need to be more specific about how you have = setup Postgres-XC and exactly which part is failing? I for one do not use i= t, so I am not really sure what datanodes, coordinator and gmt signify. On = a related note XC has its own mailing list(https://lists.sourceforge.net/li= sts/listinfo/postgres-xc-general), it may turn out there are people there that can answer the question sooner. 2) JDBC. It would seem from this thread and the other that covered this top= ic that JDBC is not really the issue. To make your life simpler I would tes= t your setup using psql until you get it running properly, then pull in JDB= C to see if it adds any problems. Also, it is generally considered not good= protocol to cross post the same issue to different lists. 3) Simple with more detail is better. Create a minimum use case and then pr= ovide maximum detail of how it was set up and run. For instance: a) What are the versions of the software? b) Where is the client being run from? c) Where is the server? d) How are both setup? e) What is being done between the client and the server? f) What do you expect to happen? g) What is actually happening? i) The actual error message(s)? > > -maz > -- Adrian Klaver adrian.klaver@gmail.com
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
On Mon, Feb 25, 2013 at 10:07 AM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
What is the node type where error happens?
Have you setup the SSL certificates on all the nodes?
Or anything that would help resolving what you see.
Does the error happen when connecting directly to a Datanode?
-- 1) Postgres-XC != Postgres. It shares a code base but adds more moving parts. Along that line, you will need to be more specific about how you have setup Postgres-XC and exactly which part is failing? I for one do not use it, so I am not really sure what datanodes, coordinator and gmt signify. On a related note XC has its own mailing list(https://lists.sourceforge.net/lists/listinfo/postgres-xc-general), it may turn out there are people there that can answer the question sooner.
Adrian is right, pgsql-general is not the place where to discuss about bugs or problems of settings regarding Postgres-XC, so please send your requests to the ML indicated by Adrian so as to allow the developers there (including me, being an active member of the XC community) solving your problem.
2) JDBC. It would seem from this thread and the other that covered this topic that JDBC is not really the issue. To make your life simpler I would test your setup using psql until you get it running properly, then pull in JDBC to see if it adds any problems. Also, it is generally considered not good protocol to cross post the same issue to different lists.
I think honestly that the problem is not JDBC itself, but the way the nodes in an XC cluster interact...
3) Simple with more detail is better. Create a minimum use case and then provide maximum detail of how it was set up and run. For instance:Yes, answering those questions on the XC mailing list would be better when you report your problem there.
a) What are the versions of the software?
b) Where is the client being run from?
c) Where is the server?
d) How are both setup?
e) What is being done between the client and the server?
f) What do you expect to happen?
g) What is actually happening?
i) The actual error message(s)?
What is the node type where error happens?
Have you setup the SSL certificates on all the nodes?
Or anything that would help resolving what you see.
Does the error happen when connecting directly to a Datanode?
Michael