PolarDB:Connect to a PolarDB for PostgreSQL (Compatible with Oracle) cluster over SSL

Prerequisites

• SSL is enabled for the cluster. For information about how to enable SSL for a cluster, see Configure SSL by using a cloud certificate or Configure a custom certificate.

• The following files are obtained:

  • (Optional) The client certificate (client.crt) and the client certificate private key (client.key). These files are required if the cluster needs to authenticate the client. For information about how to obtain these files, see Configure a client CA certificate.

  • The cluster CA certificate. For information about how to obtain this certificate, see Download a CA certificate or Create a custom certificate.

Procedure

Use pgAdmin to connect to the cluster over SSL

pgAdmin is a recommended client that you can use to connect to a PostgreSQL database. When you download the PostgreSQL software package from the PostgreSQL official website and install PostgreSQL, pgAdmin 4 is automatically downloaded and installed. The following section describes how to use pgAdmin 4 V6.2.0 to connect to a PolarDB for PostgreSQL (Compatible with Oracle) cluster.

1. Launch pgAdmin 4.

   Note

   The first time you log on to pgAdmin of a later version, you must specify a master password to protect saved passwords and other credentials.

2. Right-click Servers and choose Register>Server....

   

3. On the General tab of the Register - Server dialog box, enter the name of the server on which pgAdmin is installed.

   

4. Click the Connection tab and enter the information about the cluster to which you want to connect. The following table describes the parameters.

   

   Parameter	Description
   Host name/address	The endpoint and port number of the PolarDB for PostgreSQL (Compatible with Oracle) cluster. If you want to connect to the cluster over an internal network, enter the internal endpoint and internal port of the cluster. If you want to connect to the cluster over the Internet, enter the public endpoint and public port of the cluster.
   Port
   Username	The account and password of the PolarDB for PostgreSQL (Compatible with Oracle) cluster.
   Password

5. On the Parameters tab, configure the SSL mode and certificate parameters. The following table describes the parameters.

   Parameter	Description
   SSL mode	PolarDB for PostgreSQL (Compatible with Oracle) cluster, the cluster allows client connections over SSL. When you connect to the cluster from pgAdmin, configure the SSL mode parameter based on your specific scenario. No client access control settings are configured for the cluster. If you want to connect to the cluster over SSL, set the SSL mode parameter to Require, Verify-CA, or Verify-Full. If you do not want to connect to the cluster over SSL, set the SSL mode parameter to Disable. Client access control settings are configured for the cluster. In this case, the client must connect to the cluster over SSL. Set the SSL mode parameter to Require, Verify-CA, or Verify-Full. Require: encrypts the data connection and does not authenticate the cluster. Verify-CA: encrypts the data connection and authenticates the cluster. Verify-Full: encrypts the data connection, authenticates the cluster, and checks whether the Common Name (CN) or Domain Name System (DNS) in the cluster CA certificate is the same as the value of the Host name/address parameter.
   Client certificate	Enter the path of the client certificate (client.crt). You must configure this parameter if you have configured a client CA certificate for the cluster. For more information, see Configure a client CA certificate.
   Client certificate key	Enter the path of the private key of the client certificate (client.key). You must configure this parameter if you have configured a client CA certificate for the cluster. For more information, see Configure a client CA certificate.
   Root certificate	Enter the path of the cluster CA certificate. You must configure this parameter if you set the SSL mode parameter to Verify-CA or Verify-Full. Note You must enter the actual paths of the client certificate, client certificate private key, and cluster CA certificate. In this example, a custom certificate is configured. You can also configure a cloud certificate. For example, you can replace ca1.crt with ApsaraDB-CA-Chain.pem to configure a cloud certificate.

6. Click Save. If the information that you enter is correct, a page that is similar to the following figure appears, which indicates that the connection to the cluster is successful.

   

Use psql to connect to the cluster over SSL

1. In the var/lib/pgsql directory, create a folder named .postgresql.

   mkdir /var/lib/pgsql/.postgresql

2. Copy the following files to the .postgresql folder:

   • (Optional) Client certificate (client.crt) and client certificate private key (client.key)

   • Cluster CA certificate

   cp client.crt client.key ca1.crt /var/lib/pgsql/.postgresql/

Note

You must enter the actual paths of the client certificate, client certificate private key, and cluster CA certificate.

In this example, a custom certificate is configured. You can also configure a cloud certificate. For example, you can replace ca1.crt with ApsaraDB-CA-Chain.pem to configure a cloud certificate.

