Hologres:Encryption in transit

Use the PSQL command line to connect to Hologres

• 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 description.

  Parameter	Description
  AccessKey ID	The AccessKey ID of your Alibaba Cloud account. Go to AccessKey Management to obtain the AccessKey ID. Use environment variables for the username and password to reduce the risk of credential leaks.
  AccessKey Secret	The AccessKey secret of your Alibaba Cloud account. Go to AccessKey Management to obtain the AccessKey secret. Use environment variables for the username and password to reduce the risk of credential leaks.
  SSL Mode	The encryption in transit mode for connecting to Hologres using PSQL. Valid values: require: Uses encryption in transit. Only the data link is encrypted. verify-ca: Encrypts the data link and authenticates the Hologres instance. verify-full: Encrypts the data link, authenticates the Hologres instance, and compares the CN or DNS in the certificate with the database endpoint 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. Create a new database for your business development. For more information, see Create a database. Example: mydb.

• Connection validation.

  If you set PGSSLMODE to require, the following prompt appears when you connect to Hologres. This indicates that an SSL-encrypted connection is used.

Use JDBC to connect to Hologres

When you use JDBC to connect to Hologres, you can use the ssl and sslmode connection configuration parameters to control whether to use SSL encryption in transit. The results in Hologres vary based on the values of these parameters, 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 for the database connection. Use an environment variable to reduce the risk of credential leaks.
 properties.setProperty("user", "accessid");
 // Set the password for the database connection. Use an environment variable to reduce the risk of credential leaks.
 properties.setProperty("password", "accesskey");

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

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

  // Configure the SSL mode. Valid values are 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, assume 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();
  }