Use the Always confidential database feature to protect privacy data - ApsaraDB RDS

The Always confidential database feature separates data ownership from database administration: the data department encrypts sensitive columns, so database administrators and other departments can only see ciphertext — never plaintext. This tutorial walks through a complete insurance company example, from key provisioning to writing encrypted data to demonstrating role-based access control.

Prerequisites

Before you begin, ensure that you have:

A working understanding of the Always confidential database feature. See Overview.
The client SDK or Java Database Connectivity (JDBC) driver for the Always confidential database feature installed. See Configure the Always confidential database feature on an Intel Software Guard Extensions (SGX)-based security-enhanced ApsaraDB RDS for PostgreSQL instance.
The EncDB extension downloaded and extracted. This tool generates SQL statements for importing master encryption keys (MEKs), registering certificates or public/private key pairs, and issuing or revoking behavior control lists (BCLs).

After extracting the EncDB package, run ./genEncdbSQLCommand.sh --help to see all available options.

For more information about BCLs, see Grant access permissions on multi-user data of an ApsaraDB RDS for PostgreSQL instance.

Use case

An insurance company stores policyholder records in a table named person, with sensitive columns for name, phone number, ID card number, bank card number, and address. All five columns are encrypted using the enc_text type.

Name (name)	Phone number (phone)	ID card number (ID)	Bank card number (debit_card)	Address (address)
Xiaobao TAO	13900001111	111222190002309999	6225888888888888	No. 888, ABC Road, Hangzhou, Zhejiang
Sanduo DING	13900002222	111222190002308888	6225666666666666	No. 666, DEF Road, Hangzhou, Zhejiang

The data in this table is for reference only.

The data department owns the encryption keys and can read plaintext. The sales department can query the table but only receives ciphertext — the BCL authorization mechanism prevents decryption without an explicit grant from the data department.

Set up the environment

The following examples run on CentOS.

Step 1: Configure the cipher suite.

Set the cipher suite variable:

cipher_suite=RSA_WITH_AES_128_CBC_SHA256

Step 2: Generate key and BCL files.

Run the following command to generate configuration files (key files and authorization files) into the sample directory. These files are required throughout the rest of this tutorial.

./generateSampleKeyAndBCLs.sh -c ${cipher_suite}

Step 3: Create and initialize the database.

Create a database named demo. You can use any name — replace demo with your actual database name in all subsequent commands.

CREATE DATABASE demo;

Connect to demo, then create the encdb extension:

CREATE EXTENSION encdb;

Step 4: Export and format the database public key certificate.

Retrieve the database's public key:

SELECT encode(db_process_msg_api('{"request_type":0,"version":"1.2.8"}'),'escape')::json->'server_info'->'public_key'

