Re: SSL for JDBC

Hi

I try to connect to postgresql server by
SSL via JDBC.

But I got always error. The handshake always fail.

I create the SSL Private key 'server.key' and selfsigned certificate
'server.crt' by openssl.
and configure postgresql to accept ssl.

When I connect by psql like

#psql -h 192.168.1.20 TestDB -u
---Outpot---------
psql: Warning: The -u option is deprecated. Use -U.
User name: testuser
Password:testpass
Welcome to psql, the PostgreSQL interactive terminal.

Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit

SSL connection (cipher: DES-CBC3-SHA, bits: 168)
----------------------

So SSL connection is correctly setup in the postgresql server.

Then, I modified the postgresql Driver, PG_Stream.java and replcace the
Socket by SSLSocket as following.

++++++++++++++++++++++++++++
//connection = new Socket(host, port);
SSLSocketFactory factory = (SSLSocketFactory) SSLSocketFactory.getDefault();
connection = (SSLSocket) factory.createSocket(host,port);
connection.startHandshake();

~~
//private Socket connection
private SSLSocket connection;
+++++++++++++++++++++++++

Than I add the certificate into the root CA to be trusted.
keytool -import -storetype jks -keystore cacerts -file server.crt
cacerts is in java_home/jre/lib/security/cacerts.

But I still got the error.

-->Exception: The connection attempt failed because Exception:
javax.net.ssl.SSL
Exception: Unrecognized SSL handshake.
Stack Trace:
javax.net.ssl.SSLException: Unrecognized SSL handshake.
at com.sun.net.ssl.internal.ssl.InputRecord.read(DashoA6275)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA6275)
at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA6275)
at com.sun.net.ssl.internal.ssl.AppOutputStream.write(DashoA6275)
at java.io.OutputStream.write(OutputStream.java:58)
at
com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(DashoA6275)
at org.postgresql.PG_Stream.<init>(PG_Stream.java:32)
at
org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJ
dbc1Connection.java:160)
at org.postgresql.Driver.connect(Driver.java:122)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnection(DriverManager.java:171)
at test.connectdb(test.java:47)

I am wondering if anybody have successfuly connected from JDBC to PostgreSQL
by SSL.
Please help me!

Hopefully, SSL supported driver is coming soon!
Thank you.

Tak

Takeo,

There is more work necessary than what you have tried. Please look at
the documentation for how the postgres server negotiates a connection
with the client regarding ssl.
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=protocol-protocol.html#AEN54636

Basically, the connection is initiated with non-ssl and then converted
to ssl later. I was just looking at the java ssl API and I don't see a
way to do this in java. Does anyone more familiar with java ssl support
know how you can convert a regular socket connection to ssl after you
have created and used it?

thanks,
--Barry

Takeo Shibata wrote:
> Hi
>
> I try to connect to postgresql server by
> SSL via JDBC.
>
> But I got always error. The handshake always fail.
>
> I create the SSL Private key 'server.key' and selfsigned certificate
> 'server.crt' by openssl.
> and configure postgresql to accept ssl.
>
> When I connect by psql like
>
> #psql -h 192.168.1.20 TestDB -u
> ---Outpot---------
> psql: Warning: The -u option is deprecated. Use -U.
> User name: testuser
> Password:testpass
> Welcome to psql, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> SSL connection (cipher: DES-CBC3-SHA, bits: 168)
> ----------------------
>
> So SSL connection is correctly setup in the postgresql server.
>
> Then, I modified the postgresql Driver, PG_Stream.java and replcace the
> Socket by SSLSocket as following.
>
> ++++++++++++++++++++++++++++
> //connection = new Socket(host, port);
> SSLSocketFactory factory = (SSLSocketFactory) SSLSocketFactory.getDefault();
> connection = (SSLSocket) factory.createSocket(host,port);
> connection.startHandshake();
>
> ~~
> //private Socket connection
> private SSLSocket connection;
> +++++++++++++++++++++++++
>
> Than I add the certificate into the root CA to be trusted.
> keytool -import -storetype jks -keystore cacerts -file server.crt
> cacerts is in java_home/jre/lib/security/cacerts.
>
> But I still got the error.
>
> -->Exception: The connection attempt failed because Exception:
> javax.net.ssl.SSL
> Exception: Unrecognized SSL handshake.
> Stack Trace:
> javax.net.ssl.SSLException: Unrecognized SSL handshake.
> at com.sun.net.ssl.internal.ssl.InputRecord.read(DashoA6275)
> at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA6275)
> at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA6275)
> at com.sun.net.ssl.internal.ssl.AppOutputStream.write(DashoA6275)
> at java.io.OutputStream.write(OutputStream.java:58)
> at
> com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(DashoA6275)
> at org.postgresql.PG_Stream.<init>(PG_Stream.java:32)
> at
> org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJ
> dbc1Connection.java:160)
> at org.postgresql.Driver.connect(Driver.java:122)
> at java.sql.DriverManager.getConnection(DriverManager.java:512)
> at java.sql.DriverManager.getConnection(DriverManager.java:171)
> at test.connectdb(test.java:47)
>
>
> I am wondering if anybody have successfuly connected from JDBC to PostgreSQL
> by SSL.
> Please help me!
>
> Hopefully, SSL supported driver is coming soon!
> Thank you.
>
> Tak
>
>
>
>
> ---------------------------(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)
>

