This topic describes how to configure the fully encrypted database feature on an ApsaraDB RDS for PostgreSQL instance.

Prerequisites

  • RDS instance
  • Local client
    • Java SE 8 or a later version is installed.
    • The EncDB dependency package that matches the minor engine version of the RDS instance is downloaded.
      Minor engine version of the RDS instanceVersion of EncDB on the RDS instanceEncDB dependency package on the local client
      202105311.1.5libencdb-1.2.1.jar
      202109301.1.6libencdb-1.2.4.jar
      20211031 to 202201301.1.7libencdb-1.2.5.jar
      20220228 to 202207301.1.9libencdb-1.2.6.jar
      20220830 to 20221030
      Note When the minor engine version of RDS PostgreSQL 15 is 20221030, the supporting plug-in version is 1.1.12.
      		1.1.11libencdb-1.2.8.jar
      20221230 or a later version1.1.12libencdb-1.2.10.jar
    • A master encryption key (MEK) is generated.

      An MEK is a hexadecimal string that is 16 bytes in length. An example MEK is 0x00112233445566778899aabbccddeeff. You can use a random() function that is provided by a password generator or by a programming language to generate an MEK. For example, you can use the OpenSSL password generator, which allows you to run the openssl rand -hex 16 command to generate an MEK.

      Warning Your MEK is the root credentials that you use to access encrypted data. The fully encrypted database feature cannot generate or back up MEKs. You must manually create an MEK. If you lose your MEK, you can no longer access the existing encrypted data. We recommend that you back up your MEK.

Configure the fully encrypted database feature on the RDS instance

The fully encrypted database feature is deployed as an extension named EncDB on the RDS instance. This section describes how to install the EncDB extension and create an encrypted table on the RDS instance.

Note Before you begin, you must connect to the RDS instance. For more information, see Connect to an ApsaraDB RDS for PostgreSQL instance.
  1. Install the EncDB extension. 
    CREATE EXTENSION encdb;
  2. Create an encrypted table.
    When you create an encrypted table, you can specify sensitive fields and replace the original data types of the sensitive fields with the matching encrypted data types. For example, a regular table that contains the following fields is created:
    CREATE TABLE example (
  id      INTEGER,
  name    VARCHAR,
  price   INTEGER,
  miles   REAL,
  secret  TEXT,
  PRIMARY KEY (id)
);
    If you specify the price, miles, and secret fields from the regular table as sensitive fields, the original data types of the fields are replaced with the matching encrypted data types:
    CREATE TABLE example (
  id     INTEGER,
  name   VARCHAR,
  price  rnd_type,
  miles  det_type,
  secret rnd_type,
  PRIMARY KEY (id)
);
    Note
    • The fully encrypted database feature supports the following two encrypted data types: det_type and rnd_type. The det_type data type is a type of deterministic encryption, which generates the same ciphertext for specific data regardless of how many times the data is encrypted. The rnd_type data type is a type of probabilistic encryption, which generates a different ciphertext for specific data every time the data is encrypted.
    • When you use the det_type and rnd_type data types in your application, you must convert the host variables of the det_type and rnd_type to strings.
    • You cannot alter an existing regular table into an encrypted table by using the ALTER TABLE statement. If you want to encrypt the data of an existing regular table, you must create an encrypted table, use an SDK to encrypt the data of the regular table, and then import the encrypted data into the encrypted table.

Configure the fully encrypted database feature on the local client

This section describes how to connect to the RDS instance and encrypt or decrypt data.

