PolarDB:Use the always-confidential database feature from a client

EncJDBC

Download drivers and configure dependencies

1. Download the EncJDBC driver.

   EncJDBC depends on PostgreSQL drivers provided by the community.

   PolarDB for PostgreSQL minor version	Version of EncDB on the PolarDB for PostgreSQL cluster	EncDB dependency package on the client
   PolarDB for PostgreSQL 14: 20231030 (14.9.14.0) or later	1.1.13	encjdbc-1.0.6.jar

2. Configure Maven dependencies.

   Note

   In this section, Maven is used to configure the client.

   1. Run the following command to install the EncJDBC dependency package to your on-premises repository:

      mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=<Name of the installed JAR package> -Dversion=<Version of the installed JAR package> -Dpackaging=jar -Dfile=<File name of the installed JAR package>

      Example:

      mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=encjdbc -Dversion=1.0.6 -Dpackaging=jar -Dfile=D:\encdb\libs\encjdbc-1.0.6.jar

      Note

      • In this example, the EncDB dependency package is stored in the D:\encdb\libs path.

      • In this example, Maven 3.9.2 is used. If you use an earlier version of Maven, upgrade the Maven version and try again.

   2. After you install the EncJDBC dependency package in your on-premises repository, add the following dependencies to the pom.xml configuration file of your Maven project:

      <dependencies>
         ...
         <dependency>
            <groupId>com.alibaba.encdb</groupId>
            <artifactId>encjdbc</artifactId>
            <version>1.0.6</version>
         </dependency>
         <!-- https://mvnrepository.com/artifact/org.bouncycastle/bcprov-jdk15on -->
         <dependency>
           <groupId>org.bouncycastle</groupId>
           <artifactId>bcprov-jdk15on</artifactId>
           <version>1.62</version>
         </dependency>
         <!-- https://mvnrepository.com/artifact/org.bouncycastle/bcpkix-jdk15on -->
         <dependency>
           <groupId>org.bouncycastle</groupId>
           <artifactId>bcpkix-jdk15on</artifactId>
           <version>1.62</version>
         </dependency>
         <dependency>
           <groupId>com.alibaba.fastjson2</groupId>
           <artifactId>fastjson2</artifactId>
           <version>2.0.2</version>
         </dependency>
         <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
         <dependency>
           <groupId>org.postgresql</groupId>
           <artifactId>postgresql</artifactId>
           <version>42.2.23</version>
         </dependency>
         <dependency>
           <groupId>com.google.guava</groupId>
           <artifactId>guava</artifactId>
           <version>24.1.1-jre</version>
         </dependency>
         <dependency>
           <groupId>com.alibaba</groupId>
           <artifactId>druid</artifactId>
           <version>1.2.4</version>
         </dependency>
         <dependency>
           <groupId>org.jgrapht</groupId>
           <artifactId>jgrapht-core</artifactId>
           <!-- jgrapht does not support java 1.8 since 1.5.0 -->
           <version>1.4.0</version>
        </dependency>
         ...
      </dependencies>

Query data from the client (sample code)

• URL configuration description

  You can use EncJDBC in the same manner that you use Java Database Connectivity (JDBC). Before you use EncJDBC, you must perform the following configuration to ensure data security:

  // The connection information such as the domain name (hostname), port number (port), database name (dbname), username (username), and password (password).
  
  // For more information, see the "Data security-related parameters" section of this topic.
  String mek=...;
  String encAlgo=...;
  
  String dbUrl = String.format("encjdbc:postgresql://%s:%s/%s?mek=%s&enc_algo=%s", hostname, port, dbname, mek, encAlgo);
  Class.forName("com.alibaba.encdb.encjdbc.EncDriver");
  
  Connection dbConnection = DriverManager.getConnection(dbUrl, username, password);
  
  // ... Initiate a query ...

  Data security-related parameters

  Parameter	Example (string type)	Description
  mek	0x00112233445566778899aabbccddeeff	The MEK that is specified by the data owner. You can obtain the MEK by using one of the following methods: Use a password generation tool such as OpenSSL or openssl rand -hex 16, invoke the random function in a programming language, or use a third-party key management service (KMS). Valid values: a hexadecimal string of 32 characters in length. Warning An MEK is the root credential that you use to access encrypted data. To ensure security, the always-confidential database feature does not generate, store, or back up your MEK. You must manually generate an MEK and make sure that the MEK is securely stored. If you lose your MEK, you can no longer access the existing encrypted data. We recommend that you back up your MEK.
  enc_algo	SM4_128_CBC	The encryption algorithm. Valid values: Internationally accepted algorithms: AES_128_GCM AES_128_CBC AES_128_ECB ShangMi (SM) algorithms: SM4_128_GCM SM4_128_CBC (default) SM4_128_ECB Note The CTR encryption algorithm is not supported. The AES_128_ECB and SM4_128_ECB encryption algorithms cannot ensure high security. We recommend that you select other encryption algorithms that provide higher security.
  enc_scheme	RND	The encryption method. Valid values: RND (default): probabilistic encryption. DET: deterministic encryption. Note This parameter is invalid if you set the enc_algo parameter to AES_128_ECB or SM4_128_ECB.
  dek_gen_mode	ENCLAVE	The method that is used to generate a DEK. Valid values: ENCLAVE (default): The database server generates a DEK in the trusted enclave. LOCAL: The client generates a DEK.
  stateless	true	The Stateless attribute that is applied on the always-confidential database. Valid values: true (default): The generated MEK remains valid after the client is disconnected from the PolarDB for PostgreSQL cluster. false: The generated MEK becomes invalid after the client is disconnected from the PolarDB for PostgreSQL cluster.

  Note

  • You can use ampersands (&) to concatenate multiple parameters.

  • The mek parameter and other parameters are configured on the client side and transmitted to the server side by using envelope encryption. In the process, the confidentiality of the mek value is ensured.

