Proxy Commands

Now, let's explore how to issue commands to the database proxy. These commands are divided into two categories: proxy commands and options commands. Both types are designed to resemble SQL syntax closely and can utilize function wrappers in the following format:

ANTIMATTER('<ANTIMATTER COMMAND>');

You can also call an Antimatter command stand-alone, but wrapping the command in an ANTIMATTER function call offers a significant advantage: it allows you to include Antimatter proxy commands in scripts without causing any breaking changes. The query remains valid SQL, whether it's executed directly upstream or through the Antimatter proxy. When the proxy receives a query that includes a call to the ANTIMATTER function, it captures and processes it internally. However, if this same query is sent directly to an upstream database, a custom ANTIMATTER function on the upstream database could be invoked instead.

This flexibility lets you define custom actions if the query referencing this function is issued directly to the upstream database. For example, if you want the ANTIMATTER function call to be ignored, you could add the following function to the upstream database:

-- Returns nothing
CREATE OR REPLACE FUNCTION ANTIMATTER(VARIADIC args TEXT[]) RETURNS void AS $$
BEGIN
END;
$$ LANGUAGE plpgsql;

Alternatively, you might want to ensure that calls to the ANTIMATTER function are only handled by the Antimatter database proxy and should fail if executed directly on the upstream database (to catch a misconfiguration). To achieve this, you could define the upstream ANTIMATTER function as follows:

-- Raises an appropriate exception
CREATE OR REPLACE FUNCTION ANTIMATTER(VARIADIC args TEXT[]) RETURNS void AS $$
BEGIN
RAISE EXCEPTION 'Error: Not connected to an Antimatter database proxy.';
END;
$$ LANGUAGE plpgsql;

This last example is safe to use because the call to the ANTIMATTER function is caught and handled by the proxy without being propagated upstream. However, if the query containing the ANTIMATTER function call is mistakenly sent directly to the upstream database, the defined function would trigger and raise an exception.

ENCRYPT - Table onboarding

The command used for onboarding a table and its specified columns for encryption is ENCRYPT. To use this command, follow the format detailed below:

ALTER TABLE [<schema_name>.]<table_name> ADD ENCRYPTION COLUMNS
  <column_name> DECRYPT ERROR RAISES <error_text> [REPLACE ORIGINAL <value>] 
  [, <column_name> DECRYPT ERROR RAISES <error_text> [REPLACE ORIGINAL <value>]]...;

Where:

table_name is the relation name that contains the columns to onboard. Only one relation can be onboarded at a time.
schema_name is the schema name for the relation that contains the columns to onboard.
column_name is the column to onboard.
error_text is the message returned in the exception raised by a failed Antimatter cryptographic operation.
value is the replacement value to use in the plaintext column once it has been encrypted. Omitting this clause will see NULL, or type equivalent, instead. The value provided must be the same type as specified by the column's relation schema.

For example:

ALTER TABLE salaries ADD ENCRYPTION COLUMNS
  salary DECRYPT ERROR RAISES "redacted salary" REPLACE ORIGINAL 0,
  to_date DECRYPT ERROR RAISES "redacted to_date";

or:

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"');

caution

Error text, column name and all other string parameters must use double quotes as they are part of a single quoted string.

Successful use of this command will result in the addition of a new column to the upstream database for each column onboarded by the ENCRYPT command. These new columns are structured as follows:

antimatter_<column_name>

All future ciphertext generated by Antimatter's database proxy is stored in each respective antimatter_<column_name> column.

It's important to note that successfully executing this command does not automatically encrypt existing plaintext data. The original data remains unaffected and can still be found in the original columns that were onboarded.

DROP ENCRYPT - Table offboarding

The command used for offboarding a table and its specified columns from encryption is DROP ENCRYPTION. To use this command, follow the structure outlined below:

ALTER TABLE [<schema_name>.]<table_name> DROP ENCRYPTION
  COLUMNS <column_name> [, <column_name>]...

Where:

table_name is the relation name that contains the columns to offboard. Only one relation can be offboarded at a time.
schema_name is the schema name for the relation that contains the columns to offboard.
column_name is the column to offboard.

For example:

ALTER TABLE salaries DROP ENCRYPTION COLUMNS salary, to_date;

or:

SELECT ANTIMATTER('ALTER TABLE salaries DROP ENCRYPTION COLUMNS salary, to_date');

Successful completion of this command will result in the removal of the antimatter_<column_name> column from the targeted table, and all Antimatter-stored metadata related to the column will be deleted.

info

This command will only succeed if there are no encrypted records in the antimatter_<column_name> column and if ciphertext generation for the target column is disabled. These conditions are explained in more detail in their respective sections.

