Connect to an ApsaraDB RDS for PostgreSQL instance over SSL connections - ApsaraDB RDS

This topic describes how to connect to an ApsaraDB RDS for PostgreSQL instance from a database client over SSL connections. After you configure SSL encryption for an RDS instance, you can connect to the RDS instance from a database client by using pgAdmin, PostgreSQL CLI, or Java Database Connectivity (JDBC).

Prerequisites

SSL encryption is enabled for the RDS instance. For more information about how to enable SSL encryption, see Configure SSL encryption for an ApsaraDB RDS for PostgreSQL instance or Configure a custom certificate on an ApsaraDB RDS for PostgreSQL instance.
The following files are obtained:
- The client.crt file that contains the client certificate and the client.key file that contains the private key of the client certificate: If you have configured a client certification authority (CA) certificate in the ApsaraDB RDS console, you must obtain these files. Otherwise, these files are optional. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance.
- The file that contains the server CA certificate: For more information about how to obtain this file, see Configure SSL encryption for an ApsaraDB RDS for PostgreSQL instance or Configure a custom certificate on an ApsaraDB RDS for PostgreSQL instance.

Procedure

Use pgAdmin to connect to the RDS instance over SSL connections

pgAdmin is a recommended PostgreSQL client that you can use to connect to an RDS instance. 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 provides an example on how to use pgAdmin 4 V6.2.0 to connect to an RDS instance.

If you do not want to install PostgreSQL, you can download only pgAdmin for remote connections.

Start pgAdmin 4.
Note If this is the first time you log on to pgAdmin of a later version, you must specify a master password that is used to protect saved passwords and other credentials.
Right-click Servers and choose Register > Server.
On the General tab of the Create - Server dialog box, enter the name of the server on which pgAdmin is installed.

Click the Connection tab and enter the information that is used to connect to the RDS instance. Connection Information


Parameter	Description
Host name/address	Enter the endpoint and port of the RDS instance. If you want to connect to the RDS instance over an internal network, enter the internal endpoint and internal port of the RDS instance. If you want to connect to the RDS instance over the Internet, enter the public endpoint and public port of the RDS instance. You can view the preceding information on the Database Connection page of the RDS instance. For more information, see View and change the endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
Port
Username	Enter the username and password that are used to log on to the RDS instance. For more information about how to create an account for an RDS instance, see Create a database and an account on an ApsaraDB RDS for PostgreSQL instance.
Password

Click the SSL tab and configure the required parameters. The following table describes the parameters. sslpgadmin


Parameter	Description
SSL mode	If SSL encryption is enabled for the RDS instance, the RDS instance allows SSL connections from the database client. You must set the SSL mode parameter based on the following scenarios: No access control lists (ACLs) are configured for the RDS instance. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance. If you want to connect to the RDS instance from the database client over SSL connections, set the SSL mode parameter to Require, Verify-CA, or Verify-Full. If you do not want to connect to the RDS instance from the database client over SSL connections, set the SSL mode parameter to Disable. ACLs are configured for the RDS instance. In this case, you can connect to the RDS instance from the database client only over SSL connections. Set the SSL mode parameter to Require, Verify-CA, or Verify-Full. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance. The following list provides the meanings of the different values of the SSL mode parameter: Require: The database client encrypts the SSL connections that are used to transmit data. However, the database client does not validate the RDS instance. Verify-CA: The database client encrypts the SSL connections that are used to transmit data and validates the RDS instance. Verify-Full: The database client encrypts the SSL connections that are used to transmit data, validates the RDS instance, and verifies that the CN or Domain Name System (DNS) specified in the server certificate is consistent with the endpoint that is configured during connection establishment.
Client certificate	You must set this parameter if you have configured a client certificate. This parameter specifies the save path of the file that contains the client certificate. The client certificate is contained in the client.crt file. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance.
Client certificate key	You must set this parameter if you have configured a client certificate. This parameter specifies the save path of the file that contains the private key of the client certificate. The private key is contained in the client.key file. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance.
Root certificate	You must set this parameter if you set the SSL mode parameter to Verify-CA or Verify-Full. This parameter specifies the save path of the file that contains the server CA certificate. Note In this example, you must configure the paths to the client.crt file, client.key file, and ca1.crt file based on your business requirements. In this example, a custom certificate is configured. You can also configure a cloud certificate. For example, to configure a cloud certificate, you can replace `ca1.crt` with `ApsaraDB-CA-Chain.pem`.

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 RDS instance is successful.
Important The postgres database is the default system database. Do not perform operations on the postgres database.

Use psql to connect to the RDS instance over SSL

Note This method uses psql to connect to an RDS instance over SSL connections. Make sure that PostgreSQL is installed on your computer. For more information, see PostgreSQL documentation.