Tak,

i have modified a driver for this purpose. It works in a production system together with a v7.1.2 back end. I have tried to make a patch against the current jdbc cvs and back-end but i faced several problems as follows

the most significant objection is that the current back end uses TLSv1. Using TLSv1 the JSSE reference implementation(v1.0.3) from Sun Microsystems fails to carry out the complete handshakeing process. I rewrote several parts of the 7.3 back-end to use SSLv2/3 instead of TLSv1(openssl api) and a modified driver build from the current cvs (first week of jan. this year) was able to work correctly and connected to postgresql 7.3 too.

Read the $PGSQL_SRC/backend/libpq/README.SSL and $PGSQL_SRC/interfaces/libpq/fe-connect.c,fe-secure.c,fe-misc.c (v7.3) or $PGSQL_SRC/interfaces/libpq/fe-connect.c (v7.1.2) very carefully before any programming to study the method how a client asks the back end to use a secure channel. You have to implement the postgresql "handshake" process first in the driver then the SSL handshake. For details of message type formats of the postgresql see the documentation.

regards,
Istvan Nagy

----- Original Message -----
From: Takeo Shibata <shibata(at)areabe(dot)com>
To: <pgsql-jdbc(at)postgresql(dot)org>
Sent: Wednesday, February 19, 2003 12:23 AM
Subject: [JDBC] SSL for JDBC

