VESencrypt for Databases

>> INSERT INTO users (id,name,ssn) VALUES (1,'John Smith','123-45-6789');
    >> INSERT INTO users (id,name,ssn) VALUES (1,'John Smith','$ve$@JdwB$hsPt8ptbYgOpXfo$');

>> SELECT * FROM users;
    >> SELECT * FROM users;
    << id | name       | ssn
<< id | name       | ssn
    << 1  | John Smith | $ve$@JdwB$hsPt8ptbYgOpXfo$
<< 1  | John Smith | 123-45-6789

VESencrypt encryption configuration for database proxies identifies the columns that need to be encrypted, and specifies the encryption options.

Encrypting DB Columns

Variable width columns

String or binary columns of variable length - varchar(N), varbinary(N), text, blob, bytea and similar types - can be encrypted, provided that the column has sufficient width to fit the encrypted value, see VESencrypt Entry Size.

Fixed width columns

For columns of fixed width, such as binary(N) or bpchar(N), to be encrypted, the fixPad option must be supplied in the encryption configuration. The value of fixPad is a single char string – the right padding character for the column, i.e. space character for bpchar, \0 for binary etc. The column width considerations are same as for variable width columns.

Non-encryptable columns

Columns of specialized types - numeric, date/time etc - can be encrypted only after converting them to varchar(N) / varbinary(N) etc, with the width sufficient to fit the maximum encrypted entry length.

NULL

The encryption is NULL safe, any NULL values are passed througn encryption / decryption without change.

Indexed columns

A number of additional considerations applies to encrypting columns that are indexible / searchable, i.e. are used in query conditions.

Only deterministic encryption (seed = 0) must be used, otherwise search conditions will fail.
Unlike any other proxy operations, conditions on encrypted columns do not permit mixing encrypted and unencrypted data. All values in an indexed column must be promptly encrypted after saving the encryption settings (bulk encrypt operation). Queries that involve conditions on the indexed column may fail between the moment the encryption settings are saved and the completion of the bulk update.
Ordering and greater/less comparison will work on a deterministically encrypted column upon the bulk update is completed, but will produce a sorting order that is different than the natural sorting order of unencrypted values.
Fulltext search may be considered in conjunction with lead/trail options when the goal is to match first/last N unencrypted characters.

SQL Query Rewriting

When a comparison between an encrypted column and a constant (= | != | <> | < | > | <= | >= | IN | NOT IN) is found anywhere in an SQL query – the constant is encrypted according to the column's encryption settings. If the encryption is non-deterministic and the failLevel >= 12 (see Fail Safety) – the proxy raises an error without forwarding the query to the server.

For UPDATE and INSERT with a column list, the assigned constants are encrypted according to the encryption settings of the target column.

For INSERT without a column list, the proxy issues an addinional reconnaissance query SELECT * FROM table WHERE FALSE to find the column list, then the encryption is applied accordingly to the constant values.

Fail Safety

The failLevel setting in the encryption configuration determines how the proxy will handle unsafe requests. Unsafe requests include the following categories:

Request packets that do not match current protocol specifications;
Certain kinds of request that are not processed by the current proxy module, but are known to be able to write constants into the DB, such as LOAD DATA, COPY FROM etc.
SQL syntax errors, such as unclosed parentheses or quotes;
SQL queries where same constant is assigned to different columns with different encryption settings, making the encryption ambiguous;
SQL queries that contain comparisons of a non-deterministically encrypted column to a constant;
SQL queries that cannot be parsed by the proxy but contain certain dangerous bare words, such as INSERT or UPDATE.

If failLevel is set to 0 – the proxy passes any unsafe requests to the server with best effort processing, or without any processing at all.

For higher failLevel values some unsafe requests will trigger of error from the proxy, without being passed to the server, to prevent unintentional saving of unencrypted data. For failLevel = 15, the proxy will reject all identified unsafe requests with an error.

Encryption Configuration

The encryption configuration is a JSON object supplied through the Profile Manager.

The failLevel property specifies the fail safety level, an integer 0 to 15.

The columns property provides an array of Column Encryption Configuration objects.

The Column Encryption Configuration has the following properties:

table: String, required: DB table name
column: String, required: DB column name
database: String, default null: Database/schema name. If not specified - match any
schema: String, default null: Schema name. If not specified - match any. If only one of database and schema is supplied, they are treated identically. Specifying both makes sense for protocols that support both database and schema dimension, such as Postgres.
encrypt: Bool, default: true: true: Encrypt the column value on INSERT / UPDATE, decrypt the result value. On data retrieval, if the VESencrypt Entry framing is detected but the decryption fails - throw an error to the client.; false: Pass unencrypted column value on INSERT / UPDATE, decrypt the result value. If the VESencrypt Entry framing is detected but the decryption fails - do best effort decryption and return a possibly corrupted result.
seed: Int, default: 4: Add N bytes of random seed, 0 produces deterministic encryption.
pad: Int, default: 0: Add a random length padding of up to N bytes to conceal the plaintext length.
bin: Bool, default: false: Store the ciphertext as binary, instead of Base64. If the DB column does not support binary values, this option may cause data corruption.
mac: Bool, default: false: Add a Message Authorization Core to the ciphertext to validate the integrity of the entry.
lead: Int, default: 0: Keep N leading characters of the value unencrypted. Useful for preview, can be used with fulltext search.
trail: Int, default: 0: Keep N trailing characters of the value unencrypted. Useful for preview, can be used with fulltext search.
fixPad: String, default: null: The right padding character for a column with fixed padding – space for char / bpchar, \0 for padded binary.

VESencrypt Entry Size

See VESencrypt Entry Specifications for the format details.

The encrypted entry is at least 7 bytes longer that the plaintext, because of 6 bytes of framing and the flags byte.
The MAC flag ("mac": true) adds 16 binary bytes.
The PAD flag ("pad": N) randomly adds 1 to N + 1 binary bytes.
Base64 encoding (BIN flag off; "bin": false) adds the proportional 33% Base64 overhead (no "=" padding) on top of the binary ciphertext length.
The seed ("seed": N) adds N bytes flat, not subject to Base64 encoding.
The KEY flag ("key": N) adds 1 byte flat, not subject to Base64 encoding.