How to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

How to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL

mr16
I have got a Debian Jessie box with a PostgreSQL (9.4) database installed and configured to get SSL connection.
On Windows 10 box I'm writing a Java (1.8.0_111) application that connects to the database through SSL using PostgreSQL-JDBC driver (9.4.1211).
To let the application get a database certificate I did the following:
   on the Debian Jessy box:
      cp /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
      openssl x509 -in server.crt -out server.crt.der -outform der
   on the Windows 10 box:
      cd C:\Program Files (x86)\Java\jre1.8.0_111\lib\security
      keytool -keystore cacerts -alias postgresql -import -file server.crt.der
My application successfully connects to the database through SSL.

Than (to get more opportunities for debugging the application) I try to get connection to the database from Squirrel SQL (3.7.1) with the following configuration:
Squirrel SQL connection configuration:
   Alias:        hospice32_fr
   Driver:      PostgreSQL
   URL:         jdbc:postgresql://192.168.1.10/hospice_fr
   User:         hospice
   Password: ****************
Squirrel SQL connection driver properties:
   ssl:    <checked>    true
   sslcert: <checked> C:\Program Files\Java\jre1.8.0_111\lib\security\cacerts
   all the rest options are unchecked

Connection failed with such an error message:
   java.util.concurrent.ExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed:
   sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
   [...]

I also tried to replace the sslcert property with <SOME_DIR>/server.crt.der and <SOME_DIR>/server.crt, to check PGDBNAME, PGHOST, PGPORT,user properties.
Result was the same: No connection.

Could anybody please help me to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL.

Thank you, Mikhail.



------------------------------------------------------------------------------
The Command Line: Reinvented for Modern Developers
Did the resurgence of CLI tooling catch you by surprise?
Reconnect with the command line and become more productive.
Learn the new .NET and ASP.NET CLI. Get your free copy!
http://sdm.link/telerik
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL

Gerd Wagner-3
Could you please send a code example, what a plain JDBC connect to your
database looks like? It would be nice if you could do it outside your
application's project just to make sure the example doesn't profit from
any configurations you did for your application.

Thanks Gerd

Am 29.10.2016 um 09:13 schrieb [hidden email]:

> I have got a Debian Jessie box with a PostgreSQL (9.4) database installed and configured to get SSL connection.
> On Windows 10 box I'm writing a Java (1.8.0_111) application that connects to the database through SSL using PostgreSQL-JDBC driver (9.4.1211).
> To let the application get a database certificate I did the following:
>    on the Debian Jessy box:
>       cp /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
>       openssl x509 -in server.crt -out server.crt.der -outform der
>    on the Windows 10 box:
>       cd C:\Program Files (x86)\Java\jre1.8.0_111\lib\security
>       keytool -keystore cacerts -alias postgresql -import -file server.crt.der
> My application successfully connects to the database through SSL.
>
> Than (to get more opportunities for debugging the application) I try to get connection to the database from Squirrel SQL (3.7.1) with the following configuration:
> Squirrel SQL connection configuration:
>    Alias:        hospice32_fr
>    Driver:      PostgreSQL
>    URL:         jdbc:postgresql://192.168.1.10/hospice_fr
>    User:         hospice
>    Password: ****************
> Squirrel SQL connection driver properties:
>    ssl:    <checked>    true
>    sslcert: <checked> C:\Program Files\Java\jre1.8.0_111\lib\security\cacerts
>    all the rest options are unchecked
>
> Connection failed with such an error message:
>    java.util.concurrent.ExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed:
>    sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
>    [...]
>
> I also tried to replace the sslcert property with <SOME_DIR>/server.crt.der and <SOME_DIR>/server.crt, to check PGDBNAME, PGHOST, PGPORT,user properties.
> Result was the same: No connection.
>
> Could anybody please help me to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL.
>
> Thank you, Mikhail.
>
>
>
> ------------------------------------------------------------------------------
> The Command Line: Reinvented for Modern Developers
> Did the resurgence of CLI tooling catch you by surprise?
> Reconnect with the command line and become more productive.
> Learn the new .NET and ASP.NET CLI. Get your free copy!
> http://sdm.link/telerik
> _______________________________________________
> Squirrel-sql-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL

