ApsaraDB RDS:Connect to an ApsaraDB RDS for PostgreSQL instance over SSL

Step 1: Get the certificate files

Download the following files to the host where your Java application runs:

• server-ca.crt: The server CA certificate. See the Prerequisites section for how to get it.

• client.crt and client.key: Required only if you configured a client CA certificate.

Step 2: Convert the client private key to PKCS8 (PK8) format

JDBC requires the client private key in PKCS8 (PK8) format. Run the following command on the host where your application runs:

openssl pkcs8 -topk8 -inform PEM -in client.key -outform der -out client.pk8 -v1 PBE-MD5-DES
# Enter the encryption password when prompted. You will use this password in your code.
Enter Encryption Password:
Verifying - Enter Encryption Password:

Step 3: Add the PostgreSQL JDBC driver dependency

Add the following dependency to your pom.xml:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.2.10</version>
</dependency>

Step 4: Connect to the RDS instance

The example below uses a custom certificate (server-ca.crt). To use a cloud certificate, replace server-ca.crt with ApsaraDB-CA-Chain.pem.

public class PgSslDemo {
    public static void main(String[] args) {
        // Endpoint of the RDS instance
        String hostname = "pgm-bp1gclw58u36s6****.pg.rds.aliyuncs.com";
        // Port number (default: 5432)
        String port = "5432";
        // Database name
        String dbname = "postgres";

        String jdbcUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname + "?binaryTransfer=true";

        Properties properties = new Properties();
        // Database account username
        properties.setProperty("user", "test_user");
        // Database account password
        properties.setProperty("password", "test_pwd");
        // Directory where certificate files are stored
        String path = "D:\\ssl\\";

        // Enable SSL
        properties.setProperty("ssl", "true");
        // Path to the server CA certificate
        properties.setProperty("sslrootcert", path + "/" + "server-ca.crt");
        // Path to the client private key (PKCS8 format)
        properties.setProperty("sslkey", path + "/" + "client.pk8");
        // Path to the client certificate
        properties.setProperty("sslcert", path + "/" + "client.crt");
        // The encryption password set when converting client.key to PKCS8 format
        properties.setProperty("sslpassword", "test_ssl_pwd");

        // SSL mode: require, verify-ca, or verify-full
        properties.setProperty("sslmode", "verify-ca");

        try {
            Class.forName("org.postgresql.Driver");
            Connection connection = DriverManager.getConnection(jdbcUrl, properties);
            // Example: query all rows from the students01 table
            PreparedStatement preparedStatement = connection.prepareStatement("select * from students01");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ResultSetMetaData rsmd = resultSet.getMetaData();
                int columnCount = rsmd.getColumnCount();
                Map map = new HashMap();
                for (int i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                }
                System.out.println(map);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}