problema con el backend

Hi, I have seen the same problem in the past of this list but I don't know how (and if) it has been solved.
I'm trying to connect my java stand alone application with postgres 7.4.2 via SSL.

I followed the istructions
...
http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php
...
and then I tryed an SSL connection with pgAdmin: all works correctly
After I tryed to connect via java and the following error appear on the screen:

PostgreSQL 7.4.3 JDBC3 with SSL (build 214)
ssl = true
compatible = 7.4
loglevel = 2
Using Protocol Version3
Asking server if it supports ssl
Server response was (S=Yes,N=No): S
server does support ssl
converting regular socket connection to ssl
org.postgresql.util.PSQLException
at org.postgresql.core.PGStream.flush(PGStream.java:415)
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(Abstrac
tJdbc1Connection.java:284)
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJ
dbc1Connection.java:213)
at org.postgresql.Driver.connect(Driver.java:139)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at SmitConnection.<init>(SmitConnection.java:42)
at Config.<init>(Config.java:120)
at ServerTask$RemindTask.run(ServerTask.java:146)
at java.util.TimerThread.mainLoop(Timer.java:432)
at java.util.TimerThread.run(Timer.java:382)
Exception: org.postgresql.util.PSQLException: Si Þ verificato un errore di I/O m
entre si svuotava il buffer duscita - {0}
getConnection failed: org.postgresql.util.PSQLException: Si Þ verificato un erro
re di I/O mentre si svuotava il buffer duscita - {0}
Syncroro: errore nella fase di connessione al database di sistema.
org.postgresql.util.PSQLException: Si Þ verificato un errore di I/O mentre si sv
uotava il buffer duscita - {0}
at org.postgresql.core.PGStream.flush(PGStream.java:415)
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(Abstrac
tJdbc1Connection.java:284)
at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJ
dbc1Connection.java:213)
at org.postgresql.Driver.connect(Driver.java:139)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:140)
at SmitConnection.<init>(SmitConnection.java:42)
at Config.<init>(Config.java:120)
at ServerTask$RemindTask.run(ServerTask.java:146)
at java.util.TimerThread.mainLoop(Timer.java:432)
at java.util.TimerThread.run(Timer.java:382)
java.lang.NullPointerException
at Config.<init>(Config.java:122)
at ServerTask$RemindTask.run(ServerTask.java:146)
at java.util.TimerThread.mainLoop(Timer.java:432)
at java.util.TimerThread.run(Timer.java:382)

I'have no changed my java code. I have only changed the jdbc url by adding ?ssl&loglevel=2 at the end of the url.

Thanks in advance.

RedS

On Wed, 14 Jul 2004, Stefano Bonnin wrote:

> Hi, I have seen the same problem in the past of this list but I don't
> know how (and if) it has been solved. I'm trying to connect my java
> stand alone application with postgres 7.4.2 via SSL.
>
> I followed the istructions ...
> http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php ... and
> then I tryed an SSL connection with pgAdmin: all works correctly After I
> tryed to connect via java and the following error appear on the screen:
>
> converting regular socket connection to ssl
> org.postgresql.util.PSQLException
> at org.postgresql.core.PGStream.flush(PGStream.java:415)
> at org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(Abstrac
> tJdbc1Connection.java:284)

I can't say I've seen this error before. Do you have any other
information? The server log might say something about what happened on
that end.

Kris Jurka

The postgresql server log gives me the following error:

****
could not initialize SSL connection: sslv3 alert certificate unknown
****

What's wrong in my operations?
About the certificate I did the following operations:

cd \ postgres_data_dir
openssl req -new -text -out server.req
openssl rsa -in privkey.pem -out server.key
rm privkey.pem
openssl req -x509 -in server.req -text -key server.key -out server.crt
chmod og-rwx server.key
openssl x509 -in server.crt -out server.crt.der -outform der

keytool -keystore /usr/local/j2sdk1.4.2_04/jre/lib/security/cacerts -alias
postgres -import -file server.crt.der

... then I typed changeit as password

What I did't undestand in this steps is the following:

keytool -keystore ... etc ...

import the certificate in the java keystore and the JDBC driver *must* find
the certificate in the keystore and download it on the client, is't true? (I
dont't know if this is true) BUT if my affermation is true HOW the JDBC
driver (on the client) can find it in
/usr/local/j2sdk1.4.2_04/jre/lib/security?

Reds.

----- Original Message -----
From: "Kris Jurka" <books(at)ejurka(dot)com>
To: "Stefano Bonnin" <stefano(dot)bonnin(at)comai(dot)to>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Thursday, July 15, 2004 9:44 AM
Subject: Re: [JDBC] SSL Problem