mr16
Gerd,
firstly thank you for your answer.

To be honest I don't understand what you mean by saying "... what a plain JDBC connect to your database looks like". So I'm sending you a simple test application that successfully connects to the database server.

package test_pg_ssl;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;

public class TestPgSSL {
   public static void main(String[] args) {
      Connection conn = null;
      String url = "jdbc:postgresql://192.168.1.10/hospice_fr";
      Properties props = new Properties();
      props.setProperty("user","hospice");
      props.setProperty("password","************");
      props.setProperty("ssl","true");
      try {
         conn = DriverManager.getConnection(url,props);
      }
      catch (SQLException ex) {
         ex.printStackTrace();
     }

      String stop = "Is connection established?";
   }
}
To simplify the task I ran the test_pg_ssl under debugger on the client and when getting the code line 'String stop = "Is connection established?"' I ran the 'ps' command on the server. I was able to see the connection is successfully established then:
mikhail@debian16:~$ ps fax | grep hospice | grep -v grep
 1126 ?        Ss     0:00  \_ postgres: hospice_fr hospice 192.168.1.101(59790) idle
Note:
192.168.1.101 is my client IP
By the way. If I commented out a code line 'props.setProperty("ssl","true")' I got such an exception:
org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.101", user "hospice", database "hospice_fr", SSL off


I also send you a client and a server configurations.

Here is a PostgreSQL database server configuration:
mikhail@debian16:/etc/postgresql/9.4/main$ cat postgresql.conf | grep ssl
ssl = true
#ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
#ssl_prefer_server_ciphers = on
#ssl_ecdh_curve = 'prime256v1'
#ssl_renegotiation_limit = 512MB
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
#ssl_ca_file = ''
#ssl_crl_file = ''
mikhail@debian16:/etc/postgresql/9.4/main$ cat pg_hba.conf | grep host
hostssl hospice_fr hospice 192.168.1.10/32  md5
hostssl hospice_fr hospice 192.168.1.101/32 md5
hostssl hospice_fr hospice 192.168.1.102/32 md5
hostssl hospice_fr hospice 192.168.1.202/32 md5
host all all 127.0.0.1/32 md5
host all all ::1/128      md5
# Allow replication connections from localhost, by a user with the
#host replication postgres 127.0.0.1/32 md5
#host replication postgres ::1/128      md5
Notes:
192.168.1.10 - is a PostgreSQL database server IP
192.168.1.101|102|... are client boxes IP

Here is a code fragment that successfully let my Java clients connect to the PostgreSQL database server:
public class Data {
    public static Connection connACF = null;
    public static Connection connACT = null;
    [...]
    public static void initConn() throws SQLException {
        String url = "jdbc:postgresql://"+Conf.db_ip+"/"+Conf.DB_NAME;
        Properties props = new Properties();
        props.setProperty("user",Conf.DB_USER)
        props.setProperty("password","************");
        props.setProperty("ssl","true");
        props.setProperty("logUnclosedConnections","true");
        props.setProperty("socketTimeout",Conf.DB_TIMEOUT);

        if (connACF!=null && !connACF.isClosed())
            connACF.close();
        connACF = DriverManager.getConnection(url,props);
        connACF.setAutoCommit(false);
        connACF.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
               
        if (connACT!=null && !connACT.isClosed())
            connACT.close();
        connACT = DriverManager.getConnection(url,props);
        connACT.setAutoCommit(true);
    }
    [...]
}
Notes:
Conf.db_ip = '192.168.1.10'
Conf.DB_NAME = 'hospice_fr'
Conf.DB_USER = 'hospice'
Conf.DB_TIMEOUT = 10
The application uses two connection:
    one with autoCommit==true (when it needs to do one update during a transaction),
    the other with autoCommit==false (when it needs to do more than one updates during a transaction)

