Connect to an RDS for PostgreSQL instance over SSL

pgAdmin

pgAdmin is the official tool recommended by PostgreSQL for database connections. When you download and install PostgreSQL from the PostgreSQL official website, the pgAdmin 4 client is automatically installed. This procedure describes how to connect to an ApsaraDB for RDS for PostgreSQL instance, using pgAdmin 4 V6.2.0 as an example.

If you do not want to install PostgreSQL, you can download the pgAdmin client separately to connect to remote databases.

1. Start the pgAdmin 4 client.

   Note

   When you log on to a recent version of the client for the first time, you must set a Master Password to protect saved passwords and other credentials.

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

   

3. On the General tab, set a name for the connection.

4. Click the Connection tab and enter the connection information for your instance.

   Parameter	Description
   Host name/address	The endpoint and port of the ApsaraDB RDS for PostgreSQL instance. To connect over an internal network, enter the internal endpoint and port of the instance. To connect over the internet, enter the public endpoint and port of the instance. You can view this information on the Database Connection page of your ApsaraDB RDS for PostgreSQL instance. For more information, see View and change endpoints and ports.
   Port
   Username	The username and password of your ApsaraDB RDS for PostgreSQL instance account. For information about how to create an account, see Create an account and a database.
   Password

5. Select the Parameters tab, add parameters for SSL authentication, and configure the certificates.

   Parameter	Description
   SSL mode	After you enable SSL encryption, your PostgreSQL instance can accept SSL connections. When connecting from a client, select a value for the SSL mode parameter based on the following scenarios: If you have not configured an access control list (ACL) for the database: To connect over SSL, set SSL mode to Require, Verify-CA, or Verify-Full. To connect without SSL, set SSL mode to Disable. If you have configured an access control list (ACL) for the database, you must connect over SSL. Set SSL mode to Require, Verify-CA, or Verify-Full. The modes are defined as follows: Require: Encrypts the connection but does not verify the database identity. Verify-CA: Encrypts the connection and verifies the database identity. Verify-Full: Encrypts the connection, verifies the database identity, and checks whether the Common Name (CN) or DNS name in the certificate matches the endpoint configured for the connection.
   Client certificate	If you followed the procedure in Configure a client CA certificate, you must configure this parameter. This parameter specifies the client certificate (client.crt).
   Client certificate key	If you followed the procedure in Configure a client CA certificate, you must configure this parameter. This parameter specifies the client private key (client.key).
   Root certificate	If you set SSL mode to Verify-CA or Verify-Full, you must configure this parameter. It specifies the path of the server CA certificate. Note In this example, configure the file paths for the client certificate (client.crt), client private key (client.key), and server CA certificate (server-ca.crt) based on your actual environment. This example uses a custom certificate. You can also configure a cloud certificate. For example, replace server-ca.crt with ApsaraDB-CA-Chain.pem.

6. Click Save.

   If the connection information is correct, the server is successfully added.

   Important

   The postgres database is the default system database for the ApsaraDB RDS instance. Do not perform any operations in this database.

psql

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

   mkdir /var/lib/pgsql/.postgresql

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

   • (Optional) The client certificate (client.crt) and client private key (client.key): These files are required if you have configured a client CA certificate in the ApsaraDB RDS console. For more information, see Configure a client CA certificate.

   • The server CA certificate: For more information, see Download a CA certificate or Generate a self-signed certificate.

   cp client.crt client.key server-ca.crt /var/lib/pgsql/.postgresql/

   Note

   • In this example, configure the file paths for the client certificate (client.crt), client private key (client.key), and server CA certificate (server-ca.crt) based on your actual environment.

   • This example uses a custom certificate. You can also use a cloud certificate. For example, replace server-ca.crt with ApsaraDB-CA-Chain.pem.

3. Change the permissions on the .postgresql folder and its contents.

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

4. Run the following command to edit the environment variables for the postgres user:

   vim /var/lib/pgsql/.bash_profile

5. Press i to enter edit mode and add the following content.

   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 edit mode, then enter :wq to save and exit.