Note In this section, Maven is used to configure the client.
  1. Install the EncDB dependency packages. 
    mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=<The name of the JAR package> -Dversion=<The version of the JAR package> -Dpackaging=jar -Dfile=<The save path of the JAR package>/<The name of the JAR package.jar>
    Example:
    mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=libencdb -Dversion=1.2.8 -Dpackaging=jar -Dfile=D:\encdb\libs\libencdb-1.2.8.jar
    Note In the preceding example, the EncDB dependency packages are saved in the D:\encdb\libs path.
  2. Add dependencies.
    Add the following dependencies to the pom.xml file:
       <dependency>
      <groupId>com.alibaba.encdb</groupId>
      <artifactId>libencdb</artifactId>
      <version>1.2.8</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>
   <!-- https://mvnrepository.com/artifact/com.alibaba/fastjson -->
   <dependency>
     <groupId>com.alibaba</groupId>
     <artifactId>fastjson</artifactId>
     <version>1.2.82</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>
  3. Use the fully encrypted database feature.
    1. Connect to the RDS instance.
      Note The MEK in the following example is only for test purposes. You must replace this MEK with your actual MEK. The Mek and EncAlgo parameters are used only as the encryption module and decryption module of the EncDB SDK. These parameters are not directly passed to the RDS instance. 
       try {
    Class.forName("org.postgresql.Driver");
} catch (ClassNotFoundException e) {
    e.printStackTrace();
}

 String hostname = "pgm-uf6qw1ap115gr2****.pg.rds.aliyuncs.com";
 String port = "5432";
 String dbname = "encdbtest";
 String username = "username";
 String password = "userpassword";
 String tableName = "example";
 String mek = "0x00112233445566778899aabbccddeeff";

 String dbUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname + "?binaryTransfer=true";
 try {
    dbConnection = DriverManager.getConnection(dbUrl, username, password);
} catch (SQLException throwables) {
    throwables.printStackTrace();
}

 /*Create an EncDB SDK instance.*/
 EncdbSDK sdk = EncdbSDKBuilder.newInstance()
         .setDbConnection(dbConnection)
         .setMek(mek)
         .setEncAlgo(Constants.EncAlgo.SM4_128_CBC)
         .setDekGenMode(Constants.DekGenMode.LOCAL)
         .setSdkMode(Constants.SDKMode.Crypto)
         .build();

 Cryptor crypto = sdk.getCryptor();
      The following table describes the crucial parameters in the preceding example.
      ParameterExample (string type)Description
      Mek0x00112233445566778899aabbccddeeff

      The MEK that is used to access encrypted data.

      Valid values: 16-byte hexadecimal strings.

      EncAlgoSM4_128_CBC

      The encryption algorithm that is used.

      Valid values:
      • AES_128_GCM
      • AES_128_CBC
      • SM4_128_CBC (This is the default value.)
      DekGenModeLOCAL

      The method that is used to generate a data encryption key (DEK). Set the value to LOCAL.

      SdkModeCryptoThe SDK mode that is used. Set the value to Crypto.
    2. Insert encrypted data. 
       PreparedStatement stmt = dbConnection.prepareStatement("insert into " + tableName + " (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, crypto.encrypt(tableName, "price", String.valueOf(price)));
 stmt.setBytes(4, crypto.encrypt(tableName, "miles", String.valueOf(miles)));
 stmt.setBytes(5, crypto.encrypt(tableName, "secret", secret));
 stmt.execute();
    3. Query decrypted data. 
       String sqlCmd = "SELECT * from " + tableName + " where miles = ?";
 PreparedStatement stmt = dbConnection.prepareStatement(sqlCmd);
 stmt.setBytes(1, crypto.encrypt(tableName, "miles", "12.34"));
 ResultSet rs = stmt.executeQuery();
 while (rs.next()) {
     int id = rs.getInt(1);
     String name = rs.getString(2);

     int price = Integer.parseInt(crypto.decryptString(rs.getBytes(3)));
     float miles = Float.parseFloat(crypto.decryptString(rs.getBytes(4)));
     String text = crypto.decryptString(rs.getBytes(5));

     System.out.println(id + ", " + name + ", " + price + ", " + miles + ", " + text);
 }
    The following query results are returned:
    • When you initiate requests on the local client to query encrypted data, the encrypted data that you query is decrypted into plaintext data. 
      1, name, 1234, 12.34, aliyun
    • Encrypted data cannot be viewed on the RDS instance. This helps protect your data from security threats inside and outside the cloud at all times. 
      select * from example;
       id  name                                   price                                                                     miles                                                                     secret
----+------+--------------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------------------------------------------------------------
  1  name  \xdf4901df087c6a3e0325175bb76942c684191a8dda2a8d0c35f295dc1e30eaeaa0c0e3  \x315102ea5ab8a659066ab672e6dfbfd89a3a2b360bf6efe3787931e00f61af05f7408c  \xed4903dfd1bda1a89ad6aa7e8905c0e6305e15db4bc9ce2d2cfac9e25094d2a3ed367d
(1 row)