Thank you again, Mikhail.


05.11.2016, 13:03, "Gerd Wagner" <[hidden email]>:

> Could you please send a code example, what a plain JDBC connect to your
> database looks like? It would be nice if you could do it outside your
> application's project just to make sure the example doesn't profit from
> any configurations you did for your application.
>
> Thanks Gerd
>
> Am 29.10.2016 um 09:13 schrieb [hidden email]:
>>  I have got a Debian Jessie box with a PostgreSQL (9.4) database installed and configured to get SSL connection.
>>  On Windows 10 box I'm writing a Java (1.8.0_111) application that connects to the database through SSL using PostgreSQL-JDBC driver (9.4.1211).
>>  To let the application get a database certificate I did the following:
>>     on the Debian Jessy box:
>>        cp /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
>>        openssl x509 -in server.crt -out server.crt.der -outform der
>>     on the Windows 10 box:
>>        cd C:\Program Files (x86)\Java\jre1.8.0_111\lib\security
>>        keytool -keystore cacerts -alias postgresql -import -file server.crt.der
>>  My application successfully connects to the database through SSL.
>>
>>  Than (to get more opportunities for debugging the application) I try to get connection to the database from Squirrel SQL (3.7.1) with the following configuration:
>>  Squirrel SQL connection configuration:
>>     Alias: hospice32_fr
>>     Driver: PostgreSQL
>>     URL: jdbc:postgresql://192.168.1.10/hospice_fr
>>     User: hospice
>>     Password: ****************
>>  Squirrel SQL connection driver properties:
>>     ssl: <checked> true
>>     sslcert: <checked> C:\Program Files\Java\jre1.8.0_111\lib\security\cacerts
>>     all the rest options are unchecked
>>
>>  Connection failed with such an error message:
>>     java.util.concurrent.ExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed:
>>     sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
>>     [...]
>>
>>  I also tried to replace the sslcert property with <SOME_DIR>/server.crt.der and <SOME_DIR>/server.crt, to check PGDBNAME, PGHOST, PGPORT,user properties.
>>  Result was the same: No connection.
>>
>>  Could anybody please help me to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL.
>>
>>  Thank you, Mikhail.
>>
>>  ------------------------------------------------------------------------------
>>  The Command Line: Reinvented for Modern Developers
>>  Did the resurgence of CLI tooling catch you by surprise?
>>  Reconnect with the command line and become more productive.
>>  Learn the new .NET and ASP.NET CLI. Get your free copy!
>>  http://sdm.link/telerik
>>  _______________________________________________
>>  Squirrel-sql-users mailing list
>>  [hidden email]
>>  https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL

Gerd Wagner-3
Mikhail,

the code you sent was just what I was asking for.

Please try the following:
- Select the SQuirreL Alias to your database.
- In the Aliases toolbar click the 6th button from the right. The tool
tip of that button is 'Edit properties of the selected Alias'.
- In the dialog go the 'Driver properties' tab.
- Check the 'Use driver properties' check box at the top
- Check the 'Specify' column for the row with name 'ssl'
- Enter true in the value column of the same row. (Note: the description
of the current selected parameter is given at the bottom of the dialog)

Hope this helps

Gerd



Am 06.11.2016 um 06:57 schrieb [hidden email]:

