Equality Query in a Fully-encrypted Database

Availability

This feature is available since MogDB 1.1.0.

Introduction

The encrypted database aims to protect privacy throughout the data lifecycle. In this way, data is always in ciphertext during transmission, computing, and storage regardless of the service scenario and environment. After the data owner encrypts data on the client and sends the encrypted data to the server, no attacker can obtain valuable information even if the attacker steals user data by exploiting system vulnerabilities. In this way, data privacy is protected.

Benefits

The entire service data flow is in ciphertext during data processing, so the following can be implemented by using a fully-encrypted database:

1. Protect data privacy and security throughout the lifecycle on the cloud. Attackers cannot obtain valid information from the database server regardless of the data status.
2. Help cloud service providers obtain third-party trust. Users, including service administrators and O&M administrators in enterprise service scenarios and application developers in consumer cloud services, can keep keys by themselves so that users with high permissions cannot obtain valid data.
3. Enable cloud databases to better comply with personal privacy protection laws and regulations with the help of the fully-encrypted database.

Description

From the perspective of users, the encrypted equality query functions are divided into three parts, which are implemented by the newly added KeyTool and the enhanced MogDB gsql client.

First, this feature provides the client key management function. Users can use KeyTool to generate, destroy, and update CMKs, and import and export keys. With the import and export functions of KeyTool, CMKs can be transmitted between different clients. In addition, the KeyTool implements key management on a single client. By configuring management files, you can store and update keys.

In addition, this feature provides the key creation and encrypted table creation functions. The SQL syntax CREATE CLINET MASTER KEY and CREATE COLUMN ENCRYPTION KEY are added to record and manage CMK and CEK metadata in the database. The CMK and CEK information is recorded in the new system catalog. The CREATE TABLE syntax is extended to specify a column encryption key and encryption algorithm for each sensitive information column in a table, facilitating subsequent ciphertext data storage.

This feature supports the encrypted equality query function, which is the core of the entire feature. Although users are unaware of the ciphertext query, the query of sensitive data is restricted by the specifications of the current encrypted equality query.

From the overall perspective, this feature is used to store and manage data based on sensitive data protection requirements and implement query tasks based on ciphertext data.

Enhancements

None.

Constraints

• Data is encrypted at the column level, and encryption policies cannot be differentiated by row level.

• Except the RENAME operation, the ALTER TABLE syntax cannot be used to change columns in an encrypted table (including the conversion between encrypted and unencrypted columns). The ADD and DROP operations can be used to add and delete encrypted columns, respectively.

• The CHECK(COLUMN IS NOT NULL) syntax can be used, but most check constraint syntax cannot be set for encrypted columns.

• When support_extended_features is set to off, primary key and unique cannot be used for encrypted columns. When support_extended_features is set to on, only primary key and unique can be used for encrypted columns.

• Different data types cannot be implicitly converted.

• The set operation cannot be performed between ciphertexts of different data types.

• Range partitioning cannot be created for encrypted columns.

• Only the repeat and empty_blob() functions can be used to encrypt columns.

• The current version supports only gsql and JDBC (deployed on a Linux OS) clients. Other clients such as ODBC do not support encrypted equality query.

• Data can only be imported to the encrypted table by running copy from stdin, \copy, or insert into values (…) on the client.

• Copying an encrypted table to a file is not supported.

• The system does not support encrypted queries, such as sorting, range query, and fuzzy query, except equality query.

• The encrypted syntax of stored procedures for some functions is supported. For details about the constraints, see “Encrypted Functions and Stored Procedures” in the Developer Guide.

• Non-encrypted table data cannot be inserted into encrypted table data using the INSERT INTO… SELECT… or MERGE INTO syntax.

• For a request in connection state, the CEK information change on the server can be detected only after the cache update operation is triggered (for example, the user is changed or the encrypted column fails to be decrypted) and the connection is re-established.

• Encrypted equality query is not supported on columns encrypted using the random encryption algorithm.

• An error is reported if the two attribute conditions used for comparison in the encrypted equality query use different data encryption keys.

• Encrypted equality query is not supported in time series tables and foreign tables. The ustore storage engine is not supported.

• If the database service configuration (such as the pg_settings system catalog, permission, key, and encrypted column) is changed, you need to re-establish a JDBC connection to make the configuration take effect.

• Multiple SQL statements cannot be executed at the same time. This constraint does not apply to the scenario where the INSERT INTO statement is executed in multiple batches.

• Encrypted equality query supports the following data types:

  Category	Type	Description
  Integer types	tinyint/tinyint(n)	Tiny integer, which is the same as int1.
  	smallint	Small integer, which is the same as int2.
  	int4	Common integer.
  	binary_integer	Oracle compatibility type. Generally, the value is an integer.
  	bigint/bigint(n)	Big integer, which is the same as int8.
  Numeric data types	numeric(p,s)	A number with the precision p.
  	number	Oracle compatibility type, which is the same as numeric(p,s).
  Floating point types	float4	Single-precision floating point.
  	float8	Double-precision floating point.
  	double precision	Double-precision floating point.
  Character data types	char/char(n)	Fixed-length character string. If the length is insufficient, add spaces. The default precision is 1.
  	varchar(n)	Variable-length character string, where n indicates the maximum number of bytes.
  	text	Text type.
  	varchar2(n)	Oracle compatibility type, which is the same as varchar(n).
  	clob	Character large object.
  Binary data types	bytea	Variable-length binary string.
  	blob	Binary large object.

Dependencies

None.