Thread: 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@postgresql.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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Oleg Samoylov
Sent: Thursday, February 20, 2003 5:34 AM
To: pgsql-jdbc@postgresql.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@postgresql.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
>
Index: jdbc/build.xml
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/jdbc/build.xml,v
retrieving revision 1.31
diff -c -r1.31 build.xml
*** jdbc/build.xml    11 Dec 2002 12:27:47 -0000    1.31
--- jdbc/build.xml    27 Feb 2003 05:38:30 -0000
***************
*** 22,27 ****
--- 22,28 ----
    <property name="builddir" value="build" />
    <property name="package" value="org/postgresql" />
    <property name="debug" value="on" />
+   <property name="ssl" value="false" />

    <property file="build.properties"/>

***************
*** 47,52 ****
--- 48,54 ----
          <equals arg1="${ant.java.version}" arg2="1.4"/>
      </condition>
      <available property="datasource" classname="javax.sql.DataSource"/>
+     <available property="ssl" classname="javax.net.ssl.SSLSocketFactory"/>
      <available property="junit" classname="junit.framework.Test" />
      <condition property="jdbc2tests">
        <and>
***************
*** 160,171 ****
          <equals arg1="${jdbc3}" arg2="true"/>
      </condition>

      <!-- Some defaults -->
      <filter token="MAJORVERSION" value="${major}" />
      <filter token="MINORVERSION" value="${minor}" />
!     <filter token="VERSION" value="PostgreSQL ${fullversion} ${edition}" />
      <filter token="JDBCCONNECTCLASS" value="${connectclass}" />
      <filter token="DEF_PGPORT" value="${def_pgport}" />

       <fail unless="major" message="'major' undefined. Please follow the directions in README."/>
       <fail unless="minor" message="'minor' undefined. Please follow the directions in README."/>
--- 162,188 ----
          <equals arg1="${jdbc3}" arg2="true"/>
      </condition>

+     <!-- determine the ssl status -->
+     <condition property="ssl_config" value="">
+         <equals arg1="${ssl}" arg2="true"/>
+     </condition>
+     <condition property="ssl_config" value="//">
+         <equals arg1="${ssl}" arg2="false"/>
+     </condition>
+     <condition property="ssl_edition" value="SSL">
+         <equals arg1="${ssl}" arg2="true"/>
+     </condition>
+     <condition property="ssl_edition" value="NO SSL">
+         <equals arg1="${ssl}" arg2="false"/>
+     </condition>
+
      <!-- Some defaults -->
      <filter token="MAJORVERSION" value="${major}" />
      <filter token="MINORVERSION" value="${minor}" />
!     <filter token="VERSION" value="PostgreSQL ${fullversion} ${edition} with ${ssl_edition}" />
      <filter token="JDBCCONNECTCLASS" value="${connectclass}" />
      <filter token="DEF_PGPORT" value="${def_pgport}" />
+     <filter token="SSL" value="${ssl_config}" />

       <fail unless="major" message="'major' undefined. Please follow the directions in README."/>
       <fail unless="minor" message="'minor' undefined. Please follow the directions in README."/>
***************
*** 181,187 ****
            tofile="${package}/Driver.java"
            filtering="yes" />

!     <echo message="Configured build for the ${edition} edition driver" />
    </target>


--- 198,204 ----
            tofile="${package}/Driver.java"
            filtering="yes" />

!     <echo message="Configured build for the ${edition} edition driver with ${ssl_edition}" />
    </target>


Index: jdbc/org/postgresql/Driver.java.in
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/Driver.java.in,v
retrieving revision 1.24
diff -c -r1.24 Driver.java.in
*** jdbc/org/postgresql/Driver.java.in    9 Feb 2003 23:45:45 -0000    1.24
--- jdbc/org/postgresql/Driver.java.in    27 Feb 2003 05:38:31 -0000
***************
*** 1,5 ****
--- 1,6 ----
  package org.postgresql;

+ import java.io.*;
  import java.sql.*;
  import java.util.*;

***************
*** 66,80 ****
       *
       * user - (optional) The user to connect as
       * password - (optional) The password for the user
       * charSet - (optional) The character set to be used for converting
       *     to/from the database to unicode.  If multibyte is enabled on the
       *     server then the character set of the database is used as the default,
       *     otherwise the jvm character encoding is used as the default.
!             * loglevel - (optional) Enable logging of messages from the driver.
!             *        The value is an integer from 1 to 2 where:
!             *          INFO = 1, DEBUG = 2
!             *        The output is sent to DriverManager.getPrintWriter() if set,
!             *        otherwise it is sent to System.out.
       * compatible - (optional) This is used to toggle
       *     between different functionality as it changes across different releases
       *     of the jdbc driver code.  The values here are versions of the jdbc