> Gerd,
> firstly thank you for your answer.
>
> To be honest I don't understand what you mean by saying "... what a plain JDBC connect to your database looks like". So I'm sending you a simple test application that successfully connects to the database server.
>
> package test_pg_ssl;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.SQLException;
> import java.util.Properties;
>
> public class TestPgSSL {
>    public static void main(String[] args) {
>       Connection conn = null;
>       String url = "jdbc:postgresql://192.168.1.10/hospice_fr";
>       Properties props = new Properties();
>       props.setProperty("user","hospice");
>       props.setProperty("password","************");
>       props.setProperty("ssl","true");
>       try {
>          conn = DriverManager.getConnection(url,props);
>       }
>       catch (SQLException ex) {
>          ex.printStackTrace();
>      }
>
>       String stop = "Is connection established?";
>    }
> }
> To simplify the task I ran the test_pg_ssl under debugger on the client and when getting the code line 'String stop = "Is connection established?"' I ran the 'ps' command on the server. I was able to see the connection is successfully established then:
> mikhail@debian16:~$ ps fax | grep hospice | grep -v grep
>  1126 ?        Ss     0:00  \_ postgres: hospice_fr hospice 192.168.1.101(59790) idle
> Note:
> 192.168.1.101 is my client IP
> By the way. If I commented out a code line 'props.setProperty("ssl","true")' I got such an exception:
> org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.101", user "hospice", database "hospice_fr", SSL off
>
>
> I also send you a client and a server configurations.
>
> Here is a PostgreSQL database server configuration:
> mikhail@debian16:/etc/postgresql/9.4/main$ cat postgresql.conf | grep ssl
> ssl = true
> #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
> #ssl_prefer_server_ciphers = on
> #ssl_ecdh_curve = 'prime256v1'
> #ssl_renegotiation_limit = 512MB
> ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
> ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
> #ssl_ca_file = ''
> #ssl_crl_file = ''
> mikhail@debian16:/etc/postgresql/9.4/main$ cat pg_hba.conf | grep host
> hostssl hospice_fr hospice 192.168.1.10/32  md5
> hostssl hospice_fr hospice 192.168.1.101/32 md5
> hostssl hospice_fr hospice 192.168.1.102/32 md5
> hostssl hospice_fr hospice 192.168.1.202/32 md5
> host all all 127.0.0.1/32 md5
> host all all ::1/128      md5
> # Allow replication connections from localhost, by a user with the
> #host replication postgres 127.0.0.1/32 md5
> #host replication postgres ::1/128      md5
> Notes:
> 192.168.1.10 - is a PostgreSQL database server IP
> 192.168.1.101|102|... are client boxes IP
>
> Here is a code fragment that successfully let my Java clients connect to the PostgreSQL database server:
> public class Data {
>     public static Connection connACF = null;
>     public static Connection connACT = null;
>     [...]
>     public static void initConn() throws SQLException {
>         String url = "jdbc:postgresql://"+Conf.db_ip+"/"+Conf.DB_NAME;
>         Properties props = new Properties();
>         props.setProperty("user",Conf.DB_USER)
>         props.setProperty("password","************");
>         props.setProperty("ssl","true");
>         props.setProperty("logUnclosedConnections","true");
>         props.setProperty("socketTimeout",Conf.DB_TIMEOUT);
>
>         if (connACF!=null && !connACF.isClosed())
>             connACF.close();
>         connACF = DriverManager.getConnection(url,props);
>         connACF.setAutoCommit(false);
>         connACF.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
>
>         if (connACT!=null && !connACT.isClosed())
>             connACT.close();
>         connACT = DriverManager.getConnection(url,props);
>         connACT.setAutoCommit(true);
>     }
>     [...]
> }
> Notes:
> Conf.db_ip = '192.168.1.10'
> Conf.DB_NAME = 'hospice_fr'
> Conf.DB_USER = 'hospice'
> Conf.DB_TIMEOUT = 10
> The application uses two connection:
>     one with autoCommit==true (when it needs to do one update during a transaction),
>     the other with autoCommit==false (when it needs to do more than one updates during a transaction)
>
> Thank you again, Mikhail.
>
>
> 05.11.2016, 13:03, "Gerd Wagner" <[hidden email]>:
>> Could you please send a code example, what a plain JDBC connect to your
>> database looks like? It would be nice if you could do it outside your
>> application's project just to make sure the example doesn't profit from
>> any configurations you did for your application.
>>
>> Thanks Gerd
>>
>> Am 29.10.2016 um 09:13 schrieb [hidden email]:
>>>  I have got a Debian Jessie box with a PostgreSQL (9.4) database installed and configured to get SSL connection.
>>>  On Windows 10 box I'm writing a Java (1.8.0_111) application that connects to the database through SSL using PostgreSQL-JDBC driver (9.4.1211).
>>>  To let the application get a database certificate I did the following:
>>>     on the Debian Jessy box:
>>>        cp /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
>>>        openssl x509 -in server.crt -out server.crt.der -outform der
>>>     on the Windows 10 box:
>>>        cd C:\Program Files (x86)\Java\jre1.8.0_111\lib\security
>>>        keytool -keystore cacerts -alias postgresql -import -file server.crt.der
>>>  My application successfully connects to the database through SSL.
>>>
>>>  Than (to get more opportunities for debugging the application) I try to get connection to the database from Squirrel SQL (3.7.1) with the following configuration:
>>>  Squirrel SQL connection configuration:
>>>     Alias: hospice32_fr
>>>     Driver: PostgreSQL
>>>     URL: jdbc:postgresql://192.168.1.10/hospice_fr
>>>     User: hospice
>>>     Password: ****************
>>>  Squirrel SQL connection driver properties:
>>>     ssl: <checked> true
>>>     sslcert: <checked> C:\Program Files\Java\jre1.8.0_111\lib\security\cacerts
>>>     all the rest options are unchecked
>>>
>>>  Connection failed with such an error message:
>>>     java.util.concurrent.ExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed:
>>>     sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
>>>     [...]
>>>
>>>  I also tried to replace the sslcert property with <SOME_DIR>/server.crt.der and <SOME_DIR>/server.crt, to check PGDBNAME, PGHOST, PGPORT,user properties.
>>>  Result was the same: No connection.
>>>
>>>  Could anybody please help me to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL.
>>>
>>>  Thank you, Mikhail.
>>>
>>>  ------------------------------------------------------------------------------
>>>  The Command Line: Reinvented for Modern Developers
>>>  Did the resurgence of CLI tooling catch you by surprise?
>>>  Reconnect with the command line and become more productive.
>>>  Learn the new .NET and ASP.NET CLI. Get your free copy!
>>>  http://sdm.link/telerik
>>>  _______________________________________________
>>>  Squirrel-sql-users mailing list
>>>  [hidden email]
>>>  https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>
> ------------------------------------------------------------------------------
> Developer Access Program for Intel Xeon Phi Processors
> Access to Intel Xeon Phi processor-based developer platforms.
> With one year of Intel Parallel Studio XE.
> Training and support from Colfax.
> Order your platform today. http://sdm.link/xeonphi
> _______________________________________________
> Squirrel-sql-users mailing list
> [hidden email]
> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: How to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL

