Should data encrypted in a database have a MAC?

Question

I need to store sensitive data in a particular column in my database (Postgres). I'm hosting with AWS, so I'm planning on using KMS to use envelope encryption. For example:

1. To encrypt a secret:

   1. Generate a 256-bit plaintext data key.
   2. Generate a 256-bit random IV.
   3. Encrypt the secret with AES-256-CBC using the plaintext data key, and the IV.
   4. Using KMS, encrypt the data key.
   5. Throw away the plaintext data key.
   6. Encode the IV, the ciphertext secret, and the ciphertext data key in the column.

2. To decrypt a payload from the column:

   1. Decode the IV, ciphertext secret, and the ciphertext data key from the column.
   2. Using KMS, decrypt the ciphertext data key.
   3. Decrypt the ciphertext secret with AES-256-CBC using the plaintext data key, and the IV.

With this in place, should I encrypt-then-MAC the message? I found this answer about using a MAC with a HSM, but I'm not sure if it still applies in the case of data stored in a database:

For checking data integrity, isn't this already covered by our database? For checking authenticity, we are the only ones who consume the ciphertext. For example, we do not exchange the ciphertext with a client where it could be modified.

Is applying a MAC to the encrypted data unnecessary overhead?

Answer 1

Ideally yes, encrypted data in a database should be authenticated as well, along with the context in which the encrypted data appears as associated data—some subset of plaintext values like (not exhaustive):

• A database instance identifier;
• The database, schema, table and column names in which the encrypted data is stored;
• The column names and values of unencrypted columns in the same row as the encrypted value.

The reason is that this protects against a privileged user—say, a malicious DBA—from cutting and pasting values around to change the interpretation that a client application will give to the data. E.g., Eve the malicious DBA could take encrypted credit card numbers in the database and swap them in order to cause the system to charge the wrong person for things.

If you don't authenticate the values, or even if you just authenticate them minimally, with no associated data, you expose yourself to such attacks. This is also true if you use too little associated data—for example, if you just use the row primary key as associated data, a malicious DBA might still be able to carry out the same attack by swapping everything else other than the ciphertext and primary key.

---

There are big downsides here, however, which is that the more thoroughly you authenticate the ciphertexts and their contexts, the harder you make it for honest users to work with the database unless you hand them the master key as well—which is of course very undesirable. For example:

1. Suppose you include all the unencrypted columns in the same row as associated data for the ciphertext's authentication tag. Well, now any non-identity update to the row's plaintext columns will cause the decryption to fail unless it also decrypts, reencrypts and authenticates the encrypted columns anew. For insert-only databases this might not be a big deal, but if your application is heavy on updating existing rows then it can easily be a deal-breaker.
2. A very common operation in relational database management is to create custom views (logical or physical) that present a subset of the data from one or many tables to some user or application. If some such views present them with encrypted data that they may want to decrypt, using associated data with the authentication can make things harder or impossible:
   • If table names are used as associated data, the decryptors must keep track of which column in the view came from which base table in order to decrypt it. This defeats the data abstraction that views are supposed to afford their users.
   • If the views available to some user or application allow them to see a ciphertext column but not all of the columns that provide its associated data, the user won't be able to reliably decrypt the ciphertext.

So these are very serious obstacles that make extensive use of associated data a tricky proposition for databases.

Answer 2

Without HMAC an attacker could change your data and you wouldn't know it. For example, the attacker could swap the content of your encrypted column with that from another record - under your scheme the fake data will be perfectly valid. By adding another HMAC column that factors the record id (which I assume is unique) you will be able to tell if anyone fiddled with your data. If, however, you are not concerned of any adversary fiddling with your data then this will obviously be redundant.