Setting Encrypted Equality Query

Overview

As enterprise data is migrated to the cloud, data security and privacy protection are facing increasingly severe challenges. The encrypted database will solve the privacy protection issues in the entire data lifecycle, covering network transmission, data storage, and data running status. Furthermore, the encrypted database can implement data privacy permission separation in a cloud scenario, that is, separate data owners from data administrators in terms of the read permission. The encrypted equality query is used to solve equality query issues of ciphertext data. The encrypted equality query supports the client tool GSQL and JDBC. The following describes how to use the client tool to perform operations related to encrypted equality query.

Using GSQL to Operate an Encrypted Database

Log in as the OS user omm to the primary node of the database.
When you use gsql to connect to database server, you need to use "-C" parameter or "--enable-client-encryption" to enable the encryption function. Run the following command to enable the encryption function and connect to the encrypted database:
```
gsql -p PORT postgres -r -C
```

Create a CMK and a CEK. For details about the syntax for creating a CMK and CEK, see CREATE CLIENT MASTER KEY and CREATE COLUMN ENCRYPTION KEY.

-- Create a CMK.
mogdb=# CREATE CLIENT MASTER KEY ImgCMK1 WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value1", ALGORITHM = RSA_2048);
mogdb=# CREATE CLIENT MASTER KEY ImgCMK WITH (KEY_STORE = localkms, KEY_PATH = "key_path_value2", ALGORITHM = RSA_2048);
mogdb=# CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE COLUMN ENCRYPTION KEY
mogdb=# CREATE COLUMN ENCRYPTION KEY ImgCEK WITH VALUES (CLIENT_MASTER_KEY = ImgCMK, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);
CREATE COLUMN ENCRYPTION KEY

The query result of the system catalog that stores the key information is as follows.

mogdb=# SELECT * FROM gs_client_global_keys;
 global_key_name | key_namespace | key_owner | key_acl |        create_date
-----------------+---------------+-----------+---------+----------------------------
 imgcmk1         |          2200 |        10 |         | 2021-04-21 11:04:00.656617
 imgcmk          |          2200 |        10 |         | 2021-04-21 11:04:05.389746
(2 rows)
mogdb=# SELECT column_key_name,column_key_distributed_id ,global_key_id,key_owner  FROM gs_column_keys;
 column_key_name | column_key_distributed_id | global_key_id | key_owner
-----------------+---------------------------+---------------+-----------
 imgcek1         |                 760411027 |         16392 |        10
 imgcek          |                3618369306 |         16398 |        10
(2 rows)

Create an encrypted table.

mogdb=# CREATE TABLE creditcard_info (id_number    int, name         text encrypted with (column_encryption_key = ImgCEK, encryption_type = DETERMINISTIC),
credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id_number' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

Query the detailed information about the table. If the value of Modifiers is encrypted, the column is encrypted.

mogdb=# \d creditcard_info
        Table "public.creditcard_info"
   Column    |       Type        | Modifiers
-------------+-------------------+------------
 id_number   | integer           |
 name        | text              |  encrypted
 credit_card | character varying |  encrypted

Insert data into the encrypted table and perform an equality query.

mogdb=# INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');
INSERT 0 1
mogdb=# INSERT INTO creditcard_info VALUES (2, 'joy','6219985678349800033');
INSERT 0 1
mogdb=# select * from creditcard_info where name = 'joe';
 id_number | name |     credit_card
-----------+------+---------------------
         1 | joe  | 6217986500001288393
(1 row)
-- Note: The data in the encrypted table is displayed in ciphertext when you use a non-encrypted client to view the data.
mogdb=# select id_number,name from creditcard_info;
 id_number |                                                                         name
-----------+------------------------------------------------------------------------------------------------------------------------------------------------------
         1 | \x011aefabd754ded0a536a96664790622487c4d366d313aecd5839e410a46d29cba96a60e4831000000ee79056a114c9a6c041bb552b78052e912a8b730609142074c63791abebd0d38
         2 | \x011aefabd76853108eb406c0f90e7c773b71648fa6e2b8028cf634b49aec65b4fcfb376f3531000000f7471c8686682de215d09aa87113f6fb03884be2031ef4dd967afc6f7901646b
(2 rows)

(Optional) Alter and update the encrypted table if necessary.

mogdb=# ALTER TABLE creditcard_info ADD COLUMN age int ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ImgCEK, ENCRYPTION_TYPE = DETERMINISTIC);
ALTER TABLE
mogdb=# \d creditcard_info
        Table "public.creditcard_info"
   Column    |       Type        | Modifiers
-------------+-------------------+------------
 id_number   | integer           |
 name        | text              |  encrypted
 credit_card | character varying |  encrypted
 age         | integer           |  encrypted
