Use the JDBC-driven column encryption driver to access encrypted columns on PostgreSQL - ApsaraDB RDS

The JDBC-driven column encryption driver lets Java applications read encrypted columns on ApsaraDB RDS for PostgreSQL without changing query logic. When a valid master encryption key (MEK) is configured and the account has the required ciphertext permission, the driver decrypts ciphertext automatically and returns plaintext.

Prerequisites

Before you begin, ensure that you have:

Enabled column encryption on your RDS instance
After you enable column encryption, the system automatically installs the rds_encdb extension. Run the following SQL statement to confirm the extension is active: ``sql SELECT EXISTS (SELECT * FROM pg_extension WHERE extname = 'rds_encdb'); ``
The connection details for your RDS instance: endpoint, port, database name, username, and password. See View and change the endpoints and port numbers
The ciphertext permission (JDBC decryption) granted to the account you use to connect
JDK 1.8 or later

Warning

Store your MEK securely and keep it confidential. The RDS instance does not generate, store, manage, or back up your MEK. If you lose the MEK, you cannot recover the existing encrypted data.

How it works

The driver intercepts JDBC calls and performs client-side decryption using your MEK. The MEK is distributed to the server using envelope encryption to ensure that the MEK is not leaked.

Step 1: Add the Maven dependency

Add the following dependency to the pom.xml file in your Maven project:

<dependencies>
  <dependency>
    <groupId>com.aliyun</groupId>
    <artifactId>aliyun-cls-jdbc</artifactId>
    <version>1.0.10-1</version>
  </dependency>
</dependencies>

Step 2: Configure the connection URL and MEK

The driver requires two settings:

Setting	Format / example	Description
URL	`jdbc:postgresql:encdb://<host>:<port>/<dbname>`	The URL must start with `jdbc:postgresql:encdb://`.
MEK	`00112233445566778899aabbccddeeff`	A 32-character hexadecimal string (16 bytes). Generate it with a tool such as `openssl rand -hex 16`, a random function in your programming language, or a third-party Key Management Service (KMS).

MEK configuration methods

Three methods are available. When more than one method is used, the driver applies them in the following priority order:

JDBC Properties configuration (highest priority)
File configuration
URL configuration (lowest priority)

JDBC Properties configuration (recommended)

Pass the MEK as a JDBC property when creating the connection:

String mek = "<your-mek>";

Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
props.setProperty("MEK", mek);

String dbUrl = String.format("jdbc:postgresql:encdb://%s:%s/%s", hostname, port, dbname);
Connection connection = DriverManager.getConnection(dbUrl, props);

File configuration

Store the MEK in a configuration file and reference it through the encJdbcConfigFile property. If you do not set this property, the driver looks for encjdbc.conf by default.

Configuration file content:

MEK=<your-mek>

Place the file in either of the following locations:

The resources directory of your project
The root directory used by the application at runtime

After placing the file, no additional code changes are needed:

String dbUrl = String.format("jdbc:postgresql:encdb://%s:%s/%s", hostname, port, dbname);
Connection connection = DriverManager.getConnection(dbUrl, username, password);

URL configuration

Append the MEK directly to the connection URL. Use & to concatenate multiple parameters:

String mek = "<your-mek>";

String dbUrl = String.format("jdbc:postgresql:encdb://%s:%s/%s?MEK=%s", hostname, port, dbname, mek);
Connection connection = DriverManager.getConnection(dbUrl, username, password);

Step 3: Query encrypted data

The following example uses JDBC Properties configuration to set the MEK.

Query encrypted columns

// Replace the placeholder values with your actual connection details.
String hostname = "pgm-****.pg.rds.aliyuncs.com";
String port     = "5432";
String dbname   = "testdb";
String username = "user";   // Must have the ciphertext permission (JDBC decryption).
String password = "password";
String mek      = "<your-mek>";

Properties props = new Properties();
props.setProperty("user", username);
props.setProperty("password", password);
props.setProperty("MEK", mek);

String dbUrl = String.format("jdbc:postgresql:encdb://%s:%s/%s", hostname, port, dbname);
Connection connection = DriverManager.getConnection(dbUrl, props);

// Run a query. Encrypted columns are decrypted automatically.
ResultSet rs = connection.createStatement().executeQuery("SELECT * FROM test_table");
while (rs.next()) {
    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) { // Column index starts from 1.
        System.out.print(rs.getString(i) + "\t");
    }
    System.out.println();
}

rs.close();
connection.close();

Common issues

The MEK is lost. There is no way to recover encrypted data without the MEK. Back up the MEK before encrypting any data.

The connection URL does not start with `jdbc:postgresql:encdb://`. The driver only intercepts connections that use the encdb URL prefix. You must specify a URL that starts with jdbc:postgresql:encdb://.

The account does not have the ciphertext permission (JDBC decryption). Queries against encrypted columns return an error. Grant the permission as described in Use the column encryption feature.

What's next

Column encryption — enable column encryption and manage encrypted columns on your RDS instance