In the /var/lib/pgsql directory, create a folder named .postgresql.
```
mkdir /var/lib/pgsql/.postgresql
```
Copy the following files to the .postgresql folder:
- client.crt and client.key: If you have configured a client CA certificate in the ApsaraDB RDS console, you must obtain these files. Otherwise, these files are optional. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance.
- ca1.crt: For more information about how to obtain this file, see Configure SSL encryption for an ApsaraDB RDS for PostgreSQL instance or Configure a custom certificate on an ApsaraDB RDS for PostgreSQL instance.
```
cp client.crt client.key ca1.crt /var/lib/pgsql/.postgresql/
```
Note
- In this example, you must configure the paths to the client.crt file, client.key file, and ca1.crt file based on your business requirements.
- In this example, a custom certificate is configured. You can also configure a cloud certificate. For example, to configure a cloud certificate, you can replace ca1.crt with ApsaraDB-CA-Chain.pem.

Modify the permissions on the .postgresql folder.

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

Run the following command to open the file that contains the environment variables:
```
vim /var/lib/pgsql/.bash_profile
```

Enter i to enable the insert mode. Then, add 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"

Press Esc to exit the insesrt mode. Then, enter :wq to save the file and exit.
Reload the environment variables.
```
source .bash_profile
```

Specify the method that is used by the database client to validate the RDS instance.

export PGSSLMODE="verify-full"

If SSL encryption is enabled for the RDS instance, the RDS instance allows SSL connections from the database client. You must set the PGSSLMODE parameter based on your business requirements.


ACL configured	SSL connection required	Value of the PGSSLMODE parameter
No	Yes	require, verify-ca, or verify-full
No	No	disable
Yes	The database client can connect to the RDS instance only over SSL.	require, verify-ca, or verify-full

Note

The following list provides the meanings of the different values of the PGSSLMODE parameter:

require: The database client encrypts the SSL connections that are used to transmit data. However, the database client does not validate the RDS instance.
verify-ca: The database client encrypts the SSL connections that are used to transmit data and validates the RDS instance.
verify-full: The database client encrypts the SSL connections that are used to transmit data, validates the RDS instance, and verifies that the CN or DNS specified in the server certificate is consistent with the endpoint that is configured during connection establishment.

Connect to the RDS instance.

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

The following table provides details of how to obtain the values of the preceding parameters from the ApsaraDB RDS console.


Parameter	Where to obtain
Endpoint	The endpoint that is protected by SSL encryption for the RDS instance. This endpoint is specified by the Protected Host parameter on the SSL Encryption tab of the Data Security page.
Username	The username of the account that is used to log on to the RDS instance. You can obtain the username from the Accounts page.
Port number	The port number that is used to connect to the RDS instance. The default port number is 5432. If you have changed the default port number, you can obtain the new port number from the Database Connection page.
Database name	The name of the database that you want to connect on the RDS instance. You can obtain the name of the database from the Database Connection page. The postgres database is the default system database. Do not perform operations on the postgres database.

Use JDBC to connect to the RDS instance over SSL connections

Download the following files to your computer:
- client.crt and client.key: If you have configured a client CA certificate in the ApsaraDB RDS console, you must obtain these files. Otherwise, these files are optional. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance.
- ca1.crt: For more information about how to obtain this file, see Configure SSL encryption for an ApsaraDB RDS for PostgreSQL instance or Configure a custom certificate on an ApsaraDB RDS for PostgreSQL instance.
Convert the client.key file to the PK8 format.
```
openssl pkcs8 -topk8 -inform PEM -in client.key -outform der -out client.pk8 -v1 PBE-MD5-DES
# Enter the password that is used to connect to the RDS instance.
Enter Encryption Password:
Verifying - Enter Encryption Password:
```
Warning You must run the openssl command on the host on which your application resides to convert the client.key file to the PK8 format. If you do not run the command on the host on which your application resides, the following error messages may appear:
- org.postgresql.util.PSQLException: Could not decrypt SSL key file C:/Users/XXX/XXX/client.pk8
- org.postgresql.util.PSQLException: SSL error: Received fatal alert: unexpected_message

In this example, the database 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>

Compile a code snippet that is used to establish a JDBC-based SSL connection to the RDS instance.

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

 // Specify the endpoint that is used to connect to the RDS instance.     
 String hostname = "pgm-bpxxxxx.pg.rds.aliyuncs.com";   
 // Specify the port number that is used to connect to the RDS instance.
 String port = "5432";   
 // Specify the name of the database to which you want to connect on the RDS instance.
 String dbname = "postgres";  

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

 Properties properties = new Properties();
 // Specify the username that is used to connect to the specified database on the RDS instance.
 properties.setProperty("user", "username"); 
 // Specify the password that is used to connect to the specified database on the RDS instance.
 properties.setProperty("password", "*****");   
 // Specify the save path of the file that contains the client certificate. 
 String path= "D:\\ssl\\"; 

 // Configure SSL encryption.
 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 postgres database contains a table named example from which data is queried. 
      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();
  }