Connecting to a Database (Using SSL)

Updated on 2024-05-07 GMT+08:00

View PDF

When establishing connections to the GaussDB server using JDBC, you can enable SSL connections to encrypt client and server communications for security of sensitive data transmission on the Internet.

This section describes how applications configure the client in SSL mode through JDBC. For details about how to configure the server, contact the administrator.

To use the method described in this section, you must have the server certificate, client certificate, and private key files. For details on how to obtain these files, see related documents and commands of OpenSSL.

Configuring the Client

Different from gsql-based programs, JDBC supports server certificate validation by default. If you use a certificate issued by a global or regional certificate authority (CA), Java applications do not need to do anything because Java has copies of most certificates issued by the CA. If a self-signed license is in use, a client program must be configured based on the openssl or Java keytool for license authentication. The procedure is as follows:

NOTE:

If the built-in certificate is used, the following steps are valid.

Upload the certificate file on the client.
1. Log in to the host where the client resides as a common user.
2. Create the /tmp/cacert directory.
```
mkdir /tmp/cacert
```
3. Save the root certificate file, client certificate file, and private key file to the created directory.
Import the root certificate to TrustStore.
```
openssl x509 -in cacert.pem -out cacert.crt.der -outform der
```
Generate the intermediate file cacert.crt.der.
```
keytool -keystore mytruststore -alias cacert -import -file cacert.crt.der
```
Enter the trustStorePassword (for example, xxxxxxxxx) as prompted to generate mytruststore.
- cacert.pem indicates the root certificate.
- cacert.crt.der indicates the intermediate file.
- mytruststore indicates the generated keystore name. You can change the name and its alias as needed.
Import the client certificate and key to KeyStore.
```
openssl pkcs12 -export -out client.pkcs12 -in client.crt -inkey client.key   
```
Enter the client key (for example, xxxxxxxxx) as prompted to generate client.pkcs12.
```
keytool -importkeystore -deststorepass xxxxxxxxxxx -destkeystore client.jks -srckeystore client.pkcs12 -srcstorepass xxxxxxxxx -srcstoretype PKCS12 -alias 1 -destkeypass xxxxxxxxx
```
deststorepass must be consistent with destkeypass, and srcstorepass must be the same as the export password in the preceding command. Generate client.jks.

Example 1: Using the NonValidatingFactory Channel

NOTE:

Select either example 1 or example 2.

    
     
       
       // There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation).
public class SSL{
    public static void main(String[] args) {
        Properties urlProps = new Properties();
        String urls = "jdbc:postgresql://$ip:$port/postgres";
        String userName = System.getenv("EXAMPLE_USERNAME_ENV");
        String password = System.getenv("EXAMPLE_PASSWORD_ENV");

        urlProps.setProperty("sslfactory","org.postgresql.ssl.NonValidatingFactory");
        urlProps.setProperty("user", userName);
        urlProps.setProperty("password", password);
        urlProps.setProperty("ssl", "true");
        

        try {
            Class.forName("org.postgresql.Driver").newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            Connection conn;
            conn = DriverManager.getConnection(urls,urlProps);
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

      

    
   

Example 2: Using a Certificate

    
     
       
       // There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation).
public class SSL{
    public static void main(String[] args) {
        Properties urlProps = new Properties();
        String urls = "jdbc:postgresql://$ip:$port/postgres";
        String userName = System.getenv("EXAMPLE_USERNAME_ENV");
        String password = System.getenv("EXAMPLE_PASSWORD_ENV");

        urlProps.setProperty("sslcert", "client.crt");
        urlProps.setProperty("sslkey", "client.key.pk8");
        urlProps.setProperty("sslrootcert", "cacert.pem");
        urlProps.setProperty("user", userName);
        urlProps.setProperty("ssl", "true");
        /* sslmode can be set to require, verify-ca, or verify-full. Select one from the following three examples: */
       /* ================== Example 2.1: Set sslmode to require to use the certificate for authentication. */
        urlProps.setProperty("sslmode", "require");
       /* ================== Example 2.2: Set sslmode to verify-ca to use the certificate for authentication. */
        urlProps.setProperty("sslmode", "verify-ca");
       /* ================== Example 2.3: Set sslmode to verify-full to use the certificate (in the Linux OS) for authentication. */
        urls = "jdbc:postgresql://world:8000/postgres";
        urlProps.setProperty("sslmode", "verify-full");

        try {
            Class.forName("org.postgresql.Driver").newInstance();
        } catch (Exception e) {
            e.printStackTrace();
        }
        try {
            Connection conn;
            conn = DriverManager.getConnection(urls,urlProps);
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
/**
 * Note: Convert the client key to the DER format.
 * openssl pkcs8 -topk8 -outform DER -in client.key -out client.key.pk8 -nocrypt
 * openssl pkcs8 -topk8 -inform PEM -in client.key -outform DER -out client.key.der -v1 PBE-MD5-DES
 * openssl pkcs8 -topk8 -inform PEM -in client.key -outform DER -out client.key.der -v1 PBE-SHA1-3DES
 * The preceding algorithms are not recommended due to their low security.
 * If the customer needs to use a higher-level private key encryption algorithm, the following private key encryption algorithms can be used after the BouncyCastle or a third-party private key is used to decrypt the password package:
 * openssl pkcs8 -in client.key -topk8  -outform DER -out client.key.der -v2 AES128
 * openssl pkcs8 -in client.key -topk8  -outform DER -out client.key.der -v2 aes-256-cbc -iter 1000000
 * openssl pkcs8 -in client.key -topk8 -out client.key.der  -outform Der -v2 aes-256-cbc -v2prf hmacWithSHA512
 * Enable BouncyCastle: Introduce the bcpkix-jdk15on.jar package for projects that use JDBC. The recommended version is 1.65 or later.
 */

      

    
   

NOTICE:

When JDBC establishes a connection in SSL mode, a strong random number is obtained on the client. During the connection establishment, the error information shown in the figure may be displayed.

Click to enlarge

The random number generation on the client is too slow to meet product requirements. The entropy source is insufficient. As a result, the service fails to be started. This problem exists in some Linux environments.

Recommended solution: Start the haveged service on the client and increase the entropy value of the system entropy pool to improve the speed of reading random numbers. The startup command is as follows: