Column encryption

solutions and ideas

Why encrypt?

keep data confidential
maintain data integrity (?)
crypto-shredding
regulations and policies
defense in depth

Database encryption variants

transport encryption (TLS/SSL)
encryption at rest (OS disk or TDE)
client-side encryption
- a.k.a. column encryption/field encryption
…

Vendor terminology varies!

Standards and regulations

PCI DSS 💳
GDPR 🇪🇺
FIPS 🇺🇸
HIPAA ⚕️
…

Client-side column-level encryption

          client                                          DB
+-------------------------+                      +-------------------+
|                         |                      | id| data          |
| INSERT         +--------| (1, '\xd3b07384d1')  | --+-------------  |
| VALUES         | libpq  |--------------------->| 1 | \xd3b07384d1  |
| (1, 'secret')  +--------|                      |   | ...           |
|                         |                      |                   |
+-------------------------+                      +-------------------+

Client-side column-level encryption

keep secure data together with normal data (referential integrity, consistent backups, etc.)
give access only to some clients
copy production data to staging/testing/analytics
prevents accidental leakage (backups, server logs)

Client-side column-level encryption

Pro:

actual security

Con:

quite hard to do

Solution: Proxy

      client                      Proxy                               DB
+----------------+               +-----+                     +-------------------+
|                |               |     |                     | id| data          |
| INSERT         | (1, 'secret') |     | (1, '\xd3b07384d1') | --+-------------  |
| VALUES         |-------------->| ??? |-------------------->| 1 | \xd3b07384d1  |
| (1, 'secret')  |               |     |                     |   | ...           |
|                |               |     |                     |                   |
+----------------+               +-----+                     +-------------------+

Proxy — Pros and cons

Pros:

easy to deploy

Cons:

additional component
third-party software
sometimes details unclear

Solution: Client code only

from somewhere import encrypt

key = ...

conn = somedriver.connect(...)
cur = conn.cursor()
cur.execute("INSERT INTO t1 (name, creditcard) VALUES (%s, %s)",
            ('Μαρία', encrypt(key, '1234')))

cur.execute("SELECT creditcard FROM t1 WHERE name = %s", ('Μαρία',))
row = cur.fetchone()
print(decrypt(key, row[0]))

Client code — Pros and cons

Pros:

can be done
flexible and customizable

Cons:

requires bespoke implementation
client code must be kept in sync with schema
implementer must be very careful!

Must use parameters!


INSERT INTO tbl VALUES (1, 'secret');     -- no, bad!
INSERT INTO tbl VALUES ($1, $2);          -- ok

SELECT * FROM tbl WHERE id = 1;           -- ok, not encrypted
SELECT * FROM tbl WHERE id = $1;          -- ok, not encrypted

Lookup by encrypted fields?


SELECT * FROM tbl WHERE data = 'secret';  -- bad!
SELECT * FROM tbl WHERE data = $2;        -- maybe? (see later)
SELECT * FROM tbl WHERE data > 'sth';     -- probably not
SELECT * FROM tbl WHERE substr(lower(data, ...)) = 'sth';  -- probably not

Solution: pgcrypto (dubious!)

CREATE EXTENSION pgcrypto;

-- bad
INSERT INTO t1 (name, creditcard)
  VALUES ('Μαρία', encrypt($1, 'KEYHERE', 'aes-cbc'));

SELECT decrypt(creditcard, 'KEYHERE', 'aes-cbc')
  FROM t1 WHERE name = 'Μαρία';

-- slightly better?
INSERT INTO t1 (name, creditcard)
  VALUES ('Μαρία', encrypt($1, pg_read_file('keyfile'), 'aes-cbc'));

SELECT decrypt(creditcard, pg_read_file('keyfile'), 'aes-cbc')
  FROM t1 WHERE name = 'Μαρία';

pgcrypto — Pros and cons

Pros:

can be done with standard extensions
requires less custom code
can be more centralized in database

Cons:

not client side encryption (but ok as per-column-TDE?)
(requires changing SQL code in client? (try views))
key management?
antiquated cryptography

Solution: framework/ORM

example Django

from django.db import models

from django_cryptography.fields import encrypt