> Hi
>
> I try to connect to postgresql server by
> SSL via JDBC.
>
> But I got always error. The handshake always fail.
>
> I create the SSL Private key 'server.key' and selfsigned certificate
> 'server.crt' by openssl.
> and configure postgresql to accept ssl.
>
> When I connect by psql like
>
> #psql -h 192.168.1.20 TestDB -u
> ---Outpot---------
> psql: Warning: The -u option is deprecated. Use -U.
> User name: testuser
> Password:testpass
> Welcome to psql, the PostgreSQL interactive terminal.
>
> Type: \copyright for distribution terms
> \h for help with SQL commands
> \? for help on internal slash commands
> \g or terminate with semicolon to execute query
> \q to quit
>
> SSL connection (cipher: DES-CBC3-SHA, bits: 168)
> ----------------------
>
> So SSL connection is correctly setup in the postgresql server.
>
> Then, I modified the postgresql Driver, PG_Stream.java and replcace the
> Socket by SSLSocket as following.
>
> ++++++++++++++++++++++++++++
> //connection = new Socket(host, port);
> SSLSocketFactory factory = (SSLSocketFactory) SSLSocketFactory.getDefault();
> connection = (SSLSocket) factory.createSocket(host,port);
> connection.startHandshake();
>
> ~~
> //private Socket connection
> private SSLSocket connection;
> +++++++++++++++++++++++++
>
> Than I add the certificate into the root CA to be trusted.
> keytool -import -storetype jks -keystore cacerts -file server.crt
> cacerts is in java_home/jre/lib/security/cacerts.
>
> But I still got the error.
>
> -->Exception: The connection attempt failed because Exception:
> javax.net.ssl.SSL
> Exception: Unrecognized SSL handshake.
> Stack Trace:
> javax.net.ssl.SSLException: Unrecognized SSL handshake.
> at com.sun.net.ssl.internal.ssl.InputRecord.read(DashoA6275)
> at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA6275)
> at com.sun.net.ssl.internal.ssl.SSLSocketImpl.a(DashoA6275)
> at com.sun.net.ssl.internal.ssl.AppOutputStream.write(DashoA6275)
> at java.io.OutputStream.write(OutputStream.java:58)
> at
> com.sun.net.ssl.internal.ssl.SSLSocketImpl.startHandshake(DashoA6275)
> at org.postgresql.PG_Stream.<init>(PG_Stream.java:32)
> at
> org.postgresql.jdbc1.AbstractJdbc1Connection.openConnection(AbstractJ
> dbc1Connection.java:160)
> at org.postgresql.Driver.connect(Driver.java:122)
> at java.sql.DriverManager.getConnection(DriverManager.java:512)
> at java.sql.DriverManager.getConnection(DriverManager.java:171)
> at test.connectdb(test.java:47)
>
>
> I am wondering if anybody have successfuly connected from JDBC to PostgreSQL
> by SSL.
> Please help me!
>
> Hopefully, SSL supported driver is coming soon!
> Thank you.
>
> Tak
>
>
>
>
> ---------------------------(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 Wed, Feb 19, 2003 at 09:40:52AM -0800, Barry Lind wrote:
> Takeo,
>
> There is more work necessary than what you have tried. Please look at
> the documentation for how the postgres server negotiates a connection
> with the client regarding ssl.
> http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=protocol-protocol.html#AEN54636
>
> Basically, the connection is initiated with non-ssl and then converted
> to ssl later. I was just looking at the java ssl API and I don't see a
> way to do this in java. Does anyone more familiar with java ssl support
> know how you can convert a regular socket connection to ssl after you
> have created and used it?

I tried making this work last year and got it up and running fairly
easy. Never quite got around to clean it up and send inn a patch though.

For those interested the code is available at the following address:

http://home.himolde.no/~tarjeis/jdbc-ssl.tar.gz

The interesting bits is in the org.postgesql.SSLConnection class.

--
Tarjei Skorgenes

On Wed, Feb 19, 2003 at 09:40:52AM -0800, Barry Lind wrote:

> Basically, the connection is initiated with non-ssl and then converted
> to ssl later. I was just looking at the java ssl API and I don't see a
> way to do this in java. Does anyone more familiar with java ssl support
> know how you can convert a regular socket connection to ssl after you
> have created and used it?

javax.net.ssl.SSLSocketFactory.createSocket() is what you're after; it
wraps existing Sockets with a SSLSocket.

http://java.sun.com/j2se/1.4.1/docs/api/javax/net/ssl/SSLSocketFactory.html

-O

Takeo Shibata wrote:
> I am wondering if anybody have successfuly connected from JDBC to PostgreSQL
> by SSL.
> Please help me!

Alternative PostgreSQL JDBC driver can connect via SSL.
https://sourceforge.net/projects/jxdbcon

--
Olleg Samoylov

Hi All
Thank you fro your helps!!.
I try those driver ,, but

I try out jdbc3 from the url
and try to execute the modified sample.

import java.sql.*;
public final class TConn {
static final String driverClass="org.sourceforge.jxdbcon.JXDBConDriver";
static final String
dbURL="jdbc:postgresql:net//192.168.10.10:5432/test";
public static void main(String[] argv) {
try {
Class driver=Class.forName(driverClass);

Connection
conn=DriverManager.getConnection(dbURL,"test","test");
PreparedStatement pstSel = conn.prepareStatement("select test from test");
ResultSet rs = pstSel.executeQuery();
rs.next();
System.out.println(rs.getString(1));
rs.close();
pstSel.close();
//...
} catch (Exception ex) {
ex.printStackTrace();
}
}
}

This does not work, when I enable ssl.
and use hostssl in the pg_hba.conf.

Does this driver works with the default postgresql?
How can I enabled the SSL?
Do I hvae to change any code?
org\sourceforge\jxdbcon\postgresql\NetConnection.java
Looks like recive proerty 'info' and check if is is ssl or not.
Do I have to pass the flag in property manually?

Here are the result by Java and psql command

>>>>>>>>>For pg_hba.conf with hostssl and host
# psql -h 192.168.10.10 test -u
psql: Warning: The -u option is deprecated. Use -U.
User name: test
Password: test
Welcome to psql, the PostgreSQL interactive terminal.
Type: \copyright for distribution terms
\h for help with SQL commands
\? for help on internal slash commands
\g or terminate with semicolon to execute query
\q to quit
SSL connection (cipher: DES-CBC3-SHA, bits: 168)
test=> select test from test;
ok!

>>>>>>>>For pg_hba.conf with host
#java TConn
ok!

>>>>>>>For pg_hba.conf with hostssl
#java TConn
ErrorMsg: FATAL 1: No pg_hba.conf entry for host 192.168.10.11, user test,
database test
org.sourceforge.jxutil.sql.I18nSQLException: nopghba[192.168.10.11, test,
test]
at org.sourceforge.jxdbcon.postgresql.PGErrors.throwError(Unknown
Source)
at org.sourceforge.jxdbcon.postgresql.PGErrors.throwError(Unknown
Source)
at org.sourceforge.jxdbcon.postgresql.NetProtocolV2.doLogin(Unknown
Source)
at org.sourceforge.jxdbcon.postgresql.NetProtocolV2.connect(Unknown
Source)
at org.sourceforge.jxdbcon.postgresql.NetConnection.connect(Unknown
Source)
at org.sourceforge.jxdbcon.postgresql.PGConnection.open(Unknown
Source)
at org.sourceforge.jxdbcon.JXDBConDriver.connect(Unknown Source)
at java.sql.DriverManager.getConnection(DriverManager.java:512)
at java.sql.DriverManager.getConnec

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Oleg Samoylov
Sent: Thursday, February 20, 2003 5:34 AM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] SSL for JDBC