• Complete sample code

  // The connection information of your cluster, including the domain name (hostname), port number (port), database name (dbname), username (username), and password (password).
  String hostname = "hostname";
  String port = "port";
  String dbname = "db";
  String username = "user";
  String password = "password";
  
  String mek="00112233445566778899aabbccddeeff"; // This is an example value. We recommend that you use a more complex MEK.
  String encAlgo="SM4_128_CBC";
  
  String dbUrl = String.format("encjdbc:postgresql://%s:%d/%s?mek=%s&enc_algo=%s", hostname, port, dbname, mek, encAlgo);
  Class.forName("com.alibaba.encdb.encjdbc.EncDriver");
  Connection dbConnection = DriverManager.getConnection(dbUrl, username, password);
  
  // create table
  dbConnection.createStatement().executeUpdate("DROP TABLE IF EXISTS example");
  dbConnection.createStatement().executeUpdate("CREATE TABLE example (id INTEGER, name VARCHAR, price enc_int4, miles enc_float4, secret enc_text, PRIMARY KEY (id))");
  
  // insert data
  PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO example (id, name, price, miles, secret) VALUES(?,?,?,?,?)");
  int price = 1234;
  float miles = 12.34f;
  String secret = "aliyun";
  stmt.setInt(1, 1);
  stmt.setString(2, "name");
  stmt.setInt(3, price);
  stmt.setFloat(4, miles);
  stmt.setString(5, secret);
  stmt.execute();
  
  // check plaintext data
  String sqlCmd = "SELECT * FROM example WHERE  price > ?";
  PreparedStatement stmt = dbConnection.prepareStatement(sqlCmd);
  stmt.setInt(1, 100);
  ResultSet rs = stmt.executeQuery();
  while (rs.next()){
      int id = rs.getInt(1);
      String name = rs.getString(2);
      int price = rs.getInt(3);
      float miles = rs.getFloat(4);
      String secret = rs.getString(5);
      System.out.println(id + ", " + name + ", " + price + ", " + miles + ", " + secret);  
  }

  Sample output:

  1, name, 1234, 12.34, aliyun

In the preceding example for EncJDBC, only the driver loading and URL configurations are modified. Other data operations can be performed in the same manner as common database operations are performed. You do not need to modify business code.

EncDB SDK

Download drivers and configure dependencies

1. Download the EncDB SDK driver.

   The EncDB SDK depends on PostgreSQL drivers provided by the community.

   PolarDB for PostgreSQL minor version	Version of EncDB on the PolarDB for PostgreSQL cluster	EncDB dependency package on the client
   PolarDB for PostgreSQL 14: 20231030 (14.9.14.0) or later	1.1.13	libencdb-1.2.12.jar

