CipherStash provides transparent encryption and decryption of sensitive data in SQL databases. It works by intercepting SQL queries and encrypting or decrypting the sensitive data on the fly. This reference document outlines the limitations, quirks, and tradeoffs of using CipherStash.
When using the CipherStash driver for PostgreSQL, some SQL features are not supported on columns encrypted by CipherStash.
However, those SQL features will continue to work on plaintext columns that are not encrypted by CipherStash.
When an unsupported feature is used, the CipherStash driver will return an error.
-- Not supported: UPDATE ... SET some_encrypted_col = some_encrypted_col + 1;
It is generally not possible to
JOIN on encrypted columns that may contain the same plaintext:
-- Not supported: SELECT t.id, t.amount, p.name FROM transactions t JOIN payee p ON t.payee_email = p.email ORDER BY t.name;
SQL statements using
JOIN on plaintext columns will run successfully.
NATURAL will error, regardless of whether the
JOIN is on an encrypted or plaintext column.
-- Not supported: SELECT * FROM toy NATURAL JOIN cat; -- Not supported: SELECT post_id, title, review FROM post INNER JOIN post_comment USING(post_id);
-- Not supported: INSERT INTO some_configured_table SELECT * FROM other_table;
Partitioning splits what is logically one large table into smaller physical pieces.
Partitioning on encrypted columns is not supported:
-- Not supported: CREATE TABLE measurement ( city_id int not null, logdate date not null, peaktemp int, unitsales int ) PARTITION BY RANGE (logdate); CREATE TABLE measurement_y2006m02 PARTITION OF measurement FOR VALUES FROM ('2006-02-01') TO ('2006-03-01'); CREATE TABLE measurement_y2006m03 PARTITION OF measurement FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
Partitioning on plaintext columns is supported.
-- Not supported: SELECT id, first_name, last_name FROM employees WHERE department_id IN (SELECT department_id FROM departments WHERE location_ID = 1700);
-- Not supported: CREATE TEMPORARY TABLE table_t (column1 INT);
-- Not supported: SELECT product_name, price, group_name, AVG (price) OVER (PARTITION BY group_name) FROM products INNER JOIN product_groups USING (group_id);
These SQL features are either partially supported, or supported with workarounds.
UPDATE statements with multiple modified rows are partially supported.
Bulk updates with literal values are supported:
-- Supported: UPDATE accounts SET balance = 100 WHERE id IN (1, 2, 3);
UPDATE statements using values lists are not supported:
-- Not supported: UPDATE table SET update_column = temp.value FROM ( VALUES ('foo', 'bar'), ('baz', 'qux'), ('et', 'cetera') ) temp (id, value) WHERE key_column = temp.id;
UPDATE statements (bulk or single record) that set columns to arbitrary expressions are not supported:
-- Not supported: UPDATE account SET balance = balance + 1 WHERE id = 123.
UPDATE statements directly from other tables are not supported:
-- Not supported: UPDATE accounts SET contact_first_name = first_name, contact_last_name = last_name FROM employees WHERE employees.id = accounts.sales_person;
-- Not supported: CREATE TABLE products ( product_no integer, name text, price numeric DEFAULT 9.99 );
This can be worked around with application-level defaults (for example, in your ORM).
This will be supported in future releases.
COUNT() is supported:
-- Supported: SELECT COUNT(id) FROM users WHERE date_of_birth > '1984-01-01';
Other aggregate functions are not supported:
-- Not supported: SELECT department_id, SUM(salary) FROM employees GROUP BY department_id;
Columns managed by CipherStash cannot be used in
ON CONFLICT clauses:
-- Not supported: INSERT INTO table_a (cs_column1, cs_column2) VALUES (value1, value2), (value3, value4), (value5, value6) ON CONFLICT cs_column1 DO NOTHING; -- Not supported: INSERT INTO table_a (cs_column1, cs_column2) VALUES (value1, value2), (value3, value4), (value5, value6) ON CONFLICT cs_column1 DO UPDATE;
Columns not managed by CipherStash can be used in
ON CONFLICT clauses:
-- Supported: INSERT INTO table_a (not_cs_column1, not_cs_column2) VALUES (value1, value2), (value3, value4), (value5, value6) ON CONFLICT not_cs_column1 DO NOTHING; -- Supported: INSERT INTO table_a (not_cs_column1, not_cs_column2) VALUES (value1, value2), (value3, value4), (value5, value6) ON CONFLICT not_cs_column1 DO UPDATE;
CipherStash has the following quirks:
- Match queries against text fields can have false positives. CipherStash uses bloom filters to perform queries against some indexed columns. However, bloom filters can have false positives, which means that a query result may contain records that do not actually match the query. This problem is not unique to CipherStash and is a known tradeoff of using bloom filters. To minimize false positives, index tuning is required.
ILIKEqueries require minor modification. For
ILIKEqueries to continue to work in
encryptedmodes, letter case functions need to be removed:
-- Before: SELECT name FROM user WHERE lower(name) LIKE "%Alice%"; -- After: SELECT name FROM user WHERE name LIKE "%Alice%";
NULLs are not yet represented in an encrypted form. Because
NULLs don’t yet have an encrypted representation, the driver cannot detect if a
NULLin an encrypted source column (
__x_encryptedfor example) reflects the actual plaintext source value. This can lead to data loss during migrations. CipherStash intends to ship support for encrypted
NULLs in Q2 2023.
- Exact queries against citext (case-insensitive text) fields are case-sensitive
Let’s suppose a PostgreSQL table
userswith a field
plaintext-duplicatemode, an email with the value
Sam_Fisher@example.comwould match any equal case-insensitive lookups such as:
In order to support this query in
SELECT email from users where email = 'sam_FISHER@example.com'; SELECT email from users where email = 'firstname.lastname@example.org'; SELECT email from users where email = 'SAM_FISHER@EXAMPLE.COM';
encryptedmodes, CipherStash uses the unique index column. The default behaviour for this index column is case-sensitive, which will cause the above queries to not return the record as expected. In order to preserve the case-insensitive lookup behaviour, make sure that the unique index is defined with the downcase token filter.
This will lowercase the values before encrypting and inserting them into the index column. It will also lowercase any query terms before queries are performed.
tables: - path: users fields: - name: email in_place: false cast_type: utf8-str mode: encrypted-duplicate indexes: - version: 1 kind: unique token_filters: - kind: downcase
CipherStash makes the following tradeoffs:
- The same ciphertext may appear in multiple rows during bulk operations.
When transforming various queries (such as
UPDATE) that do bulk operations, multiple rows can be set with the same encrypted value. This can make it easier for attackers to perform certain types of inference attacks. Ideally, the same plaintext value in multiple rows and fields should have a unique ciphertext. CipherStash intends to ship a release that fixes this by Q3 2023.
- Encrypted left ciphertext. CipherStash stores the encrypted left ciphertext (as defined in the Order Revealing Encryption paper), which can make it easier for attackers to use inference attacks to determine the plaintext. This is a tradeoff that is made to provide better performance and usability.
CipherStash is the easiest and safest way to protect structured sensitive data in your organisation. By understanding its limitations, quirks, and tradeoffs, users can make informed choices about whether CipherStash is the right solution for their applications.