Takeo Shibata wrote:
> I am wondering if anybody have successfuly connected from JDBC to
PostgreSQL
> by SSL.
> Please help me!

Alternative PostgreSQL JDBC driver can connect via SSL.
https://sourceforge.net/projects/jxdbcon

--
Olleg Samoylov

---------------------------(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

Tarjei,

I just committed a set of changes that adds SSL support in the driver.
This was committed to cvs head. I have attached the diff which
shouldn't be too difficult to backpatch to 7.3 sources.

thanks,
--Barry

PS. Later tonight I will post a new development build to the
jdbc.postgresql.org website (build 202) that includes these changes.

Tarjei Skorgenes wrote:
> On Wed, Feb 19, 2003 at 09:40:52AM -0800, Barry Lind wrote:
>
>>Takeo,
>>
>>There is more work necessary than what you have tried. Please look at
>>the documentation for how the postgres server negotiates a connection
>>with the client regarding ssl.
>>http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=protocol-protocol.html#AEN54636
>>
>>Basically, the connection is initiated with non-ssl and then converted
>>to ssl later. I was just looking at the java ssl API and I don't see a
>>way to do this in java. Does anyone more familiar with java ssl support
>>know how you can convert a regular socket connection to ssl after you
>>have created and used it?
>
>
> I tried making this work last year and got it up and running fairly
> easy. Never quite got around to clean it up and send inn a patch though.
>
> For those interested the code is available at the following address:
>
> http://home.himolde.no/~tarjeis/jdbc-ssl.tar.gz
>
> The interesting bits is in the org.postgesql.SSLConnection class.
>
> --
> Tarjei Skorgenes
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

Hi Oleg and all

Thank you for those helpful information.
With simply replacing the driver and make the selfsigned
certificate to be trusted by keytool, it works fine for ssl connection
to postgresql.

One thing I found is that
if postgresql does not accept SSL connection and
the URL used the ?USESSL=force as
described in API manual as following.

It does not throw the proper exception.
It just keep trying and waiting and finaly throw "OutOfMemoryError" in the
method main
where main has the connection to the database..

Is there any way to set the tiomeout for the
database connection with this driver?

Thank you

Takeo

+++++++++++
USESSL
public static final String USESSLTry to establish SSL secured connection.
When this property is set, the driver tries to establish an SSL secured
connection to the backend. For this to work the backend has to be compiled
with SSL, and the driver has to be compiled with JSSE. If the property's
value is set to "force", an Exception will be thrown if the SSL secured
connection cannot be established. Otherwise the driver continues on the
unsecured channel. NOTE: Configure Your backend pg_hba.conf appropriately
++++++++++++++

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org]On Behalf Of Oleg Samoylov
Sent: Thursday, February 20, 2003 5:34 AM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: [JDBC] SSL for JDBC

Takeo Shibata wrote:
> I am wondering if anybody have successfuly connected from JDBC to
PostgreSQL
> by SSL.
> Please help me!

Alternative PostgreSQL JDBC driver can connect via SSL.
https://sourceforge.net/projects/jxdbcon

--
Olleg Samoylov

---------------------------(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