mr16
Gerd,

Finally, I managed to solve the problem.
It was because my environment variable "JAVA_HOME" was wrong (I installed Squirrel-SQL from squirrelsql-3.7.1-standard.ZIP).
The launch file "squirrel-sql.but" from this package uses this variable, hence the problem.

Now after I set the right JAVA_HOME variable everything works fine.
I should be more attentive, sorry.

Thank you for your time.

Mikhail.

07.11.2016, 23:20, "Gerd Wagner" <[hidden email]>:

> Mikhail,
>
> the code you sent was just what I was asking for.
>
> Please try the following:
> - Select the SQuirreL Alias to your database.
> - In the Aliases toolbar click the 6th button from the right. The tool
> tip of that button is 'Edit properties of the selected Alias'.
> - In the dialog go the 'Driver properties' tab.
> - Check the 'Use driver properties' check box at the top
> - Check the 'Specify' column for the row with name 'ssl'
> - Enter true in the value column of the same row. (Note: the description
> of the current selected parameter is given at the bottom of the dialog)
>
> Hope this helps
>
> Gerd
>
> Am 06.11.2016 um 06:57 schrieb [hidden email]:
>>  Gerd,
>>  firstly thank you for your answer.
>>
>>  To be honest I don't understand what you mean by saying "... what a plain JDBC connect to your database looks like". So I'm sending you a simple test application that successfully connects to the database server.
>>
>>  package test_pg_ssl;
>>  import java.sql.Connection;
>>  import java.sql.DriverManager;
>>  import java.sql.SQLException;
>>  import java.util.Properties;
>>
>>  public class TestPgSSL {
>>     public static void main(String[] args) {
>>        Connection conn = null;
>>        String url = "jdbc:postgresql://192.168.1.10/hospice_fr";
>>        Properties props = new Properties();
>>        props.setProperty("user","hospice");
>>        props.setProperty("password","************");
>>        props.setProperty("ssl","true");
>>        try {
>>           conn = DriverManager.getConnection(url,props);
>>        }
>>        catch (SQLException ex) {
>>           ex.printStackTrace();
>>       }
>>
>>        String stop = "Is connection established?";
>>     }
>>  }
>>  To simplify the task I ran the test_pg_ssl under debugger on the client and when getting the code line 'String stop = "Is connection established?"' I ran the 'ps' command on the server. I was able to see the connection is successfully established then:
>>  mikhail@debian16:~$ ps fax | grep hospice | grep -v grep
>>   1126 ? Ss 0:00 \_ postgres: hospice_fr hospice 192.168.1.101(59790) idle
>>  Note:
>>  192.168.1.101 is my client IP
>>  By the way. If I commented out a code line 'props.setProperty("ssl","true")' I got such an exception:
>>  org.postgresql.util.PSQLException: FATAL: no pg_hba.conf entry for host "192.168.1.101", user "hospice", database "hospice_fr", SSL off
>>
>>  I also send you a client and a server configurations.
>>
>>  Here is a PostgreSQL database server configuration:
>>  mikhail@debian16:/etc/postgresql/9.4/main$ cat postgresql.conf | grep ssl
>>  ssl = true
>>  #ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL'
>>  #ssl_prefer_server_ciphers = on
>>  #ssl_ecdh_curve = 'prime256v1'
>>  #ssl_renegotiation_limit = 512MB
>>  ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'
>>  ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'
>>  #ssl_ca_file = ''
>>  #ssl_crl_file = ''
>>  mikhail@debian16:/etc/postgresql/9.4/main$ cat pg_hba.conf | grep host
>>  hostssl hospice_fr hospice 192.168.1.10/32 md5
>>  hostssl hospice_fr hospice 192.168.1.101/32 md5
>>  hostssl hospice_fr hospice 192.168.1.102/32 md5
>>  hostssl hospice_fr hospice 192.168.1.202/32 md5
>>  host all all 127.0.0.1/32 md5
>>  host all all ::1/128 md5
>>  # Allow replication connections from localhost, by a user with the
>>  #host replication postgres 127.0.0.1/32 md5
>>  #host replication postgres ::1/128 md5
>>  Notes:
>>  192.168.1.10 - is a PostgreSQL database server IP
>>  192.168.1.101|102|... are client boxes IP
>>
>>  Here is a code fragment that successfully let my Java clients connect to the PostgreSQL database server:
>>  public class Data {
>>      public static Connection connACF = null;
>>      public static Connection connACT = null;
>>      [...]
>>      public static void initConn() throws SQLException {
>>          String url = "jdbc:postgresql://"+Conf.db_ip+"/"+Conf.DB_NAME;
>>          Properties props = new Properties();
>>          props.setProperty("user",Conf.DB_USER)
>>          props.setProperty("password","************");
>>          props.setProperty("ssl","true");
>>          props.setProperty("logUnclosedConnections","true");
>>          props.setProperty("socketTimeout",Conf.DB_TIMEOUT);
>>
>>          if (connACF!=null && !connACF.isClosed())
>>              connACF.close();
>>          connACF = DriverManager.getConnection(url,props);
>>          connACF.setAutoCommit(false);
>>          connACF.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
>>
>>          if (connACT!=null && !connACT.isClosed())
>>              connACT.close();
>>          connACT = DriverManager.getConnection(url,props);
>>          connACT.setAutoCommit(true);
>>      }
>>      [...]
>>  }
>>  Notes:
>>  Conf.db_ip = '192.168.1.10'
>>  Conf.DB_NAME = 'hospice_fr'
>>  Conf.DB_USER = 'hospice'
>>  Conf.DB_TIMEOUT = 10
>>  The application uses two connection:
>>      one with autoCommit==true (when it needs to do one update during a transaction),
>>      the other with autoCommit==false (when it needs to do more than one updates during a transaction)
>>
>>  Thank you again, Mikhail.
>>
>>  05.11.2016, 13:03, "Gerd Wagner" <[hidden email]>:
>>>  Could you please send a code example, what a plain JDBC connect to your
>>>  database looks like? It would be nice if you could do it outside your
>>>  application's project just to make sure the example doesn't profit from
>>>  any configurations you did for your application.
>>>
>>>  Thanks Gerd
>>>
>>>  Am 29.10.2016 um 09:13 schrieb [hidden email]:
>>>>   I have got a Debian Jessie box with a PostgreSQL (9.4) database installed and configured to get SSL connection.
>>>>   On Windows 10 box I'm writing a Java (1.8.0_111) application that connects to the database through SSL using PostgreSQL-JDBC driver (9.4.1211).
>>>>   To let the application get a database certificate I did the following:
>>>>      on the Debian Jessy box:
>>>>         cp /etc/ssl/certs/ssl-cert-snakeoil.pem server.crt
>>>>         openssl x509 -in server.crt -out server.crt.der -outform der
>>>>      on the Windows 10 box:
>>>>         cd C:\Program Files (x86)\Java\jre1.8.0_111\lib\security
>>>>         keytool -keystore cacerts -alias postgresql -import -file server.crt.der
>>>>   My application successfully connects to the database through SSL.
>>>>
>>>>   Than (to get more opportunities for debugging the application) I try to get connection to the database from Squirrel SQL (3.7.1) with the following configuration:
>>>>   Squirrel SQL connection configuration:
>>>>      Alias: hospice32_fr
>>>>      Driver: PostgreSQL
>>>>      URL: jdbc:postgresql://192.168.1.10/hospice_fr
>>>>      User: hospice
>>>>      Password: ****************
>>>>   Squirrel SQL connection driver properties:
>>>>      ssl: <checked> true
>>>>      sslcert: <checked> C:\Program Files\Java\jre1.8.0_111\lib\security\cacerts
>>>>      all the rest options are unchecked
>>>>
>>>>   Connection failed with such an error message:
>>>>      java.util.concurrent.ExecutionException: java.lang.RuntimeException: org.postgresql.util.PSQLException: SSL error: sun.security.validator.ValidatorException: PKIX path building failed:
>>>>      sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target
>>>>      [...]
>>>>
>>>>   I also tried to replace the sslcert property with <SOME_DIR>/server.crt.der and <SOME_DIR>/server.crt, to check PGDBNAME, PGHOST, PGPORT,user properties.
>>>>   Result was the same: No connection.
>>>>
>>>>   Could anybody please help me to configure SquirrelSQL so than it can get connection to PostgreSQL through SSL.
>>>>
>>>>   Thank you, Mikhail.
>>>>
>>>>   ------------------------------------------------------------------------------
>>>>   The Command Line: Reinvented for Modern Developers
>>>>   Did the resurgence of CLI tooling catch you by surprise?
>>>>   Reconnect with the command line and become more productive.
>>>>   Learn the new .NET and ASP.NET CLI. Get your free copy!
>>>>   http://sdm.link/telerik
>>>>   _______________________________________________
>>>>   Squirrel-sql-users mailing list
>>>>   [hidden email]
>>>>   https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
>>
>>  ------------------------------------------------------------------------------
>>  Developer Access Program for Intel Xeon Phi Processors
>>  Access to Intel Xeon Phi processor-based developer platforms.
>>  With one year of Intel Parallel Studio XE.
>>  Training and support from Colfax.
>>  Order your platform today. http://sdm.link/xeonphi
>>  _______________________________________________
>>  Squirrel-sql-users mailing list
>>  [hidden email]
>>  https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users

------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Squirrel-sql-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
Loading...