7. Reload the environment variables.

   source .bash_profile

8. Set the SSL mode for the client connection.

   export PGSSLMODE="verify-full"

   After you enable SSL encryption, your PostgreSQL instance can accept SSL connections. When connecting from a client, select a value for the PGSSLMODE parameter based on the following scenarios:

   Does the database have client access control configured?	SSL required	PGSSLMODE value
   No	Yes	require, verify-ca, or verify-full
   	No	disable
   Yes	Yes	require, verify-ca, or verify-full

   Note

   The modes are defined as follows:

   • require: Encrypts the connection but does not verify the database identity.

   • verify-ca: Encrypts the connection and verifies the database identity.

   • verify-full: Encrypts the connection, verifies the database identity, and checks whether the Common Name (CN) or DNS name in the certificate matches the endpoint configured for the connection.

9. Connect to the database.

   psql -h <endpoint> -U <username> -p <port> -d <database_name>

   You can obtain the parameters from the ApsaraDB RDS console:

   Parameter	Source
   endpoint	The encrypted endpoint. You can obtain the endpoint from the Protected Host parameter on the SSL page.
   username	You can obtain the username on the Accounts page.
   port	The default port is 5432. If you changed the port number, you can obtain it from the Database Connection page.
   database_name	The postgres database is the default system database of the RDS instance. Do not perform any operations in this database. If you need to connect to another database that you created on the RDS instance, you can obtain the database name from the Databases page.

JDBC

1. Download the following files to your local device:

   • (Optional) The client certificate (client.crt) and client private key (client.key): These files are required if you have configured a client CA certificate in the ApsaraDB RDS console. For more information, see Configure a client CA certificate.

   • The server CA certificate: For more information, see Download a CA certificate or Generate a self-signed certificate.

2. Convert the client private key (client.key) to the PKCS#8 format.

   openssl pkcs8 -topk8 -inform PEM -in client.key -outform der -out client.pk8 -v1 PBE-MD5-DES
   # The command prompts you to enter a password, which is required for the connection.
   Enter Encryption Password:
   Verifying - Enter Encryption Password:

   Warning

   Run this command on the host where your application is running. Use the openssl command on that host to convert the private key to the PKCS#8 format. Otherwise, one of the following errors may occur:

   • 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

3. For a Maven project, add the PostgreSQL dependency to the pom.xml file.

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

4. In your application code, use JDBC to connect to PostgreSQL over an SSL connection.

   Note

   This example uses a custom certificate. You can also use a cloud certificate. For example, replace server-ca.crt with ApsaraDB-CA-Chain.pem.

   public class PgSslDemo {
       public static void main(String[] args) {
           // Set the endpoint of the ApsaraDB for RDS for PostgreSQL instance.
           String hostname = "pgm-bp1gclw58u36s6****.pg.rds.aliyuncs.com";
           // Set the port of the ApsaraDB for RDS for PostgreSQL instance.
           String port = "5432";
           // Set the name of the database to connect to.
           String dbname = "postgres";
   
           String jdbcUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname + "?binaryTransfer=true";
   
           Properties properties = new Properties();
           // Set the username to connect to the database.
           properties.setProperty("user", "test_user");
           // Set the password to connect to the database.
           properties.setProperty("password", "test_pwd");
           // Set the directory where the certificates are stored.
           String path = "D:\\ssl\\";
   
           // Enable SSL connection.
           properties.setProperty("ssl", "true");
           // Specify the file name of the server CA certificate.
           properties.setProperty("sslrootcert", path + "/" + "server-ca.crt");
           // Specify the file name of the client private key in PKCS#8 format.
           properties.setProperty("sslkey", path + "/" + "client.pk8");
           // Specify the file name of the client certificate.
           properties.setProperty("sslcert", path + "/" + "client.crt");
           // Enter the password that you set when converting the private key to the PKCS#8 format.
           properties.setProperty("sslpassword", "test_ssl_pwd");
   
           // Set 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);
               // This example assumes that a table named students01 exists in the postgres database.
               // Query data 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();
           }
       }
   }