>
>
> On Wed, 14 Jul 2004, Stefano Bonnin wrote:
>
> > Hi, I have seen the same problem in the past of this list but I don't
> > know how (and if) it has been solved. I'm trying to connect my java
> > stand alone application with postgres 7.4.2 via SSL.
> >
> > I followed the istructions ...
> > http://archives.postgresql.org/pgsql-jdbc/2003-08/msg00110.php ... and
> > then I tryed an SSL connection with pgAdmin: all works correctly After I
> > tryed to connect via java and the following error appear on the screen:
> >
> > converting regular socket connection to ssl
> > org.postgresql.util.PSQLException
> > at org.postgresql.core.PGStream.flush(PGStream.java:415)
> > at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnectionV3(Abstrac
> > tJdbc1Connection.java:284)
>
> I can't say I've seen this error before. Do you have any other
> information? The server log might say something about what happened on
> that end.
>
> Kris Jurka
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)
>

On Thu, 15 Jul 2004, Stefano Bonnin wrote:
> keytool -keystore /usr/local/j2sdk1.4.2_04/jre/lib/security/cacerts -alias
> postgres -import -file server.crt.der
>
> What I did't undestand in this steps is the following:
>
> keytool -keystore ... etc ...
>
> import the certificate in the java keystore and the JDBC driver *must* find
> the certificate in the keystore and download it on the client, is't true? (I
> dont't know if this is true) BUT if my affermation is true HOW the JDBC
> driver (on the client) can find it in
> /usr/local/j2sdk1.4.2_04/jre/lib/security?

The certificate must be available to the client. There is no "find and
download" going on. These instructions were likely written for the client
on the same machine as the server so it was not emphasized that the cert
needs to be available to the client JVM.

Kris Jurka

ok,thanks
but now, do you have any idea?

in the previuos e-mail I sent you only the server error now I send you the
server log messages at postgres startup time:

2004-07-15 14:03:40 LOG: could not load root certificate file
"/usr/local/pgsql-7.4.2/bin/../../pgsql-7.4.1/data/root.crt": No such file
or directory
DETAIL: Will not verify client certificates.
2004-07-15 14:03:40 LOG: could not create IPv6 socket: Famiglia
dell'indirizzo non gestita dal protocollo
2004-07-15 14:03:40 LOG: database system was shut down at 2004-07-15
14:03:40 CEST
2004-07-15 14:03:40 LOG: checkpoint record is at 11/F6DC6DB4
2004-07-15 14:03:40 LOG: redo record is at 11/F6DC6DB4; undo record is at
0/0; shutdown TRUE
2004-07-15 14:03:40 LOG: next transaction ID: 27829164; next OID: 45696008
2004-07-15 14:03:40 LOG: database system is ready

It doesn't find any root.crt, this is right, I think.

Thanks in advance.

RedS

----- Original Message -----
From: "Kris Jurka" <books(at)ejurka(dot)com>
To: "Stefano Bonnin" <stefano(dot)bonnin(at)comai(dot)to>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Thursday, July 15, 2004 3:40 PM
Subject: Re: [JDBC] SSL Problem