2. Configure Maven dependencies.

   Note

   In this section, Maven is used to configure the client.

   1. Run the following command to install the EncDB SDK dependency package to your on-premises repository:

      mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=<Name of the installed JAR package> -Dversion=<Version of the installed JAR package> -Dpackaging=jar -Dfile=<File name of the installed JAR package>

      Example:

      mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=encjdbc -Dversion=1.2.12 -Dpackaging=jar -Dfile=D:\encdb\libs\libencdb-1.2.12.jar

      Note

      • In this example, the EncDB dependency package is stored in the D:\encdb\libs path.

      • In this example, Maven 3.9.2 is used. If you use an earlier version of Maven, upgrade the Maven version and try again.

   2. After you install the EncDB SDK dependency package in your on-premises repository, add the following dependencies to the pom.xml configuration file of your Maven project:

      <dependencies>
         ...
         <dependency>
            <groupId>com.alibaba.encdb</groupId>
            <artifactId>libencdb</artifactId>
            <version>1.2.12</version>
         </dependency>
         <!-- https://mvnrepository.com/artifact/org.bouncycastle/bcprov-jdk15on -->
         <dependency>
           <groupId>org.bouncycastle</groupId>
           <artifactId>bcprov-jdk15on</artifactId>
           <version>1.70</version>
         </dependency>
         <!-- https://mvnrepository.com/artifact/org.bouncycastle/bcpkix-jdk15on -->
         <dependency>
           <groupId>org.bouncycastle</groupId>
           <artifactId>bcpkix-jdk15on</artifactId>
           <version>1.70</version>
         </dependency>
         <dependency>
            <groupId>com.google.code.gson</groupId>
            <artifactId>gson</artifactId>
            <version>2.10.1</version>
          </dependency>
         <!-- https://mvnrepository.com/artifact/org.postgresql/postgresql -->
         <dependency>
           <groupId>org.postgresql</groupId>
           <artifactId>postgresql</artifactId>
           <version>42.2.23</version>
         </dependency>
         <dependency>
           <groupId>com.google.guava</groupId>
           <artifactId>guava</artifactId>
           <version>31.1-jre</version>
         </dependency>
         ...
      </dependencies>

Query data from the client (sample code)

• SDK configuration description

  Before you manage ciphertext data that you receive or send, you must use the EncDB SDK to encrypt or decrypt the data.

  The EncDB SDK provides the required API operations for configuring parameters related to data security when you initialize SDK objects. In most cases, you need to only configure the MEK specified by the setMek parameter and the required encryption algorithm specified by the setEncAlgo parameter. We recommend that you use the default settings for other parameters.

  // Obtain the connection information including the domain name (hostname), port number (port), database name (dbname), username (username), and password (password).
  
  // Establish a database connection. All JDBC versions are supported.
  String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
  Class.forName("org.postgresql.Driver");
  Connection dbConnection = DriverManager.getConnection(dbUrl, username, password);
  
  // Initialize the SDK.
  String mek=...;
  Constants.EncAlgo encAlgo=...;
  EncdbSDK sdk = EncdbSDKBuilder.newInstance()
          .setDbConnection(connection)
          .setMek(mek)
          .setEncAlgo(encAlgo)
          .build();
  Cryptor cryptor = sdk.getCryptor();
  
  // Invoke the API operation for data encryption or decryption.
  // byte[] cipherBytes = cryptor.encrypt(...);
  // XXX value = cryptor.decryptXXX(...):
  
  // ... Initiate a query ...

  Initialize SDK-related parameters

  Parameter	Example (string type)	Description
  Mek	0x00112233445566778899aabbccddeeff	The MEK that is specified by the data owner. You can obtain the MEK by using one of the following methods: Use a password generation tool such as OpenSSL or openssl rand -hex 16, invoke the random function in a programming language, or use a third-party KMS. Valid values: Hexadecimal strings of 32 characters in length and binary numbers of 16 bytes in length. Warning An MEK is the root credential that you use to access encrypted data. To ensure security, the always-confidential database feature does not generate, store, or back up your MEK. You must manually generate an MEK and make sure that the MEK is securely stored. If you lose your MEK, you can no longer access the existing encrypted data. We recommend that you back up your MEK.
  EncAlgo	SM4_128_CBC	The encryption algorithm. Valid values: Internationally accepted algorithms: AES_128_GCM AES_128_CBC AES_128_ECB SM algorithms: SM4_128_GCM SM4_128_CBC (default) SM4_128_ECB Note The CTR encryption algorithm is not supported. The AES_128_ECB and SM4_128_ECB encryption algorithms cannot ensure high security. We recommend that you select other encryption algorithms that provide higher security.
  EncScheme	RND	The encryption method. Valid values: RND (default): probabilistic encryption. DET: deterministic encryption. Note This parameter is invalid if you set the EncAlgo parameter to AES_128_ECB or SM4_128_ECB.
  DekGenMode	ENCLAVE	The method that is used to generate a DEK. Valid values: ENCLAVE (default): The database server generates a DEK in the trusted enclave. LOCAL: The client generates a DEK.
  SdkMode	Default	The SDK mode. Valid values: Default (default): All ciphertext data types provided by Basic Edition and Hardware-enhanced Edition are supported.
  Stateless	true	The Stateless attribute that is applied on the confidential database. Valid values: true (default): The generated MEK remains valid after the client is disconnected from the PolarDB for PostgreSQL cluster. false: The generated MEK becomes invalid after the client is disconnected from the PolarDB for PostgreSQL cluster.

  Note

  The mek parameter and other parameters are configured on the client side and transmitted to the server side by using envelope encryption. In the process, the confidentiality of the mek value is ensured.