mogdb=# ALTER TABLE creditcard_info DROP COLUMN age;
ALTER TABLE
mogdb=# update creditcard_info set credit_card = '80000000011111111' where name = 'joy';
UPDATE 1
mogdb=# select * from creditcard_info  where name = 'joy';
 id_number | name |    credit_card
-----------+------+-------------------
         2 | joy  | 80000000011111111
(1 row)

Using JDBC to Operate an Encrypted Database

Note: Currently only linux is supported.

Connecting to an Encrypted Database

For details about the JDBC connection parameters, see Development Based on JDBC. To support JDBC operations on an encrypted database, set enable_ce to 1. The following is an example:

public static Connection getConnect(String username, String passwd)
    {
        // Set the driver class.
        String driver = "org.postgresql.Driver";
        // Set the database connection descriptor.
        String sourceURL = "jdbc:postgresql://10.10.0.13:8000/postgres?enable_ce=1";
        Connection conn = null;

        try
        {
            // Load the driver.
            Class.forName(driver);
        }
        catch( Exception e )
        {
            e.printStackTrace();
            return null;
        }

        try
        {
             // Establish a connection.
            conn = DriverManager.getConnection(sourceURL, username, passwd);
            System.out.println("Connection succeed!");
        }
        catch(Exception e)
        {
            e.printStackTrace();
            return null;
        }

        return conn;
    };

NOTE:

[Proposal] When JDBC is used to perform operations on an encrypted database, one database connection object corresponds to one thread. Otherwise, conflicts may occur due to thread changes.

[Proposal] When JDBC is used to perform operations on an encrypted database, different connections change the encrypted configuration data. The client invokes the IsValid method to ensure that the connections can hold the changed encrypted configuration data. In this case, the refreshClientEncryption parameter must be set to 1 (default value). In a scenario where a single client performs operations on encrypted data, the refreshClientEncryption parameter can be set to 0.

Example of Calling the IsValid Method to Refresh the Cache

// Create a CMK.
Connection conn1 = DriverManager.getConnection("url","user","password");

// Connection 1 calls the IsValid method to refresh the cache.
try {
 if (!conn1.isValid(60)) {
  System.out.println("isValid Failed for connection 1");
 }
} catch (SQLException e) {
 e.printStackTrace();
        return null;
}

Creating Keys for Executing Encrypted Equality Query