3. Modify the permissions on the .postgresql folder.

   chown postgres:postgres /var/lib/pgsql/.postgresql/*
   chmod 600 /var/lib/pgsql/.postgresql/*

4. Run the following command to open the file that contains the environment variables:

   vim /var/lib/pgsql/.bash_profile

5. Enter i to enter the insert mode. Then, append the following content to the file:

   export PGSSLCERT="/var/lib/pgsql/.postgresql/client.crt"
   export PGSSLKEY="/var/lib/pgsql/.postgresql/client.key"
   export PGSSLROOTCERT="/var/lib/pgsql/.postgresql/ca1.crt"

6. Press the Esc key to exit the insert mode. Then, enter :wq to save and close the file.

7. Reload the environment variables.

   source .bash_profile

8. Specify the method that the client uses to authenticate the cluster.

   export PGSSLMODE="verify-full"

   After you enable SSL for a cluster, the cluster allows connections from clients over SSL. When you connect to the cluster from psql, configure the PGSSLMODE parameter based on your business requirements.

   Client access control configured	SSL connection required	PGSSLMODE value
   No	Yes	require, verify-ca, or verify-full
   	No	disable
   Yes	Yes. The client must connect to the cluster over SSL.	require, verify-ca, or verify-full

   Note

   Valid values for the PGSSLMODE parameter: require, verify-ca, and verify-full

   • require: encrypts the data connection but does not authenticate the cluster.

   • verify-ca: encrypts the data connection and authenticates the cluster.

   • verify-full: encrypts the data connection, authenticates the cluster, and checks whether the CN or DNS in the cluster CA certificate matches the configured cluster connection address.

9. Connect to the cluster.

   psql -h <Endpoint> -U <Username> -p <Port number> -d <Database name>

Use JDBC to connect to the cluster over SSL

1. Download the following files to your computer:

   • (Optional) Client certificate (client.crt) and client certificate private key (client.key)

   • Cluster CA certificate

2. Convert the client certificate private key (client.key) to the PK8 format.

   openssl pkcs8 -topk8 -inform PEM -in client.key -outform der -out client.pk8 -v1 PBE-MD5-DES
   # Enter the password, which is required when the client connects to the cluster.
   Enter Encryption Password:
   Verifying - Enter Encryption Password:

3. In this example, the client runs Maven. In this case, import the Maven dependencies of PostgreSQL into the pom.xml file.

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

4. Compile a code snippet to establish a JDBC-based SSL connection to the PolarDB for PostgreSQL (Compatible with Oracle) cluster.

   Note

   In this example, a custom certificate is configured. You can also configure a cloud certificate. For example, you can replace ca1.crt with ApsaraDB-CA-Chain.pem to configure a cloud certificate.

   // Specify the endpoint of the PolarDB for PostgreSQL cluster.     
    String hostname = "pe-bpxxxxx.pg.rds.aliyuncs.com";   
    // Specify the port number of the PolarDB for PostgreSQL cluster.
    String port = "5432";   
    // Specify the name of the database to which you want to connect.
    String dbname = "postgres";  
   
    String jdbcUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname+"?binaryTransfer=true";
   
    Properties properties = new Properties();
    // Specify the username that is used to access the specified database.
    properties.setProperty("user", "username"); 
    // Specify the password of the username that is used to access the specified database.
    properties.setProperty("password", "*****");   
    // Specify the path in which the certificates are stored. 
    String path= "D:\\ssl\\"; 
   
    // Enable SSL.
    properties.setProperty("ssl", "true");
    // Specify the public key of the CA.
    properties.setProperty("sslrootcert", path + "/" + "ca1.crt");
    // Specify the private key of the client certificate.
    properties.setProperty("sslkey", path + "/" + "client.pk8");  
    // Specify the client certificate.
    properties.setProperty("sslcert", path + "/" + "client.crt");  
    // Enter the password that you specified when you converted the client.key file to the PK8 format.
    properties.setProperty("sslpassword", "*****"); 
   
    // Specify the SSL mode. Valid values: require, verify-ca, and verify-full.
    properties.setProperty("sslmode", "verify-ca"); 
   
     try {
         Class.forName("org.postgresql.Driver");
         Connection connection = DriverManager.getConnection(jdbcUrl, properties);
         // In this example, the database named postgres contains a table named example, and data is queried from the table. 
         PreparedStatement preparedStatement = connection.prepareStatement("select * from " +
                 "example");
         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();
     }