Skip to main content

Notes

This section covers critical operational guidelines and best practices for using Antimatter's database proxy. Adhering to these practices is essential for managing customer data securely and effectively while utilizing Antimatter's services.

Reserved Customer IDs​

To aid in development and integration with the Antimatter intercept, two reserved nicknames have been introduced: antimatter_err and antimatter_warn.

  • antimatter_err: If a query is submitted to the Antimatter intercept with the customer ID set to antimatter_err, the intercept will return an error message to the client and discard the query instead of forwarding it for processing.

  • antimatter_warn: If a query is submitted with the customer ID set to antimatter_warn, the intercept will issue a warning to the client but still process the query. The query will be treated as if the supplied nickname does not have encryption enabled.

MIGRATION supports 1 customer at a time​

It's important to note that the MIGRATE and UNMIGRATE commands are designed to be used for a single customer at a time. Therefore, it's crucial to verify that the filter used in these commands ensures that only the specific customer's records are affected. The MIGRATE command will encrypt all matched records using the provided customer's nickname, and improper filtering could result in data loss.

Below is an example of what would be considered a bad filter application to a MIGRATE command:

BEGIN;  -- This is incorrect usage, do NOT run this!
SET antimatter.opts='nickname=customer_id';
SELECT ANTIMATTER('MIGRATE sensitive_table_name ENCRYPT sensitive_column_name WHERE true');
COMMIT;

This command would encrypt all records for every customer in the specified table sensitive_table_name using the keys associated with the nickname customer_id. However, if there is a column named customer_name that stores the record's owner, a more appropriate filter for the MIGRATE command would be:

BEGIN;
SET antimatter.opts='nickname=customer_id';
SELECT ANTIMATTER('MIGRATE sensitive_table_name ENCRYPT sensitive_column_name WHERE customer_name = "target_customer"');
COMMIT;

This targeted filter ensures the migration of records only for the customer referenced by the provided customer nickname, assuming that customer_id correctly corresponds to target_customer.

Limitations

While this implementation aims to support a broad range of SQL functionality, there are inherent limitations due to the proxy's reliance on foreign data wrappers and ongoing feature development.

PostgreSQL Version​

Development is currently focused on PostgreSQL 15.2. Although the system may function with other versions of PostgreSQL, it is not recommended to do so.

Connections​

In the current implementation of the intercept, all connections to databases that have not been marked for encryption are forwarded directly to the upstream server. Consequently, the number of active sessions supported for non-proxied databases is determined solely by the capacity of the upstream service.

For proxied databases, there is a limit of 100 active connections from the intercept itself. Additionally, the number of connections established to the upstream proxied databases will be N+1, where N represents the number of client connections. This additional connection is required because the PostgreSQL Foreign Data Wrapper requires a dedicated connection for itself.

NOTE: Due to how sequences are handled see sequences, each nextval call will create a separate connection to the upstream database to service the request. As a consequence, the number of additional connections between the proxy and upstream may be greater than N+1, depending on the queries being processed.

Upserts​

One implication of the current database proxy implementation is that UPSERT commands, where the conflict clause contains something other than DO NOTHING, are transparently rewritten to an equivalent form. Specifically, all UPSERT commands involving Antimatter-encrypted columns are transformed into the following form:

WITH 
  antimatter_update_expr AS (
      UPDATE ... SET ...
    ),
  antimatter_insert_expr AS (
      INSERT INTO ... 
    )
  SELECT
  FROM antimatter_update_expr UNION SELECT FROM antimatter_insert_expr;

This has the down side that a single UPSERT operation gets converted into 2 CTEs and thus has a theoretical race condition between when the update CTE resolves and the insert CTE resolves. Where possible, it is recommended to use a transaction for UPSERT operations involving Antimatter encryption.

Sequences​

Sequences in the Antimatter proxy are supported, relaying the nextval command to the upstream database, even for the portions of a query executing within the Antimatter proxy. This implementation was chosen for correctness, but may be revised in future to reduce network round trips. There is no change to the functionality or performance of sequences for queries where Antimatter encryption is not enabled or queries that do not involve Antimatter encrypted columns.

Custom types​

While custom user types, defined in the upstream database, are supported by the Antimatter database proxy, the following limitation must be considered. When submitting an UPSERT query that includes a VALUES clause containing a custom user type, it must include a manual cast. This limitation is due to the rewriting of UPSERT operations into an UPDATE and INSERT. When this is done, the database can no longer infer that a value is a custom type and selects a default type to cast it to. Consider the following example where title is a custom user type:

WITH antimatter_ops AS (SELECT ANTIMATTER('customer1'))
  INSERT INTO employees (tenant_id, emp_no, birth_date, first_name, last_name, title, hire_date)
  VALUES (1, $2, '1986-10-10', 'Justin','Credible','Dr','2023-08-17') ON CONFLICT (emp_no) DO UPDATE
  SET first_name = $3;

When executed through the Antimatter proxy, this will result in an error similar to:

ERROR:  column "title" is of type title but expression is of type text at character 100
  HINT:  You will need to rewrite or cast the expression.

To resolve this, we need to case the value to be associated with the column "title" to be of type title:

WITH antimatter_ops AS (SELECT ANTIMATTER('customer1'))
  INSERT INTO employees (tenant_id, emp_no, birth_date, first_name, last_name, title, hire_date)
  VALUES (1, $2, '1986-10-10', 'Justin','Credible','Dr'::title,'2023-08-17') ON CONFLICT (emp_no) DO UPDATE
  SET first_name = $3;

Unsupported operations and features​

There are a few PostgreSQL features that the Antimatter database proxy does not currently support:

  • Encryption in a COPY command.
  • Indexes on encrypted columns
  • Antimatter commands in prepared statements.
  • SSL encryption support between the client and proxy.

These features are currently being worked on and support will ship in upcoming releases.