This topic describes how to connect to an ApsaraDB RDS for PostgreSQL instance from a database client over SSL. 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

Use pgAdmin to connect to an RDS instance over SSL

  1. Start the pgAdmin 4 client.
    Note If the pgAdmin client runs a later version and you log on the pgAdmin client for the first time, you must specify a master password that is used to protect the saved passwords and other credentials.
  2. Right-click Servers and choose Create > Server.
  3. On the General tab of the Create - Server dialog box, enter the name of the server where the pgAdmin client runs.
  4. Click the Connection tab and enter the information that is used to connect to the RDS instance.
    Parameter Description
    Hostname/address Enter the endpoint of the RDS instance. If you want to connect to the RDS instance over an internal network, enter the internal endpoint of the RDS instance. If you want to connect to the RDS instance over the Internet, enter the public endpoint of the RDS instance. For more information, see View and change the internal and public endpoints and port numbers of an ApsaraDB RDS for PostgreSQL instance.
    Port Enter the port number that is associated with the endpoint.
    Username Enter the username of the account that you use to log on to the RDS instance.
    Password Enter the password of the account that you use to log on to the RDS instance.
  5. 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 by using 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 by using 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 by using SSL connections. For more information, see Configure a client CA certificate on an ApsaraDB RDS for PostgreSQL instance. Set the SSL mode parameter to Require, Verify-CA, or Verify-Full.
    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 at connection establishments.
    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, the file that contains the server CA certificate is stored in the C:\CA\ path. You can download and decompress the file to a folder on your computer.
  6. Click Save.
  7. If the information that you entered is correct, the following page appears, which indicates that the connection to RDS instance is successful.
    Note The postgres database is the default system database of the RDS instance. Do not perform operations on this database.

Use PostgreSQL CLI to connect to an RDS instance over SSL

Note This method uses the PostgreSQL CLI of PostgreSQL to connect to an RDS instance over SSL. Make sure that PostgreSQL is installed on your computer. For more information, see PostgreSQL documentation.
  1. In the var/lib/pgsql path, create a folder named .postgresql. 
    mkdir /var/lib/pgsql/.postgresql
  2. Copy the following files to the .postgresql folder: 
    cp client.crt client.key ca1.crt /var/lib/pgsql/.postgresql/
    Note In this example, the client.crt file, client.key file, and ca1.crt file are stored in the current path that is opened.
  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 enable the edit 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"
  6. Press Esc to exit the edit mode. Then, enter :wq to save the file and exit.
  7. Reload the environment variables. 
    source .bash_profile
  8. 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 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 at connection establishments.
  9. Connect to the RDS instance. 
    psql -h <Endpoint> -U <Username> -p <Port number> -d <Database name>
    The following table provides details about 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 that is used to connect 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 Databases page. The postgres database is a default system database. Do not perform operations on the postgres database.

Use JDBC to connect to an RDS instance over SSL

  1. Download the following files to your computer:
  2. 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:
  3. 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>
  4. Compile a code snippet that is used to establish a JDBC-based SSL connection to the RDS instance: 
     // 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 + "/" + "root.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, which can be set to require, verify-ca, or 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();
  }