Hologres:Enable SSL encryption

PSQL

• Connect to Hologres

  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. Obtain it from the AccessKey Management page. To minimize the risk of credential leaks, we recommend using environment variables instead of hardcoding your AccessKey ID.
  AccessKey Secret	The AccessKey secret of your Alibaba Cloud account. You can obtain the AccessKey secret from the AccessKey Management page. To minimize the risk of credential leaks, we recommend using environment variables instead of hardcoding your AccessKey ID.
  SSL Mode	The encryption mode for the PSQL connection to Hologres. The following values are supported: require: Enables SSL encryption and encrypts only the data sent. verify-ca: Encrypts the data sent and authenticates the Hologres instance. verify-full: Encrypts the data sent, 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: Disables SSL encryption.
  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-ap-southeast-1.hologres.aliyuncs.com.
  Database	The name of the Hologres database. Upon instance creation, Hologres automatically provisions a postgres database. While you can use postgres for initial connections, it has limited resources. For business development, it's recommended to create a new, dedicated 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.

JDBC

When you use JDBC to connect to Hologres, use the ssl and sslmode connection parameters to control whether to enable SSL encryption. 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-ap-southeast-1-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");

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

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

  // Configure the SSL mode.
  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();
  }