Client guide
This guide describes how to connect to and use a Fully Encrypted Database from a client application.
Prerequisites
You have enabled Fully Encrypted Database. For more information, see Enable Fully Encrypted Database.
You have defined which columns contain sensitive data. For more information, see Define or convert sensitive data.
Obtain the database connection details: hostname, port, database name, username, and password. For instructions on how to find the public and internal endpoints for your instance, see View or modify connection endpoints and ports.
The application described in this article is developed by using Java. Make sure that you have a Java development environment. We recommend that you use Java version
1.8or later, Maven version3.9.2, andIntelliJ IDEA Community Edition 2022.3.2as the development tool.
Notes
Please securely store your master key (MEK).
Client examples
The Fully Encrypted Database feature encrypts sensitive data in query results. To enable your application to process encrypted data, connect using one of the following client access methods:
Access method | Description | Code changes | |
Application | EncJDBC | Automatically detects encrypted data types and handles encryption and decryption. | (Recommended) Requires almost no changes to your business logic code. |
EncDB SDK | You must call encryption and decryption functions from the EncDB SDK to process plaintext or ciphertext. | Required. | |
psql command-line tool | Connects directly to the database for queries. Only ciphertext for encrypted columns is displayed. | For queries only. No code modification is required, as this method is not for application development. | |
Visual interaction tools such as the DMS console | |||
EncJDBC
Download driver and dependencies
Download the EncJDBC driver.
EncJDBCdepends on the community-provided PostgreSQL driver to work.RDS PostgreSQL minor version
Server-side plugin version
Client dependency
20230830 or later
1.1.13 or later
Configure Maven dependencies.
NoteThis guide uses a Maven project as an example.
Run the following command to install the
EncJDBCdependency package to your local repository:mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=<jar_name> -Dversion=<jar_version> -Dpackaging=jar -Dfile=<jar_filename>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.jarNoteIn this example, the EncDB dependency packages are in the
D:\encdb\libsdirectory.This example uses Maven version
3.9.2. If you encounter an installation error, upgrade your Maven version and try again.
After installing the
EncJDBCdependency in the local repository, you need to add the following dependency to the pom.xml 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>
Client query example
The code shown in this example is for demonstration purposes only. Do not set password or mek in plaintext in your production code. We recommend that you use other methods, such as external configuration files or environment variables, to handle these values and then reference them in your code.
URL configuration
You can use
EncJDBClike any other JDBC. However, you first need to configure the following information related to your data security inEncJDBC.// Prepare your connection details: hostname, port, database name (dbname), username, and password. // For more details, see the data security parameters section. 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); // ... Run queries ...NoteIn a URL configuration, you can concatenate multiple parameters with
&.The
mekand other parameters are processed locally on the client and securely distributed to the server by using envelope encryption, ensuring that themekis never leaked.
Complete code example
// Replace the hostname, port, database name (dbname), username, and password with your instance's details. String hostname = "hostname"; String port = "port"; String dbname = "db"; String username = "user"; String password = "password"; String mek="00112233445566778899aabbccddeeff"; // This is an example. Use a more complex key in production. String encAlgo="SM4_128_CBC"; 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); // 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
The preceding EncJDBC code example shows that you only need to modify the driver loading and URL configuration. The rest of the process is identical to standard database access. You do not need to modify any business-related code.
EncDB SDK
Download driver and dependencies
Download the EncDB SDK driver.
EncDB SDKdepends on the PostgreSQL driver provided by the community.RDS PostgreSQL minor version
Server-side plugin version
Client dependency
20230830 or later
1.1.13 or later
Configure Maven dependencies.
NoteThis guide uses a Maven project as an example.
Run the following command to install the
EncDB SDKdependency package to your local repository:mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=<jar_name> -Dversion=<jar_version> -Dpackaging=jar -Dfile=<jar_filename>Example:
mvn install:install-file -DgroupId=com.alibaba.encdb -DartifactId=libencdb -Dversion=1.2.12 -Dpackaging=jar -Dfile=D:\encdb\libs\libencdb-1.2.12.jarNoteIn this example, the EncDB dependency package is located at the
D:\encdb\libspath.This example uses Maven version
3.9.2. If you encounter an installation error, upgrade your Maven version and try again.
After you install the
EncDB SDKdependency in your local repository, you need to add the following dependencies to the pom.xml file in 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> <dependency> <groupId>org.slf4j</groupId> <artifactId>slf4j-api</artifactId> <version>1.7.30</version> </dependency> <dependency> <groupId>org.apache.commons</groupId> <artifactId>commons-collections4</artifactId> <version>4.4</version> </dependency> ... </dependencies>
Client query example
The code in this example is for demonstration purposes only. Do not set password or mek in plaintext in your production code. Instead, use external configuration files or environment variables and reference them in your code.
SDK configuration
Before you send or receive ciphertext, you need to use the
EncDB SDKto encrypt and decrypt data.The
EncDB SDKprovides interfaces for you to configure data security parameters when you initialize an SDK object. Typically, you only need to configure the master key (setMek) and the desired encryption algorithm (setEncAlgo). We recommend that you use the default configurations for all other parameters.// Prepare your connection details: hostname, port, database name (dbname), username, and password. // Establish a database connection using any JDBC version. 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(dbConnection) .setMek(mek) .setEncAlgo(encAlgo) .build(); Cryptor cryptor = sdk.getCryptor(); // Call the encryption/decryption interface. // byte[] cipherBytes = cryptor.encrypt(...); // XXX value = cryptor.decryptXXX(...): // ... Run queries ...NoteThe
mekand other parameters are processed locally on the client and securely distributed to the server by using envelope encryption, ensuring that themekis never leaked.Complete code example
// Replace the hostname, port, database name (dbname), username, and password with your instance's details. 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. Use a more complex key in production. Constants.EncAlgo encAlgo=Constants.EncAlgo.SM4_128_CBC; EncdbSDK sdk = EncdbSDKBuilder.newInstance() .setDbConnection(dbConnection) .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 > ?"; 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); price = cryptor.decryptInt(rs.getBytes(3)); 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
SDK reference
Java SDK module overview
EncDB SDK primarily consists of the following Java functional modules:
psql CLI
Fully Encrypted Database supports direct queries from the command line, such as using the psql command line to run SELECT * FROM example;.
postgres=# \x
Expanded display is on.
postgres=# SELECT * FROM example;
-[ RECORD 1 ]-----------------------------------------------------------
id | 1
name | name
price | \xac0900ecbd4efc9f36eaf1c17f1ef928a50c26c73d4ac35788f543a7dd0b2fc752459e
miles | \x931900f561358e7a92086b69d3b423fe453466a7ec556387639699153bef078857f99c
secret | \x1d3900a4ffe264f4dc6d0138106869bc09387a500ce150501d60744f404e22a5d9aa62
postgres=#As you can see, the id and name columns are in plaintext, while the price, miles, and secret columns are in ciphertext. The encrypted data cannot be viewed on the server, which provides effective protection against both external and internal security threats and keeps your data secure at all times.
DMS console
You can query the database directly from visual interaction tools, such as the DMS console.
Execute the SELECT * FROM example; query. The result is as follows:
id | name | price | miles | secret
---+------+--------------------------------------------------------------------------+--------------------------------------------------------------------------+--------------------------------------------------------------------------
1 | name | \xac0900ecbd4efc9f36eaf1c17f1ef928a50c26c73d4ac35788f543a7dd0b2fc752459e | \x931900f561358e7a92086b69d3b423fe453466a7ec556387639699153bef078857f99c | \x1d3900a4ffe264f4dc6d0138106869bc09387a500ce150501d60744f404e22a5d9aa62As you can see, the id and name columns are plaintext, and the price, miles, and secret columns are ciphertext.
FAQ
Q: A connection to the database fails with the following error:
org.postgresql.util.PSQLException: ERROR: db_process_msg_api: process message failure - returned 0xf7070000.A: The error code 0xf7070000 indicates an MEK import failure. This can happen if the same account tries to connect to the same encrypted database with a different MEK. Changing the master key makes data encrypted with the original key inaccessible. Ensure that you are using the original key to connect to the database.
Q: When I run my program, I get the error
Exception in thread "main" java.lang.IllegalAccessError: class com.alibaba.encdb.common.SymCrypto (in unnamed module @0x5c0369c4) cannot access class com.sun.crypto.provider.SunJCE (in module java.base) because module java.base does not export com.sun.crypto.provider to unnamed module @0x5c0369c4. How can I fix this?A: This error may be caused by inter-module permission issues due to a high JDK version. To resolve the access permission issue, add the VM option parameter
--add-exports=java.base/com.sun.crypto.provider=ALL-UNNAMEDat runtime, which exportscom.sun.crypto.providerto the unnamed module.