Copy the output into a local file — this example uses default_enclave_public_key.pem. If the value starts and ends with double quotation marks ("), omit the quotation marks when saving to the file.

Format the certificate into standard PEM format:

# Replace \\n escape sequences with actual line feeds
sed -i 's/\\\\n/\n/g' default_enclave_public_key.pem

# Remove empty lines
sed -i '/^[ ]*$/d' default_enclave_public_key.pem

Data department: encrypt and write data

The data department controls the MEK and BCL for each user. This section shows how to provision encryption keys, authorize them via BCL, and write ciphertext to the person table.

Step 1: Create users and the policyholder table

-------- Create users --------
-- Data department user, responsible for managing policyholder data
CREATE USER ins_data;
-- Sales department user
CREATE USER ins_sale;

-------- Create the policyholder table --------
-- All columns use enc_text to store encrypted values
CREATE TABLE person (
    name enc_text,
    phone enc_text,
    ID enc_text,
    debit_card enc_text,
    address enc_text
);

-- Grant full access to the data department
GRANT ALL ON person TO ins_data;

Step 2: Provision an MEK for the data department

An MEK (master encryption key) is bound to a database user and stored in the SGX enclave. Each user must have a separate MEK before they can encrypt or decrypt data.

The EncDB extension generates SQL statements from the parameters you provide. Run each command to get the SQL output, then execute that SQL in the database.

./genEncdbSQLCommand.sh -r MEK_PROVISION \
  --mek sample/default_mek_data.bin \
  -e default_enclave_public_key.pem \
  -c ${cipher_suite}

After executing the generated SQL, retrieve the MEK ID of the ins_data user:

SELECT encdb_get_current_mek_id();

Store the MEK ID for later use (the value 178079820457738240 is an example):

mekid_data=178079820457738240

./genEncdbSQLCommand.sh -r BCL_REGISTER \
  --mekid ${mekid_data} \
  --mek sample/default_mek_data.bin \
  --puk sample/usr_puk_data.pem \
  --pri sample/usr_pri_data.pem \
  -c ${cipher_suite}

Step 3: Create a data encryption key (DEK)

A DEK (data encryption key) is bound to a specific column. The groupid returned here is used in the next step to authorize encryption via BCL.

Run the following SQL statements as the ins_data user. The encdb.dek_xxx() and encdb.keyname_xxx() functions are data conversion functions. See Data conversion functions.

Generate a DEK for the name column and record the returned groupid:

-- Returns a groupid, for example: b6785611-0c49-4f13-87a9-13f151de9b4d
SELECT encdb.dek_generate(encdb.keyname_generate('ins_data','demo','public','person','name'));

In this example, all columns share the same DEK. Copy the key to the remaining columns:

SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','phone'),encdb.keyname_generate('ins_data','demo','public','person','name'));
SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','ID'),encdb.keyname_generate('ins_data','demo','public','person','name'));
SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','debit_card'),encdb.keyname_generate('ins_data','demo','public','person','name'));
SELECT encdb.dek_copy_keyname(encdb.keyname_generate('ins_data','demo','public','person','address'),encdb.keyname_generate('ins_data','demo','public','person','name'));

Step 4: Authorize the DEK via BCL

Database users cannot use a DEK to encrypt data until the DEK's groupid is added to an issued BCL. This is the authorization mechanism that controls who can encrypt — and later decrypt — data.

Update the BCL with the groupid from Step 3:
b6785611-0c49-4f13-87a9-13f151de9b4d is an example value. Get the actual groupid by running SELECT encdb.dek_generate(encdb.keyname_generate('ins_data','demo','public','person','name'));.
```
./setGroupIdBCL.sh -d b6785611-0c49-4f13-87a9-13f151de9b4d
```

Issue the BCL (two-step signing — subject sign, then issuer sign):

./genEncdbSQLCommand.sh -r BCL_ISSUE --subject_sign \
  --spriv sample/usr_pri_data.pem \
  --spuk sample/usr_puk_data.pem \
  --ipuk sample/usr_puk_data.pem \
  --bcl sample/bcl_for_data_insert.txt \
  -c ${cipher_suite}

./genEncdbSQLCommand.sh -r BCL_ISSUE --issuer_sign \
  --ipriv sample/usr_pri_data.pem \
  --spuk sample/usr_puk_data.pem \
  --ipuk sample/usr_puk_data.pem \
  --bcl sample/bcl_for_data_insert.txt \
  -c ${cipher_suite}

Step 5: Write encrypted data

Run the following statements as the ins_data user.

INSERT INTO person VALUES(
    encdb.enc_text_encrypt('Xiaobao TAO', encdb.keyname_generate('ins_data','demo','public','person','name')),
    encdb.enc_text_encrypt('13900001111', encdb.keyname_generate('ins_data','demo','public','person','phone')),
    encdb.enc_text_encrypt('111222190002309999', encdb.keyname_generate('ins_data','demo','public','person','ID')),
    encdb.enc_text_encrypt('6225888888888888', encdb.keyname_generate('ins_data','demo','public','person','debit_card')),
    encdb.enc_text_encrypt('No. 888, ABC Road, Hangzhou, Zhejiang', encdb.keyname_generate('ins_data','demo','public','person','address'))
);

INSERT INTO person VALUES(
    encdb.enc_text_encrypt('Sanduo DING', encdb.keyname_generate('ins_data','demo','public','person','name')),
    encdb.enc_text_encrypt('13900002222', encdb.keyname_generate('ins_data','demo','public','person','phone')),
    encdb.enc_text_encrypt('111222190002308888', encdb.keyname_generate('ins_data','demo','public','person','ID')),
    encdb.enc_text_encrypt('6225666666666666', encdb.keyname_generate('ins_data','demo','public','person','debit_card')),
    encdb.enc_text_encrypt('No. 666, DEF Road, Hangzhou, Zhejiang', encdb.keyname_generate('ins_data','demo','public','person','address'))
);

Step 6: Verify access as the data department

Query without decryption — any user who can access the table sees ciphertext:

SELECT name, phone FROM person;

Expected output (ciphertext — this is normal for any user without BCL authorization):

                                          name                                    |                                  phone
--------------------------------------------------------------------------+--------------------------------------------------------------------------
 \xdf4901df087c6a3e0325175bb76942c684191a8dda2a8d0c35f295dc1e30eaeaa0c0e3 | \x315102ea5ab8a659066ab672e6dfbfd89a3a2b360bf6efe3787931e00f61af05f7408c
 \xed4903dfd1bda1a89ad6aa7e8905c0e6305e15db4bc9ce2d2cfac9e25094d2a3ed367d | \xd75bb76942c682a8d0c35f295dc5ab8a659066ab672e6de00f61af0a1a89ad6aa7e890
(2 rows)

Query with decryption — run as ins_data, which holds the BCL authorization:

SELECT encdb.decrypt(name) FROM person
WHERE name LIKE encdb.enc_text_encrypt('TAO%', encdb.keyname_generate('ins_data','demo','public','person','name'));

Expected output (plaintext):

   decrypt
-----------
 Xiaobao TAO
(1 row)

Sales department: ciphertext-only access

The sales department has its own MEK and BCL registration, but the data department has not granted it any decryption authorization. This section confirms that the access control is working as intended.

Step 1: Provision an MEK for the sales department

./genEncdbSQLCommand.sh -r MEK_PROVISION \
  --mek sample/default_mek_sale.bin \
  -e default_enclave_public_key.pem \
  -c ${cipher_suite}

Retrieve the MEK ID for ins_sale:

SELECT encdb_get_current_mek_id();

Store it (the value 2715553450389700608 is an example):

mekid_sale=2715553450389700608

./genEncdbSQLCommand.sh -r BCL_REGISTER \
  --mekid ${mekid_sale} \
  --mek sample/default_mek_sale.bin \
  --puk sample/usr_puk_sale.pem \
  --pri sample/usr_pri_sale.pem \
  -c ${cipher_suite}

Step 2: Verify that the sales department cannot decrypt data

The data department has not granted the sales department any BCL authorization, so ins_sale can only see ciphertext.

Plain query — returns the same ciphertext as any other user:

SELECT name, phone FROM person;

Expected output (ciphertext — this is correct behavior):

                                          name                                    |                                  phone
--------------------------------------------------------------------------+--------------------------------------------------------------------------
 \xdf4901df087c6a3e0325175bb76942c684191a8dda2a8d0c35f295dc1e30eaeaa0c0e3 | \x315102ea5ab8a659066ab672e6dfbfd89a3a2b360bf6efe3787931e00f61af05f7408c
 \xed4903dfd1bda1a89ad6aa7e8905c0e6305e15db4bc9ce2d2cfac9e25094d2a3ed367d | \xd75bb76942c682a8d0c35f295dc5ab8a659066ab672e6de00f61af0a1a89ad6aa7e890
(2 rows)

If you see ERROR: permission denied for table person, run GRANT SELECT ON person TO ins_sale; to grant read access to the table, then retry.

Decrypt attempt — run as ins_sale:

SELECT encdb.decrypt(name) FROM person;

Expected output (BCL not found — this confirms that access control is working as intended):

WARNING:  -- encdb -- -- Untrusted log -- 4 - src/core/untrusted/src/encdb_untrusted_enclave.cpp,256,encdb_ecall: Select BCL (subject_mekid: 2715553450389700608, issuer_mekid: 178079820457738240) from table fail - returned 0xfa030000
ERROR:  encdb_ext_enc_text_decrypt: enc_text decrypt errno:fa030000

This error means the SGX enclave found no BCL authorizing ins_sale to decrypt data owned by ins_data. It is the expected result of a correctly configured privacy protection setup — not a system failure.

Next steps

Grant access permissions on multi-user data of an ApsaraDB RDS for PostgreSQL instance — issue a BCL to authorize the sales department to decrypt specific columns.
Data conversion functions — reference for encdb.dek_xxx() and encdb.keyname_xxx() functions.