|
From: Gerd W. <ger...@t-...> - 2016-11-07 20:20:16
|
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
>
|
