Re: [JDBC] JDBC connection test with SSL on PG 9.2.1 server

> On Tuesday, January 29, 2013 10:12 PM danap wrote:
>> Hari Babu wrote:
>>> On Monday, January 28, 2013 10:20 PM, Dave Cramer wrote:
>>>
>>>   >>On Mon, Jan 28, 2013 at 9:03 AM, Hari Babu<haribabu.kommi@huawei.com
>>> <mailto:haribabu.kommi@huawei.com>>  wrote:
>>>
>>>   >>While testing PostgreSQL JDBC java client to connect to the PG 9.2.1
>>>   >>database server using SSL.
>>>   >>we got the following behavior.
>>>   >>
>>>   >>The test steps as below：
>>>   >>
>>>   >>url = "jdbc:postgresql://" + "10.145.98.227" + ':'
>>>   >>  + "8707" + '/'
>>>   >>  + "POSTGRES";
>>>   >>  Properties props = new Properties();
>>>   >>  props.setProperty("user", "CLIENT");
>>>   >>  props.setProperty("password", "1234@QWER");
>>>   >>  props.setProperty("ssl", "true");
>>>   >>
>>>   >>  System.setProperty("javax.net.ssl.trustStore", "193store");
>>>   >>  System.setProperty("javax.net.ssl.keyStore", "193client.jks");
>>>   >>  System.setProperty("javax.net.ssl.trustStorePassword", "qwerty");
>>>   >>  System.setProperty("javax.net.ssl.keyStorePassword", "qwerty");
>>>   >>
>>>   >>  /*Begin the first ssl connection*/
>>>   >>  conn1 = DriverManager.getConnection(url, props);
>>>   >>  System.out.println("Connection1 successful!");
>>>   >>
>>>   >>
>>>   >>  System.setProperty("javax.net.ssl.trustStore", "193store");
>>>   >>  System.setProperty("javax.net.ssl.keyStore", "193client.jks");
>>>   >>  System.setProperty("javax.net.ssl.trustStorePassword", "qwerty");
>>>   >>  System.setProperty("javax.net.ssl.keyStorePassword", "wrongpassword");
>>>   >>
>>>   >>  /*Begin the second ssl connection*/
>>>   >>  conn2 = DriverManager.getConnection(url, props);
>>>   >>  System.out.println("Connection2 successful!");
>>>   >>
>>>   >>Before first connection we set
>>>   >>"System.setProperty("javax.net.ssl.keyStorePassword", "qwerty");"
>>> qwerty is
>>>   >>the right password
>>>   >>and before second SSL connection we set
>>>   >>"System.setProperty("javax.net.ssl.keyStorePassword", "wrongpassword");"
>>>   >>wrongpassword is the wrong password.
>>>   >>
>>>   >>we expect the first SSL connection will be successful and second failed
>>>   >>because of wrong password, but actually we get two successful SSL
>>>   >>connections.
>>>   >>We found that if the first SSL connections password set right, all the
>>>   >>following SSL connections are fine ,even set wrong keystroke password.
>>>   >>
>>>   >>1. Is this a defect about JDBC?
>>>   >>2. Is it SSL behavior to authenticate only once?
>>>   >>3. Is it system property behavior can be set only once.
>>>   >>4. Is it because of any other problems?
>>>   >>
>>>   >>please give your suggestions?
>>>
>>>   >JDBC uses java's SSL infrastructure, as such I don't think it's a
>>> defect in JDBC. It could be because your truststore>does not require a
>>> password.
>>>
>>> I removed the trustStorePassword setting from the test, still the second
>>> connection is getting success with the wrong
>>>
>>> keyStorePassword.
>
>> Can you please set the property logLevel=1, INFO
>> and then reply back with the output. You may also
>> try logLevel=2, DEBUG for additional information.
>
> How to set logLevel=1 INFO and logLevel =2 DEBUG. Is it is JDBC logging or something else?
>
> We tried to get the SSL specific log by setting the system property for javax.net.debug as
> "ssl" (system.setProperty("javax.net.debug", "ssl"). With this we got connection logs for each of the connection
whichare attached in the mail. 
>
> For the first connection, it is opening the keys file and then does init for keyStore and trustStore. But incase of
secondconnection it just uses the previous cached session and does not open any of the file set in the property. So may
bethat is the reason even if wrong file or password is given before second connection, connection is successful. 
>
>  From the logs we feel that SSL caching may be causing the problem.
> Is there any exposed JSSE interface function to disable SSL session caching?
> If you can derive something from the attached logs, please let us know.
>
> How to set the SSL property "sslfactory" from application with some valid class?
> Our idea is that JDBC convert function execution goes to the else part of
> "if (classname == null)".
>
> The code snippet is attached:
>
>         String classname = info.getProperty("sslfactory");
>        if (classname == null)
>          {
>            //If sslmode is set, use the libp compatible factory
>            if (sslmode!=null)
>            {
>              factory = new LibPQFactory(info);
>            }
>            else
>            {
>              factory = (SSLSocketFactory)SSLSocketFactory.getDefault();
>            }
>          }
>          else
>          {
>              try
>              {
>                  factory = (SSLSocketFactory)instantiate(classname, info, true, info.getProperty("sslfactoryarg"));
>              }
>              catch (Exception e)
>              {
>                  throw new PSQLException(GT.tr("The SSLSocketFactory class provided {0} could not be instantiated.",
classname),PSQLState.CONNECTION_FAILURE, e); 
>              }
>          }
>
> Regards,
> Hari babu.

>Hello Hari,

>I thought at first setting props.setProperty("loglevel", "1") may derive
>additional information. It will not in this case. I already suspected and
>believe that the System property is not changing, cached as you indicated.

>Please try this first to see if System Properties can be uncached, changed
>between the two connections.

>danap.

>url = "jdbc:postgresql://" + host + "/" + database;
>Properties props = new Properties();
>props.setProperty("user", username);
>props.setProperty("password", password);
>props.setProperty("loglevel", "1");

>Properties systemProperties = System.getProperties();
>systemProperties.setProperty("javax.net.ssl.trustStore", "193store");
>systemProperties.setProperty("javax.net.ssl.keyStore", "193client.jks");
>systemProperties.setProperty("javax.net.ssl.trustStorePassword", "qwerty");
>systemProperties.setProperty("javax.net.ssl.keyStorePassword", "qwerty");

>System.setProperties(systemProperties);
>System.out.println(System.getProperty("javax.net.ssl.keyStorePassword"));

/*Begin the first ssl connection*/
>conn1 = DriverManager.getConnection(url, props);
>System.out.println("Connection1 successful!");

>System.setProperties(null);
>System.out.println(System.getProperty("javax.net.ssl.keyStorePassword"));

>systemProperties.setProperty("javax.net.ssl.keyStorePassword", "wrongqwerty");
>System.setProperties(systemProperties);
>System.out.println(System.getProperty("javax.net.ssl.keyStorePassword"));

>/*Begin the second ssl connection*/
>conn2 = DriverManager.getConnection(url, props);
>System.out.println("Connection2 successful!");

We tried the approach as suggested by you but still it is not working as shown in the below log (I had enabled logLevel
as1) 
keystore passowrd is qwerty
19:26:22.666 (1) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.451 (1) Receive Buffer Size is 43808
19:26:23.452 (1) Send Buffer Size is 25386
getConnection returning driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection1 successful! Conn1:org.postgresql.jdbc4.Jdbc4Connection@6baa9f99
null
wrongqwerty
DriverManager.getConnection("jdbc:postgresql://127.0.0.1:15432/postgres")
    trying driver[className=sun.jdbc.odbc.JdbcOdbcDriver,sun.jdbc.odbc.JdbcOdbcDriver@3597a37c]
*Driver.connect (jdbc:postgresql://127.0.0.1:15432/postgres)
    trying driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
19:26:23.835 (2) PostgreSQL 9.2 JDBC4 (build 1002)
19:26:23.847 (2) Receive Buffer Size is 43808
19:26:23.848 (2) Send Buffer Size is 25386
getConnection returning driver[className=org.postgresql.Driver,org.postgresql.Driver@3f7fa65e]
Connection2 successful! Conn2:org.postgresql.jdbc4.Jdbc4Connection@2e958bb8

Connect OK

There is function as SSL_CTX_SETSESSIONCACHEMODE(ctxt, mode) in C library of SSL.
Can you please let us  know if there is some similar function in JSSE also.

Regards,
Hari Babu.