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 instance Version of EncDB on the RDS instance EncDB dependency package on the local client
      20210531 1.1.5 libencdb-1.2.1.jar
      20210930 1.1.6 libencdb-1.2.4.jar
      20211031 to 20220130 1.1.7 libencdb-1.2.5.jar
      20220228 or a later version 1.1.9 libencdb-1.2.6.jar
    • A master encryption key (MEK) is generated.
      Note
      • 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. Therefore, we recommend that you back up your MEK.
      • 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 password generator OpenSSL, which allows you to run the openssl rand -hex 16 command to generate an 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 local client.
  1. Install the EncDB dependency package.
    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.4 -Dpackaging=jar -Dfile=D:\encdb\libs\libencdb-1.2.4.jar
    Note In the preceding example, the EncDB dependency package is 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.4</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.62</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 for test purposes only. 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.
      Parameter Example (string type) Description
      Mek 0x00112233445566778899aabbccddeeff

      The MEK that is used to access encrypted data.

      Valid values: 16-byte hexadecimal strings.

      EncAlgo SM4_128_CBC

      The encryption algorithm that is used.

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

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

      SdkMode Crypto The 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)