This topic describes how to create a fully encrypted database on an ApsaraDB RDS for PostgreSQL instance.

Prerequisites

Procedure

  1. Enable the encdb plug-in.
  2. Create an encrypted table.
  3. Install the libencdb.jar package.
  4. Add dependencies.
  5. Encrypt the data on the client side.
  6. Decrypt the data on the client side.

Enable the encdb plug-in

The fully encrypted database feature is implemented by using the encdb plug-in. You do not need to enable the fully encrypted database feature. Execute the following statement to enable the encbd plug-in.

create extension encdb;
Note To enable the encdb plug-in, you must have the permissions of the superuser account.

Create an encrypted table

When you create an encrypted table, you can replace the data types of sensitive fields in the original table with the corresponding encrypted data types based on your business requirements. In this example, the following fields are defined in the original table:

CREATE TABLE example (
  id     integer,
  name   varchar,
  price  integer,
  miles  float4,
  secret text,
  primary key (id)
)

Create an encrypted table in which the price, miles, and secret fields are specified as sensitive fields. The following code provides an example:

CREATE TABLE example (
  id     integer,
  name   varchar,
  price  rnd_type,
  miles  det_type,
  secret rnd_type,
  primary key (id)
)
Note
  • A fully encrypted database supports the following encrypted data types: det_type and rnd_type. The det_type data type is a type of deterministic encryption. In this type of encryption, you can obtain only one ciphertext when you encrypt a number. The rnd_type data type is a type of non-deterministic encryption. In this type of encryption, you can obtain different ciphertexts when you encrypt a number.
  • If you require the det_type and rnd_type data types in your application, you must convert the host variables of the det_type and rnd_type data types to the string data type.
  • You cannot directly alter the original table to obtain an encrypted table. You must create an encrypted table from the original table. You can use an SDK to encrypt the data of the original table. Then, you can import the encrypted data into the encrypted table that you created.

Install the libencdb.jar package

If you built your Java project by using Maven, run the following command to install the libencdb.jar package in your repository.
mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=<The name of the JAR package> -Dversion=<The version of the libencdb plug-in in the JAR package> -Dpackaging=jar -Dfile=libs/<The name of the JAR package.jar>
Example:
mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=libencdb -Dversion=1.2.0 -Dpackaging=jar -Dfile=libs/libencdb-1.2.0.jar
Note In this example, the libencdb-1.2.0.jar package is downloaded to the libs path.

Add dependencies

In your Java project, you can use libencdb and native Java Database Connectivity (JDBC) to interact with the fully encrypted database. When your application interacts with the fully encrypted database, your application calls the encryption and decryption functions that are provided by libencdb to encrypt or decrypt the data.

Add the following dependencies to the pom.xml file:

    <dependency>
      <groupId>com.alibaba.encdb</groupId>
      <artifactId>libencdb</artifactId>
      <version>1.2.0</version>
    </dependency>
    <dependency>
      <groupId>org.postgresql</groupId>
      <artifactId>postgresql</artifactId>
      <version>42.2.10</version>
    </dependency>
    <dependency>
      <groupId>org.bouncycastle</groupId>
      <artifactId>bcpkix-jdk15on</artifactId>
      <version>1.62</version>
    </dependency>

    <dependency>
      <groupId>com.alibaba</groupId>
      <artifactId>fastjson</artifactId>
      <version>1.2.62</version>
    </dependency>

    <dependency>
      <groupId>com.google.guava</groupId>
      <artifactId>guava</artifactId>
      <version>24.1.1-jre</version>
    </dependency>

Encrypt the data on the client side

For security purposes, you must encrypt data that is inserted into the business code.

  • Step 1

    Declare variables and create a Crypto instance. The following code provides an example:

    Class.forName("org.postgresql.Driver");
    
    String hostname = "11.160. 48.100"; // The hostname
    String port = "9994"; // The port number
    String dbname = "postgres"; // The name of the fully encrypted database
    String username = "encdb_test"; // The username that is used to connect to the fully encrypted database
    String password = "encdb_test"; // The password that is used to connect to the fully encrypted database
    String tableName = "example"; // The name of the encrypted table
    byte[] mek = new byte[] {0x00, 0x11, 0x22, 0x33, 0x44, 0x55, 0x66, 0x77, (byte) 0x88, (byte) 0x99, (byte) 0xaa, (byte) 0xbb, (byte) 0xcc, (byte) 0xdd, (byte) 0xee, (byte) 0xff}; 
                                        // The root key that is used. The size of the root key must be 16 bytes in length. 
    
    String dbUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname+"?binaryTransfer=true";
    Connection dbConnection = DriverManager.getConnection(dbUrl, username, password);
    
    
    int algo = CryptoConstants.AES_ 128_CBC; // The encryption algorithm. You can use the following encryption algorithms: AES_128_GCM, AES_128_CTR, AES_128_CBC, and SM4_128_CBC
    CryptoSDK crypto = CryptoManager.createCryptoNoTEE(username, mek, dbConnection, algo);
    Note The mek and algo parameters are used only as the encryption and decryption modules of libencdb. These parameters are not transmitted to the RDS instance.
  • Step 2

    Encrypt data before you insert the data. The following code provides an example:

    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();
    Note In this example, a set of data is inserted into the example table of the postgres database. The price, miles and secret fields are encrypted.
  • Step 3

    Connect to the fully encrypted database and run the following command to view the data that you inserted:

    postgres=# select * from example;

    Result:

     id | name |                                  price                                   |                                  miles                                   |                                  secret
    ----+------+--------------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------------------------------------------------------------
      1 | name | \xdf4901df087c6a3e0325175bb76942c684191a8dda2a8d0c35f295dc1e30eaeaa0c0e3 | \x315102ea5ab8a659066ab672e6dfbfd89a3a2b360bf6efe3787931e00f61af05f7408c | \xed4903dfd1bda1a89ad6aa7e8905c0e6305e15db4bc9ce2d2cfac9e25094d2a3ed367d
    (1 row)
    Note The plaintext data cannot be viewed on the RDS instance. This protects your data in the cloud from internal and external security threats.

Decrypt the data on the client side

You can decrypt data that is obtained from the fully encrypted database in your business code. The following code provides an example:

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("price:" + price + "\n" + "miles:" + miles + "\n" + "text:" + text);

}
Note In the output of the preceding code, you can view the data after decryption.