• Complete sample code

  // The connection information of your cluster, including the domain name (hostname), port number (port), database name (dbname), username (username), and password (password).
  String hostname = "hostname";
  String port = "port";
  String dbname = "db";
  String username = "user";
  String password = "password";
  
  // Establish a database connection.
  String dbUrl = String.format("jdbc:postgresql://%s:%s/%s?binaryTransfer=true", hostname, port, dbname);
  Class.forName("org.postgresql.Driver");
  Connection dbConnection = DriverManager.getConnection(dbUrl, username, password);
  
  // Initialize the SDK.
  String mek="00112233445566778899aabbccddeeff"; // This is an example value. We recommend that you use a more complex MEK.
  Constants.EncAlgo encAlgo=Constants.EncAlgo.SM4_128_CBC;
  EncdbSDK sdk = EncdbSDKBuilder.newInstance()
          .setDbConnection(connection)
          .setMek(mek)
          .setEncAlgo(encAlgo)
          .build();
  Cryptor cryptor = sdk.getCryptor();
  
  // create table
  dbConnection.createStatement().executeUpdate("DROP TABLE IF EXISTS example");
  dbConnection.createStatement().executeUpdate("CREATE TABLE example (id INTEGER, name VARCHAR, price enc_int4, miles enc_float4, secret enc_text, PRIMARY KEY (id))");
  
  // insert data
  PreparedStatement stmt = dbConnection.prepareStatement("INSERT INTO example (id, name, price, miles, secret) VALUES(?,?,?,?,?)");
  int price = 1234;
  float miles = 12.34f;
  String secret = "aliyun";
  stmt.setInt(1, 1);
  stmt.setString(2, "name");
  stmt.setBytes(3, cryptor.encrypt("example", "price", price));
  stmt.setBytes(4, cryptor.encrypt("example", "miles", miles));
  stmt.setBytes(5, cryptor.encrypt("example", "secret", secret));
  stmt.execute();
  
  // check plaintext data
  String sqlCmd = "SELECT * FROM example WHERE  price > ?";
  PreparedStatement stmt = dbConnection.prepareStatement(sqlCmd);
  stmt.setBytes(1, cryptor.encrypt("example", "price", 100));
  ResultSet rs = stmt.executeQuery();
  while (rs.next()) {
      int id = rs.getInt(1);
      String name = rs.getString(2);
      int price = cryptor.decryptInt(rs.getBytes(3));
      float miles = cryptor.decryptFloat(rs.getBytes(4));
      String text = cryptor.decryptString(rs.getBytes(5));
      System.out.println(id +", " + name + ", " + price + ", " + miles + ", " + text);
  }

  Sample output:

  1, name, 1234, 12.34, aliyun

References

Introduction to the Java SDK module

The EncDB SDK contains the following Java feature modules:

// Creates an EncdbSDKBuilder instance. Use this method to construct EncdbSDKBuilder. 
EncdbSDKBuilder newInstance();
// Required. Configure a database connection for libencdb-related key management. The connection can be different from the database connection used in the actual business scenario. 
EncdbSDKBuilder setDbConnection(java.sql.Connection dbConnection);
// Required. Specify the type of the client that is connected to libencdb. If you use a confidential MySQL database, specify MYSQL_PROXY.
EncdbSDKBuilder setKeyMgmtType(KeyMgmtType kmType);
// Required. Specify an MEK. The MEK must be a 16-byte byte[] object or a hexadecimal string that has 32 characters in length.
EncdbSDKBuilder setMek(byte[] mek);
EncdbSDKBuilder setMek(String mekStr);
// Optional. Configure the encryption algorithm. Valid values: AES_128_GCM, AES_128_CBC, AES_128_ECB, SM4_128_CBC, SM4_128_ECB, and SM4_128_GCM. If you do not configure the encryption algorithm, SM4_128_CBC is used. 
EncdbSDKBuilder setEncAlgo(EncAlgo encAlgo);
// Constructs an EncdbSDK object after the preceding configurations are complete.
EncdbSDK build();