>
>
> On Thu, 15 Jul 2004, Stefano Bonnin wrote:
> > keytool -keystore
/usr/local/j2sdk1.4.2_04/jre/lib/security/cacerts -alias
> > postgres -import -file server.crt.der
> >
> > What I did't undestand in this steps is the following:
> >
> > keytool -keystore ... etc ...
> >
> > import the certificate in the java keystore and the JDBC driver *must*
find
> > the certificate in the keystore and download it on the client, is't
true? (I
> > dont't know if this is true) BUT if my affermation is true HOW the JDBC
> > driver (on the client) can find it in
> > /usr/local/j2sdk1.4.2_04/jre/lib/security?
>
> The certificate must be available to the client. There is no "find and
> download" going on. These instructions were likely written for the client
> on the same machine as the server so it was not emphasized that the cert
> needs to be available to the client JVM.
>
> Kris Jurka
>

On Thu, 15 Jul 2004, Stefano Bonnin wrote:

> 2004-07-15 14:03:40 LOG: could not load root certificate file
> "/usr/local/pgsql-7.4.2/bin/../../pgsql-7.4.1/data/root.crt": No such file
> or directory
> DETAIL: Will not verify client certificates.

This is fine. You do not need a root.crt file. This is used to
authenticate clients to the server which is optional and not necessary to
establish a SSL connection.

Again the problem seems to be that you have not made the server cert
available to the connecting jvm. Adding -Djavax.net.debug=ssl to your
java command will produce a lot of debug information, but will likely
confirm this. The key line will be in the first part of the output where
it displays which trustStore you are using. The server cert must be in
this file.

Kris Jurka

cordial saludo Investigadores de postgreSQL

me esta ocurriendo el siguiente error

y no se que serà porque me habla de error de entrada y salida

el aplicativo si abre otras tablas y trabaja en ella denro de la misma
base de datos

pero al consultar en una tablita reporta en el sigueite error:

Error ::> portal.bdatos ::> clase UsuarioDAO ::> function validar(String
usuario, String password) ::> SQLException ::> An I/O error occured while
reading from backend - Exception: java.net.SocketException: Connection
timed out
Stack Trace:

Att,

Tania Gutierrez

Problem solved.

I copied the certificate that I created on the server to the client and then
I execute "keytool" on the client.
So, every time that I install my application on a new PC I have to execute
keytool operation on that machine.

Thaks for the help.
RedS
----- Original Message -----
From: "Kris Jurka" <books(at)ejurka(dot)com>
To: "Stefano Bonnin" <stefano(dot)bonnin(at)comai(dot)to>
Cc: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Thursday, July 15, 2004 8:18 PM
Subject: Re: [JDBC] SSL Problem

>
>
> On Thu, 15 Jul 2004, Stefano Bonnin wrote:
>
> > 2004-07-15 14:03:40 LOG: could not load root certificate file
> > "/usr/local/pgsql-7.4.2/bin/../../pgsql-7.4.1/data/root.crt": No such
file
> > or directory
> > DETAIL: Will not verify client certificates.
>
> This is fine. You do not need a root.crt file. This is used to
> authenticate clients to the server which is optional and not necessary to
> establish a SSL connection.
>
> Again the problem seems to be that you have not made the server cert
> available to the connecting jvm. Adding -Djavax.net.debug=ssl to your
> java command will produce a lot of debug information, but will likely
> confirm this. The key line will be in the first part of the output where
> it displays which trustStore you are using. The server cert must be in
> this file.
>
> Kris Jurka
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>

Dear Stefano and Kris,

I've been using JWS to deploy an application that uses postgresql.
I've configured pg to use MD5 for a minimum of security (user and
passwd) - how can I deploy an app that uses SSL WITHOUT having to run
keytool on each machine?
Can I "show" the certificate (self signed) and ask the user if he/she
would like to accept it as valied? Is thera a HOWTO anywhere or some
sample code showing how to do that?
I also have the same problem using LDAP (and OpenLDAP)...

Thanks in advance,
José Carlos Stevenson.

Stefano Bonnin wrote:
> Problem solved.
>
> I copied the certificate that I created on the server to the client and then
> I execute "keytool" on the client.
> So, every time that I install my application on a new PC I have to execute
> keytool operation on that machine.
>
> Thaks for the help.
> RedS
> ----- Original Message -----
> From: "Kris Jurka" <books(at)ejurka(dot)com>
> To: "Stefano Bonnin" <stefano(dot)bonnin(at)comai(dot)to>
> Cc: <pgsql-jdbc(at)postgresql(dot)org>
> Sent: Thursday, July 15, 2004 8:18 PM
> Subject: Re: [JDBC] SSL Problem
>
>
>
>>
>>On Thu, 15 Jul 2004, Stefano Bonnin wrote:
>>
>>
>>>2004-07-15 14:03:40 LOG: could not load root certificate file
>>>"/usr/local/pgsql-7.4.2/bin/../../pgsql-7.4.1/data/root.crt": No such
>
> file
>
>>>or directory
>>>DETAIL: Will not verify client certificates.
>>
>>This is fine. You do not need a root.crt file. This is used to
>>authenticate clients to the server which is optional and not necessary to
>>establish a SSL connection.
>>
>>Again the problem seems to be that you have not made the server cert
>>available to the connecting jvm. Adding -Djavax.net.debug=ssl to your
>>java command will produce a lot of debug information, but will likely
>>confirm this. The key line will be in the first part of the output where
>>it displays which trustStore you are using. The server cert must be in
>>this file.
>>
>>Kris Jurka
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 5: Have you checked our extensive FAQ?
>>
>> http://www.postgresql.org/docs/faqs/FAQ.html
>>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly
>

On Fri, 16 Jul 2004, [ISO-8859-1] Jos Carlos Stevenson wrote:

> I've been using JWS to deploy an application that uses postgresql.
> I've configured pg to use MD5 for a minimum of security (user and
> passwd) - how can I deploy an app that uses SSL WITHOUT having to run
> keytool on each machine?
> Can I "show" the certificate (self signed) and ask the user if he/she
> would like to accept it as valied? Is thera a HOWTO anywhere or some
> sample code showing how to do that?

One answer is to use a server key/cert that has been signed by a
certificate authority thats already distributed with the JVM, but that's
going to cost you money.

A number of people have asked to not require a trusted cert to get around
both this problem and something like an applet which has no control. The
decrease in security has made me hesitant to do this. A while back Chris
Smith proposed a patch to allow the user to supply their own
SSLSocketFactory.

http://archives.postgresql.org/pgsql-jdbc/2004-02/msg00218.php

I didn't like this at the time, but perhaps we should revisit it.

Kris Jurka