Hologres:Transmission encryption

Use the PostgreSQL CLI to connect to the Hologres instance

• Command

  PG_USER=<AccessKey ID> 
  PG_PASSWORD=<AccessKey Secret> 
  PG_SSLMODE=<SSL Mode> 
  PG_SSLROOTCERT=<certificate folder> 
  PGSSLMODE=$PG_SSLMODE PGSSLROOTCERT=$PG_SSLROOTCERT PGUSER=$PG_USER PGPASSWORD=$PG_PASSWORD psql -p <Port> -h <Endpoint> -d <Database>

• Parameters

  Parameter	Description
  AccessKey ID	The AccessKey ID of the Alibaba Cloud account. You can obtain the AccessKey ID from the AccessKey Pair page. We recommend that you configure environment variables and obtain the AccessKey ID and AccessKey secret from the environment variables. This helps reduce the leak risk.
  AccessKey Secret	The AccessKey secret of the Alibaba Cloud account. You can obtain the AccessKey secret from the AccessKey Pair page. We recommend that you configure environment variables and obtain the AccessKey ID and AccessKey secret from the environment variables. This helps reduce the leak risk.
  SSL Mode	The encryption mode in which the PostgreSQL client connects to the Hologres instance. Valid values: require: The client uses SSL to encrypt the connections that are used to transmit data. verify-ca: The client uses SSL to encrypt the connections that are used to transmit data and validates the Hologres instance. verify-full: The client uses SSL to encrypt the connections that are used to transmit data, validates the Hologres instance, and checks whether the CN or DNS specified in the CA certificate is consistent with the Hologres endpoint that is specified during the connection establishment. disable: The client does not use SSL for encrypted transmission.
  certificate folder	The storage path of the CA certificate. If you set SSL Mode to verify-ca or verify-full, you must configure this parameter.
  Port	The public port number of the Hologres instance. Example: 80.
  Endpoint	The public endpoint of the Hologres instance. Example: xxx-cn-hangzhou.hologres.aliyuncs.com.
  Database	The name of the Hologres database to which you want to connect. After you purchase a Hologres instance, a database named postgres is automatically created. You can connect to the postgres database. However, limited resources are allocated to this database. We recommend that you go to the Hologres console and create a database to ensure sufficient resources for your business. For more information, see Create a database. Example: mydb.

• Verification

  If you set the PGSSLMODE parameter to require and the following message appears when you connect to Hologres, the connection is encrypted by using SSL.

Use a JDBC driver to connect to the Hologres instance

If you use a JDBC driver to connect to the Hologres instance, you can use the ssl and sslmode parameters to specify whether to enable SSL-encrypted transmission. The connection results in Hologres vary based on the value of each parameter. The following table describes the mappings between values and results.

Sample code

// Specify the endpoint of the Hologres instance.
 String hostname = "hgxxxxxxx-cn-hangzhou-vpc.hologres.aliyuncs.com:80";
 // Specify the port number of the Hologres instance.
 String port = "80";
 //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 AccessKey ID that is used to connect to the database. We recommend that you configure environment variables and obtain the AccessKey ID from the environment variables. 
 properties.setProperty("user", "accessid");
 // Specify the AccessKey secret that is used to connect to the database. We recommend that you configure environment variables and obtain the AccessKey secret from the environment variables. 
 properties.setProperty("password", "accesskey");

  // Use SSL for encrypted transmission.
  properties.setProperty("ssl", "true");

  // Specify the public key of the CA.
  properties.setProperty("sslrootcert", path + "/" + "hologres_certificate.crt");

  // Specify the SSL connection mode. Valid values: require, verify-ca, and verify-full.
  properties.setProperty("sslmode", "verify-full");

  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();
  }