Examples

The examples below can be used as a guide for how to onboard tables and migrate data based on the following SQL schema.

info

These examples assume that the domain ID used in your Antimatter proxy configuration has a correctly configured peer with the nickname 'customer1'.

CREATE TYPE gender AS ENUM ('M','F');

CREATE TABLE employees (
                           emp_no int NOT NULL,
                           birth_date date DEFAULT NULL,
                           first_name varchar(14) DEFAULT NULL,
                           last_name varchar(16) DEFAULT NULL,
                           gender gender NOT NULL,
                           hire_date date NOT NULL,
                           tenant_id int DEFAULT NULL,
                           PRIMARY KEY (emp_no)
);

CREATE TABLE salaries (
                          emp_no int NOT NULL,
                          salary int NOT NULL,
                          from_date date NOT NULL,
                          to_date date DEFAULT NULL,
                          tenant_id int DEFAULT NULL,
                          PRIMARY KEY (emp_no,from_date),
                          CONSTRAINT salaries_ibfk_1 FOREIGN KEY (emp_no) REFERENCES employees (emp_no) ON DELETE CASCADE
);

ENCRYPT - Table onboarding

1. Onboard the single column last_name in the employees table. If a cryptographic operation fails, raise an exception with the text 'redacted'. Replace the plaintext with NULL when the ciphertext is generated.

SELECT ANTIMATTER('ALTER TABLE employees ADD ENCRYPTION COLUMNS last_name DECRYPT ERROR RAISES "redacted"');

2. Onboard the single column last_name in the employees relation. If a cryptographic operation fails, raise an exception with the text 'redacted' Replace the plaintext with 'placeholder' when ciphertext is generated.

SELECT ANTIMATTER('ALTER TABLE employees ADD ENCRYPTION COLUMNS 
last_name DECRYPT ERROR RAISES "redacted" REPLACE ORIGINAL "placeholder"');

3. Onboard multiple columns salary and to_date in relation salaries. If a cryptographic operation fails, raise the unique exceptions 'redacted salary' and 'redacted to_date', respectively. Replace plaintext in salary with the integer 0 and use NULL for to_date's plaintext when ciphertext is generated.

SELECT ANTIMATTER('ALTER TABLE salaries ADD ENCRYPTION COLUMNS
salary DECRYPT ERROR RAISES "redacted salary" REPLACE ORIGINAL 0,
to_date DECRYPT ERROR RAISES "redacted to_date"');

The expected result upon successful operation would be:

INSERT 0 1

MIGRATE

Keep in mind that MIGRATE commands must occur within a transaction.

1. Encrypt all data in the last_name column of the onboarded employees table, using the compartment information specified in the SET operation.

BEGIN;
SET antimatter.opts='nickname=customer1';
SELECT ANTIMATTER('UPDATE TABLE employees ENCRYPT last_name');
COMMIT;

2. Encrypt the salary and to_date columns in the onboarded salaries table, applying a filter to affect only the records for the employee Itzchak Lichtner.

BEGIN;
SET antimatter.opts='nickname=customer1';
SELECT ANTIMATTER('UPDATE TABLEE salaries ENCRYPT salary, to_date
  FROM employees WHERE employees.emp_no = salaries.emp_no
  AND first_name = "Itzchak" AND last_name = "Lichtner"');
COMMIT;

The expected result upon successful completion would be:

BEGIN
SET
UPDATE <rows_touched>
COMMIT