Syntax

Encrypting columns in a new table

CREATE TABLE <table name>  (
     <column_name> <data_type> ENCRYPT,
     <column_name> <data_type> NULL ENCRYPT,
             <column_name> <data_type> NOT NULL ENCRYPT
             );

Adding an encrypted column to an existing table

ALTER TABLE <table_name> ADD COLUMN <column_name> <data_type> ENCRYPT;

Encryption methods

ENCRYPT ( <column name to encrypt> , <Secret Key of exactly 256-bit (32-byte) length> )

Decryption method

DECRYPT ( <column name to decrypt> , <Secret Key of exactly 256-bit (32-byte) length> )

Examples

Encrypting a new table

CREATE TABLE client_name  (
     id BIGINT NOT NULL ENCRYPT,
     first_name TEXT ENCRYPT,
     last_name TEXT,
     salary INT ENCRYPT);

Inserting encrypt player salary (INT data type)

INSERT INTO NBA (player_name, team_name, jersey_number, position, age, height, weight, college, salary)
VALUES ('Jayson Christopher Tatum', 'Boston Celtics', 0, 'SF', 25, '6-8', 210 , 'Duke', ENCRYPT ( 32600060 , '6a8431f6e9c2777ee356c0b8aa3c12c0c63bdf366ac3342c4c9184b51697b47f'));

Similar example using COPY FROM

COPY NBA
(
player_name, team_name, jersey_number, position, age, height, weight, college,
ENCRYPT (salary, '6a8431f6e9c2777ee356c0b8aa3c12c0c63bdf366ac3342c4c9184b51697b47f')
)
FROM WRAPPER csv_fdw
OPTIONS
(location = '/tmp/source_file.csv', quote='@');

Query the encrypted data

SELECT player_name, DECRYPT( salary, '6a8431f6e9c2777ee356c0b8aa3c12c0c63bdf366ac3342c4c9184b51697b47f') FROM NBA
WHERE player_name ='Jayson Christopher Tatum';

player_name             |salary    |
------------------------+----------+
Jayson Christopher Tatum|1500000   |

Query the encrypted data using WHERE clause on an encrypted column

SELECT player_name, DECRYPT( salary, '6a8431f6e9c2777ee356c0b8aa3c12c0c63bdf366ac3342c4c9184b51697b47f')
FROM NBA
WHERE DECRYPT( salary, '6a8431f6e9c2777ee356c0b8aa3c12c0c63bdf366ac3342c4c9184b51697b47f') > 1000000;

player_name             |salary    |
------------------------+----------+
Jayson Christopher Tatum|1500000   |
------------------------+----------+
Marcus Smart            |1350000   |

Example of COPY TO using DECRYPT

COPY
  (SELECT player_name, DECRYPT( salary, '6a8431f6e9c2777ee356c0b8aa3c12c0c63bdf366ac3342c4c9184b51697b47f')
  FROM NBA
  WHERE player_name ='Jayson Christopher Tatum')
TO WRAPPER parquet_fdw
OPTIONS (LOCATION = '/tmp/file.parquet');

Limitations

The following functionality is not supported by the encryption feature: Catalog queries, Utility commands, Foreign Tables, Create AS SELECT.
A single encryption key must be used per column - using a different key would result in an error.
Compression of encrypted columns is limited to the following types: Flat, LZ4, PD4, DICT, RLE.
This feature is not backward compatible with previous versions of SQreamDB.
The encryption feature affect performance and compression.

Permissions

The Data Encryption feature does not require a specific permission, users with relevant TABLE and COLUMN permissions may utilize it.