// Obtains a Cryptor object used to perform operations on the ciphertext or plaintext. 
Cryptor getCryptor();

/**
 * @brief Encrypt interface
 *
 * @param schemaName schema name, used together with table name and column name to look up user's data encryption key from the given database connection.
 *
 * @param tblName    table name, used together with column name to look up user's data encryption key from the given database connection.
 *
 * @param colName    column name, used together with table name. To use user's default data encryption key, set tblName = "default", colName="default"
 *
 * @param type       a valid encdb Type
 * @param val        value
 * @return           encrypted bytes
 */
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, byte[] val);
byte[] encrypt(String schemaName, String tblName, String colName, byte[] val);
byte[] encrypt(String tblName, String colName, EncType type, byte[] val);
byte[] encrypt(String tblName, String colName, byte[] val);
// for int
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, int val);
byte[] encrypt(String schemaName, String tblName, String colName, int val);
byte[] encrypt(String tblName, String colName, EncType type, int val);
byte[] encrypt(String tblName, String colName, int val);
// for long
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, long val);
byte[] encrypt(String schemaName, String tblName, String colName, long val);
byte[] encrypt(String tblName, String colName, EncType type, long val);
byte[] encrypt(String tblName, String colName, long val);
// for float
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, float val);
byte[] encrypt(String schemaName, String tblName, String colName, float val);
byte[] encrypt(String tblName, String colName, EncType type, float val);
byte[] encrypt(String tblName, String colName, float val);
// for double
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, double val);
byte[] encrypt(String schemaName, String tblName, String colName, double val);
byte[] encrypt(String tblName, String colName, EncType type, double val);
byte[] encrypt(String tblName, String colName, double val);
// for String
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, String val);
byte[] encrypt(String schemaName, String tblName, String colName, String val);
byte[] encrypt(String tblName, String colName, EncType type, String val);
byte[] encrypt(String tblName, String colName, String val);
// for BigDecimal
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, BigDecimal val);
byte[] encrypt(String schemaName, String tblName, String colName, BigDecimal val);
byte[] encrypt(String tblName, String colName, EncType type, BigDecimal val);
byte[] encrypt(String tblName, String colName, BigDecimal val);
// for Timestamp
byte[] encrypt(String schemaName, String tblName, String colName, EncType type, Timestamp val);
byte[] encrypt(String schemaName, String tblName, String colName, Timestamp val);
byte[] encrypt(String tblName, String colName, EncType type, Timestamp val);
byte[] encrypt(String tblName, String colName, Timestamp val);


/**
 * @brief Decrypt interface
 *
 * @param schemaName schema name, used together with table name and column name to look up user's data encryption key from the given database connection.
 *
 * @param tblName    table name, used together with column name to look up user's data encryption key.
 *
 * @param colName    column name, used together with table name. To use user's default data encryption key,
 *                set tblName = "default", colName="default"
 *
 * @param val        val can be in either hex binary format or pg bytea bytes, e.g., \\x00621c14
 * @return           decrypted value is in hex binary format or given type format
 */
byte[] decrypt(String schemaName, String tblName, String colName, byte[] val);
byte[] decrypt(String tblName, String colName, byte[] val);
byte[] decrypt(byte[] val);
// Decrypt interface for `int`
int decryptInt(byte[] val);
// Decrypt interface for `long`
long decryptLong(byte[] val);
// Decrypt interface for `float`
float decryptFloat(byte[] val);
// Decrypt interface for `double`
double decryptDouble(byte[] val);
// Decrypt interface for `String`
String decryptString(byte[] val);
// Decrypt interface for `BigDecimal`
BigDecimal decryptDecimal(byte[] val);
// Decrypt interface for `TimeStamp`
Timestamp decryptTimestamp(byte[] val);

psql

You can use psql to query data in confidential databases. For example, you can execute the SELECT * FROM example; statement by using psql.

In the preceding output, id and name are plaintext columns, and price, miles, and secret are ciphertext columns. Encrypted data cannot be viewed on the PolarDB for PostgreSQL cluster. This helps protect your data from security threats inside and outside the cloud at all times.

DMS console

You can use visual and interactive tools such as the DMS console to query data in confidential databases.

In the preceding figure, id and name are plaintext columns, and price, miles, and secret are ciphertext columns.