--- 67,83 ----
       *
       * user - (optional) The user to connect as
       * password - (optional) The password for the user
+      * ssl - (optional) Use SSL when connecting to the server
       * charSet - (optional) The character set to be used for converting
       *     to/from the database to unicode.  If multibyte is enabled on the
       *     server then the character set of the database is used as the default,
       *     otherwise the jvm character encoding is used as the default.
!      *   This value is only used when connecting to a 7.2 or older server.
!      * loglevel - (optional) Enable logging of messages from the driver.
!      *        The value is an integer from 1 to 2 where:
!      *          INFO = 1, DEBUG = 2
!      *        The output is sent to DriverManager.getPrintWriter() if set,
!      *        otherwise it is sent to System.out.
       * compatible - (optional) This is used to toggle
       *     between different functionality as it changes across different releases
       *     of the jdbc driver code.  The values here are versions of the jdbc
***************
*** 136,143 ****
          }
          catch (Exception ex2)
          {
!             if (Driver.logDebug)
                  Driver.debug("error", ex2);
              throw new PSQLException("postgresql.unusual", ex2);
          }
      }
--- 139,147 ----
          }
          catch (Exception ex2)
          {
!             if (Driver.logDebug) {
                  Driver.debug("error", ex2);
+             }
              throw new PSQLException("postgresql.unusual", ex2);
          }
      }
***************
*** 211,217 ****
       */
      public static String getVersion()
      {
!         return "@VERSION@ jdbc driver build " + m_buildNumber;
      }

      /*
--- 215,221 ----
       */
      public static String getVersion()
      {
!         return "@VERSION@ (build " + m_buildNumber + ")";
      }

      /*
***************
*** 248,254 ****
          String key = "";
          String value = "";

!         StringTokenizer st = new StringTokenizer(url, ":/;=&?", true);
          for (int count = 0; (st.hasMoreTokens()); count++)
          {
              String token = st.nextToken();
--- 252,268 ----
          String key = "";
          String value = "";

!         String l_urlServer = url;
!         String l_urlArgs = "";
!
!         int l_qPos = url.indexOf('?');
!         if (l_qPos != -1) {
!             l_urlServer = url.substring(0,l_qPos);
!             l_urlArgs = url.substring(l_qPos+1);
!         }
!
!         //parse the server part of the url
!         StringTokenizer st = new StringTokenizer(l_urlServer, ":/", true);
          for (int count = 0; (st.hasMoreTokens()); count++)
          {
              String token = st.nextToken();
***************
*** 318,342 ****
                      urlProps.put("PGDBNAME", token);
                      state = -2;
                  }
!                 else if (state <= -2 && (count % 2) == 1)
!                 {
!                     // PM Aug 2 1997 - added tests for ? and &
!                     if (token.equals(";") || token.equals("?") || token.equals("&") )
!                         state = -3;
!                     else if (token.equals("="))
!                         state = -5;
!                 }
!                 else if (state <= -2 && (count % 2) == 0)
!                 {
!                     if (state == -3)
!                         key = token;
!                     else if (state == -5)
!                     {
!                         value = token;
!                         urlProps.put(key, value);
!                         state = -2;
!                     }
!                 }
              }
          }

--- 332,350 ----
                      urlProps.put("PGDBNAME", token);
                      state = -2;
                  }
!             }
!         }
!
!         //parse the args part of the url
!         StringTokenizer qst = new StringTokenizer(l_urlArgs, "&");
!         for (int count = 0; (qst.hasMoreTokens()); count++)
!         {
!             String token = qst.nextToken();
!             int l_pos = token.indexOf('=');
!             if (l_pos == -1) {
!                 urlProps.put(token, "");
!             } else {
!                 urlProps.put(token.substring(0,l_pos), token.substring(l_pos+1));
              }
          }

***************
*** 419,425 ****
      {
          if (logDebug)
          {
!             DriverManager.println(msg + ex != null ? ex.getMessage() : "null Exception");
          }
      }
      /*
--- 427,436 ----
      {
          if (logDebug)
          {
!             DriverManager.println(msg);
!             if(ex != null) {
!                 DriverManager.println(ex.toString());
!             }
          }
      }
      /*
***************
*** 441,449 ****
      {
          if (logInfo)
          {
!             DriverManager.println(msg + ex != null ? ex.getMessage() : "null Exception");
          }
      }

      //The build number should be incremented for every new build
      private static int m_buildNumber = 201;
--- 452,480 ----
      {
          if (logInfo)
          {
!             DriverManager.println(msg);
!             if(ex != null) {
!                 DriverManager.println(ex.toString());
!             }
          }
      }
+
+
+     public static void makeSSL(PG_Stream p_stream) throws IOException {
+ @SSL@        if (logDebug)
+ @SSL@            debug("converting regular socket connection to ssl");
+ @SSL@        javax.net.ssl.SSLSocketFactory factory = (javax.net.ssl.SSLSocketFactory)
javax.net.ssl.SSLSocketFactory.getDefault();
+ @SSL@        p_stream.connection = (javax.net.ssl.SSLSocket)
factory.createSocket(p_stream.connection,p_stream.host,p_stream.port,true);
+ @SSL@        p_stream.pg_input = new BufferedInputStream(p_stream.connection.getInputStream(), 8192);
+ @SSL@        p_stream.pg_output = new BufferedOutputStream(p_stream.connection.getOutputStream(), 8192);
+     }
+
+     public static boolean sslEnabled() {
+         boolean l_return = false;
+ @SSL@        l_return = true;
+         return l_return;
+     }
+

      //The build number should be incremented for every new build
      private static int m_buildNumber = 201;
Index: jdbc/org/postgresql/PG_Stream.java
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/PG_Stream.java,v
retrieving revision 1.17
diff -c -r1.17 PG_Stream.java
*** jdbc/org/postgresql/PG_Stream.java    20 Aug 2002 04:26:02 -0000    1.17
--- jdbc/org/postgresql/PG_Stream.java    27 Feb 2003 05:38:31 -0000
***************
*** 20,28 ****
  //    This class handles all the Streamed I/O for a org.postgresql connection
  public class PG_Stream
  {
!     private Socket connection;
!     private InputStream pg_input;
!     private BufferedOutputStream pg_output;
      private byte[] byte_buf = new byte[8*1024];

      /*
--- 20,30 ----
  //    This class handles all the Streamed I/O for a org.postgresql connection
  public class PG_Stream
  {
!     public String host;
!     public int port;
!     public Socket connection;
!     public InputStream pg_input;
!     public BufferedOutputStream pg_output;
      private byte[] byte_buf = new byte[8*1024];

      /*
***************
*** 33,40 ****
       * @param port the port number that the postmaster is sitting on
       * @exception IOException if an IOException occurs below it.
       */
