Confidential databases in PolarDB for PostgreSQL let you store and query sensitive data entirely in encrypted form — without exposing plaintext to the database engine. This page describes the supported data types, SQL operations, indexes, and access control features.
Scope
These features apply to PolarDB for PostgreSQL clusters running PostgreSQL 14 with minor engine version 2.0.14.9.14.0 or later.
To check your current minor engine version, run the following statement or view it in the console:
SHOW polardb_version;If your cluster does not meet the version requirement, upgrade the minor engine version before using these features.
Encrypted data types
Confidential databases define the following encrypted data types. All types support standard SQL syntax and are compatible with transactions and SQL queries.
| Data type | Plaintext equivalent | Supported ciphertext operations |
|---|---|---|
enc_int4 | 4-byte integer | +, -, *, /, %, >, =, <, >=, <=, != |
enc_int8 | 8-byte integer | |
enc_float4 | 4-byte single-precision float | +, -, *, /, >, =, <, >=, <=, != |
enc_float8 | 8-byte double-precision float | +, -, *, /, >, =, <, >=, <=, !=, pow |
enc_decimal | Decimal | +, -, *, /, >, =, <, >=, <=, !=, pow, % |
enc_text | UTF-8 text string | substr/substring, ||, like, ~~, !~~, >, =, <, >=, <=, != |
enc_timestamp | Timestamp without time zone | extract year, >, =, <, >=, <=, != |
det_type | Any type (deterministic) | = |
rnd_type | Any type (randomized) | None — storage only |
ore_int8 | 8-byte integer (order-preserving) | >, =, <, >=, <=, != |
ore_float8 | 8-byte double-precision float (order-preserving) | >, =, <, >=, <=, != |
Choose an encryption strategy
The det_type, rnd_type, and ore_* types represent different encryption strategies with different trade-offs:
| Strategy | When to use | Operations supported |
|---|---|---|
det_type (deterministic) | Columns that need equality lookups (=) but not range queries | Equality only (=) |
rnd_type (randomized) | Columns that only need secure storage with no computation — the same plaintext produces different ciphertext each time | Storage only; no computation |
ore_int8, ore_float8 (order-preserving) | Numeric columns that need range queries or ORDER BY without trusted hardware | Comparison operators (>, <, >=, <=, =, !=) |
For columns that require arithmetic operations (+, -, *, /), use the corresponding enc_* type (enc_int4, enc_float8, and so on).
Example: create a table with encrypted columns
CREATE TABLE example (
account enc_int4, -- Account number; plaintext is integer
name enc_text, -- Name; plaintext is text
balance enc_float4, -- Account balance; plaintext is real
credit enc_float4, -- Credit limit; plaintext is real
quota real, -- Plaintext column
address enc_text, -- Address; plaintext is text
remark text, -- Remarks (plaintext)
PRIMARY KEY (account)
);Supported SQL clauses
The following SQL clauses work on encrypted columns. In the examples, table_name is the table name, col is a column name, and ciphertext_col is an encrypted column.
WHERE and ORDER BY
SELECT col1, col2
FROM table_name
WHERE ciphertext_col1 > ciphertext_col2
ORDER BY col1;GROUP BY and HAVING
SELECT col1, count(*)
FROM table_name
GROUP BY col1
HAVING col1 IS NOT NULL;INTERSECT, EXCEPT, UNION, LIMIT, and OFFSET
SELECT ACCOUNT
FROM table_name
WHERE col1 IS NOT NULL
EXCEPT
SELECT ACCOUNT
FROM table_name
WHERE ciphertext_col1 > ciphertext_col2
LIMIT 1;Index acceleration
Create a B-tree or hash index on an encrypted column to accelerate 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);Multi-user authorization
Ciphertext data is isolated between users by default. To allow collaborative computation on shared encrypted data, issue a Behavior Control List (BCL) grant to the target users.
For detailed steps, see Grant access to multiple users.
Plaintext-ciphertext conversion
Convert a column between plaintext and ciphertext without recreating the table. For detailed steps, see Convert between plaintext and ciphertext.
BCL authorization is required to use this feature.
Limitations
Mixed plaintext and ciphertext operations are not supported. Queries that compare a plaintext column directly with a ciphertext column return an error. For example, the following statement is invalid:
-- Invalid: cannot compare plaintext_col with ciphertext_col
SELECT * FROM table_name WHERE plaintext_col < ciphertext_col;Customer master key (CMK) management is your responsibility.
Confidential databases do not provide a service to generate or back up customer master keys (CMKs). You must generate your own CMK. If you lose the key, you can no longer access your existing data. Therefore, back up your CMK in a secure location.