ENABLE/DISABLE - Ciphertext generation

Enabling and disabling Antimatter ciphertext generation is done using the ENABLE and DISABLE commands respectively. To use these commands, follow the structure outlined below:

ALTER TABLE [<schema_name>.]<table_name> (ENABLE | DISABLE) ENCRYPTION
  COLUMNS <column_name> [, <column_name>]...');

Where:

table_name is the relation name that contains the columns to enable/disable ciphertext generation on.
schema_name is the schema name for the relation that contains the columns to enable/disable ciphertext generation on.
column_name is the column to enable/disable ciphertext generation on.

For example:

ALTER TABLE salaries DISABLE ENCRYPTION COLUMNS salary, to_date;

or (Note that this is an ENABLE command):

SELECT ANTIMATTER('ALTER TABLE salaries ENABLE ENCRYPTION 
  COLUMNS salary, to_date');

Disabling ciphertext generation for a column will result in all newly inserted or updated data for that column being stored in plaintext within the plaintext column. Despite disabling ciphertext generation, decryption of existing ciphertext for the column in the relation remains active.

ENCRYPT - Data migration

The migrate command is used to bulk encrypt plaintext data in an onboarded table. To use this command, follow the structure outlined below:

UPDATE TABLE [<schema_name>.]<table_name>
  ENCRYPT <column_name> [, <column_name>]... [<filter>];

Where:

table_name is the relation name that contains the columns to migrate.
schema_name is the schema name for the relation that contains the columns to migrate.
column_name is the column to migrate.
filter is a SQL from_item onward expression that can be used to filter rows to encrypt. The migrated table is automatically added to the target list of the filter.

Successful execution of this command will populate the <column_name>'s ciphertext column (antimatter_<column_name>, as generated by ENCRYPT) with ciphertext produced by Antimatter's cryptographic services for all targeted rows. Additionally, you will notice that the <column_name> plaintext column will now contain the <value> specified in the onboarding ENCRYPT command.

For example:

UPDATE TABLE salaries ENCRYPT salary, to_date
  FROM employees WHERE employees.emp_no = salaries.emp_no
  AND first_name = "Itzchak" AND last_name = "Lichtner";

or:

SELECT ANTIMATTER('UPDATE TABLE salaries ENCRYPT salary, to_date
  FROM employees WHERE employees.emp_no = salaries.emp_no
  AND first_name = "Itzchak" AND last_name = "Lichtner"');

info

Please note that data migration can only be performed within a transaction block.

REVERT MIGRATE - Revert data migration

To revert encrypted data back to its unencrypted state in the database, use:

UPDATE TABLE [<schema_name>.]<table_name>
  DECRYPT <column_name> [, <column_name>]... [<filter>];

Where:

table_name is the relation name that contains the columns to revert to plaintext.
schema_name is the schema name for the relation that contains the columns to unmigrate.
column_name is the column to unmigrate.
filter is SQL from_item onward expressions that can be used to filter rows to decrypt. The unmigrate table is automatically added to the target list of the filter.

For example:

UPDATE TABLE salaries DECRYPT salary, to_date
  FROM employees WHERE employees.emp_no = salaries.emp_no
  AND first_name = "Itzchak" AND last_name = "Lichtner";

or:

SELECT ANTIMATTER('UPDATE TABLE salaries DECRYPT salary, to_date
  FROM employees WHERE employees.emp_no = salaries.emp_no
  AND first_name = "Itzchak" AND last_name = "Lichtner"');

If successful, this command will revert the changes made by the prior ENCRYPT command to the upstream database for all targeted rows. Additionally, the antimatter_<column_name> column will be populated with null values for all affected rows.

info

NOTE: Data migration can only be performed within a transaction block.

Options Commands

By default, all queries sent to the proxy are treated as non-encrypted and are forwarded directly to the upstream Postgres server. This opt-in behavior ensures that existing operations remain unaffected by the Antimatter proxy. To mark a query as one involving Antimatter encryption, you need to supply the proxy with the necessary arguments to support cryptographic operations. Currently, this includes:

Peer Nickname: The nickname of the peered tenant under which the encryption and decryption should occur.

There are three mechanisms available for supplying these parameters to the proxy, depending on the desired level of isolation. The supported isolation levels are:

Session level
Transaction level
Statement level

Session/Transaction Isolation

For session and transaction level isolation, the Antimatter options are supplied using a SET command that must be executed within the given session or transaction. If used in a transaction, all subsequent commands for the remainder of that transaction are assumed to involve Antimatter encryption and will be evaluated by the proxy. It’s important to note that in this environment, it is safe to execute queries that do not involve Antimatter encryption. The proxy will only intercept and rewrite queries that refer to columns marked for encryption.

The format of the SET command is a comma-separated list of key=value pairs, and is structured as follows:

SET antimatter.opts = 'nickname=<string>'

This command sets a reserved variable, antimatter.opts, to the supplied string value. When the proxy receives this command, it processes the string and extracts the Antimatter parameters. It's important to note that when this command is submitted via the proxy, the message is fully consumed and not forwarded to the upstream database. As a result, a subsequent SHOW antimatter.opts will return nothing.

The string component of the SET command is parsed into a series of key/value pairs, which must include nickname at a minimum. After this, any number of key/value pairs can be supplied, as they will be treated as labels. An example of this command would be:

antimatter.opts='nickname=customer1, product=default, service=default, contract/type=personal, region/timezone=GMT'

In this example, the peer nickname is set to customer1, the product and service are both set to default, and two labels are supplied: contract/type=personal and region/timezone=GMT.

Statement Isolation

For statement level isolation, the antimatter arguments can be supplied using a Common Table Expression (CTE) using the following format:

WITH antimatter_ops AS (SELECT ANTIMATTER('<peer nickname>')) ... 

Similar to the SET command described above, the Antimatter argument is supplied as a string. In the case of a CTE, this is achieved through a variadic function, ANTIMATTER. When a query containing a CTE using ANTIMATTER is submitted to the proxy, the CTE is extracted, and its contents are used to attempt to rewrite the associated CTEs and the query. As with session and transaction encryption, it is safe to include a query that does not involve Antimatter encryption. Even with the Antimatter arguments supplied, the query will only be rewritten if it or the CTEs involve encrypted columns.

An example of a simple query using this approach would be:

WITH antimatter_ops AS (SELECT ANTIMATTER('customer1'))
		SELECT salaries.salary
		FROM salaries
		WHERE tenant_id = $1 AND emp_no = $2

caution

The antimatter_ops CTE does not currently support parameterized arguments.

Helper Functions

Helper functions are available when connected to an Antimatter proxy for queries in an Antimatter encrypted context. Helper functions provide additional functionality when working with Antimatter-encrypted data. These functions are available whenever you're connected to an Antimatter proxy and executing queries in an encrypted context. They allow you to perform various checks and operations related to encryption, ensuring that your queries interact correctly with encrypted data. By using these helper functions, you can easily determine the encryption status of specific columns, manage encryption settings, and perform other essential tasks while maintaining the integrity and security of your data.

ANTIMATTER ENCRYPTED - Is the column encrypted?

Marking a column for encryption does not guarantee that all records in the table have encrypted data in that column. This could happen if a table with existing records was onboarded (onboarding alone does not encrypt existing records), or if encryption was disabled (see ANTIMATTER DISABLE) for certain modify or write operations.

To check whether a specific column in a record has been encrypted, use the antimatter_encrypted function. This function accepts a single column as an argument and returns a boolean value indicating whether that column in a given record has been encrypted. Follow the structure below to use this function:

ANTIMATTER_ENCRYPTED(<COLUMN_NAME>)

Where column_name specifies the name of the column you want to check for encrypted data.

For example:

WITH antimatter_ops AS (SELECT ANTIMATTER('customer1'))
SELECT first_name, last_name FROM salaries WHERE antimatter_encrypted(salary);

info

Note that we use statement isolation in the example query to enter into an antimatter encrypted context.

This helper function can also be used within the filter clauses of MIGRATE or UNMIGRATE commands. For example, if you want to perform a migration only on records where the salary column is unencrypted, you can use the following approach:

BEGIN;
SET antimatter.opts='external_id=customer1,product=default,service=default';
SELECT ANTIMATTER('MIGRATE salaries ENCRYPT salary WHERE customer_id = "customer1" AND antimatter_encrypted(\"salary\") = false');
COMMIT;

info

In the example query, transaction isolation is used to enter an Antimatter encrypted context.

info

In this MIGRATE example, we escape the double quotes to ensure they are preserved.

ENCRYPT - Table onboarding​

DROP ENCRYPT - Table offboarding​

ENABLE/DISABLE - Ciphertext generation​

ENCRYPT - Data migration​

REVERT MIGRATE - Revert data migration​

Options Commands​

Session/Transaction Isolation​

Statement Isolation​

Helper Functions

ANTIMATTER ENCRYPTED - Is the column encrypted?​

ENCRYPT - Table onboarding

DROP ENCRYPT - Table offboarding

ENABLE/DISABLE - Ciphertext generation

ENCRYPT - Data migration

REVERT MIGRATE - Revert data migration

Options Commands

Session/Transaction Isolation

Statement Isolation

ANTIMATTER ENCRYPTED - Is the column encrypted?