!     public PG_Stream(String host, int port) throws IOException
      {
          connection = new Socket(host, port);

          // Submitted by Jason Venner <jason@idiom.com> adds a 10x speed
--- 35,44 ----
       * @param port the port number that the postmaster is sitting on
       * @exception IOException if an IOException occurs below it.
       */
!     public PG_Stream(String p_host, int p_port) throws IOException
      {
+         host = p_host;
+         port = p_port;
          connection = new Socket(host, port);

          // Submitted by Jason Venner <jason@idiom.com> adds a 10x speed
Index: jdbc/org/postgresql/errors.properties
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/errors.properties,v
retrieving revision 1.17
diff -c -r1.17 errors.properties
*** jdbc/org/postgresql/errors.properties    12 Feb 2003 06:13:04 -0000    1.17
--- jdbc/org/postgresql/errors.properties    27 Feb 2003 05:38:31 -0000
***************
*** 19,24 ****
--- 19,26 ----
  postgresql.con.pass:The password property is missing. It is mandatory.
  postgresql.con.refused:Connection refused. Check that the hostname and port are correct and that the postmaster is
acceptingTCP/IP connections. 
  postgresql.con.setup:Protocol error. Session setup failed.
+ postgresql.con.sslfail:An error occured while getting setting up the SSL connection.
+ postgresql.con.sslnotsupported:The server does not support SSL
  postgresql.con.strobj:The object could not be stored. Check that any tables required have already been created in the
database.
  postgresql.con.strobjex:Failed to store object - {0}
  postgresql.con.toolong:The SQL Statement is too long - {0}
Index: jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java,v
retrieving revision 1.15
diff -c -r1.15 AbstractJdbc1Connection.java
*** jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java    5 Feb 2003 11:12:39 -0000    1.15
--- jdbc/org/postgresql/jdbc1/AbstractJdbc1Connection.java    27 Feb 2003 05:38:32 -0000
***************
*** 34,39 ****
--- 34,40 ----
      protected String PG_DATABASE;
      protected boolean PG_STATUS;
      protected String compatible;
+     protected boolean useSSL;

      // The PID an cancellation key we get from the backend process
      protected int pid;
***************
*** 100,106 ****
       * @exception SQLException if a database access error occurs
       */
      public void openConnection(String host, int port, Properties info, String database, String url,
org.postgresql.Driverd) throws SQLException 
!     {
          firstWarning = null;

          // Throw an exception if the user or password properties are missing
--- 101,107 ----
       * @exception SQLException if a database access error occurs
       */
      public void openConnection(String host, int port, Properties info, String database, String url,
org.postgresql.Driverd) throws SQLException 
!       {
          firstWarning = null;

          // Throw an exception if the user or password properties are missing
***************
*** 121,126 ****
--- 122,136 ----
          PG_HOST = host;
          PG_STATUS = CONNECTION_BAD;

+         if (info.getProperty("ssl") != null && this_driver.sslEnabled())
+         {
+             useSSL = true;
+         }
+         else
+         {
+             useSSL = false;
+         }
+
          if (info.getProperty("compatible") == null)
          {
              compatible = d.getMajorVersion() + "." + d.getMinorVersion();
***************
*** 156,161 ****
--- 166,176 ----
          //Print out the driver version number
          if (org.postgresql.Driver.logInfo)
              org.postgresql.Driver.info(org.postgresql.Driver.getVersion());
+         if (org.postgresql.Driver.logDebug) {
+             org.postgresql.Driver.debug("    ssl = " + useSSL);
+             org.postgresql.Driver.debug("    compatible = " + compatible);
+             org.postgresql.Driver.debug("    loglevel = " + l_logLevel);
+         }

          // Now make the initial connection
          try
***************
*** 173,178 ****
--- 188,243 ----
          {
              throw new PSQLException ("postgresql.con.failed", e);
          }
+
+         // Now we need to construct and send an ssl startup packet
+         try
+         {
+             if (useSSL) {
+                 if (org.postgresql.Driver.logDebug)
+                     org.postgresql.Driver.debug("Asking server if it supports ssl");
+                 pg_stream.SendInteger(8,4);
+                 pg_stream.SendInteger(80877103,4);
+
+                 // now flush the ssl packets to the backend
+                 pg_stream.flush();
+
+                 // Now get the response from the backend, either an error message
+                 // or an authentication request
+                 int beresp = pg_stream.ReceiveChar();
+                 if (org.postgresql.Driver.logDebug)
+                     org.postgresql.Driver.debug("Server response was (S=Yes,N=No): "+(char)beresp);
+                 switch (beresp)
+                     {
+                     case 'E':
+                         // An error occured, so pass the error message to the
+                         // user.
+                         //
+                         // The most common one to be thrown here is:
+                         // "User authentication failed"
+                         //
+                         throw new PSQLException("postgresql.con.misc", pg_stream.ReceiveString(encoding));
+
+                     case 'N':
+                         // Server does not support ssl
+                         throw new PSQLException("postgresql.con.sslnotsupported");
+
+                     case 'S':
+                         // Server supports ssl
+                         if (org.postgresql.Driver.logDebug)
+                             org.postgresql.Driver.debug("server does support ssl");
+                         org.postgresql.Driver.makeSSL(pg_stream);
+                         break;
+
+                     default:
+                         throw new PSQLException("postgresql.con.sslfail");
+                     }
+             }
+         }
+         catch (IOException e)
+         {
+             throw new PSQLException("postgresql.con.failed", e);
+         }
+

          // Now we need to construct and send a startup packet
          try
Index: jdbc/org/postgresql/util/PSQLException.java
===================================================================
RCS file: /cvsroot/pgsql-server/src/interfaces/jdbc/org/postgresql/util/PSQLException.java,v
retrieving revision 1.7
diff -c -r1.7 PSQLException.java
*** jdbc/org/postgresql/util/PSQLException.java    19 Nov 2001 22:33:39 -0000    1.7
--- jdbc/org/postgresql/util/PSQLException.java    27 Feb 2003 05:38:33 -0000
***************
*** 27,33 ****
       */
      public PSQLException(String error, Object[] args)
      {
!         //super();
          translate(error, args);
      }

--- 27,33 ----
       */
      public PSQLException(String error, Object[] args)
      {
!         super();
          translate(error, args);
      }

You can also try the regular postgresql driver, and use stunnel or ssh port
forwarding to provide transport security.  stunnel is a handy tool for
securing services that do not have native SSL support.  The only catch is
you would not be able to tell where the original connection came from, since
the connections would effectively be "fronted" through another listener.

-- Bill

> -----Original Message-----
> From: pgsql-jdbc-owner@postgresql.org
> [mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Oleg Samoylov
> Sent: Thursday, February 20, 2003 5:34 AM
> To: pgsql-jdbc@postgresql.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!

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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org]On Behalf Of Oleg Samoylov
Sent: Thursday, February 20, 2003 5:34 AM
To: pgsql-jdbc@postgresql.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@postgresql.org so that your
message can get through to the mailing list cleanly