|
From: <mr...@tu...> - 2016-11-06 06:12:55
|
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
|
