Hologres:Encryption in transit

Connect to Hologres using the PSQL command line

• Connection statement

  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>

• Parameter descriptions

  Parameter	Description
  AccessKey ID	The AccessKey ID of your Alibaba Cloud account. You can obtain the AccessKey ID from the AccessKey Management page. We recommend that you use environment variables to call the username and password to reduce the risk of credential leaks.
  AccessKey Secret	The AccessKey secret of your Alibaba Cloud account. You can obtain the AccessKey secret from the AccessKey Management page. We recommend that you use environment variables to call the username and password to reduce the risk of credential leaks.
  SSL Mode	The encryption mode for the PSQL connection to Hologres. The following values are supported: require: Uses encryption in transit and encrypts only the data link. verify-ca: Encrypts the data link and authenticates the Hologres instance. verify-full: Encrypts the data link, authenticates the Hologres instance, and verifies that the CN or DNS in the certificate matches the database endpoint that is configured for the connection. disable: Does not use encryption in transit.
  certificate folder	The storage path of the CA certificate. This parameter is required if the SSL Mode parameter is set to verify-ca or verify-full.
  Port	The public port 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. After you create a Hologres instance, the system automatically creates the postgres database. You can use the postgres database to connect to Hologres. However, this database is allocated few resources. For business development, we recommend that you create a new database. For more information, see Create a database. Example: mydb.

• Connection verification

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

Connect to Hologres using JDBC

When you use JDBC to connect to Hologres, you can use the ssl and sslmode connection parameters to control whether to use SSL encryption in transit. The results in Hologres vary based on the parameter values, as shown in the following table.

The following code provides an example.

 // Set the endpoint of the Hologres instance.
 String hostname = "hgxxxxxxx-cn-hangzhou-vpc.hologres.aliyuncs.com:80";
 // Set the port of the Hologres instance.
 String port = "80";
 // 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. We recommend that you use an environment variable.
 properties.setProperty("user", "accessid");
 // Set the password to connect to the database. We recommend that you use an environment variable.
 properties.setProperty("password", "accesskey");

  // Configure SSL access.
  properties.setProperty("ssl", "true");

  // Set the public key name of the certificate authority.
  properties.setProperty("sslrootcert", path + "/" + "hologres_certificate.crt");

  // Configure the SSL 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);
      // This example assumes that a table named 'example' exists in the 'postgres' database. The following code queries data from the 'example' 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();
  }