SSL Configuration

When an SSL client makes a connection to a server, it will get the servers Certificate and validate the Certificate based on the digital signature of a trusted Certificate Authority (CA).  A trusted CA is a CA which is located in the client's trusted store, which is located locally.  Lets take a web browser as an example.  If you use Google Chrome to connect to https://store.aquafold.com and click on the green lock you can see the Certificate and how it has been validated (see attached Trusted-CA).  If you try to connect to an HTTP server which has a self signed Certificate, such as the default web server on a Sonicwall firewall, Chrome will give you an error message because it can't validate the Certificate with a trusted CA (see attached Untrusted-CA).  In the Chrome settings you can manage the trusted CAs (see attached Chrome-Trusted-Certificates).

Aqua Data Studio is a Java application and the JDBC drivers are written in Java.  The Java platform provides an API to make SSL connections.  With this API you can programmatically make a "Validating" SSL connection or a "Non-Validating" SSL connection.  A validating SSL connection requires that the server certificate is validated with the Java trust store, while a Non-Validating SSL connection will succeed even if the Certificate can't be validated. 

Java SSL API : https://docs.oracle.com/javase/8/docs/api/javax/net/ssl/SSLSocketFactory.html

The Java CA trusted store is located in <JAVA_HOME>\jre\lib\security\cacerts.  For ADS, Java is located in <ADS_HOME>\jre\.  You can read more about it here ...

http://superuser.com/questions/55470/which-trusted-root-certificates-are-included-in-java
http://stackoverflow.com/questions/21038249/certificates-trusted-by-the-default-jdk-installation

JDBC drivers use the Java SSL API to make SSL connections.  Some drivers will always use Non-Validating connections (such as SQL Server).  Some drivers will always use Validating connections (such as Vertica and Hive).  And some drivers will default to a Validating connection while providing a JDBC properties to enable a Non-validating connection (such as PostgreSQL and MongoDB).  The drivers that require or use a Validating connection can either use the Java trust store or their own configurable trust store.  It is generally not recommended to modify the Java trust store so most drivers provide documentation on how to create your own trust store and configure it only for their JDBC driver.
 

Here is a tutorial on how to create and manage keystores :
https://www.digitalocean.com/community/tutorials/java-keytool-essentials-working-with-java-keystores

Example: Configuring SSL Connection to PostgreSQL 9.6

1. Open the Server Properties dialog box for the PostgreSQL 9.6 connection and select the Driver tab.
2. Enter the following in the Parameters field: ?client_encoding=sslmode=verify-ca&sslrootcert=/Users/nhilam/myssl/server.crt
   • Replace "/Users/nhilam/myssl/server.crt" with the actual path of the server certificate file.
3. If the database server is configured for 2-way SSL (you usually get the "FATAL: connection requires a valid client certificate" error if your connection is configured as 1-way SSL), then do the following:
   • Obtain the client certificate and private key files from the database admin. Let's name these files client.crt and client.pk8.
     • This client certificate must be trusted by the database server.
   • Note that the client private key file must be PKCS8 and stored in DER format. If the client.key file is in PEM format (i.e. starts with something like -----BEGIN PRIVATE KEY-----) you can convert it to DER format with openssl as follows:
     • openssl pkcs8 -topk8 -inform PEM -outform DER -in client.key -out client.pk8
     • chmod 600 client.pk8
   • Enter the following in the Parameters field: ?sslmode=verify-ca&sslrootcert=/Users/nhilam/myssl/server.crt&sslcert=/Users/nhilam/myssl/client.crt&sslkey=/Users/nhilam/myssl/client.pk8&sslpassword=my_secret_password
     • ​Replace file paths and password with the actual file paths and password.

Example: Configuring SSL Connection to PostgreSQL 9.6 Using Self-Signed Certificate

1. Generate the server certificate and key files as follows:
   • openssl req -new -text -nodes -keyout server.key -out server.csr -subj '/C=US/ST=California/L=Fremont/O=Example/OU=CoreDev/CN=server_name'
     • Replace "server_name" with the actual server name or IP address.
   • openssl req -x509 -text -in server.csr -key server.key -out server.crt
   • cp server.crt root.crt
   • rm server.csr
   • chmod og-rwx server.key
   • openssl req -new -nodes -keyout client.key -out client.csr -subj '/C=US/ST=California/L=Fremont/O=Example/OU=CoreDev/CN=postgres'
   • openssl x509 -req -CAcreateserial -in client.csr -CA root.crt -CAkey server.key -out client.crt
   • rm client.csr
   • chmod og-rwx client.key
   • openssl pkcs8 -topk8 -inform PEM -outform DER -in client.key -out client.pk8
   • chmod og-rwx client.pk8
2. Copy server.crt, server.key and root.crt to the PostgreSQL server machine, data directory.
3. On the server machine, data directory:
   • In the pg_hba.conf file, configure 2-way SSL as follows: hostssl all all 0.0.0.0/0 cert clientcert=1
   • In the postgresql.conf file, configure the following settings:
     • ssl = on
     • ssl_cert_file = server.crt
     • ssl_key_file = server.key
     • ssl_ca_file = root.crt
   • Restart the PostgreSQL server
4. Copy server.crt, client.crt and client.pk8 to the client machine where Aqua Data Studio is running.
5. In Aqua Data Studio:
   • Open the Server Properties dialog box for the PostgreSQL 9.6 connection and select the Driver tab.
   • Enter the following in the Parameters field: ?sslmode=verify-ca&sslrootcert=/Users/nhilam/myssl/server.crt&sslcert=/Users/nhilam/myssl/client.crt&sslkey=/Users/nhilam/myssl/client.pk8&sslpassword=my_secret_password
     • ​Replace file paths and password with the actual file paths and password.