// Create a CMK.
Connection conn = DriverManager.getConnection("url","user","password");
Statement stmt = conn.createStatement();
int rc = stmt.executeUpdate("CREATE CLIENT MASTER KEY ImgCMK1 WITH ( KEY_STORE = localkms, KEY_PATH = \"key_path_value\" , ALGORITHM = RSA_2048);

NOTE: Before creating a CMK, you need to use the gs_ktool tool to generate a key.

// Create a CEK.
int rc2 = stmt.executeUpdate("CREATE COLUMN ENCRYPTION KEY ImgCEK1 WITH VALUES (CLIENT_MASTER_KEY = ImgCMK1, ALGORITHM  = AEAD_AES_256_CBC_HMAC_SHA256);");

Creating an Encrypted Table for Executing Encrypted Equality Query

int rc3 = stmt.executeUpdate("CREATE TABLE creditcard_info (id_number    int, name  varchar(50) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC),credit_card  varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC));");
// Insert data.
int rc4 = stmt.executeUpdate("INSERT INTO creditcard_info VALUES (1,'joe','6217986500001288393');");
// Query the encrypted table.
ResultSet rs = null;
rs = stmt.executeQuery("select * from creditcard_info where name = 'joe';");
// Close the statement object.
stmt.close();

Precompiling the Encrypted Table

// Create a prepared statement object by calling the prepareStatement method in Connection.
PreparedStatement pstmt = con.prepareStatement("INSERT INTO creditcard_info VALUES (?, ?, ?);");
// Set parameters by triggering the setShort method in PreparedStatement.
pstmt.setInt(1, 2);
pstmt.setString(2, "joy");
pstmt.setString(3, "6219985678349800033");
// Execute the precompiled SQL statement by triggering the executeUpdate method in PreparedStatement.
int rowcount = pstmt.executeUpdate();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();

Batch Processing on Encrypted Tables

// Create a prepared statement object by calling the prepareStatement method in Connection.
Connection conn = DriverManager.getConnection("url","user","password");
PreparedStatement pstmt = conn.prepareStatement("INSERT INTO batch_table (id, name, address) VALUES (?,?,?)");
// Call the setShort method for each piece of data, and call addBatch to confirm that the setting is complete.
int loopCount = 20;
 for (int i = 1; i < loopCount + 1; ++i) {
      statemnet.setInt(1, i);
      statemnet.setString(2, "Name " + i);
      statemnet.setString(3, "Address " + i);
      // Add row to the batch.
      statemnet.addBatch();
}
// Execute batch processing by calling the executeBatch method in PreparedStatement.
int[] rowcount = pstmt.executeBatch();
// Close the precompiled statement object by calling the close method in PreparedStatement.
pstmt.close();

Encrypted Functions and Stored Procedures

In the current version, only encrypted functions and stored procedures in SQL or PL/pgSQL are supported. Because users are unaware of the creation and execution of functions or stored procedures in an encrypted stored procedure, the syntax has no difference from that of non-encrypted functions and stored procedures.

The gs_encrypted_proc system catalog is added to the function or stored procedure for encrypted equality query to store the returned original data type.

Creating and Executing a Function or Stored Procedure that Involves Encrypted Columns

Create a key. For details, see [Using GSQL to Operate an Encrypted Database](#Using GSQL to Operate an Encrypted Database) and [Using JDBC to Operate an Encrypted Database](#Using JDBC to Operate an Encrypted Database).

Create an encrypted table.

mogdb=# CREATE TABLE creditcard_info (
mogdb(#   id_number int,
mogdb(#   name  text,
mogdb(#   credit_card varchar(19) encrypted with (column_encryption_key = ImgCEK1, encryption_type = DETERMINISTIC)
mogdb(# ) with (orientation=row) distribute by hash(id_number);
CREATE TABLE

Insert data.

mogdb=# insert into creditcard_info values(1, 'Avi', '1234567890123456');
INSERT 0 1
mogdb=# insert into creditcard_info values(2, 'Eli', '2345678901234567');
INSERT 0 1

Create a function supporting encrypted equality query.

mogdb=# CREATE FUNCTION f_encrypt_in_sql(val1 text, val2 varchar(19)) RETURNS text AS 'SELECT name from creditcard_info where name=$1 or credit_card=$2 LIMIT 1' LANGUAGE SQL;
CREATE FUNCTION
mogdb=# CREATE FUNCTION f_encrypt_in_plpgsql (val1 text, val2 varchar(19))
mogdb-# RETURNS text AS $$
mogdb$# DECLARE
mogdb$# c text;
mogdb$# BEGIN
mogdb$#     SELECT into c name from creditcard_info where name=$1 or credit_card =$2 LIMIT 1;
mogdb$#     RETURN c;
mogdb$# END; $$
mogdb-# LANGUAGE plpgsql;
CREATE FUNCTION

Execute the function.

mogdb=# SELECT f_encrypt_in_sql('Avi','1234567890123456');
 f_encrypt_in_sql
------------------
 Avi
(1 row)

mogdb=# SELECT f_encrypt_in_plpgsql('Avi', val2=>'1234567890123456');
 f_encrypt_in_plpgsql
----------------------
 Avi
(1 row)

NOTE:

Because the query, that is, the dynamic query statement executed in a function or stored procedure, is compiled during execution, the table name and column name in the function or stored procedure must be known in the creation phase. The input parameter cannot be used as a table name or column name, or any connection mode.

Among the RETURNS, IN, and OUT parameters, encrypted and non-encrypted parameters cannot be used together. Although the parameter types are all original, the actual types are different.

For advanced package interfaces, decryption is not performed on the interfaces whose output is printed on the server. This is because when the encrypted data type is forcibly converted into the plaintext original data type, the default value of the data type is printed.

In the current version, LANGUAGE of functions and stored procedures can only be SQL and PL/pgSQL, and does not support other procedural languages such as C and Java.

Other functions or stored procedures for querying encrypted columns cannot be executed in a function or stored procedure.

In the current version, default values cannot be assigned to variables in DEFAULT or DECLARE statements, and return values in DECLARE statements cannot be decrypted. You can use input parameters and output parameters instead when executing functions.

gs_dump cannot be used to back up functions involving encrypted columns.

Keys cannot be created in functions or stored procedures.

In this version, encrypted functions and stored procedures do not support triggers.

Encrypted equality query functions and stored procedures do not support the escape of the PL/pgSQL syntax. The CREATE FUNCTION AS 'Syntax body' syntax whose syntax body is marked with single quotation marks (") can be replaced with the CREATE FUNCTION AS 'Syntax body' syntax.

The definition of an encrypted column cannot be modified in an encrypted equality query function or stored procedure, including creating an encrypted table and adding an encrypted column. Because the function is executed on the server, the client cannot determine whether the cache needs to be refreshed. The column can be encrypted only after the client is disconnected or the cache of the encrypted column on the client is refreshed.

Issue