All Products
Search

This Product

    PolarDB:Features

    Document Center

    PolarDB:Features

    Last Updated:May 17, 2024

    This topic describes the features of an always-confidential database.

    Prerequisites

    Your cluster runs PolarDB for PostgreSQL 14 whose revision version is 14.9.14.0 or later.

    Note

    To view the revision version of your PolarDB for PostgreSQL cluster, execute the following statement:

    PostgreSQL 14

    select version();

    Limits

    • You cannot execute a statement to perform operations on plaintext and ciphertext columns at the same time. Sample statement: 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.

    • An always-confidential database does not 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 securely store your MEK.

    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.

    Data type

    Description

    Supported operator

    enc_int4

    An encrypted integer that corresponds to a 4-byte integer in plaintext.

    +, -, *, /, %, >, =, <, ≥, ≤, and !=

    enc_int8

    An encrypted integer that corresponds to an 8-byte integer in plaintext.

    enc_float4

    An encrypted floating-point number that corresponds to a 4-byte single-precision floating-point number in plaintext.

    +, -, *, /, >, =, <, ≥, ≤, and !=

    enc_float8

    An encrypted floating-point number that corresponds to an 8-byte double-precision floating-point number in plaintext.

    +, -, *, /, >, =, <, ≥, ≤, !=, and pow

    enc_decimal

    An encrypted decimal number that corresponds to plaintext data of the DECIMAL data type.

    +, -, *, /, >, =, <, ≥, ≤, !=, pow, and %

    enc_text

    An encrypted character string that has a variable length and corresponds to plaintext data of the TEXT data type. The string is encoded in UTF-8.

    substr/substring, ||, like, ~~, !~~, >, =, <, ≥, ≤, and !=

    enc_timestamp

    An encrypted timestamp that corresponds to plaintext data of the TIMESTAMP WITHOUT TIME ZONE data type.

    extract year, >, =, <, ≥, ≤, and !=

    det_type

    Any encrypted data. The same plaintext data is encrypted into the same ciphertext data.

    =

    rnd_type

    Any encrypted data. The same plaintext data is encrypted into different ciphertext data.

    N/A (Ciphertext storage is supported, but ciphertext computing is not supported.)

    ore_int8

    An order-preserving encrypted integer that corresponds to an 8-byte integer in plaintext. The encryption does not depend on trusted hardware.

    >, =, <, >=, <=, and !=

    ore_float8

    An order-preserving encrypted floating-point number that corresponds to an 8-byte double-precision floating-point number in plaintext. The encryption does not depend on trusted hardware.

    >, =, <, >=, <=, and !=

    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)     -- Specifies that the account column is used as the primary key.
);

    Query clauses

    An always-confidential database supports the following clauses required for common database queries.

    Note

    In the following examples, table_name indicates the table name, col indicates the column name, and ciphertext_col indicates the ciphertext column.

    Clause

    Example

    WHERE

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

    ORDER BY

    GROUP BY

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

    HAVING

    INTERSECT

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

    EXCEPT

    UNION

    LIMIT

    OFFSET

    Indexes

    An always-confidential database 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 an always-confidential database, the ciphertext data of different users is automatically isolated. To perform integrated computing on 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.

    Conversion between plaintext and ciphertext

    An always-confidential database allows you to change the column type. You can change the column type between plaintext and ciphertext. For more information, see Convert data between plaintext and ciphertext.

    Important

    Before you convert data between plaintext and ciphertext, make sure that you are authorized to do so by using a BCL.