Always confidential database encrypts column data at rest and keeps it encrypted during computation. This page covers the data types, SQL operators, query clauses, indexing, multi-user authorization, and plaintext-ciphertext conversion supported by the feature.
Prerequisites
Before you begin, make sure that:
The minor engine version of your ApsaraDB RDS for PostgreSQL instance is 20230830 or later. To update the minor engine version, see Update the minor engine version.
A master encryption key (MEK) that you created and stored in a safe location. The feature cannot generate or back up MEKs. A lost MEK permanently prevents access to the existing encrypted data.
Encryption modes
Always confidential database provides four encryption modes for encrypted columns. The mode determines which SQL operators are available on that column.
| Encryption mode | How it works | Operators available |
|---|---|---|
Standard encrypted (enc_* types) | Encrypts data and supports arithmetic and comparison operators on the ciphertext. | Arithmetic (+, -, *, /, %), comparison (>, =, <, ≥, ≤, !=), and type-specific functions |
Deterministic (det_type) | Always encrypts the same plaintext to the same ciphertext. Enables equality lookups. | = only |
Randomized (rnd_type) | Encrypts the same plaintext to different ciphertext values each time. Provides the strongest confidentiality, but supports ciphertext storage only. | None (no ciphertext computing) |
Order-preserving (ore_int8, ore_float8) | Encrypts integers and floating-point numbers so that the order of ciphertext matches the order of plaintext. Does not require trusted hardware. | >, =, <, >=, <=, != |
Supported data types and operators
Always confidential database defines the following encrypted data types, all compatible with standard SQL syntax.
Standard encrypted types:
| Data type | Plaintext equivalent | Supported operators |
|---|---|---|
enc_int4 | 4-byte integer (INTEGER) | +, -, *, /, %, >, =, <, ≥, ≤, != |
enc_int8 | 8-byte integer | (not specified) |
enc_float4 | 4-byte single-precision float (REAL) | +, -, *, /, >, =, <, ≥, ≤, != |
enc_float8 | 8-byte double-precision float | +, -, *, /, >, =, <, ≥, ≤, !=, pow |
enc_decimal | DECIMAL | +, -, *, /, >, =, <, ≥, ≤, !=, pow, % |
enc_text | Variable-length UTF-8 string (TEXT) | substr/substring, ||, like, ~~, !~~, >, =, <, ≥, ≤, != |
enc_timestamp | TIMESTAMP WITHOUT TIME ZONE | extract year, >, =, <, ≥, ≤, != |
Deterministic and randomized types:
| Data type | Behavior | Supported operators |
|---|---|---|
det_type | Same plaintext always produces the same ciphertext. Supports equality lookups. | = |
rnd_type | Same plaintext produces different ciphertext each time. Ciphertext storage only. | None |
Order-preserving types (do not require trusted hardware):
| Data type | Plaintext equivalent | Supported operators |
|---|---|---|
ore_int8 | 8-byte integer | >, =, <, >=, <=, != |
ore_float8 | 8-byte double-precision float | >, =, <, >=, <=, != |
Example table definition:
CREATE TABLE example (
account enc_int4, -- Encrypted INTEGER: used as the primary key
name enc_text, -- Encrypted TEXT: account name
balance enc_float4, -- Encrypted REAL: account balance
credit enc_float4, -- Encrypted REAL: credit limit
quota real, -- Plaintext column
address enc_text, -- Encrypted TEXT: mailing address
remark text, -- Plaintext remarks
PRIMARY KEY (account)
);Supported query clauses
Always confidential database supports all standard query clauses. The following examples use table_name for the table name, col for a column name, and ciphertext_col for an encrypted column.
| Clause | Example |
|---|---|
WHERE, ORDER BY | SELECT col1, col2 FROM table_name WHERE ciphertext_col1 > ciphertext_col2 ORDER BY col1; |
GROUP BY, HAVING | SELECT col1, count(*) FROM table_name GROUP BY col1 HAVING col1 IS NOT NULL; |
INTERSECT, EXCEPT, UNION, LIMIT, OFFSET | SELECT ACCOUNT FROM table_name WHERE col1 IS NOT NULL EXCEPT SELECT ACCOUNT FROM table_name WHERE ciphertext_col1 > ciphertext_col2 LIMIT 1; |
Indexes
Create B-tree or hash indexes on encrypted columns to speed up queries:
CREATE INDEX IF NOT EXISTS name_index_btree ON table_name USING btree (name);
CREATE INDEX IF NOT EXISTS name_index_hash ON table_name USING hash (name);ApsaraDB RDS for PostgreSQL also supports the encdb_btree extension, which provides additional operations on ciphertext B-tree indexes. For more information, see Use encdb_btree to facilitate operations on ciphertext indexes.
Multi-user authorization
Ciphertext data from different users is automatically isolated. To perform cross-user ciphertext computation, issue a behavior control list (BCL) to grant the required access permissions. For more information, see Grant access permissions on multi-user data.
Conversion between plaintext and ciphertext
Always confidential database lets you change a column's type between plaintext and ciphertext.
Obtain BCL authorization before converting data between plaintext and ciphertext.
For the conversion procedure, see Convert plaintext and ciphertext.
Limitations
Mixed plaintext-ciphertext expressions are not supported. Queries that compare a plaintext column with a ciphertext column in the same expression fail. For example,
SELECT * FROM table_name WHERE plaintext_col < ciphertext_col;is not supported. Keep plaintext and ciphertext operations separate.MEK management is manual. Always confidential database cannot generate or back up master encryption keys (MEKs). Create and back up your MEK before using the feature. A lost MEK permanently prevents access to the encrypted data.
rnd_typedoes not support ciphertext computing. Columns of typernd_typecan store encrypted values but cannot be used in queries that compute on ciphertext.