ApsaraDB RDS:Supported capabilities

Limits

• You cannot execute statements to perform operations on both plaintext and ciphertext columns at the same time. Example: SELECT * FROM table_name WHERE plaintext_col < ciphertext_col;.

  Note

  In the preceding statement, table_name indicates the table name, plaintext_col indicates the plaintext column, and ciphertext_col indicates the ciphertext column.

• The Always confidential database feature cannot be used to generate and back up master encryption keys (MEKs). You must manually create an MEK. If you lose your MEK, you can no longer access the existing encrypted data. We recommend that you back up your MEK.

Usage notes

To use the Always confidential database feature, you must make sure that the minor engine version of your ApsaraDB RDS for PostgreSQL instance is 20230830 or later.

Supported data types and related operators

The Always confidential database feature defines and provides the following data types. The data types and related operators support SQL queries and transactions and are compatible with the standard SQL syntax.

Sample SQL statement:

CREATE TABLE example ( 
  account enc_int4,         -- The account that corresponds to plaintext data of the INTEGER type.
  name enc_text,            -- The name that corresponds to plaintext data of the TEXT type.
  balance enc_float4,       -- The account balance that corresponds to plaintext data of the REAL type.
  credit enc_float4, -- The credit line of the account that corresponds to plaintext data of the REAL type.
  quota real,               --  The plaintext column.
  address enc_text,         -- The address that corresponds to plaintext data of the TEXT type.
  remark text,              -- The remarks.
  PRIMARY KEY (account)     -- The account column that is used as the primary key.
);

Query clauses

The Always confidential database feature supports the following clauses required for common database queries.

SELECT col1, col2
    FROM table_name
    WHERE ciphertext_col1 > ciphertext_col2 
    ORDER BY col1;

SELECT col1, count(*)
    FROM table_name
    GROUP BY col1
    HAVING col1 IS NOT NULL;

SELECT ACCOUNT 
    FROM table_name
    WHERE col1 IS NOT NULL
EXCEPT
    SELECT ACCOUNT 
    FROM table_name
    WHERE ciphertext_col1 > ciphertext_col2 
LIMIT 1;

Indexes

The Always confidential database feature allows you to create indexes on encrypted columns to accelerate queries. Examples:

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

In a Always confidential database, the ciphertext data of different users is automatically isolated. To perform integrated computing of the ciphertext data, you can issue a behavior control list (BCL) to grant access permissions on the ciphertext data. For more information, see Grant access permissions on multi-user data of an ApsaraDB RDS for PostgreSQL instance.

Conversion between plaintext and ciphertext

The Always confidential database feature allows you to change the column type. You can change the column type between plaintext and ciphertext. For more information, see Convert plaintext and ciphertext.