|
From: <mr...@tu...> - 2016-11-08 04:08:18
|
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" <ger...@t-...>:
> 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 mr...@tu...:
>> 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" <ger...@t-...>:
>>> 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 mr...@tu...:
>>>> 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
>>>> Squ...@li...
>>>> 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
>> Squ...@li...
>> https://lists.sourceforge.net/lists/listinfo/squirrel-sql-users
|