class MyModel(models.Model):
    name = models.CharField(max_length=50)
    sensitive_data = encrypt(models.CharField(max_length=50))

Framework/ORM — Pros and cons

Pros:

easy to use
possibly mature

Cons:

specific to one product
some dubious solutions (pgcrypto wrappers)

Interlude: Encryption methods

cipher: AES-128, AES-256, ChaCha20, (DES), …
mode: CBC, CTR, GCM, SIV, …
key, IV, nonce, …
randomized, deterministic, homomorphic

Encryption terminology

randomized:

encrypt(key, 'foo') = 'd3b073'
encrypt(key, 'foo') = '978e5b'

deterministic:

encrypt(key, 'foo') = 'd3b073'
encrypt(key, 'foo') = 'd3b073'

homomorphic:

encrypt(key, 'foo') = 'iga'
encrypt(key, 'bar') = 'Ogo'
encrypt(key, 'foobar') = 'igaOgo'

Encryption: padding

-- dubious
encrypt(key, 'none') =               '7e5b152f'
encrypt(key, 'some text') =          'b12ae13f59e1dc69b4'
encrypt(key, 'very long ... text') = '5372b3............66dd'

-- better
encrypt(key, 'none') =               '46366f48e18fe88d1b51affe5c5e5048'
encrypt(key, 'some text') =          '0bf4a9eda093afe048d11eec6edd7988'
encrypt(key, 'very long ... text') = 'bd8dc6c34d2235dfa21f355547b5b99d ....'

revealing length cannot be avoided completely

Encryption modes

mode	standard	padding	nonce reuse res.	integrity	FIPS	OpenSSL	JDK
AES-CBC + HMAC	no	yes	yes	yes	yes	yes	yes
AES-SIV	yes	no	yes	no	no	3.0	no
AES-GCM	yes	no	no	yes	yes	yes	yes
AES-GCM-SIV	yes	no	yes	yes	no	3.2	no
ChaCha20-Poly1305	yes	no	no	yes	no	yes	yes
XChaCha20-Poly1305	no	no	no	yes	no	no	no
XChaCha20-SIV	no	no	yes	yes	no	no	no
your own	no	?	?	yes?	?	?	?

Solution: pgsodium

CREATE EXTENSION pgsodium;

SELECT * FROM pgsodium.create_key();

CREATE TABLE private.users (
  id bigserial PRIMARY KEY,
  secret text
);

SECURITY LABEL FOR pgsodium ON COLUMN private.users.secret
  IS 'ENCRYPT WITH KEY ID dfc44293-fa78-4a1a-9ef9-7e600e63e101';

pgsodium — Pros and cons

Pros:

modern cryptography
deep and wide feature set
mostly transparent to client code

Cons:

third-party server plugin
(single-person project?)
encryption is still server-side

Idea: built-in

CREATE COLUMN MASTER KEY cmk1;

CREATE COLUMN ENCRYPTION KEY cek1
  WITH VALUES (column_master_key = cmk1,
               algorithm = 'RSAES_OAEP_SHA_1',
               encrypted_value = '\x53cd6a6c91...');

CREATE TABLE employees (
  id int PRIMARY KEY,
  name text NOT NULL,
  ...
  ssn text ENCRYPTED WITH (column_encryption_key = cek1,
                           encryption_type = deterministic)
);

Built-in — Pros and cons

Pros:

might make things a lot easier and standardized
no client code changes

Cons:

not done yet
requires updated client drivers
forced encryption needs client code changes
no encoding conversion, no data type enforcement, no text/binary format choice

Key management

          client                                      DB
+-------------------------+                      +----------+
|                +--------| (1, '\xd3b07384d1')  |          |
| (1, 'secret')  | libpq  |--------------------->|          |
|                +--------|                      |          |
+-------------------------+                      +----------+
                     ^
                     |
                     |
                 +--------+
                 |  KMS   |
                 +--------+

Key management

master key rotation (easy)
column key rotation (hard)
different key management server products
…

Other feature ideas

secure enclave
tie encrypted value to primary key

Other caveats

cannot hide existence or absence of data
cannot completely hide length of values
does not protect against “malicious DBA”

Summary

look for mature and general solutions
beware pgcrypto
beware homomorphic encryption
feedback welcome